Thread: disable triggers isolated to transaction only?

I'm planning to split a large table into partitions.  During the
migration, all new data will be added to the sub-tables, and I will be
moving the data from the master table to the proper sub tables at the
same time.  The trick is that I have an INSERT trigger to keep track
of various counters.  I need to be able to disable that trigger for
the data I'm moving, yet leave it intact for the new data being
inserted.

My question is this: will ALTER TABLE ONLY $subtable DISABLE TRIGGER
ALL within a transaction only affect my transaction, or will it affect
anyone inserting into this subtable.  If it blocks external inserts
that's ok since my transactions are small while moving the data.  I
guess at worse I lock the table.

On Mar 2, 2010, at 9:48 AM, Vick Khera wrote:

>   I guess at worse I lock the table.

Before you go there, assuming you cannot just disable a trigger for a session, then depending on how many counters your
inserttrigger modifies, it might be better to simply undo the trigger's effects in the same transaction as the
migration. 

Vick Khera <vivek@khera.org> writes:
> My question is this: will ALTER TABLE ONLY $subtable DISABLE TRIGGER
> ALL within a transaction only affect my transaction, or will it affect
> anyone inserting into this subtable.  If it blocks external inserts
> that's ok since my transactions are small while moving the data.  I
> guess at worse I lock the table.

Yeah, ALTER TABLE will lock the table anyway.  As long as you re-enable
the triggers before committing, it won't affect any other transaction.

            regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> My question is this: will ALTER TABLE ONLY $subtable DISABLE TRIGGER
> ALL within a transaction only affect my transaction, or will it affect
> anyone inserting into this subtable.  If it blocks external inserts
> that's ok since my transactions are small while moving the data.  I
> guess at worse I lock the table.

ALTER TABLE will lock and block, but I'd be remiss if I didn't point
out the use of session_replication_role as a much better solution to
this particular class of problem. (Even if your version does not
support it, Vick, it should be noted here for the archives). The
session_replication_role was added in 8.3:

http://www.postgresql.org/docs/8.3/interactive/sql-altertable.html

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201003031020
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkuOhDYACgkQvJuQZxSWSsiPxwCg1JGjrfxvv0gmJDJPGCd2pLdE
X0sAn3t+IYPnAIPcZqqxtBIaUUbkm1jL
=US8W
-----END PGP SIGNATURE-----

On 03/03/10 15:46, Greg Sabino Mullane wrote:
> ALTER TABLE will lock and block, but I'd be remiss if I didn't point
> out the use of session_replication_role as a much better solution to
> this particular class of problem. (Even if your version does not
> support it, Vick, it should be noted here for the archives). The
> session_replication_role was added in 8.3:
>
> http://www.postgresql.org/docs/8.3/interactive/sql-altertable.html

That wouldn't have occurred to me. Definitely worth adding to the archives.

--
   Richard Huxton
   Archonet Ltd