From: Tom Lane <tgl@sss.pgh.pa.us>
To: kmanh999@gmail.com
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17439: DROP FUNCTION functionName(); drops associated generated column without using CASCADE
Date: Tue, 15 Mar 2022 14:33:20 -0400
Message-ID: <339142.1647369200@sss.pgh.pa.us> (raw)
In-Reply-To: <17439-7df4421197e928f0@postgresql.org>
References: <17439-7df4421197e928f0@postgresql.org>
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
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, kmanh999@gmail.com, peter.eisentraut@enterprisedb.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17439: DROP FUNCTION functionName(); drops associated generated column without using CASCADE
In-Reply-To: <339142.1647369200@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