SQL extractor silently drops CREATE FUNCTION with PL/pgSQL-only bodies (PERFORM / := still dropped after #1910)

[briantanseng]

Summary

The SQL extractor still silently drops CREATE FUNCTION definitions whose PL/pgSQL body contains PL/pgSQL-only statements. On a real (private) PostgreSQL schema this loses 57 of 186 functions (31%) from a single file, with no warning and exit code 0 — so a consumer cannot tell the graph is incomplete.

This looks like an incomplete fix for #1910 ("ERROR nodes on PostgreSQL/PLpgSQL functions", closed by 49df466, shipped 0.9.17). That fix's closing note says the extractor now recovers names from ERROR nodes for "PL/pgSQL bodies with OUT params, tagged dollar-quotes, PERFORM/:= parse as ERROR". PERFORM and := still drop in 0.9.26 — see the matrix below.

Version: graphify 0.9.26 (graphify update <path> --no-cluster), macOS (darwin 25.5.0), Node 24.16.

Minimal reproduction

Two files, identical except for one statement in the body. Both are valid PostgreSQL, both RETURNS "void".

migrations/20260101000001_dropped.sql:

CREATE OR REPLACE FUNCTION "public"."probe_no_return"("p_id" "uuid") RETURNS "void"
    LANGUAGE "plpgsql" SECURITY DEFINER
    SET "search_path" TO 'public'
    AS $$
BEGIN
  RAISE EXCEPTION 'insufficient_privilege' USING ERRCODE = '42501';
END;
$$;

migrations/20260101000002_extracted.sql:

CREATE OR REPLACE FUNCTION "public"."probe_with_return"("p_id" "uuid") RETURNS "void"
    LANGUAGE "plpgsql" SECURITY DEFINER
    SET "search_path" TO 'public'
    AS $$
BEGIN
  RETURN;
END;
$$;

Then:

$ graphify update . --no-cluster
[graphify watch] Rebuilt (no clustering): 3 nodes, 1 edges

Expected: 4 nodes (2 file nodes + 2 function nodes).
Actual: 3 nodes — only "public"."probe_with_return"() is present. probe_no_return is absent, with no diagnostic.

Which body statements decide it

Same 7-line function each time, RETURNS "void", varying only the single body statement:

Body statement	Result
RETURN;	extracted
INSERT INTO public.t (a) VALUES (1);	extracted
UPDATE public.t SET a = 1;	extracted
NULL;	dropped
RAISE EXCEPTION 'boom';	dropped
RAISE NOTICE 'hi';	dropped
PERFORM public.other_fn();	dropped
x := 1;	dropped
IF true THEN RAISE EXCEPTION 'x'; END IF;	dropped

Bodies parseable as plain SQL survive; bodies using PL/pgSQL-only constructs are dropped. PERFORM and := are the two cases #1910's fix specifically claims to handle.

Impact at scale

Measured on one generated schema file: ~35,000 lines, 186 CREATE OR REPLACE FUNCTION declarations, single schema, no overloads.

• 186 declared → 129 extracted → 57 silently missing (30.6%).
• Of the 57 missing, 44 have bodies using PL/pgSQL-only constructs (RAISE/PERFORM/:=/IF…THEN/DECLARE/LOOP/bare NULL;).
• The predicate is not clean at that scale, though: 76 functions that also use those constructs extract fine, and 13 of the missing use none of them. So the single-statement repro above is deterministic, but full-file behaviour likely also depends on the statement mix and where tree-sitter's error recovery resyncs. I did not narrow that further.

Ruled out as the discriminator (each checked across all 186 declarations): position in file / truncation, dollar-quote tag ($$ vs $_$), RETURNS type, SET search_path shape, argument count, identifier length, body length, gap to the next declaration, redefinition in a later file, node-id collisions, and quote-escaping ('') inside a trailing COMMENT ON FUNCTION.

Why this matters more than the coverage number

The failure is silent. Exit code is 0, no ERROR node is surfaced, and the file node is created normally — so the graph looks healthy while a third of the schema's callable surface is missing.

That makes a negative result untrustworthy: "graphify has no node for function X" cannot be distinguished from "function X does not exist". We have had to tell our agents that a hit is trustworthy but a miss is not proof of absence, and to fall back to grep/pg_proc for any database-object question — which removes much of the graph's value for the SQL layer specifically.

Same shape of complaint as #1689 (R files silently dropped with zero warning).

Suggested resolution (in preference order)

1. Extend the ERROR nodes on PostgreSQL/PLpgSQL functions #1910 ERROR-node name recovery to cover these bodies — at minimum RAISE, PERFORM, :=, IF…THEN, and bare NULL;, which are ordinary PL/pgSQL rather than edge cases.
2. Failing that, make the drop loud: emit a per-file warning with a count and the offsets it could not recover (e.g. extracted N of M CREATE FUNCTION statements in <file>). A visible partial result is far more useful than a silent one, and lets CI gate on it.
3. Longer term, the tree-sitter-postgres grammar suggested in ERROR nodes on PostgreSQL/PLpgSQL functions #1910 addresses the class rather than the symptom.

Happy to test a patch against the 186-function schema — it is a good stress case, and I can report the before/after extraction count.

[safishamsi]

Fixed in v0.9.27 (#2188, thanks @Souptik96). SQL routines with unparseable PL/pgSQL bodies are recovered by a raw-text scan, gated on a failed parse so a clean file can't fabricate routines from commented-out DDL. https://github.com/Graphify-Labs/graphify/releases/tag/v0.9.27