From: Tom Lane <tgl@sss.pgh.pa.us>
To: Josh Kupershmidt <schmiddy@gmail.com>
Cc: pgsql-performance@postgresql.org, Matt Klein <matt@locately.com>
Subject: Re: COMMIT stuck for days after bulk delete
Date: Tue, 14 Jan 2014 12:36:01 -0500
Message-ID: <19782.1389720961@sss.pgh.pa.us> (raw)
In-Reply-To: <CAK3UJRHFhwnVyK-U6=aNmp=XU_ro-FtDygH6mo0NJusQffzxTA@mail.gmail.com>
References: <CAK3UJRHFhwnVyK-U6=aNmp=XU_ro-FtDygH6mo0NJusQffzxTA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Josh Kupershmidt <schmiddy@gmail.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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, schmiddy@gmail.com, matt@locately.com
Subject: Re: COMMIT stuck for days after bulk delete
In-Reply-To: <19782.1389720961@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox