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

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

> "Thomas F. O'Connell" <tfo@sitening.com> 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)