Re: ALTER TABLE ... DETACH PARTITION just sitting there

On 6/28/21 4:16 PM, Tom Lane wrote:
> Ron <ronljohnsonjr(at)gmail(dot)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.