From: Tom Lane <tgl@sss.pgh.pa.us>
To: remi.cura@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18464: Replacing a SQL function silently drops the generated columns that use this function
Date: Tue, 14 May 2024 11:55:18 -0400
Message-ID: <1104765.1715702118@sss.pgh.pa.us> (raw)
In-Reply-To: <18464-9f1edc4037b65003@postgresql.org>
References: <18464-9f1edc4037b65003@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, remi.cura@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18464: Replacing a SQL function silently drops the generated columns that use this function
In-Reply-To: <1104765.1715702118@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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