Re: BUG #17439: DROP FUNCTION functionName(); drops associated generated column without using CASCADE

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
- 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?

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

Thanks,
Kevin Humphreys

On Tue, Mar 15, 2022 at 5:18 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I wrote:
> > 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.
>
> Ugh ... this opens a much larger can of worms than I thought.
> There are two problems:
>
> 1. If these dependencies are NORMAL, then we cannot tell them apart from
> the column's other dependencies -- such as the ones on its type and
> collation. (The generation expression could easily have dependencies on
> types and collations.) I think we really have to switch them so that
> the referencing object is the pg_attrdef entry not the column itself,
> just as is done for ordinary defaults. That's easy so far as
> StoreAttrDefault itself is concerned, but ...
>
> 2. ALTER TABLE contains multiple assumptions about the structure of
> dependencies for generation expressions, and they'll all be broken
> by such a change. There's even a very explicit claim that any such
> dependency could only be on another column of the same table :-(.
>
> The regression tests reveal two or three places in tablecmds.c that
> need to change, and I'm worried there may be other places that
> aren't covered.
>
> So it's looking to me like we probably can't fix this in the back
> branches; it'll have to be a HEAD-only change.
>
> regards, tom lane
>

--
Kevin Humphreys