Re: COMMIT stuck for days after bulk delete

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

 



On Tue, Jan 14, 2014 at 12:36 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
> Josh Kupershmidt <schmiddy@xxxxxxxxx> writes:
>> We have a 9.1.11 backend (Ubuntu 12.04 x86_64, m1.medium EC2 instance)
>> which seems to be stuck at COMMIT for 2 days now:
>> ...
>> The transaction behind that COMMIT has been the only thing running on
>> this Postgres instance for the past 3 days or so, since Postgres was
>> started on that machine. I spun the EC2 instance for this database up
>> solely to test a database subsetting process, which is what the
>> transaction was doing before it got stuck at COMMIT -- using a bunch
>> of DELETEs and ALTER TABLE ... DROP|ADD CONSTRAINTs to delete 90% or
>> so of our data in order to be able to pg_dump a slimmed-down
>> development copy.
>
> A plausible guess is that the backend is running around trying to verify
> that some deferred foreign key constraints still hold.  But without
> knowing what your schema is, that's only a guess.

Yeah, that's a good guess. A bunch of the FK constraints I am dropping
and re-adding are marked DEFERRABLE INITIALLY DEFERRED; there are 167
counted by:

SELECT COUNT(*)
  FROM pg_catalog.pg_constraint c
  WHERE contype = 'f' AND condeferrable AND condeferred AND
  connamespace =
    (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'public') ;

> If that is it, a likely solution is to drop *all* the FK constraints
> before doing the bulk delete, then (in a new transaction, probably)
> recreate the ones you still want.

Will try that, thanks for the suggestion.

Josh


-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux