Bug: unresolvable plan drift for IMMUTABLE plpgsql functions returning a user-defined type #329
Description
Summary
pgschema plan perpetually reports a "modify function" diff for any plpgsql function that is IMMUTABLE and returns a user-defined type (e.g. a custom enum). Re-running plan or apply never resolves it — the identical diff appears on every run.
The secondary consequence: if the function is referenced in a GENERATED ALWAYS AS STORED column, pgschema plan fails outright:
ERROR: generation expression is not immutable (SQLSTATE 42P17)
Note: The bug does not reproduce for IMMUTABLE functions returning built-in types (e.g. RETURNS integer). It is specific to user-defined return types.
Confirmed on pgschema 1.6.2, PostgreSQL 16, 17, and 18.
Schema to reproduce
CREATE TYPE item_status AS ENUM ('pending', 'active', 'done');
CREATE OR REPLACE FUNCTION compute_status(x integer)
RETURNS item_status
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
IF x > 0 THEN RETURN 'active'::item_status; END IF;
IF x < 0 THEN RETURN 'done'::item_status; END IF;
RETURN 'pending'::item_status;
END;
$$;
-- Needed to trigger the harder failure (plan errors instead of just drifting):
CREATE TABLE demo (
id integer PRIMARY KEY,
status item_status GENERATED ALWAYS AS (compute_status(id)) STORED
);Observed plan output (perpetual drift)
Every run of pgschema plan reports:
Plan: 1 to modify.
Summary by type:
functions: 1 to modify
Functions:
~ compute_status
DDL to be executed:
--------------------------------------------------
CREATE OR REPLACE FUNCTION compute_status(
x integer
)
RETURNS item_status
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
IF x > 0 THEN RETURN 'active'::item_status; END IF;
IF x < 0 THEN RETURN 'done'::item_status; END IF;
RETURN 'pending'::item_status;
END;
$$;
Running pgschema apply does not resolve it. The same diff appears on the next plan.
Self-contained reproducer
requirements.txt
pytest
testcontainers[postgres]
psycopg2-binary
test_immutable_bug.py
"""
Reproducer for pgschema bug: unresolvable plan drift for IMMUTABLE plpgsql
functions that return a user-defined type.
Prerequisites:
- pgschema 1.6.2 installed and on PATH
- pip install -r requirements.txt
- Docker running
Run:
pytest test_immutable_bug.py -v
Expected: 6 passed
Actual: 3 passed (dump tests), 3 failed (plan drift tests, pg16/17/18)
"""
import subprocess
import textwrap
import psycopg2
import pytest
from testcontainers.postgres import PostgresContainer
POSTGRES_IMAGES = [
pytest.param("postgres:16", id="pg16"),
pytest.param("postgres:17", id="pg17"),
pytest.param("postgres:18", id="pg18-latest"),
]
ENUM_TYPE_SQL = "CREATE TYPE item_status AS ENUM ('pending', 'active', 'done');"
IMMUTABLE_FUNCTION_SQL = textwrap.dedent("""\
CREATE OR REPLACE FUNCTION compute_status(x integer)
RETURNS item_status
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
IF x > 0 THEN RETURN 'active'::item_status; END IF;
IF x < 0 THEN RETURN 'done'::item_status; END IF;
RETURN 'pending'::item_status;
END;
$$;
""")
GENERATED_TABLE_SQL = textwrap.dedent("""\
CREATE TABLE demo (
id integer PRIMARY KEY,
status item_status GENERATED ALWAYS AS (compute_status(id)) STORED
);
""")
@pytest.fixture(params=POSTGRES_IMAGES)
def pg(request):
with PostgresContainer(request.param) as container:
yield container
def pgschema(*args, check=True):
result = subprocess.run(["pgschema", *args], capture_output=True, text=True, check=False)
if check and result.returncode != 0:
raise subprocess.CalledProcessError(result.returncode, result.args,
output=result.stdout, stderr=result.stderr)
return result
def create_schema(container, *sql_statements):
conn = psycopg2.connect(host=container.get_container_host_ip(),
port=container.get_exposed_port(5432),
user=container.username, password=container.password,
dbname=container.dbname)
conn.autocommit = True
cur = conn.cursor()
for sql in sql_statements:
cur.execute(sql)
conn.close()
def test_dump_preserves_immutable(pg, tmp_path):
"""pgschema dump correctly includes IMMUTABLE (this passes — bug is in plan)."""
create_schema(pg, ENUM_TYPE_SQL, IMMUTABLE_FUNCTION_SQL)
schema_file = tmp_path / "main.sql"
pgschema("dump",
"--host", pg.get_container_host_ip(), "--port", str(pg.get_exposed_port(5432)),
"--user", pg.username, "--password", pg.password, "--db", pg.dbname,
"--schema", "public", "--multi-file", "--file", str(schema_file))
fn_file = tmp_path / "functions" / "compute_status.sql"
dump = fn_file.read_text()
assert "IMMUTABLE" in dump, f"pgschema dump dropped IMMUTABLE:\n{dump}"
def test_plan_is_noop_after_dump(pg, tmp_path):
"""pgschema plan must be a no-op when schema matches the dump — fails with drift."""
create_schema(pg, ENUM_TYPE_SQL, IMMUTABLE_FUNCTION_SQL, GENERATED_TABLE_SQL)
schema_file = tmp_path / "main.sql"
host, port = pg.get_container_host_ip(), str(pg.get_exposed_port(5432))
pgschema("dump",
"--host", host, "--port", port,
"--user", pg.username, "--password", pg.password, "--db", pg.dbname,
"--schema", "public", "--multi-file", "--file", str(schema_file))
assert "IMMUTABLE" in (tmp_path / "functions" / "compute_status.sql").read_text(), \
"Dump dropped IMMUTABLE — schema file is incomplete"
assert "GENERATED ALWAYS AS" in (tmp_path / "tables" / "demo.sql").read_text(), \
"Dump dropped generated column — schema file is incomplete"
result = pgschema("plan",
"--file", str(schema_file),
"--host", host, "--port", port,
"--user", pg.username, "--password", pg.password, "--db", pg.dbname,
"--schema", "public",
"--plan-host", host, "--plan-port", port,
"--plan-user", pg.username, "--plan-password", pg.password,
"--plan-db", pg.dbname,
"--output-human", "stdout",
check=False)
assert result.returncode == 0, \
f"plan failed (generation expression is not immutable):\n{result.stderr}\n{result.stdout}"
assert "No changes" in result.stdout or result.stdout.strip() == "", \
f"Unresolvable plan drift — same diff on every run:\n{result.stdout}"