Thread: ALTER TABLE ... DISABLE TRIGGER vs. AccessExclusiveLock
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
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