Transient error invoking PGSQL user defined functions from Node backend using 'pg' library.
Has anyone else experienced transient errors invoking a stored function in neon from an external source (pg library in node). Neon throws a 42883 error claiming the function does not exist for a period of several minutes and then returns to normal - with the function executing normally. It doesn't seem to be a parameter type issue as all params are cast to the appropriate types. Here is a sample of the invoking code and of the function definition in Neon - any help is GREATLY appreciated , I am scratching my head.
Invoking code:
result = await client.query(
matches AS ( SELECT a.id , a.name, a.name_state, a.state, a.county, a.geometry, similarity(a.name_state, search) AS similarity , a.lat , a.lon FROM public.us_geocoding_public a where similarity(a.name_state, search) >= 0.2 order by similarity desc LIMIT 300 ) SELECT matches.id , matches.name::text, matches.state::text, matches.county::text, matches.similarity::float , ST_Distance(location , matches.geometry)::float as distance , matches.lat::float , matches.lon::float FROM matches order by similarity desc , distance asc limit 5; END; $$ LANGUAGE plpgsql;
select geomatch( $1::text , $2::float, $3::float); , [userInput , userLat , userLon]);
function definition:
CREATE OR REPLACE FUNCTION GeoMatch(search text, longitude double precision, latitude double precision)
RETURNS TABLE (id integer , name TEXT, state TEXT, county TEXT, similarity FLOAT , distance FLOAT , lat FLOAT , lon FLOAT)
AS $$
declare
location geometry = st_SetSRID(ST_Point(longitude , latitude),4326);
BEGIN
RETURN QUERY
with
matches AS ( SELECT a.id , a.name, a.name_state, a.state, a.county, a.geometry, similarity(a.name_state, search) AS similarity , a.lat , a.lon FROM public.us_geocoding_public a where similarity(a.name_state, search) >= 0.2 order by similarity desc LIMIT 300 ) SELECT matches.id , matches.name::text, matches.state::text, matches.county::text, matches.similarity::float , ST_Distance(location , matches.geometry)::float as distance , matches.lat::float , matches.lon::float FROM matches order by similarity desc , distance asc limit 5; END; $$ LANGUAGE plpgsql;
0 Replies