ALTER TABLE ... DETACH PARTITION just sitting there

Ron <ronljohnsonjr@gmail.com> writes:

AWS RDS Postgresql 12.5.

We've got a table partitioned by month range (FOR VALUES FROM ('2011-07-01')
TO (2011-08-01')), and I've been detaching partitions from oldest to newest,
one at a time. Whenever it's failed due to a FK constraint (and there are
many of them!), I dropped the "same month" partition from TABLE_B, and then
returned and dropped the partition from TABLE_A.

But now, after 17 dropped partitions it's just sitting there on "ALTER TABLE
table_a DROP PARTITION table_a_p2011_07;"  I'm the only user on this test
instance, and validated that nothing else is blocking me.

Are the FK validations what's causing the apparent "hang"?  (EXPLAIN ALTER
TABLE... does not work.)

Perhaps the corresponding TABLE_B partition lacks an index on the
referencing column? I've not looked at how this particular case
is implemented, but typically, lack of such an index is fine
until you try to delete PK-side rows.

regards, tom lane

On 2021-Jun-28, Ron wrote:

We've got a table partitioned by month range (FOR VALUES FROM ('2011-07-01')
TO (2011-08-01')), and I've been detaching partitions from oldest to newest,
one at a time. Whenever it's failed due to a FK constraint (and there are
many of them!), I dropped the "same month" partition from TABLE_B, and then
returned and dropped the partition from TABLE_A.

But now, after 17 dropped partitions it's just sitting there on "ALTER TABLE
table_a DROP PARTITION table_a_p2011_07;"� I'm the only user on this test
instance, and validated that nothing else is blocking me.

Did you look in pg_locks for ungranted locks?

Are the FK validations what's causing the apparent "hang"?� (EXPLAIN ALTER
TABLE... does not work.)

Sure, it is possible. Do you have any FKs that are missing indexes in
the referencing side?

--
�lvaro Herrera Valdivia, Chile
<inflex> really, I see PHP as like a strange amalgamation of C, Perl, Shell
<crab> inflex: you know that "amalgam" means "mixture with mercury",
more or less, right?
<crab> i.e., "deadly poison"

On 6/28/21 4:16 PM, Tom Lane wrote:

Ron <ronljohnsonjr@gmail.com> writes:

AWS RDS Postgresql 12.5.
We've got a table partitioned by month range (FOR VALUES FROM ('2011-07-01')
TO (2011-08-01')), and I've been detaching partitions from oldest to newest,
one at a time. Whenever it's failed due to a FK constraint (and there are
many of them!), I dropped the "same month" partition from TABLE_B, and then
returned and dropped the partition from TABLE_A.
But now, after 17 dropped partitions it's just sitting there on "ALTER TABLE
table_a DROP PARTITION table_a_p2011_07;"  I'm the only user on this test
instance, and validated that nothing else is blocking me.
Are the FK validations what's causing the apparent "hang"?  (EXPLAIN ALTER
TABLE... does not work.)

Perhaps the corresponding TABLE_B partition lacks an index on the
referencing column? I've not looked at how this particular case
is implemented, but typically, lack of such an index is fine
until you try to delete PK-side rows.

There are seven FK constraints, all in the format of (field_1, field_2). 
Two referenced table have a supporting index on both columns, and five have
a supporting index on only field_1.

--
Angular momentum makes the world go 'round.

On Mon, 2021-06-28 at 16:52 -0500, Ron wrote:

On 6/28/21 4:16 PM, Tom Lane wrote:

Ron <ronljohnsonjr@gmail.com> writes:

AWS RDS Postgresql 12.5.
We've got a table partitioned by month range (FOR VALUES FROM ('2011-07-01')
TO (2011-08-01')), and I've been detaching partitions from oldest to newest,
one at a time. Whenever it's failed due to a FK constraint (and there are
many of them!), I dropped the "same month" partition from TABLE_B, and then
returned and dropped the partition from TABLE_A.
But now, after 17 dropped partitions it's just sitting there on "ALTER TABLE
table_a DROP PARTITION table_a_p2011_07;" I'm the only user on this test
instance, and validated that nothing else is blocking me.
Are the FK validations what's causing the apparent "hang"? (EXPLAIN ALTER
TABLE... does not work.)

Perhaps the corresponding TABLE_B partition lacks an index on the
referencing column? I've not looked at how this particular case
is implemented, but typically, lack of such an index is fine
until you try to delete PK-side rows.

There are seven FK constraints, all in the format of (field_1, field_2).
Two referenced table have a supporting index on both columns, and five have
a supporting index on only field_1.

Those five foreign keys that are not fully indexed may well be your problem.

Yours,
Laurenz Albe