Thread: Disable Triggers
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) -- 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 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