Disable Triggers

On Feb 21, 2008, at 10:20 AM, Terry Lee Tucker wrote:

Greetings:

We have been working diligently toward integrating Slony into our
production
databases. We've been having trouble with various tables, although
being
replicated perfectly in the initial replication stage, afterwards,
getting
out of sync.

I have finally figured out what the problem is. We have a Perl
process that
continually updates certain columns across all databases. That Perl
process
calls a function we have written called disable_triggers which updates
pg_class, setting reltriggers to 0 for the given table, and then
later, after
the work is complete, resetting reltriggers to the original value.
Unfortunately, during this process, the Slony trigger is disabled
as well
which is causing our problem.

My questions is this: how would I go about changing my function so
that all
the triggers EXCEPT the Slony trigger would be disabled? Any ideas?

Version:
PostgreSQL 7.4.19 on i686-redhat-linux-gnu, compiled by GCC gcc
(GCC) 3.4.6
20060404 (Red Hat 3.4.6-9)

Couldn't your triggers check some flag to determine if they should
continue?

Cheers,
M

On Thursday 21 February 2008 11:26, A.M. wrote:

On Feb 21, 2008, at 10:20 AM, Terry Lee Tucker wrote:

Greetings:

We have been working diligently toward integrating Slony into our
production
databases. We've been having trouble with various tables, although
being
replicated perfectly in the initial replication stage, afterwards,
getting
out of sync.

I have finally figured out what the problem is. We have a Perl
process that
continually updates certain columns across all databases. That Perl
process
calls a function we have written called disable_triggers which updates
pg_class, setting reltriggers to 0 for the given table, and then
later, after
the work is complete, resetting reltriggers to the original value.
Unfortunately, during this process, the Slony trigger is disabled
as well
which is causing our problem.

My questions is this: how would I go about changing my function so
that all
the triggers EXCEPT the Slony trigger would be disabled? Any ideas?

Version:
PostgreSQL 7.4.19 on i686-redhat-linux-gnu, compiled by GCC gcc
(GCC) 3.4.6
20060404 (Red Hat 3.4.6-9)

Couldn't your triggers check some flag to determine if they should
continue?

Cheers,
M

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

--
Thanks for the response. I do, in fact have a different function which can
disable any trigger by trigger name which works by creating an entry in a
table where, when the given trigger does fire, it checks for an entry in the
table at the top of the trigger and takes the appropiate action. The problem
is that the solution for disabling all triggers is used in several utility
programs and I'm trying to avoid changing a bunch of code.
I appreciate your input.

Work: 1-336-372-6812
Cell: 1-336-404-6987
email: terry@chosen-ones.org

On Thu, Feb 21, 2008 at 11:44:25AM -0500, Terry Lee Tucker wrote:

table where, when the given trigger does fire, it checks for an entry in the
table at the top of the trigger and takes the appropiate action. The problem
is that the solution for disabling all triggers is used in several utility
programs and I'm trying to avoid changing a bunch of code.
I appreciate your input.

Well, you could try rewriting the function to disable all but the Slony
trigger. But there's something else wrong here.

I seem to recall that we found some code path where reltriggers wasn't
checked properly anyway, so disabling triggers wouldn't work exactly as you
are doing it. This was part of the reason for the catalogue-breaking oid
fiddling Slony does on replicated tables, IIRC. So I'm not even sure your
current approach will work reliably as you think.

Probably the right answer, I'm afraid, is to change your trigger functions
to fire more selectively, then make the disable trigger function a no-op (so
you don't have to change all your other code right now).

A

On Thursday 21 February 2008 12:20, Andrew Sullivan wrote:

Well, you could try rewriting the function to disable all but the Slony
trigger. But there's something else wrong here.

I seem to recall that we found some code path where reltriggers wasn't
checked properly anyway, so disabling triggers wouldn't work exactly as you
are doing it. This was part of the reason for the catalogue-breaking oid
fiddling Slony does on replicated tables, IIRC. So I'm not even sure your
current approach will work reliably as you think.

Probably the right answer, I'm afraid, is to change your trigger functions
to fire more selectively, then make the disable trigger function a no-op
(so you don't have to change all your other code right now).

A

Thanks for the input. I've been using the reltriggers in pg_class for a long
time and it does work; however, I did notice in the documentation on
pg_trigger that tgenabled is not checked properly and using that will give
inconsistant results. We have several valid reasons for disabling all
triggers that I won't elaborate here.

Unless I get a better idea, I'm going to change the disable_triggers function
to duplicate all the records in pg_trigger belonging to a given table, delete
the records except for the Slony trigger, update pg_class setting reltriggers
to 1, do the work, and then restore everything with a call to
enable_triggers. Does this sound reasonable to you?

--
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987
terry@turbocorp.com
www.turbocorp.com

On Thu, Feb 21, 2008 at 9:20 AM, Terry Lee Tucker <terry@turbocorp.com> wrote:

Greetings:

We have been working diligently toward integrating Slony into our production
databases. We've been having trouble with various tables, although being
replicated perfectly in the initial replication stage, afterwards, getting
out of sync.

I have finally figured out what the problem is. We have a Perl process that
continually updates certain columns across all databases. That Perl process
calls a function we have written called disable_triggers which updates
pg_class, setting reltriggers to 0 for the given table, and then later, after
the work is complete, resetting reltriggers to the original value.
Unfortunately, during this process, the Slony trigger is disabled as well
which is causing our problem.

Disabling all triggers is not something you do on a live, running
database with users accessing and possibly changing it, it's something
you do to a database during maintenance when no one else is connected.
You'll have to go with the solution you talked about, i.e. disabling
individual triggers by name, etc...

Andrew Sullivan <ajs@crankycanuck.ca> writes:

On Thu, Feb 21, 2008 at 11:44:25AM -0500, Terry Lee Tucker wrote:

table where, when the given trigger does fire, it checks for an entry in the
table at the top of the trigger and takes the appropiate action. The problem
is that the solution for disabling all triggers is used in several utility
programs and I'm trying to avoid changing a bunch of code.
I appreciate your input.

Well, you could try rewriting the function to disable all but the Slony
trigger. But there's something else wrong here.

I seem to recall that we found some code path where reltriggers wasn't
checked properly anyway, so disabling triggers wouldn't work exactly as you
are doing it.

No, reltriggers is reliable as a disable-all-triggers mechanism; when
it's zero the code won't even look in pg_trigger. But you can't use it
to disable just some triggers. I think the bug you are remembering is
that there's always been a pg_trigger.tgenabled field, but it wasn't
always honored everywhere, so it was unreliable as a selective-disable
mechanism until some recent release (I don't recall which, but I'm
afraid 7.4 is too old).

regards, tom lane

On Thu, Feb 21, 2008 at 12:49:48PM -0500, Terry Lee Tucker wrote:

Thanks for the input. I've been using the reltriggers in pg_class for a long
time and it does work; however, I did notice in the documentation on
pg_trigger that tgenabled is not checked properly and using that will give
inconsistant results. We have several valid reasons for disabling all
triggers that I won't elaborate here.

I'm not arguing that you have those valid reasons. I'm just warning you
that your success so far with this strategy does not guarantee future
results.

Unless I get a better idea, I'm going to change the disable_triggers function
to duplicate all the records in pg_trigger belonging to a given table, delete
the records except for the Slony trigger, update pg_class setting reltriggers
to 1, do the work, and then restore everything with a call to
enable_triggers. Does this sound reasonable to you?

I expect you're going to have to get everyone to disconnect after that,
because the triggers oids will all have changed and you'll get errors to
that effect. Also, are there these triggers on the slony replicas? You
really need to be doing DROP TRIGGER/STORE TRIGGER operations if so.
Otherwise, very surprising things may happen.

A

On Thu, Feb 21, 2008 at 01:03:13PM -0500, Tom Lane wrote:

to disable just some triggers. I think the bug you are remembering is
that there's always been a pg_trigger.tgenabled field, but it wasn't
always honored everywhere,

You're quite right. My apologies. (Especially since I've now repeated the
warning.)

A

On Thursday 21 February 2008 12:56, Scott Marlowe wrote:

On Thu, Feb 21, 2008 at 9:20 AM, Terry Lee Tucker <terry@turbocorp.com>

wrote:

Greetings:

We have been working diligently toward integrating Slony into our
production databases. We've been having trouble with various tables,
although being replicated perfectly in the initial replication stage,
afterwards, getting out of sync.

I have finally figured out what the problem is. We have a Perl process
that continually updates certain columns across all databases. That Perl
process calls a function we have written called disable_triggers which
updates pg_class, setting reltriggers to 0 for the given table, and then
later, after the work is complete, resetting reltriggers to the original
value. Unfortunately, during this process, the Slony trigger is disabled
as well which is causing our problem.

Disabling all triggers is not something you do on a live, running
database with users accessing and possibly changing it, it's something
you do to a database during maintenance when no one else is connected.
You'll have to go with the solution you talked about, i.e. disabling
individual triggers by name, etc...

I have failed to mention that we are disabling all the triggers on a given
table only done during a transaction; thus, it affects no one else.

Thanks for the input...
--
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987
terry@turbocorp.com
www.turbocorp.com

On Thursday 21 February 2008 13:05, Andrew Sullivan wrote:

Unless I get a better idea, I'm going to change the disable_triggers
function to duplicate all the records in pg_trigger belonging to a given
table, delete the records except for the Slony trigger, update pg_class
setting reltriggers to 1, do the work, and then restore everything with a
call to
enable_triggers. Does this sound reasonable to you?

I expect you're going to have to get everyone to disconnect after that,
because the triggers oids will all have changed and you'll get errors to
that effect. Also, are there these triggers on the slony replicas? You
really need to be doing DROP TRIGGER/STORE TRIGGER operations if so.
Otherwise, very surprising things may happen.

Gee, I hadn't thought about that. Back to the drawing board...
Thanks for the help.

--
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987
terry@turbocorp.com
www.turbocorp.com

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

My questions is this: how would I go about changing my function so
that all the triggers EXCEPT the Slony trigger would be disabled?
Any ideas?

..

PostgreSQL 7.4.19 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6

This won't help you immediately, but you might want to look at the new
enable replica replica trigger functionality added in 8.3 (thanks Jan!):

http://www.postgresql.org/docs/current/static/sql-altertable.html

Could be more ammo to get you off of that old 7.4 :)

I have failed to mention that we are disabling all the triggers on
a given table only done during a transaction; thus, it affects no
one else.

Be careful: if you are directly manipulating the system tables,
you still run the risk of problems as the system tables are
not completely MVCC safe unless you lock them.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200802211338
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAke9xUQACgkQvJuQZxSWSsifbACffN6/ohNCwvkvZ10Uvamyg264
nckAnRarfpLgrZYkLe6Q/FSW+edC2hQC
=9GqX
-----END PGP SIGNATURE-----

Tom Lane wrote:

Andrew Sullivan <ajs@crankycanuck.ca> writes:

On Thu, Feb 21, 2008 at 11:44:25AM -0500, Terry Lee Tucker wrote:

table where, when the given trigger does fire, it checks for an entry in the
table at the top of the trigger and takes the appropiate action. The problem
is that the solution for disabling all triggers is used in several utility
programs and I'm trying to avoid changing a bunch of code.
I appreciate your input.

Well, you could try rewriting the function to disable all but the Slony
trigger. But there's something else wrong here.

I seem to recall that we found some code path where reltriggers wasn't
checked properly anyway, so disabling triggers wouldn't work exactly as you
are doing it.

No, reltriggers is reliable as a disable-all-triggers mechanism; when
it's zero the code won't even look in pg_trigger. But you can't use it
to disable just some triggers. I think the bug you are remembering is
that there's always been a pg_trigger.tgenabled field, but it wasn't
always honored everywhere, so it was unreliable as a selective-disable
mechanism until some recent release (I don't recall which, but I'm
afraid 7.4 is too old).

How might we find out which release it was fixed in? Back patching
7.4.19 with the fix might be easier then trying to move up to the fixed
version.

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
- Benjamin Franklin

On Thu, Feb 21, 2008 at 04:35:28PM -0500, Geoffrey wrote:

How might we find out which release it was fixed in? Back patching
7.4.19 with the fix might be easier then trying to move up to the fixed
version.

According to HISTORY, there was a significant fix in this area in 8.1:

* Add "ALTER TABLE ENABLE/DISABLE TRIGGER" to disable triggers
(Satoshi Nagayasu)

I think your chances of successfully back-porting something like that from
8.1 to 7.4.x are way lower than your chances of fixing your application to
use a later database system. Also, if you get off 7.4, you get rid of the
horrifying checkpoint storms in that version, and get a whack of other
improvements and bugfixes.

A

On Thursday 21 February 2008 17:07, Andrew Sullivan wrote:

On Thu, Feb 21, 2008 at 04:35:28PM -0500, Geoffrey wrote:

How might we find out which release it was fixed in? Back patching
7.4.19 with the fix might be easier then trying to move up to the fixed
version.

According to HISTORY, there was a significant fix in this area in 8.1:

* Add "ALTER TABLE ENABLE/DISABLE TRIGGER" to disable triggers
(Satoshi Nagayasu)

I think your chances of successfully back-porting something like that from
8.1 to 7.4.x are way lower than your chances of fixing your application to
use a later database system. Also, if you get off 7.4, you get rid of the
horrifying checkpoint storms in that version, and get a whack of other
improvements and bugfixes.

Yea, upgrading is slated to begin in April. We needed to get replication going
now. You've been a big help. Thanks...
--
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987
terry@turbocorp.com
www.turbocorp.com

Terry Lee Tucker wrote:

Greetings:

We have been working diligently toward integrating Slony into our production
databases. We've been having trouble with various tables, although being
replicated perfectly in the initial replication stage, afterwards, getting
out of sync.

I have finally figured out what the problem is. We have a Perl process that
continually updates certain columns across all databases. That Perl process
calls a function we have written called disable_triggers which updates
pg_class, setting reltriggers to 0 for the given table, and then later, after
the work is complete, resetting reltriggers to the original value.
Unfortunately, during this process, the Slony trigger is disabled as well
which is causing our problem.

My questions is this: how would I go about changing my function so that all
the triggers EXCEPT the Slony trigger would be disabled? Any ideas?

Version:
PostgreSQL 7.4.19 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6
20060404 (Red Hat 3.4.6-9)

Me thinks you forgot to mention that you are working on implementing
this on Postgresql 8.3.1.

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
- Benjamin Franklin