v1.9.0: plan fails with "operator does not exist" when function body contains qualified references to the target schema

[mdca]

Summary

In v1.9.0, pgschema plan strips the target schema prefix from top-level constructs (declarations, GRANT/REVOKE, COMMENT targets) but not from qualified references inside function bodies. When a function in schema X takes a parameter of type X.enum_type and the body reads from X.table where a column is of the same enum type, the temp-schema staging produces an operator does not exist error because the parameter has been rewritten to reference the temporary schema's type while the body still references the real schema's type.

This appears to be a follow-on from #376 / #379 — the temp-schema leak fix may have been too conservative about rewriting function bodies, introducing a type-identity mismatch in the opposite direction.

Version

pgschema 1.9.0@e5673daf linux/amd64 2026-04-08 (linux-amd64 binary from the GitHub release, run inside debian:bookworm-slim)

Reproduced against PostgreSQL 17.6 on two independent targets:

• Local Supabase stack (supabase/cli managed Postgres 17).
• A throwaway plain postgres:17 Docker container with no Supabase involvement.

Minimal reproduction

Setup

Target Postgres must have the schema pre-created with the type and table (they exist in the real DB and pgschema diffs against them):

DROP SCHEMA IF EXISTS pgschema_bug_repro CASCADE;
CREATE SCHEMA pgschema_bug_repro;
CREATE TYPE pgschema_bug_repro.role_type AS ENUM ('OWNER', 'MEMBER');
CREATE TABLE pgschema_bug_repro.role_caps (
  role pgschema_bug_repro.role_type NOT NULL,
  capability text NOT NULL,
  PRIMARY KEY (role, capability)
);

Desired state (repro.sql)

CREATE TYPE pgschema_bug_repro.role_type AS ENUM ('OWNER', 'MEMBER');

CREATE TABLE pgschema_bug_repro.role_caps (
    role pgschema_bug_repro.role_type NOT NULL,
    capability text NOT NULL,
    PRIMARY KEY (role, capability)
);

CREATE OR REPLACE FUNCTION pgschema_bug_repro.role_has_cap(
    p_role pgschema_bug_repro.role_type,
    p_cap text
) RETURNS boolean
LANGUAGE sql
STABLE
AS $$
    SELECT EXISTS (
        SELECT 1
        FROM pgschema_bug_repro.role_caps rc
        WHERE rc.role = p_role
          AND rc.capability = p_cap
    );
$$;

Command

pgschema plan \
  --host <host> --port <port> --db <db> --user <user> \
  --plan-host <host> --plan-port <port> --plan-db <db> --plan-user <user> \
  --schema pgschema_bug_repro --file repro.sql

--plan-host / --plan-db / --plan-user point pgschema at the same target database for staging, bypassing the embedded Postgres planner. This form is the most reliable reproduction — in one of the test environments pgschema's embedded planner failed earlier in startup because it could not reach Maven Central, so the external-plan-DB form is what I recommend for verification.

Expected

Plan succeeds and shows the function being added.

Actual

Error: failed to apply desired state: failed to apply schema SQL to temporary schema pgschema_tmp_20260410_200825_ff6f2835: ERROR: operator does not exist: pgschema_bug_repro.role_type = role_type (SQLSTATE 42883)

Root cause (from --debug output)

pgschema rewrites repro.sql before applying it to the temp schema. The rewritten SQL (apply desired state SQL to temporary schema debug log entry) is:

CREATE TYPE role_type AS ENUM ('OWNER', 'MEMBER');

CREATE TABLE role_caps (
    role role_type NOT NULL,
    capability text NOT NULL,
    PRIMARY KEY (role, capability)
);

CREATE OR REPLACE FUNCTION role_has_cap(
    p_role role_type,
    p_cap text
) RETURNS boolean
LANGUAGE sql
STABLE
AS $$
    SELECT EXISTS (
        SELECT 1
        FROM pgschema_bug_repro.role_caps rc    -- ← schema prefix NOT stripped
        WHERE rc.role = p_role
          AND rc.capability = p_cap
    );
$$;

Observations:

• CREATE TYPE pgschema_bug_repro.role_type → CREATE TYPE role_type (stripped, now in temp schema)
• CREATE TABLE pgschema_bug_repro.role_caps → CREATE TABLE role_caps (stripped, now in temp schema)
• Function signature p_role pgschema_bug_repro.role_type → p_role role_type (stripped; references temp-schema type)
• Function body FROM pgschema_bug_repro.role_caps rc → unchanged (references the real schema's table, whose role column is the real schema's role_type)

At function-body parse time (with check_function_bodies = on, which is the Postgres default), Postgres resolves rc.role to the real pgschema_bug_repro.role_type but p_role to the temp-schema role_type. These are distinct types with no implicit cast, so the equality operator lookup fails.

Impact

Any function that:

1. Lives in schema X,
2. Takes a parameter or has a column of type X.some_enum (or any user-defined type), and
3. Has a body that SELECT ... FROM X.some_table WHERE col = param where col is of that same type

will fail to plan. In real codebases that use qualified schema references consistently (a common Postgres style for multi-schema projects), this can affect many functions.

Suggested fix

The rewriter should either:

• Strip schema-prefix references inside function bodies consistently with the rest of the statement (same behavior as 1.6.2 but without the downstream GRANT/REVOKE leak that pgschema leaks temporary desired-state schema into GRANT/REVOKE ON FUNCTION for user-defined argument types #376 fixed), or
• Create the temp staging schema with a predictable name equal to the target schema (applied to a throwaway plan-DB), so no rewriting is needed and body references resolve to the same temp schema's objects.

Happy to test any candidate fix against this reproduction or against a larger real-world schema if useful.