pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18464: Replacing a SQL function silently drops the generated columns that use this function
6+ messages / 4 participants
[nested] [flat]
* BUG #18464: Replacing a SQL function silently drops the generated columns that use this function
@ 2024-05-14 15:26 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 6+ messages in thread
From: PG Bug reporting form @ 2024-05-14 15:26 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: remi.cura@gmail.com
The following bug has been logged on the website:
Bug reference: 18464
Logged by: Rémi Cura
Email address: remi.cura@gmail.com
PostgreSQL version: 14.10
Operating system: Linux
Description:
I have a SQL function that I defined myself. Let's call it MyFunction().
I use MyFunction in generated columns of 2 distinct tables.
Whe trying to delete MyFunction, the DB correctly raises an error.
However, when I `CREATE OR REPLACE` this existing function in place, the DB
silently drop all the generated columns depending on this function.
This is not the expected behavior.
I should get either an error, or no columns should be dropped.
if they are dropped, I should get at the very least a warning.
This is pretty major: having columns disappear magically on prod dbs is not
what postgres usually do.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18464: Replacing a SQL function silently drops the generated columns that use this function
@ 2024-05-14 15:42 Muhammad Waqas <waqas.m@bitnine.net>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 6+ messages in thread
From: Muhammad Waqas @ 2024-05-14 15:42 UTC (permalink / raw)
To: remi.cura@gmail.com; pgsql-bugs@lists.postgresql.org
When you drop any object with cascade option all associated object will be
dropped. That is the normal behavior just and other behavior is not normal
2024년 5월 14일 (화) 오후 8:37, PG Bug reporting form <noreply@postgresql.org>님이
작성:
> The following bug has been logged on the website:
>
> Bug reference: 18464
> Logged by: Rémi Cura
> Email address: remi.cura@gmail.com
> PostgreSQL version: 14.10
> Operating system: Linux
> Description:
>
> I have a SQL function that I defined myself. Let's call it MyFunction().
> I use MyFunction in generated columns of 2 distinct tables.
> Whe trying to delete MyFunction, the DB correctly raises an error.
> However, when I `CREATE OR REPLACE` this existing function in place, the DB
> silently drop all the generated columns depending on this function.
> This is not the expected behavior.
> I should get either an error, or no columns should be dropped.
> if they are dropped, I should get at the very least a warning.
>
> This is pretty major: having columns disappear magically on prod dbs is not
> what postgres usually do.
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18464: Replacing a SQL function silently drops the generated columns that use this function
@ 2024-05-14 15:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2024-05-14 15:55 UTC (permalink / raw)
To: remi.cura@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> I have a SQL function that I defined myself. Let's call it MyFunction().
> I use MyFunction in generated columns of 2 distinct tables.
> Whe trying to delete MyFunction, the DB correctly raises an error.
> However, when I `CREATE OR REPLACE` this existing function in place, the DB
> silently drop all the generated columns depending on this function.
> This is not the expected behavior.
Indeed not, and it does not happen like that for me:
$ psql
psql (14.12)
Type "help" for help.
regression=# create function testfunc(int) returns int strict immutable language sql as 'select $1+1';
CREATE FUNCTION
regression=# create table foo (f1 int, f2 int generated always as (testfunc(f1)) stored);
CREATE TABLE
regression=# insert into foo values(1),(10);
INSERT 0 2
regression=# table foo;
f1 | f2
----+----
1 | 2
10 | 11
(2 rows)
regression=# create or replace function testfunc(int) returns int strict immutable language sql as 'select $1+2';
CREATE FUNCTION
regression=# table foo;
f1 | f2
----+----
1 | 2
10 | 11
(2 rows)
Now you could make a reasonable argument that the generated column
should have auto-updated. We don't implement that (at least not
yet), on the grounds that what I did above broke the function's
promise of immutability. The same change would also have broken
indexes using the function, so generated columns are not any worse.
In any case, please provide a self-contained example demonstrating
your claim.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18464: Replacing a SQL function silently drops the generated columns that use this function
@ 2024-05-15 13:30 Rémi Cura <remi.cura@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Rémi Cura @ 2024-05-15 13:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
Hey Tom, thanks for taking the time to answer (and more generally your work
on Postgres! ).
I tried to reproduce, and as you pointed out, updating the function in
place is not what dropped the columns.
Instead, I must have dropped and recreated the function, which indeed drops
the generated columns.
I still feel this is a very dangerous behavior.
For instance, if you use this function in an index, postgres will reject
dropping the function because it's used in the index
`ERROR: cannot drop function testfunc(integer) because other objects depend
on it
Detail: index delete_me depends on function testfunc(integer)`
Of course you can use the `DROP FUNCTION .. CASCADE`, which will cascade to
deleting the index.
The issue is that it should be the same for generated columns. They are a
"dependency", they should have the same behavior as index on function.
--> trying to delete the function used in generated column --> it errors,
listing the generated column as a dependency
Here is the updated test script:
```
create function testfunc(int) returns int strict immutable language sql as
'select $1+1';
DROP TABLE IF EXISTS foo;
create table foo (f1 int, f2 int generated always as (testfunc(f1)) stored);
CREATE INDEX delete_me ON foo (testfunc(f1));
insert into foo values(1),(10);
SELECT * FROM foo; -- 2 cols
DROP FUNCTION testfunc(int);
--> raise error bc index use the function, does not say anything about
generated column using this function
DROP INDEX delete_me ;
DROP FUNCTION testfunc(int);
--> raise NO error
--> cascade to deleting the generated column (not that the CASCADE key
word was not user)
SELECT * FROM foo; -- *1* col
```
On Tue, May 14, 2024 at 5:55 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
> > I have a SQL function that I defined myself. Let's call it MyFunction().
> > I use MyFunction in generated columns of 2 distinct tables.
> > Whe trying to delete MyFunction, the DB correctly raises an error.
> > However, when I `CREATE OR REPLACE` this existing function in place, the
> DB
> > silently drop all the generated columns depending on this function.
> > This is not the expected behavior.
>
> Indeed not, and it does not happen like that for me:
>
> $ psql
> psql (14.12)
> Type "help" for help.
>
> regression=# create function testfunc(int) returns int strict immutable
> language sql as 'select $1+1';
> CREATE FUNCTION
> regression=# create table foo (f1 int, f2 int generated always as
> (testfunc(f1)) stored);
> CREATE TABLE
> regression=# insert into foo values(1),(10);
> INSERT 0 2
> regression=# table foo;
> f1 | f2
> ----+----
> 1 | 2
> 10 | 11
> (2 rows)
>
> regression=# create or replace function testfunc(int) returns int strict
> immutable language sql as 'select $1+2';
> CREATE FUNCTION
> regression=# table foo;
> f1 | f2
> ----+----
> 1 | 2
> 10 | 11
> (2 rows)
>
> Now you could make a reasonable argument that the generated column
> should have auto-updated. We don't implement that (at least not
> yet), on the grounds that what I did above broke the function's
> promise of immutability. The same change would also have broken
> indexes using the function, so generated columns are not any worse.
>
> In any case, please provide a self-contained example demonstrating
> your claim.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18464: Replacing a SQL function silently drops the generated columns that use this function
@ 2024-05-15 15:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rémi Cura <remi.cura@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2024-05-15 15:56 UTC (permalink / raw)
To: Rémi Cura <remi.cura@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
=?UTF-8?Q?R=C3=A9mi_Cura?= <remi.cura@gmail.com> writes:
> I tried to reproduce, and as you pointed out, updating the function in
> place is not what dropped the columns.
> Instead, I must have dropped and recreated the function, which indeed drops
> the generated columns.
> I still feel this is a very dangerous behavior.
Yeah, we changed this at v15. See
https://www.postgresql.org/message-id/flat/17439-7df4421197e928f0%40postgresql.org
https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=cb02fcb4c
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18464: Replacing a SQL function silently drops the generated columns that use this function
@ 2024-05-15 18:12 Rémi Cura <remi.cura@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Rémi Cura @ 2024-05-15 18:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
Awesome!
The irony is that I triggered this behavior while solving issues in order
to upgrade to PG16 :-)
thank you very much again
Rémi
On Wed, May 15, 2024, 17:56 Tom Lane <tgl@sss.pgh.pa.us> wrote:
> =?UTF-8?Q?R=C3=A9mi_Cura?= <remi.cura@gmail.com> writes:
> > I tried to reproduce, and as you pointed out, updating the function in
> > place is not what dropped the columns.
> > Instead, I must have dropped and recreated the function, which indeed
> drops
> > the generated columns.
> > I still feel this is a very dangerous behavior.
>
> Yeah, we changed this at v15. See
>
>
> https://www.postgresql.org/message-id/flat/17439-7df4421197e928f0%40postgresql.org
>
>
> https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=cb02fcb4c
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-05-15 18:12 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-14 15:26 BUG #18464: Replacing a SQL function silently drops the generated columns that use this function PG Bug reporting form <noreply@postgresql.org>
2024-05-14 15:42 ` Muhammad Waqas <waqas.m@bitnine.net>
2024-05-14 15:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-05-15 13:30 ` Rémi Cura <remi.cura@gmail.com>
2024-05-15 15:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-05-15 18:12 ` Rémi Cura <remi.cura@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