Search Postgresql Archives

Re: CREATE TABLE (with INHERITS) and ACCESS EXCLUSIVE locks

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On Apr 4, 2006, at 4:53 PM, Tom Lane wrote:

"Thomas F. O'Connell" <tfo@xxxxxxxxxxxx> writes:
As for how this plays out in the real world, a pg_dumpall will start
and run for a few hours. Sometime during that, this function might
get called. When it does, an ACCESS EXCLUSIVE lock is held against
the table identified as t13, here directly referenced only as a
FOREIGN KEY.

It's the addition of a foreign key constraint that's biting you.  That
requires installing triggers on the pre-existing table (t13, also t14
in your example), and that requires an exclusive lock.

Since we don't currently allow any ON SELECT triggers, it's possible
that adding a trigger could be downgraded to just ExclusiveLock (which
wouldn't conflict with pg_dump's AccessShareLock), but I can't say that
I'm enthusiastic about that idea.

			regards, tom lane

Thanks! At least we can create a workaround for the moment...

I've brought this up to an extent in the past, but is there an easy way to extend section 12.3.1 (or create some form of appendix) such that it reveals all possible locking paths for SQL commands in postgres? I've had a number of application design (actually, more often debugging) scenarios where it would be helpful to have a full reference that showed which locks were acquired by given commands or constructs and in which order.

From this specific instance, it seems like it wouldn't be too tough to patch the docs to include something like "[ FOREIGN KEY ] REFERENCES, when used with CREATE TABLE" to the ACCESS EXCLUSIVE section of 12.3.1.

But I'd be as interested to have the detail visually available for all SQL commands. E.g., that when foreign key constraints are created that they install triggers, and that that process requires ACCESS EXCLUSIVE locking. I knew (from familiarity with postgres) that referential integrity was trigger-based, but I didn't know (and don't see any way of knowing from the docs) that it required ACCESS EXCLUSIVE locking.

I'd be happy to contribute to a chart or diagram of something like this if developers could give me some reasonable starting points and don't think this idea is so unwieldy as to be ultimately unworkable.

--
Thomas F. O'Connell
Database Architecture and Programming
Co-Founder
Sitening, LLC

http://www.sitening.com/
3004 B Poston Avenue
Nashville, TN 37203-1314
615-260-0005 (cell)
615-469-5150 (office)
615-469-5151 (fax)




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux