Re: COMMIT stuck for days after bulk delete

On Tue, Jan 14, 2014 at 12:36 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Josh Kupershmidt <schmiddy(at)gmail(dot)com> 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