From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-hackers@lists.postgresql.org
cc: Manuel Rigger <rigger.manuel@gmail.com>
Subject: Broken defenses against dropping a partitioning column
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <31919.1562526703.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sun, 07 Jul 2019 15:11:43 -0400
Message-ID: <31920.1562526703@sss.pgh.pa.us>
Precedence: bulk

(Moved from pgsql-bugs thread at [1])

Consider

regression=3D# create domain d1 as int;
CREATE DOMAIN
regression=3D# create table t1 (f1 d1) partition by range(f1);
CREATE TABLE
regression=3D# alter table t1 drop column f1;
ERROR:  cannot drop column named in partition key

So far so good, but that defense has more holes than a hunk of
Swiss cheese:

regression=3D# drop domain d1 cascade;
psql: NOTICE:  drop cascades to column f1 of table t1
DROP DOMAIN

Of course, the table is now utterly broken, e.g.

regression=3D# \d t1
psql: ERROR:  cache lookup failed for type 0

(More-likely variants of this include dropping an extension that
defines the type of a partitioning column, or dropping the schema
containing such a type.)

The fix I was speculating about in the pgsql-bugs thread was to add
explicit pg_depend entries making the table's partitioning columns
internally dependent on the whole table (or maybe the other way around;
haven't experimented).  That fix has a couple of problems though:

1. In the example, "drop domain d1 cascade" would automatically
cascade to the whole partitioned table, including child partitions
of course.  This might leave a user sad, if a few terabytes of
valuable data went away; though one could argue that they'd better
have paid more attention to what the cascade cascaded to.

2. It doesn't fix anything for pre-existing tables in pre-v12 branches.


I thought of a different possible approach, which is to move the
"cannot drop column named in partition key" error check from
ATExecDropColumn(), where it is now, to RemoveAttributeById().
That would be back-patchable, but the implication would be that
dropping anything that a partitioning column depends on would be
impossible, even with CASCADE; you'd have to manually drop the
partitioned table first.  Good for data safety, but a horrible
violation of expectations, and likely of the SQL spec as well.
I'm not sure we could avoid order-of-traversal problems, either.


Ideally, perhaps, a DROP CASCADE like this would not cascade to
the whole table but only to the table's partitioned-ness property,
leaving you with a non-partitioned table with most of its data
intact.  It would take a lot of work to make that happen though,
and it certainly wouldn't be back-patchable, and I'm not really
sure it's worth it.

Thoughts?

			regards, tom lane

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