Function return-type change does not recreate a dependent view; apply fails with SQLSTATE 2BP01

[christophostertag]

Summary

Changing a function's return type from integer to bigint while retaining a view that calls it generates only DROP FUNCTION and function recreation. The dependent view is absent from the plan, so PostgreSQL rejects the function drop.

Observed with pgschema 1.13.0 and PostgreSQL 18.4, in an isolated synthetic test on 2026-09-13. This report reuses the recorded run; it does not claim a runtime reproduction against current main.

Reproduction

old.sql:

CREATE FUNCTION public.calculate(x integer)
RETURNS integer LANGUAGE sql IMMUTABLE AS $$ SELECT x+1 $$;
CREATE VIEW public.answer AS SELECT public.calculate(4) AS answer;

desired.sql:

CREATE FUNCTION public.calculate(x integer)
RETURNS bigint LANGUAGE sql IMMUTABLE AS $$ SELECT x+2 $$;
CREATE VIEW public.answer AS SELECT public.calculate(4) AS answer;

Use two fresh, disposable databases on a local PostgreSQL 18.4 instance. The role must own the target objects and be able to create/drop planning schemas in the planning database. Set PGPASSWORD separately if authentication requires it.

export PGHOST=127.0.0.1 PGPORT=5432 PGUSER=postgres
createdb pgschema_repro
createdb pgschema_repro_plan
psql -X -v ON_ERROR_STOP=1 -d pgschema_repro -f old.sql
pgschema plan --host "$PGHOST" --port "$PGPORT" \
  --db pgschema_repro --user "$PGUSER" --sslmode disable \
  --plan-host "$PGHOST" --plan-db pgschema_repro_plan \
  --plan-user "$PGUSER" --plan-sslmode disable \
  --file desired.sql --output-json plan.json --output-sql plan.sql --no-color
pgschema apply --host "$PGHOST" --port "$PGPORT" \
  --db pgschema_repro --user "$PGUSER" --sslmode disable \
  --plan plan.json --auto-approve --no-color

Observed result

Planning exits successfully. The entire generated migration is:

DROP FUNCTION IF EXISTS calculate(integer);

CREATE OR REPLACE FUNCTION calculate(
    x integer
)
RETURNS bigint
LANGUAGE sql
IMMUTABLE
AS $$ SELECT x+2
$$;

Applying the saved plan exits 1:

Error: failed to execute concatenated statements in group 1: ERROR: cannot drop function calculate(integer) because other objects depend on it (SQLSTATE 2BP01)

The recorded before/after catalog snapshot was unchanged after the failed apply. Although the desired view SQL is textually unchanged, its output column must change from integer to bigint as a consequence of the function return type.

Expected behavior

The planner should recognize that recreating the function also requires recreating the dependent view in dependency order: drop view, drop/recreate function, then recreate view. Alternatively, it should reject unsupported dependency handling during planning with a useful explanation.

After successful application:

SELECT answer = 6 AND pg_typeof(answer) = 'bigint'::regtype
FROM public.answer; -- true

A fix should not merely add CASCADE: all affected dependents must be accounted for and restored, including required grants, ownership, comments and view options. This minimal fixture demonstrates the dependency failure; it does not test those extra attributes.

Relationship to existing fixes

#326 / #327 added function drop/recreation for return-type changes. That part is already present in this plan. The missing part here is handling an existing view that depends on the recreated function.

#480 / #488 address the opposite dependency direction: a function whose signature depends on a view being recreated. Here the view calls the function and depends on it.

No matching report for this direction was found in the open/closed issue search on 2026-09-13. At inspected main commit 319b88c83d62b2c9a62eff7a09ec6563954bcf4b, internal/diff/function.go still handles return-type changes through function drop/recreation. That is supporting code inspection only; current main has not been runtime-tested for this report.