Thread: ALTER TABLE ... DISABLE TRIGGER vs. AccessExclusiveLock

ALTER TABLE ... DISABLE TRIGGER vs. AccessExclusiveLock

From
James Robinson
Date:
Hackers,

Experience and a read through backend/commands/tablecmds.c's  
AlterTable() indicate that ALTER TABLE ... DISABLE TRIGGER obtains an  
exclusive lock on the table (as does any ALTER TABLE).

Blocking other readers from a table when we've, within the body of a  
transaction performing a bulk update operation where we don't want /  
need triggers to fire, seems at first glance to be over-kill. I can  
see how AlterTable()'s complex logic is made less complex through 'get  
and keep a big lock', since most of its operational modes really do  
need exclusive access, but is it strictly required for ... DISABLE /  
REENABLE TRIGGER?

Could, say, RowExclusiveLock hypothetically provide adequate  
protection, allowing concurrent reads, but blocking out any other  
writers (for ENABLE / DISABLE TRIGGER) -- such as if driven through a  
new statement other than ALTER TABLE -- such as "DISABLE TRIGGER foo  
ON tbar" ?

Thanks!
----
James Robinson
Socialserve.com



Re: ALTER TABLE ... DISABLE TRIGGER vs. AccessExclusiveLock

From
Robert Haas
Date:
On Tue, Jul 27, 2010 at 3:07 PM, James Robinson
<jlrobins@socialserve.com> wrote:
> Experience and a read through backend/commands/tablecmds.c's AlterTable()
> indicate that ALTER TABLE ... DISABLE TRIGGER obtains an exclusive lock on
> the table (as does any ALTER TABLE).
>
> Blocking other readers from a table when we've, within the body of a
> transaction performing a bulk update operation where we don't want / need
> triggers to fire, seems at first glance to be over-kill. I can see how
> AlterTable()'s complex logic is made less complex through 'get and keep a
> big lock', since most of its operational modes really do need exclusive
> access, but is it strictly required for ... DISABLE / REENABLE TRIGGER?
>
> Could, say, RowExclusiveLock hypothetically provide adequate protection,
> allowing concurrent reads, but blocking out any other writers (for ENABLE /
> DISABLE TRIGGER) -- such as if driven through a new statement other than
> ALTER TABLE -- such as "DISABLE TRIGGER foo ON tbar" ?

Funny you should mention this.  There is a pending patch to do
something very much along these line.

https://commitfest.postgresql.org/action/patch_view?id=347

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company