From: Tom Lane <tgl@sss.pgh.pa.us>
To: kmanh999@gmail.com
cc: Peter Eisentraut <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: <17439-7df4421197e928f0@postgresql.org>
References: <17439-7df4421197e928f0@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Tue, 15 Mar 2022 15:11:17 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <339141.1647369200.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 15 Mar 2022 14:33:20 -0400
Message-ID: <339142.1647369200@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/339142.1647369200%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> We have the following DDL
> ...
>     area          double precision generated always as (map.area(geometr=
y)) stored

> If I execute `DROP FUNCTION IF EXISTS map.area(geometry)`, it should err=
or
> 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=3D# create function foo(int) returns int as 'select $1+1' langu=
age sql immutable;
CREATE FUNCTION
regression=3D# create table bar (x int, y int generated always as (foo(x))=
 stored);
CREATE TABLE

regression=3D# select pg_describe_object(classid,objid,objsubid) as obj, p=
g_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=3D# 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