Re: Broken defenses against dropping a partitioning column

On Mon, Jul 08, 2019 at 10:58:56AM -0400, Tom Lane wrote:
>Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
>> That said, I'm not sure I see the use case for an ALTER TABLE .. DROP
>> COLUMN command that turns a partitioned table (with existing partitions
>> containing data) into one non-partitioned table with all data minus the
>> partitioning column(s).
>
>Yeah, it'd be a lot of work for a dubious goal.
>
>> This seems vaguely related to the issue of dropping foreign keys; see
>> https://postgr.es/m/20190329152239.GA29258@alvherre.pgsql wherein I
>> settled with a non-ideal solution to the problem of being unable to
>> depend on something that did not cause the entire table to be dropped
>> in certain cases.
>
>That's an interesting analogy. Re-reading that thread, what I said
>in <29497(dot)1554217629(at)sss(dot)pgh(dot)pa(dot)us> seems pretty apropos to the
>current problem:
>
>>> FWIW, I think that the dependency mechanism is designed around the idea
>>> that whether it's okay to drop a *database object* depends only on what
>>> other *database objects* rely on it, and that you can always make a DROP
>>> valid by dropping all the dependent objects. That's not an unreasonable
>>> design assumption, considering that the SQL standard embeds the same
>>> assumption in its RESTRICT/CASCADE syntax.
>
>I think that is probably a fatal objection to my idea of putting an error
>check into RemoveAttributeById(). As an example, consider the possibility
>that somebody makes a temporary type and then makes a permanent table with
>a partitioning column of that type. What shall we do at session exit?
>Failing to remove the temp type is not an acceptable choice, because that
>leaves us with a permanently broken temp schema (compare bug #15631 [1]).
>
>Also, I don't believe we can make that work without order-of-operations
>problems in cases comparable to the original bug in this thread [2].
>One or the other order of the object OIDs is going to lead to the column
>being visited for deletion before the whole table is, and again rejecting
>the column deletion is not going to be an acceptable behavior.
>
>So I think we're probably stuck with the approach of adding new internal
>dependencies. If we go that route, then our options for the released
>branches are (1) do nothing, or (2) back-patch the code that adds such
>dependencies, but without a catversion bump. That would mean that only
>tables created after the next minor releases would have protection against
>this problem. That's not ideal but maybe it's okay, considering that we
>haven't seen actual field reports of trouble of this kind.
>

Couldn't we also write a function that adds those dependencies for
existing objects, and request users to run it after the update?

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services