Re: Broken defenses against dropping a partitioning column

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.

regards, tom lane

[1] https://www.postgresql.org/message-id/flat/15631-188663b383e1e697%40postgresql.org

[2] https://www.postgresql.org/message-id/flat/CA%2Bu7OA4JKCPFrdrAbOs7XBiCyD61XJxeNav4LefkSmBLQ-Vobg%40mail.gmail.com