Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mon, 2021-06-28 at 16:52 -0500, Ron wrote:
> On 6/28/21 4:16 PM, Tom Lane wrote:
> > Ron <ronljohnsonjr@xxxxxxxxx> 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






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux