From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Humphreys <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: 
 <CADdM_H3ibA8e4sijO0XSg1tLgmvLah=8Sgu-dOUzN08NpaKbWQ@mail.gmail.com>
References: <17439-7df4421197e928f0@postgresql.org>
 <339142.1647369200@sss.pgh.pa.us> <412466.1647374777@sss.pgh.pa.us>
 <425437.1647379135@sss.pgh.pa.us>
 <CADdM_H3ibA8e4sijO0XSg1tLgmvLah=8Sgu-dOUzN08NpaKbWQ@mail.gmail.com>
Comments: In-reply-to Kevin Humphreys <kmanh999@gmail.com>
	message dated "Tue, 15 Mar 2022 21:46:35 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <453333.1647397423.1@sss.pgh.pa.us>
Date: Tue, 15 Mar 2022 22:23:43 -0400
Message-ID: <453334.1647397423@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/453334.1647397423%40sss.pgh.pa.us>
Precedence: bulk

Kevin Humphreys <kmanh999@gmail.com> writes:
> Thanks for deep-diving into this Tom! I don't have any experience with the
> internal workings of Postgres but if I am understanding correctly:
> - This is definitely a bug and not intended or expected behavior and goes
> against SQL specifications

Looks like a bug to me.  The aspect of this that a drop of a table
column causes silent drop of dependent generated columns is clearly
intentional, but AFAICS that's based on a misreading of the spec.
I suspect that the fact that it carries over to other dependencies
of the generation expression was just failure to consider that case.

> - This is a non-trivial fix
> - This is a fix that can not be back-ported to Postgres 13?
> - This is a fix that can be made to Postgres 14?

Yes, yes, no.  I don't think we'd risk trying to change this for
any released branch, because redefining catalog contents in
existing installations carries all sorts of hazards.  Given that
it's been wrong since v12 and you're the first to complain,
I judge that fixing it in released branches is not worth taking
such risks for.  We should definitely endeavor to get it fixed
for v15 though.

> Is there any recommendation you would have for mitigation besides not
> dropping functions that may be used by generated columns?

I don't see any good user-level workaround :-(.

			regards, tom lane