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

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"