Re: BUG #18464: Replacing a SQL function silently drops the generated columns that use this function

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> PG Bug reporting form <noreply(at)postgresql(dot)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
>