Unresolvable drift on CHECK constraints referencing functions/types in same schema

[andyc-valstro]

Summary

pgschema plan reports unresolvable and insignificant diffs on CHECK constraints that reference functions or custom types defined in the same schema being managed. The plan perpetually wants to drop and re-create these constraints, even immediately after a successful pgschema apply.

Reproduction

# 1. Create a schema with a function used in a CHECK constraint
psql -c "
CREATE SCHEMA test_schema;
SET search_path TO test_schema, public;
CREATE FUNCTION validate_foo(x int) RETURNS boolean LANGUAGE sql IMMUTABLE AS 'SELECT x > 0';
CREATE TABLE bar (id int PRIMARY KEY, val int, CONSTRAINT val_positive CHECK (validate_foo(val)));
"

# 2. Dump the schema
pgschema dump --schema test_schema --file test_schema.sql ...

# 3. Plan against the same DB (expect no changes)
pgschema plan --schema test_schema --file test_schema.sql ...
# EXPECTED: "No changes detected."

# ACTUAL: "1 to modify"
Plan: 1 to modify.

Summary by type:
  tables: 1 to modify

Tables:
  ~ bar
    - val_positive (constraint)
    + val_positive (constraint)

DDL to be executed:
--------------------------------------------------

ALTER TABLE bar DROP CONSTRAINT val_positive;

ALTER TABLE test_schema.bar
ADD CONSTRAINT val_positive CHECK (validate_foo(val)) NOT VALID;

ALTER TABLE test_schema.bar VALIDATE CONSTRAINT val_positive;

Root Cause

When comparing desired vs current state, pgschema normalizes constraint expressions inconsistently:

Desired state (loaded into temp plan schema):

• Constraint is created in temp schema → PostgreSQL stores function ref qualified to temp schema
• pgschema strips temp schema prefix → result: validate_foo(val) (unqualified)

Current state (read from live DB):

• pg_get_constraintdef() renders with schema qualification based on the active search_path
• If search_path does not include the target schema, the function is rendered as test_schema.validate_foo(val)
• pgschema does NOT strip the target schema prefix → result: test_schema.validate_foo(val) (qualified)

Comparison: validate_foo(val) ≠ test_schema.validate_foo(val) → spurious diff.

Fix Options

1. Set search_path to include the target schema before querying pg_get_constraintdef() (and similar catalog rendering functions) for the current state:

SET search_path TO <target_schema>, public;
SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE ...;

This makes PostgreSQL render function/type references without schema qualification when they're in the target schema — matching how the desired state is normalized.

2. Alternatively, normalize both sides by stripping the managed schema name from constraint expressions before comparison.

Affected Constraint Types

• CHECK constraints calling functions: CHECK (my_func(col))
• CHECK constraints with type casts to custom types: CHECK (col <> 'val'::my_enum)

Impact

• Every plan reports changes → noise in CI/CD
• Every apply unnecessarily drops/re-creates constraints (uses NOT VALID + VALIDATE pattern)
• VALIDATE CONSTRAINT triggers full table scan on every deploy
• Brief enforcement gap between DROP and re-ADD

Environment

• pgschema version: 1.9.0
• PostgreSQL: 15.6
• Affected schemas: any schema with CHECK constraints referencing same-schema functions or types

Workaround

None currently. The drift is cosmetic and idempotent (safe to apply repeatedly), but cannot be eliminated