pg.ddx.io  pgsql-docs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
ALTER SCHEMA: renaming does not update string-literal function bodies
7+ messages / 5 participants
[nested] [flat]

* ALTER SCHEMA: renaming does not update string-literal function bodies
@ 2026-10-09 19:32  PG Doc comments form <noreply@postgresql.org>
  0 siblings, 2 replies; 7+ messages in thread

From: PG Doc comments form @ 2026-10-09 19:32 UTC (permalink / raw)
  To: pgsql-docs@lists.postgresql.org; +Cc: ben@mlabs.city

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."







^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: ALTER SCHEMA: renaming does not update string-literal function bodies
@ 2026-10-09 21:07  David G. Johnston <david.g.johnston@gmail.com>
  parent: PG Doc comments form <noreply@postgresql.org>
  1 sibling, 0 replies; 7+ messages in thread

From: David G. Johnston @ 2026-10-09 21:07 UTC (permalink / raw)
  To: ben@mlabs.city; pgsql-docs@lists.postgresql.org

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.

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: ALTER SCHEMA: renaming does not update string-literal function bodies
@ 2026-10-09 21:22  Laurenz Albe <laurenz.albe@cybertec.at>
  parent: PG Doc comments form <noreply@postgresql.org>
  1 sibling, 1 reply; 7+ messages in thread

From: Laurenz Albe @ 2026-10-09 21:22 UTC (permalink / raw)
  To: ben@mlabs.city; pgsql-docs@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: ALTER SCHEMA: renaming does not update string-literal function bodies
@ 2026-10-09 21:36  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Laurenz Albe <laurenz.albe@cybertec.at>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2026-10-09 21:36 UTC (permalink / raw)
  To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: ben@mlabs.city; pgsql-docs@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: ALTER SCHEMA: renaming does not update string-literal function bodies
@ 2026-10-12 14:22  Matemática A3K <matematica.a3k@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Matemática A3K @ 2026-10-12 14:22 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; ben@mlabs.city; pgsql-docs@lists.postgresql.org

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

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: ALTER SCHEMA: renaming does not update string-literal function bodies
@ 2026-10-12 14:35  David G. Johnston <david.g.johnston@gmail.com>
  parent: Matemática A3K <matematica.a3k@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: David G. Johnston @ 2026-10-12 14:35 UTC (permalink / raw)
  To: Matemática A3K <matematica.a3k@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Laurenz Albe <laurenz.albe@cybertec.at>; ben@mlabs.city <ben@mlabs.city>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>

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.

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: ALTER SCHEMA: renaming does not update string-literal function bodies
@ 2026-10-12 15:32  Matemática A3K <matematica.a3k@gmail.com>
  parent: David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Matemática A3K @ 2026-10-12 15:32 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Laurenz Albe <laurenz.albe@cybertec.at>; ben@mlabs.city <ben@mlabs.city>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>

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 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.
>

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?


> David J.
>
>

^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2026-10-12 15:32 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-09 19:32 ALTER SCHEMA: renaming does not update string-literal function bodies PG Doc comments form <noreply@postgresql.org>
2026-10-09 21:07 ` David G. Johnston <david.g.johnston@gmail.com>
2026-10-09 21:22 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-10-09 21:36   ` Tom Lane <tgl@sss.pgh.pa.us>
2026-10-12 14:22     ` Matemática A3K <matematica.a3k@gmail.com>
2026-10-12 14:35       ` David G. Johnston <david.g.johnston@gmail.com>
2026-10-12 15:32         ` Matemática A3K <matematica.a3k@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox