From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bryce Nesbitt <bryce2@obviously.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better)
Date: Tue, 22 Sep 2009 13:35:39 -0400
Message-ID: <13526.1253640939@sss.pgh.pa.us> (raw)
In-Reply-To: <4AB90198.2010109@obviously.com>
References: <4AB90198.2010109@obviously.com>
Bryce Nesbitt <bryce2@obviously.com> writes:
> 1) Why the AccessExclusiveLock on create table?
It has to install a trigger on the referenced table. There has been
some discussion that maybe CREATE TRIGGER could take just ExclusiveLock
and not AccessExclusiveLock, but it hasn't been done yet; and I'm not
sure how much that would help you anyway. It would only help if the
referenced table (contexts) is essentially read-only to the rest of
your workload, else it'll block anyhow.
> 2) Why is the foreign key check a heavy operation, since a new table
> will have zero foreign keys, it can't possibly violate the constraint yet.
It's not a heavy operation in that case. The problem doubtless is that
it's backed up behind some other transaction that is sitting on a lock
on the contexts table. And then everything else backs up behind it.
> 3) Other than eliminating dynamic table creation, how can this operation
> be altered?
Get rid of long-running transactions that hold locks on the contexts
table.
regards, tom lane
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-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, bryce2@obviously.com
Subject: Re: AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better)
In-Reply-To: <13526.1253640939@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