From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Josh Kupershmidt <schmiddy(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org, Matt Klein <matt(at)locately(dot)com> |
Subject: | Re: COMMIT stuck for days after bulk delete |
Date: | 2014-01-14 17:36:01 |
Message-ID: | 19782.1389720961@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
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.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Kupershmidt | 2014-01-14 18:24:55 | Re: COMMIT stuck for days after bulk delete |
Previous Message | Josh Kupershmidt | 2014-01-14 16:51:42 | COMMIT stuck for days after bulk delete |