ALTER SCHEMA: renaming does not update string-literal function bodies
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/18/sql-alterschema.html
Description:
The ALTER SCHEMA page has no Notes section, and renaming a schema has one
consequence that is easy to miss.
Objects that store parsed references follow the rename: views, functions
with SQL-standard bodies (BEGIN ATOMIC), and privileges, including ALTER
DEFAULT PRIVILEGES entries. Function bodies written as string constants (AS
$$ ... $$, and every non-SQL language) are stored as text, so a
schema-qualified reference inside them keeps the old name and fails at the
next call. On 14.10, after ALTER SCHEMA old RENAME TO newsch, a LANGUAGE sql
function containing SELECT count(*) FROM old.t fails with ERROR: relation
"old.t" does not exist, while the BEGIN ATOMIC version returns the right
count.
Suggested note:
"Renaming a schema does not update references to it inside function and
procedure bodies written as string constants, since those are parsed only at
execution time (see CREATE FUNCTION). Views and SQL-standard function bodies
are updated automatically."
On Fri, Oct 9, 2026 at 1:42 PM PG Doc comments form <noreply@postgresql.org>
wrote:
"Renaming a schema does not update references to it inside function and
procedure bodies written as string constants, since those are parsed only
at
execution time (see CREATE FUNCTION). Views and SQL-standard function
bodies
are updated automatically."
Schemas are not special in this regard. It's mostly incumbent upon the
place that breaks this kind of expectation to note its deviating
rather than expect one feature to be aware of the implementation details of
another. In short, if the function/language pages don't cover that their
string literal queries are not exposed for rename operations or dependency
tracking that should be fixed in those locations.
David J.
On Fri, 2026-10-09 at 19:32 +0000, PG Doc comments form wrote:
Page: https://www.postgresql.org/docs/18/sql-alterschema.html
The ALTER SCHEMA page has no Notes section, and renaming a schema has one
consequence that is easy to miss.Objects that store parsed references follow the rename: views, functions
with SQL-standard bodies (BEGIN ATOMIC), and privileges, including ALTER
DEFAULT PRIVILEGES entries. Function bodies written as string constants (AS
$$ ... $$, and every non-SQL language) are stored as text, so a
schema-qualified reference inside them keeps the old name and fails at the
next call. On 14.10, after ALTER SCHEMA old RENAME TO newsch, a LANGUAGE sql
function containing SELECT count(*) FROM old.t fails with ERROR: relation
"old.t" does not exist, while the BEGIN ATOMIC version returns the right
count.Suggested note:
"Renaming a schema does not update references to it inside function and
procedure bodies written as string constants, since those are parsed only at
execution time (see CREATE FUNCTION). Views and SQL-standard function bodies
are updated automatically."
I think that would be useful information (but it would have to be worded
differently: neither views nor SQL-standard function bodies are really
updated).
However, it feels odd to document that for schemas, but not for tables, columns,
functions and all other kinds of objects. It would be even worse to document
that over and over for all the ALTER commands.
How about describing that in a generic location, so that it is clear that it
applies to all objects? I am not sure where, but perhaps some introductory
note in the "Data Definition" chapter?
Yours,
Laurenz Albe
Laurenz Albe <laurenz.albe@cybertec.at> writes:
However, it feels odd to document that for schemas, but not for tables, columns,
functions and all other kinds of objects. It would be even worse to document
that over and over for all the ALTER commands.
Yeah, that way madness lies.
How about describing that in a generic location, so that it is clear that it
applies to all objects? I am not sure where, but perhaps some introductory
note in the "Data Definition" chapter?
I'm pretty sure there is already some text about this somewhere,
but maybe it's not in the most optimal place.
regards, tom lane
On Fri, Oct 9, 2026 at 6:36 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Laurenz Albe <laurenz.albe@cybertec.at> writes:
However, it feels odd to document that for schemas, but not for tables,
columns,
functions and all other kinds of objects. It would be even worse to
document
that over and over for all the ALTER commands.
Yeah, that way madness lies.
How about describing that in a generic location, so that it is clear
that it
applies to all objects? I am not sure where, but perhaps some
introductory
note in the "Data Definition" chapter?
I'm pretty sure there is already some text about this somewhere,
but maybe it's not in the most optimal place.regards, tom lane
I think the behaviour is documented in
https://www.postgresql.org/docs/18/ddl-depend.html
but for DROPs.
Perhaps here a paragraph noting that this applies to ALTERs too (i.e. like
the proposed addition)
and then something like "Be aware of how [dependency tracking] works before
proceeding."
Although it would have to be put in all relevant places to be consistent...
This may be a lot of work
but I volunteer to do it if deemed so
On Monday, October 12, 2026, Matemática A3K <matematica.a3k@gmail.com>
wrote:
Although it would have to be put in all relevant places to be
consistent... This may be a lot of work
but I volunteer to do it if deemed so
It’s unclear where “all relevant places” are but it seems like a lot of
effort in response to a single report of user confusion (this one). I.e.,
maybe we aren’t as thorough as we could be but it doesn’t seem to matter in
the real world.
That said, 5.7 has a couple of example-providing sections for renaming
tables and columns. Expanding that a bit would be acceptable to me.
https://www.postgresql.org/docs/current/ddl-alter.html#DDL-ALTER-RENAMING-TABLE
David J.
On Mon, Oct 12, 2026 at 11:36 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:
On Monday, October 12, 2026, Matemática A3K <matematica.a3k@gmail.com>
wrote:Although it would have to be put in all relevant places to be
consistent... This may be a lot of work
but I volunteer to do it if deemed soIt’s unclear where “all relevant places” are but it seems like a lot of
effort in response to a single report of user confusion (this one). I.e.,
maybe we aren’t as thorough as we could be but it doesn’t seem to matter in
the real world.
Indeed
That said, 5.7 has a couple of example-providing sections for renaming
tables and columns. Expanding that a bit would be acceptable to me.https://www.postgresql.org/docs/current/ddl-alter.html#DDL-ALTER-RENAMING-TABLE
With that in mind, a way to tackle this may be improving the dependencies
page (general case) with something like
"PostgreSQL also uses this tracking for updating relevant objects when
possible on ALTER operations
(i.e. when renaming)."
and update the spotted particular cases (tables, schemas) with something
like "
This will trigger updates to the related objects tracked by [Postgres'
dependency tracker], which may be not sufficient
to keep all the previous functioning in the new situation (i.e.
user-defined functions)."
Also, the main caveat here seems to be with user-defined functions, which
may or may not be updated accordingly,
this could also be the key point to note."
This will trigger updates to the related objects tracked by [Postgres'
dependency tracker], which may be not sufficient
to keep all the previous functioning (i.e. user-defined functions)."
If we iterate over this, perhaps we can arrive to concrete and useful
improvement patch?
Show quoted text
David J.