2026-09-11 · run 2026-09-11-postgres-schema-rename-semantics

what `ALTER SCHEMA … RENAME TO` carries, and the one thing it drops

Scale: one schema, one table, one row, one view, two functions, one role
Database: PostgreSQL 14.10 (postgres:14.10-alpine), single node, container
Topology: single container, --rm

Environment

Field	Value
Keycloak from → to	n/a — no Keycloak in this run
Distribution / start mode	n/a
Database	PostgreSQL 14.10 (`postgres:14.10-alpine`), single node, container
Why 14	`BEGIN ATOMIC` function bodies arrived in 14, and the contrast with string-constant bodies is the whole test. 14 is the floor, not a preference
Postgres tuning	stock image defaults, untouched
Dataset scale	one schema, one table, one row, one view, two functions, one role
Seeding method	direct SQL (`psql -f`)
Topology	single container, `--rm`
Host class	carbon (Ben's workstation)
JVM heap	n/a
Wall-clock elapsed	seconds; duration is not what this run measures

Method

Build a schema old holding a table, a view over it, and two functions that differ only in how their bodies are stored — one LANGUAGE sql AS $$ … $$ (string constant), one LANGUAGE sql BEGIN ATOMIC … END (SQL-standard body). Grant a role USAGE on the schema and SELECT on the table, and set an ALTER DEFAULT PRIVILEGES entry. Then ALTER SCHEMA old RENAME TO newsch and read back what each object says about itself.

Results

Object	After the rename
View definition	follows — `pg_get_viewdef` reads `FROM newsch.t`
`BEGIN ATOMIC` function body	follows — rewritten to `newsch.t`, still returns 1
String-constant function body	does not follow — still reads `old.t`
Calling the string-constant function	`ERROR: relation "old.t" does not exist`
Role's schema `USAGE`	survives — `has_schema_privilege` true
Role's table `SELECT`	survives — `has_table_privilege` true
`ALTER DEFAULT PRIVILEGES` entry	survives — the `pg_default_acl` row now points at `newsch`

The mechanism, in one sentence from the docs

CREATE FUNCTION says the standard body is "parsed at function definition time, the string constant form is parsed at execution time." Everything stored as a parse tree references the schema by OID and does not care what it is called. Everything stored as text is text. Privileges behave like the first group: ACLs hang off the object, not off the name.

Outcome

Every claim in SOC-2026-09-008 is backed by a line of this output
The failure mode is loud (a runtime error) rather than a wrong answer — under these conditions; see the untested row below, which is where that could stop being true

Verification

Claim	Primary source	Checked
Views follow a schema rename	`pg_get_viewdef` after rename → `newsch.t`	☑
`BEGIN ATOMIC` bodies follow it	`pg_get_functiondef` after rename → `newsch.t`; call returns 1	☑
String-constant bodies do not	`pg_get_functiondef` still `old.t`; call errors	☑
Grants survive	`has_schema_privilege` / `has_table_privilege` both true	☑
Default privileges survive	`pg_default_acl.defaclnamespace` = `newsch`	☑
`pg_restore` has no `REMAP_SCHEMA`	postgresql.org/docs/17/app-pgrestore.html — `-n` filters only	☑
The parse-time difference is the cause	postgresql.org/docs/17/sql-createfunction.html	☑
A `search_path` on the role masking the broken function	not tested — and this is the one that matters, because it would turn the loud error into a quiet wrong answer	☐
`plpgsql` bodies	not tested; they are string constants too and should behave identically	☐
Anything above 14.10	not tested. The docs sentence is unchanged through 17, but this run is 14.10 only	☐