From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Bryce Nesbitt <bryce2(at)obviously(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better) |
Date: | 2009-09-22 17:35:39 |
Message-ID: | 13526.1253640939@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Bryce Nesbitt <bryce2(at)obviously(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Bryce Nesbitt | 2009-09-23 00:10:18 | Re: AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better) |
Previous Message | Bryce Nesbitt | 2009-09-22 16:55:52 | AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better) |