PG Bug reporting form <noreply@postgresql.org> writes:
> We have the following DDL
> ...
> area double precision generated always as (map.area(geometry)) stored
> If I execute `DROP FUNCTION IF EXISTS map.area(geometry)`, it should error
> out saying it is depended on by building.area. However, instead it
> successfully drops map.area(geometry) and also drops the building.area
> column.
Yeah. I think this might be intentional, but it's surely a POLA
violation. To reproduce:
regression=# create function foo(int) returns int as 'select $1+1' language sql immutable;
CREATE FUNCTION
regression=# create table bar (x int, y int generated always as (foo(x)) stored);
CREATE TABLE
regression=# select pg_describe_object(classid,objid,objsubid) as obj,
pg_describe_object(refclassid,refobjid,refobjsubid)as ref, deptype from pg_depend where objid > ...;
obj | ref | deptype
-----------------------------------------+------------------------------------+---------
function foo(integer) | transform for integer language sql | n
function foo(integer) | schema public | n
type bar | table bar | i
type bar[] | type bar | i
table bar | schema public | n
default value for column y of table bar | column y of table bar | a
column y of table bar | column x of table bar | a
column y of table bar | function foo(integer) | a
(8 rows)
So the dependencies of the generation expression have been attached
to the column itself with 'a' (automatic) deptype, which explains
the behavior. But is that actually sensible? I think 'n' deptype
would provide the semantics that one would expect. Maybe there is
something in the SQL spec motivating references to other columns of
the same table to be handled this way, but surely that's not sane
for references to anything else.
It also seems dubious for the default -> column deptype to be 'a'
rather than 'i' for a GENERATED column. I see that we have some
special-case code that prevents a direct drop:
regression=# alter table bar alter column y drop default;
ERROR: column "y" of relation "bar" is a generated column
HINT: Use ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION instead.
but I don't have a lot of faith that that covers all possible
code paths. An 'i' dependency would make it much harder to
screw this up.
regards, tom lane