Re: BUG #17439: DROP FUNCTION functionName(); drops associated generated column without using CASCADE - Mailing list pgsql-bugs

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



pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #17439: DROP FUNCTION functionName(); drops associated generated column without using CASCADE
Next
From: Tom Lane
Date:
Subject: Re: BUG #17439: DROP FUNCTION functionName(); drops associated generated column without using CASCADE