Bug: `plpgsql_show_dependency_tb` misses tables referenced only via `%ROWTYPE` in DECLARE

[racinette]

Summary

plpgsql_show_dependency_tb does not report a table dependency when the table is referenced only via %ROWTYPE in a PL/pgSQL DECLARE block. The dependency is real and dynamic — %ROWTYPE variables track the table's live composite type at runtime — but the function is not surfaced as depending on the table.

Tables referenced via SQL statements (SELECT, INSERT, PERFORM, etc.) in the function body are reported correctly. The gap is specific to %ROWTYPE declarations that are the only reference to a table (no SQL statement in the body touches the table directly).

Impact

Tools that rely on plpgsql_show_dependency_tb for dependency tracking will miss %ROWTYPE-only table dependencies. If the table's structure changes (column dropped), the function breaks at runtime — and plpgsql_check_function_tb does catch the error — but the dependency list gave no indication that the function depended on the table, so a consumer wouldn't know to re-check it.

Reproduction

CREATE EXTENSION IF NOT EXISTS plpgsql_check;

-- Table referenced ONLY via %ROWTYPE (no SQL statement touches it in the function body).
CREATE TABLE public.rto (
    id    integer,
    val   text,
    extra boolean
);

-- Function declares r public.rto%ROWTYPE and uses r.extra in the body,
-- but never executes a SQL statement that references public.rto.
CREATE FUNCTION public.demo_fn() RETURNS text
LANGUAGE plpgsql AS $$
DECLARE
    r public.rto%ROWTYPE;
BEGIN
    -- r.extra is used (not just declared), creating a real structural dependency:
    IF r.extra IS NULL THEN
        r.val := 'default';
    ELSE
        r.val := 'set';
    END IF;
    RETURN r.val;
END;
$$;

-- Expected: public.rto appears as a RELATION dependency.
-- Actual:   empty result — no dependencies reported at all.
SELECT type, schema, name
FROM plpgsql_show_dependency_tb('public.demo_fn()')
ORDER BY name;

-- -- Expected output --
--  type     | schema | name
-- ----------+--------+------
--  RELATION | public | rto

-- -- Actual output --
-- (0 rows)

-- dropping a column used via the %ROWTYPE variable breaks the function.
ALTER TABLE public.rto DROP COLUMN extra;

-- plpgsql_check_function_tb catches the breakage — proving the function
-- depends on the table's structure. But plpgsql_show_dependency_tb above
-- didn't report the dependency, so a consumer wouldn't know to re-check.
SELECT message, level
FROM plpgsql_check_function_tb('public.demo_fn()');

-- -- Output --
--                message               | level
-- -------------------------------------+-------
--  record "r" has no field "extra"     | error

-- Cleanup
DROP FUNCTION public.demo_fn();
DROP TABLE public.rto;

Environment

• plpgsql_check 2.10.1
• PostgreSQL 18 (tested via PGlite WASM build; behavior is not WASM-specific)

Root cause (from source inspection)

plpgsql_show_dependency_tb calls detect_dependency_walker (src/expr_walk.c:43), which walks the planned query tree (Query → rtable → RTE_RELATION entries). %ROWTYPE in DECLARE is resolved by the PL/pgSQL compiler to a composite type OID stored on the PLpgSQL_rec datum — it does not generate a RangeTblEntry in any SQL query plan. Therefore the dependency walker never sees it.

A fix would need to additionally walk the function's datum list (PLpgSQL_function->datums) and, for each PLpgSQL_rec variable whose type is a table's composite type (typrelid != InvalidOid), emit a RELATION dependency for typrelid.

Contrast with %TYPE

%TYPE (in DECLARE or function signatures) is frozen at CREATE time — it resolves to a scalar type OID and does not create a dynamic structural dependency. Missing %TYPE references from the dependency list is acceptable (the type doesn't change at runtime). %ROWTYPE is different — it's dynamic, and the omission is a real gap.

[okbob]

please check e352749

[okbob]

%TYPE is frozen (at create time) only when it is used in argument list. Inside function body cannot be frozen. PL/pgSQL does only validity check at create time - there is not any transformation. Unfortunately the reference is not stored in AST, and then plpgsql_check cannot to report this as dependency.

[racinette]

%TYPE is frozen (at create time) only when it is used in argument list. Inside function body cannot be frozen. PL/pgSQL does only validity check at create time - there is not any transformation. Unfortunately the reference is not stored in AST, and then plpgsql_check cannot to report this as dependency.

Confirmed fixed — thanks for the quick turnaround.

Built at e352749 against PostgreSQL 18.4 (Linux). My original repro now reports the dependency:

   type   | schema | name
----------+--------+------
 RELATION | public | rto

make installcheck passes 6/6. I also spot-checked composite types, views, matviews and partitioned tables, and a plain record variable (correctly reports nothing) — all good.

---

While testing I hit one case that regresses against v2.10.3: foreign tables.

reports_used_rowtypes_dependency() dispatches on relkind and handles c/r/p/v/m, but not f. For an unhandled relkind it emits nothing — yet cstate->rel_oids is still updated at the end of the block (check_function.c:173), so the query walker in expr_walk.c then skips the relation as already-reported. If a function both declares a %ROWTYPE variable of a foreign table and queries that foreign table, the dependency that v2.10.3 reported is now lost.

Repro:

CREATE EXTENSION plpgsql_check;
CREATE EXTENSION file_fdw;
CREATE SERVER filesrv FOREIGN DATA WRAPPER file_fdw;

CREATE TABLE t (a int, b text);
CREATE FOREIGN TABLE ft (a int, b text)
  SERVER filesrv OPTIONS (filename '/dev/null', format 'csv');

-- ordinary table: %ROWTYPE variable + SELECT on the same relation
CREATE FUNCTION f_tbl() RETURNS bigint LANGUAGE plpgsql AS $$
DECLARE r t%ROWTYPE; n bigint;
BEGIN SELECT count(*) INTO n FROM t; RETURN n; END; $$;

-- foreign table: identical shape
CREATE FUNCTION f_ft() RETURNS bigint LANGUAGE plpgsql AS $$
DECLARE r ft%ROWTYPE; n bigint;
BEGIN SELECT count(*) INTO n FROM ft; RETURN n; END; $$;

SELECT type, schema, name FROM plpgsql_show_dependency_tb('f_tbl');
SELECT type, schema, name FROM plpgsql_show_dependency_tb('f_ft');

f4f7a91 (v2.10.3):

-- f_tbl --                      -- f_ft --
   type   | schema | name           type   | schema | name
----------+--------+------       ----------+--------+------
 RELATION | public | t            RELATION | public | ft

e352749:

-- f_tbl --                      -- f_ft --
   type   | schema | name        type | schema | name
----------+--------+------       -----+--------+------
 RELATION | public | t           (0 rows)

Adding the missing relkind is sufficient to fix this:

             else if (relkind == RELKIND_RELATION ||
-                     relkind == RELKIND_PARTITIONED_TABLE)
+                     relkind == RELKIND_PARTITIONED_TABLE ||
+                     relkind == RELKIND_FOREIGN_TABLE)

There is an option of moving the bms_add_member() inside the branch that actually emits, so a relkind that isn't handled degrades to "not reported by this pass" rather than suppressing the query walker as well.

---

One smaller thing, also from the new code: the two paths label the same object differently. A materialized view reached through a query comes back as RELATION, but reached through %ROWTYPE it comes back as VIEW:

same matview mv:
  reached via SELECT     -> RELATION | public | mv
  reached via %ROWTYPE   -> VIEW     | public | mv

---

Also — thanks for the %TYPE correction. I had it wrong in the report. I've since confirmed your point: body-level %TYPE re-resolves on each compile (a fresh session picks up an ALTER TABLE ... ALTER COLUMN ... TYPE, the same session does not), and plpgsql_parse_cwordtype() passes NULL for origtypname with the comment that the type is treated as found-by-OID — so there's genuinely nothing left in the tree to walk. Only the argument-list form is frozen, in pg_proc.proargtypes. The fact that I considered it frozen was only due to me using a single session (pglite is in-memory), so the schema got torn down and I never had the chance to notice. Thanks again.

[okbob]

should be fixed now

[racinette]

Thanks! One follow-up — plpgsql_show_dependency_tb on a trigger function now crashes the backend:

CREATE TABLE trg_target (a int, b text);
CREATE FUNCTION trg_fn() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN NEW.b := 'x'; RETURN NEW; END; $$;
CREATE TRIGGER trg BEFORE INSERT ON trg_target
  FOR EACH ROW EXECUTE FUNCTION trg_fn();

SELECT * FROM plpgsql_show_dependency_tb('trg_fn', 'trg_target');
-- server closed the connection unexpectedly

reports_used_rowtypes_dependency() dereferences rec->datatype, which is NULL for the trigger NEW/OLD records. Fix + regress test in #222.

[okbob]

Thenk you for bug report and PR

[racinette]

Thenk you for bug report and PR

You're welcome!

Thank you for the software! I'm building a project with its help: https://github.com/racinette/pgsid

I'm also integrating your project into pglite distribution:

electric-sql/pglite#1057
electric-sql/postgres-pglite#80

So I think it's fair to say that more people will be using the project soon!