LANGUAGE sql function returns 0 rows; identical inlined body and identical plpgsql function both return rows

[8bvhpm7t7f-hash]

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?

[VectorPeak]

The screenshots narrow this down pretty well: the second one shows the same session seeing visible_chunks = 3 for authenticated with the expected jwt_sub, while the SQL RPC wrapper returns function_rows = 0. The catalog screenshot also rules out the common “wrong overload / SECURITY DEFINER / SET search_path” explanation: there’s one match_kb_chunks(vector,integer), security_definer = false, owner postgres, and set_clauses = NULL.

This does not look like a PostgREST pooler or prepared-statement cache issue. Since it reproduces directly in the SQL editor in the same transaction after set local role authenticated, PostgREST and the pooler are mostly out of the path. notify pgrst, 'reload schema' would only affect PostgREST’s schema cache anyway; it would not change behavior for a direct SQL call.

The thing I’d check hardest is SQL-function name binding, not pgvector. In LANGUAGE sql functions, unqualified argument names can lose to column names inside the function body. That can make a function that looks semantically identical behave differently from the manually inlined query, especially if the function has args like embedding, match_count, query_embedding, source_document_id, etc. A plpgsql version may avoid the same binding path, which matches what you’re seeing.

Try recreating the SQL function using positional args only, fully qualified table aliases, and an explicit search path/operator schema if needed:

create or replace function public.match_kb_chunks(
 query_embedding extensions.vector,
 match_count integer
)
returns table (
 -- your return columns here
)
language sql
stable
security invoker
set search_path = public, extensions
as $$
 select
 -- columns here
 from public.knowledge_base_documents kbd
 where kbd.source_document_id is not null
 and kbd.embedding is not null
 -- use $1 and $2, not argument names
 -- example:
 -- and (kbd.embedding <=> $1) < some_threshold
 order by kbd.embedding <=> $1
 limit $2
$$;

If that fixes it, the root cause was almost certainly identifier resolution inside the SQL function body. If it does not, the next useful comparison is:

explain (analyze, verbose, settings)
select *
from public.match_kb_chunks(
 (
 select ('[' || string_agg('0.01', ',') || ']')::extensions.vector(1536)
 from generate_series(1, 1536)
 ),
 8
);

and the same EXPLAIN for the raw inlined SELECT. The VERBOSE output should show whether the function body is being inlined, which filters are actually applied, and whether the vector expression is the same expression you think it is.

---

If my answer solved your problem, you can click answered the question. I'm really here to help, and along the way I'm also collecting Galaxy Brain badges haha 😆

[yakubka]

The behavior you are seeing is a known PostgreSQL quirk with STABLE sql-language functions under RLS.

When PostgreSQL plans a STABLE LANGUAGE sql function it may inline the body into the calling query and re-evaluate security context at that point. With SECURITY INVOKER and RLS enabled, the planner can see a different row-level security check depending on whether the function body is inlined or executed as a standalone call. This is why the raw SELECT, the plpgsql wrapper, and the inlined form all return 3 rows while the sql function returns 0: the planner inlines the sql function body and the RLS policy evaluation changes in that context.

The most reliable fix is to mark the function SECURITY DEFINER and add it to a non-public search_path:

CREATE OR REPLACE FUNCTION public.match_kb_chunks(query_embedding vector, match_count integer)
RETURNS SETOF your_table
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
SELECT * FROM your_table
ORDER BY embedding <=> query_embedding
LIMIT match_count;
$$;

This prevents the planner from inlining across the security boundary and the function runs with the owner role, bypassing RLS for the match itself. If you want RLS to still apply, keep SECURITY INVOKER and switch to LANGUAGE plpgsql, which PostgreSQL never inlines.

[harrysun2006]

The detail that it also reproduces in the SQL editor (not just PostgREST) is important — that rules out the pooler/prepared-statement/plan-cache angle from your question (1) and points at the function boundary itself.

The usual culprit when a LANGUAGE sql function behaves differently from the same body inlined and from a plpgsql copy is search_path + operator resolution. With proconfig NULL, your sql function inherits the caller's search_path, but a simple LANGUAGE sql function is a candidate for inlining, and the operator/opclass resolution for pgvector's <=>/<-> gets pinned relative to the schema search path at that point. If extensions isn't consistently on the path the moment the planner resolves the operator inside the function, you can silently get a different distance operator (or a different plan) than the inlined SELECT you ran by hand — which is exactly the kind of thing that returns 0 rows instead of 3, while your hand-inlined query and the plpgsql version (which is not inlined and re-plans on execute) both work.

Two concrete things to try:

1. Pin the path on the function and see if it changes:

   alter function public.match_kb_chunks(vector, integer) set search_path = public, extensions;

2. Schema-qualify the operator/opclass in the body (e.g. use operator(extensions.<=>) and the explicit vector_cosine_ops opclass) so resolution can't drift.

For diagnosis, EXPLAIN (VERBOSE, ANALYZE) the function call vs. the inlined query and compare — I'd bet one is doing an index scan with the vector opclass and the other isn't. If pinning search_path fixes it, that was it; if not, sticking with the plpgsql version you already have is a perfectly legitimate workaround (it forces a fresh plan and avoids the inlining path entirely).

If you can paste the function body and the WHERE/ORDER BY that uses the vector operator, I can point at the exact line.

[yakubka]

Good point on the operator resolution angle. With proconfig NULL confirmed in the catalog, the alter function set search_path approach is the lowest-risk test before touching the function body. If pinning the path does not change the result, the EXPLAIN VERBOSE comparison between the function call and the inlined SELECT should show the difference immediately. The inlining path is where SECURITY INVOKER combined with RLS can also flip the result, so both angles are worth checking in order.

[harrysun2006]

One thing worth pulling out of the OP's own evidence before leaning on the RLS angle: the hand-inlined SELECT returns 3. If Postgres is auto-inlining the SQL function (the usual suspect), that inlined plan should behave like the manual inline — so "inlining flips the RLS check" doesn't cleanly explain standalone-function = 0 vs manual-inline = 3 for a SECURITY INVOKER function running as the same role in the same transaction. RLS is evaluated as the invoker either way. That points away from RLS and toward what's resolved inside the function boundary — operator/opclass under search_path, or the vector parameter's cast.

So I'd test in this order:

1. alter function public.match_kb_chunks(vector, integer) set search_path = public, extensions; — cheapest; if it flips to 3 rows, done.
2. If still 0: EXPLAIN (VERBOSE, ANALYZE) the function call vs the manual inline and diff them — check which <=> operator/opclass each resolves and whether one uses the hnsw/ivfflat index while the other does a seq scan. Operator drift or a cast difference will show up right there.

And a caution on SECURITY DEFINER: if it "fixes" it, that's really just masking RLS (the function runs as owner and skips the policy), not explaining the 0 — only worth it if you actually intend the match to bypass RLS.