v1.10.0: repeat plan drift for same-schema policy and CHECK expressions after apply

[mdca]

Summary

In v1.10.0, pgschema apply succeeds for a schema with RLS policies and CHECK constraints that reference same-schema objects, but a repeat pgschema plan immediately reports changes again.

This is not the old hard planning failure from #399. The apply succeeds. The problem is that pgschema cannot converge after apply because its desired expression form and PostgreSQL's stored expression form are compared differently.

Version

Version: 1.10.0@592c19c9 linux/amd64 2026-05-30 05:52:01

Tested against:

PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 13.2.0, 64-bit

The target database was a disposable local Supabase/Postgres scratch database.

Minimal reproduction

Bootstrap

The target database has a pre-created auth.uid() stub because the policy references it:

CREATE SCHEMA auth;
CREATE SCHEMA profiles;

CREATE FUNCTION auth.uid() RETURNS uuid
LANGUAGE sql
STABLE
AS $$
  SELECT '00000000-0000-0000-0000-000000000001'::uuid;
$$;

Desired state

Run pgschema with --schema profiles --file repeat-plan-drift-repro.sql:

CREATE TYPE profiles.profile_status AS ENUM ('active', 'disabled');

CREATE TABLE profiles.profiles (
    id uuid PRIMARY KEY,
    user_id uuid NOT NULL,
    status profiles.profile_status NOT NULL DEFAULT 'active'::profiles.profile_status,
    CONSTRAINT profiles_status_check CHECK (status <> 'disabled'::profiles.profile_status)
);

CREATE TABLE profiles.profile_media (
    id uuid PRIMARY KEY,
    profile_id uuid NOT NULL REFERENCES profiles.profiles (id)
);

ALTER TABLE profiles.profile_media ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can read their own media" ON profiles.profile_media
    FOR SELECT
    USING (profile_id IN (
        SELECT id
        FROM profiles.profiles
        WHERE user_id = auth.uid()
    ));

Commands

pgschema apply \
  --host <host> --port <port> --db pgschema_trial_drift_repro --user postgres \
  --password postgres --sslmode disable \
  --schema profiles \
  --file repeat-plan-drift-repro.sql \
  --plan-host <host> --plan-port <port> --plan-db pgschema_trial_drift_repro \
  --plan-user postgres --plan-password postgres --plan-sslmode disable \
  --auto-approve

pgschema plan \
  --host <host> --port <port> --db pgschema_trial_drift_repro --user postgres \
  --password postgres --sslmode disable \
  --schema profiles \
  --file repeat-plan-drift-repro.sql \
  --plan-host <host> --plan-port <port> --plan-db pgschema_trial_drift_repro \
  --plan-user postgres --plan-password postgres --plan-sslmode disable

Expected

The first apply succeeds. The repeat plan immediately after apply should report:

No changes detected.

Actual

The first apply succeeds.

The repeat plan reports:

Plan: 2 to modify.

Summary by type:
  tables: 2 to modify

Tables:
  ~ profile_media
    ~ Users can read their own media (policy)
  ~ profiles
    - profiles_status_check (constraint)
    + profiles_status_check (constraint)

The repeat plan DDL is:

ALTER POLICY "Users can read their own media" ON profile_media USING (profile_id IN ( SELECT profiles.id FROM profiles WHERE (profiles.user_id = auth.uid())));

ALTER TABLE profiles DROP CONSTRAINT profiles_status_check;

ALTER TABLE profiles.profiles
ADD CONSTRAINT profiles_status_check CHECK (status <> 'disabled'::profile_status) NOT VALID;

ALTER TABLE profiles.profiles VALIDATE CONSTRAINT profiles_status_check;

PostgreSQL stores the policy expression as:

(profile_id IN ( SELECT profiles.id
   FROM profiles.profiles
  WHERE (profiles.user_id = auth.uid())))

The desired source used:

SELECT id
FROM profiles.profiles
WHERE user_id = auth.uid()

So after apply, pgschema still wants to rewrite a policy expression that PostgreSQL already accepted and stored in an equivalent normalized form.

The same repeat plan also wants to drop and recreate the CHECK constraint, changing the cast from schema-qualified:

'disabled'::profiles.profile_status

to unqualified:

'disabled'::profile_status

Impact

This blocks using pgschema as a clean migration planner in CI/CD:

• A clean database cannot reach No changes detected after apply.
• Every deployment plan would include repeat policy changes and constraint drop/recreate statements.
• The constraint drift is especially risky because repeated apply uses DROP CONSTRAINT, ADD ... NOT VALID, and VALIDATE CONSTRAINT.
• In a real schema graph, this creates enough noise that legitimate changes are hard to review.

Related issues

• v1.9.0: plan fails with "operator does not exist" when function body contains qualified references to the target schema #399 is related because it also involved schema-qualified references inside expressions, but it was a hard planning failure in function bodies. This issue is different: apply succeeds, but the repeat plan is not clean.
• Policy expressions show perpetual diff due to schema qualification mismatch #220 looks closely related for policy expression perpetual diffs, but it was reported against v1.5.1 and is closed.
• Unresolvable drift on CHECK constraints referencing functions/types in same schema #445 looks closely related for same-schema CHECK constraints involving functions/types, but this v1.10.0 repro still shows same-schema enum cast drift after successful apply.

Suggested direction

The comparison step likely needs to treat target-schema-qualified and unqualified references as equivalent inside policy and constraint expressions when they resolve to the same object in the managed schema.

Two possible approaches:

• Normalize both desired and current expressions by stripping the managed schema qualifier where it points to the target schema.
• Or make PostgreSQL catalog rendering use a search path that includes the managed schema before comparing expressions, so the current side is rendered closer to the desired side.