Re: CREATE TABLE (with INHERITS) and ACCESS EXCLUSIVE locks - Mailing list pgsql-general

From Tom Lane
Subject Re: CREATE TABLE (with INHERITS) and ACCESS EXCLUSIVE locks
Date
Msg-id 14860.1144187581@sss.pgh.pa.us
Whole thread Raw
In response to Re: CREATE TABLE (with INHERITS) and ACCESS EXCLUSIVE locks  ("Thomas F. O'Connell" <tfo@sitening.com>)
Responses Re: CREATE TABLE (with INHERITS) and ACCESS EXCLUSIVE locks  ("Thomas F. O'Connell" <tfo@sitening.com>)
List pgsql-general
"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

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Spurious rows returned with left join?
Next
From: Edmund Bacon
Date:
Subject: Re: Spurious rows returned with left join?