Project ref: kcsbwuptmqjkscpprkmy
Summary: A plain LANGUAGE sql STABLE function (public.match_kb_chunks(vector, integer) — single overload, SECURITY INVOKER, owner postgres, proconfig NULL) returns 0 rows. In the same transaction, same impersonated role (authenticated) and same request.jwt.claims: (a) the function's WHERE clause executed directly returns 3 rows; (b) the function body inlined as a raw SELECT returns 3 rows; (c) a newly created LANGUAGE plpgsql function with identical semantics (kb_search_chunks) returns 3 rows. Dropping and recreating the sql function (fresh pg_proc OID) plus notify pgrst, 'reload schema' changed nothing. Reproduces both through PostgREST rpc and directly in the SQL editor.
Catalog state: single pg_proc row; prosecdef = false; owner postgres; proconfig NULL. Table RLS enabled, not forced, owner postgres. pgvector 0.8.2 in extensions schema.
Questions: (1) Any pooler/prepared-statement/plan-cache mechanism that could cause this? (2) Any known pgvector operator resolution issue inside LANGUAGE sql functions? (3) Can anyone check server-side plans/logs for these calls?
Ellie Nartey reports an issue where a LANGUAGE sql function returns 0 rows, while an identical inlined body and a plpgsql function return 3 rows. This occurs under the same transaction and role. The issue persists despite dropping and recreating the function. Ellie seeks insights into potential causes related to pooler, prepared-statement, or plan-cache mechanisms, and pgvector operator resolution issues.