Postgres Pro
Downloads
Home   >   mailing lists

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

ALTER TABLE ... DETACH PARTITION just sitting there

From
Ron
Date:
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.)

-- 
Angular momentum makes the world go 'round.

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

From
Tom Lane
Date:
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

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

From
Alvaro Herrera
Date:
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"

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

From
Ron
Date:
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.

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

From
Laurenz Albe
Date:
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