Message-ID: <1a0a5a4c-5ab0-3e84-9d1b-9e0ed650b729@enterprisedb.com>
Date: Wed, 16 Mar 2022 20:05:48 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:91.0)
 Gecko/20100101 Thunderbird/91.7.0
Subject: Re: BUG #17439: DROP FUNCTION functionName(); drops associated
 generated column without using CASCADE
Content-Language: en-US
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: kmanh999@gmail.com, pgsql-bugs@lists.postgresql.org
References: <17439-7df4421197e928f0@postgresql.org>
 <339142.1647369200@sss.pgh.pa.us> <412466.1647374777@sss.pgh.pa.us>
From: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
In-Reply-To: <412466.1647374777@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/1a0a5a4c-5ab0-3e84-9d1b-9e0ed650b729%40enterprisedb.com>
Precedence: bulk

On 15.03.22 21:06, Tom Lane wrote:
> 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.

The reported case is a DROP FUNCTION, but looking at <drop routine 
statement>, it doesn't say anything about what to do with generation 
expressions.  That might be a bug in the standard, too.