From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Cc: kmanh999@gmail.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 16:06:17 -0400
Message-ID: <412466.1647374777@sss.pgh.pa.us> (raw)
In-Reply-To: <339142.1647369200@sss.pgh.pa.us>
References: <17439-7df4421197e928f0@postgresql.org>
<339142.1647369200@sss.pgh.pa.us>
I wrote:
> 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.
I looked into SQL:2021, and AFAICS the existing behavior is flat wrong,
even for cross references to other table columns. I think you read
11.23 <drop column definition> general rule 3, which seems to say to
unconditionally drop any generated column depending on the target column
... but you missed syntax rule 7f, which says
7) If RESTRICT is specified, then C shall not be referenced in any of the
following:
...
f) The generation expression of any column descriptor.
GR3 would be very strange if read in isolation anyway, because it
says to drop the generated column with CASCADE, which could cause
arbitrary stuff to go away. That is sensible if you know that 7f
prevents us from getting here unless the original drop said CASCADE,
but otherwise it's a pretty astonishing thing.
So it looks to me like the generation expression's dependencies
should be NORMAL not AUTO in all cases. I'm less sure about
whether to mess with any other aspects of the dependency linkages.
That might not be something to fool with in back branches, anyway.
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, peter.eisentraut@enterprisedb.com, kmanh999@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17439: DROP FUNCTION functionName(); drops associated generated column without using CASCADE
In-Reply-To: <412466.1647374777@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