Re: [GENERAL] disable triggers using psql
Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 The saga continues. I've reverted to a multi-step process to try and figure this out. I create the initial database, then load it from the command line psql as follows: pro-# \set session_replication_role replica; pro-# \o db.out pro-# \i dump.txt This is a database set, not a psql on, so you do not want the backslash before the "set". SET session_replication_role = replica; I'd recommend adding a: SHOW session_replication_role; to the dump.txt as a sanity check. For the sake of completeness, I've attempted the above, same result. We have decided to take a different approach and attempt to clean up the data in the database, then convert. I do appreciate all the time you've devoted to this. There must be something in the dump that is causing these issues. Thanks again Greg. I'll certainly update the list once we have a working solution. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201102211529 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk1iy74ACgkQvJuQZxSWSsgWQACgrxKDvN/yCZD5GZJvlqFMyyIC 9mwAnjOMJ9QDRa3IoiBCvaS9mT5sMR6f =JYCs -END PGP SIGNATURE- -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > The saga continues. I've reverted to a multi-step process to try and > figure this out. I create the initial database, then load it from the > command line psql as follows: > > pro-# \set session_replication_role replica; > pro-# \o db.out > pro-# \i dump.txt This is a database set, not a psql on, so you do not want the backslash before the "set". SET session_replication_role = replica; I'd recommend adding a: SHOW session_replication_role; to the dump.txt as a sanity check. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201102211529 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk1iy74ACgkQvJuQZxSWSsgWQACgrxKDvN/yCZD5GZJvlqFMyyIC 9mwAnjOMJ9QDRa3IoiBCvaS9mT5sMR6f =JYCs -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I'm not sure how to address this. I'm not exactly sure where to place session_replication_role. It's very close to the top of the file: Is this a pg_dumpall? A \connect later on will reset the session_replication_role. If so, add the SET right after the \connect. Alternatively, you could create a special user to invoke psql as, which has: ALTER USER dangerous_bob SET session_replication_role = replica; Be *very* careful with that account though, as using it for anything other than this special case could be very bad. The saga continues. I've reverted to a multi-step process to try and figure this out. I create the initial database, then load it from the command line psql as follows: pro-# \set session_replication_role replica; pro-# \o db.out pro-# \i dump.txt I still get: psql:dump.txt:2077301: ERROR: insert or update on table "cust" violates foreign key constraint "$1" DETAIL: Key (country,state)=(US,GA) is not present in table "state". So there's something in the dump that's changing the session_replication_role? - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201102181408 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk1ew9MACgkQvJuQZxSWSshETwCg2oEEicHhokORuQRl3sxkLkpj ghIAnRe02LCuyyRlyzvKZ67QCYUyfPzC =H9Wb -END PGP SIGNATURE- -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I'm not sure how to address this. I'm not exactly sure where to place session_replication_role. It's very close to the top of the file: Is this a pg_dumpall? A \connect later on will reset the session_replication_role. If so, add the SET right after the \connect. Alternatively, you could create a special user to invoke psql as, which has: It is a pg_dump. There is no \connect in the code. ALTER USER dangerous_bob SET session_replication_role = replica; Be *very* careful with that account though, as using it for anything other than this special case could be very bad. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201102181408 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk1ew9MACgkQvJuQZxSWSshETwCg2oEEicHhokORuQRl3sxkLkpj ghIAnRe02LCuyyRlyzvKZ67QCYUyfPzC =H9Wb -END PGP SIGNATURE- -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 cut and paste: set ON_ERROR_ROLLBACK; Should be \set ON_ERROR_ROLLBACK on You can also set this when calling psql like so: psql --set ON_ERROR_ROLLBACK=on But that's getting off-topic now, as we've got the problem narrowed: INSERT 0 1 This shows the session_replication_role is working as it should. Double check where and how you are setting it; your foreign key problems will go away once it is set correctly. Okay, thanks. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201102181243 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk1esCsACgkQvJuQZxSWSsh5JgCeK/Mk+e598LAhDsYvNmTCWM8E F+sAoN9YX32TFKF/5YDp3CoNBwfnbnqb =u8rB -END PGP SIGNATURE- -- Geoffrey Myers Myers Consulting Inc. 770.592.1651 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
li...@serioustechnology.com (Geoffrey Myers) writes: > Greg Sabino Mullane wrote: >> -BEGIN PGP SIGNED MESSAGE- >> Hash: RIPEMD160 >> >> >>> cut and paste: >>> >>> set ON_ERROR_ROLLBACK; >> >> Should be >> >> \set ON_ERROR_ROLLBACK on >> >> You can also set this when calling psql like so: >> >> psql --set ON_ERROR_ROLLBACK=on >> >> But that's getting off-topic now, as we've got the problem narrowed: >> >>> INSERT 0 1 >> >> This shows the session_replication_role is working as it >> should. Double check where and how you are setting it; your foreign >> key problems will go away once it is set correctly. > > I'm not sure how to address this. I'm not exactly sure where to place > session_replication_role. It's very close to the top of the file: > > -- > -- PostgreSQL database dump > -- > > SET client_encoding = 'UTF-8'; > SET standard_conforming_strings = off; > SET check_function_bodies = false; > SET client_min_messages = warning; > SET escape_string_warning = off; > > set session_replication_role = replica; > > I'm still getting the errors. If it doesn't belong at the beginning > of this process, I'm not exactly sure where it should go. Hmm. Are you sure 'replica' is the right value to set for session_replication_role? I'd expect that when pulling in data from pg_dump, that 'local' might be the right value, since pg_dump isn't acting as a replication manager. Don't trust me blindly on this - I could be wrong - but you should certainly validate that you're setting that role GUC appropriately. -- select 'cbbrowne' || '@' || 'gmail.com'; http://linuxfinances.info/info/slony.html You shouldn't anthropomorphize computers; they don't like it. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > I'm not sure how to address this. I'm not exactly sure where to place > session_replication_role. It's very close to the top of the file: Is this a pg_dumpall? A \connect later on will reset the session_replication_role. If so, add the SET right after the \connect. Alternatively, you could create a special user to invoke psql as, which has: ALTER USER dangerous_bob SET session_replication_role = replica; Be *very* careful with that account though, as using it for anything other than this special case could be very bad. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201102181408 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk1ew9MACgkQvJuQZxSWSshETwCg2oEEicHhokORuQRl3sxkLkpj ghIAnRe02LCuyyRlyzvKZ67QCYUyfPzC =H9Wb -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 cut and paste: set ON_ERROR_ROLLBACK; Should be \set ON_ERROR_ROLLBACK on You can also set this when calling psql like so: psql --set ON_ERROR_ROLLBACK=on But that's getting off-topic now, as we've got the problem narrowed: INSERT 0 1 This shows the session_replication_role is working as it should. Double check where and how you are setting it; your foreign key problems will go away once it is set correctly. I'm not sure how to address this. I'm not exactly sure where to place session_replication_role. It's very close to the top of the file: -- -- PostgreSQL database dump -- SET client_encoding = 'UTF-8'; SET standard_conforming_strings = off; SET check_function_bodies = false; SET client_min_messages = warning; SET escape_string_warning = off; set session_replication_role = replica; I'm still getting the errors. If it doesn't belong at the beginning of this process, I'm not exactly sure where it should go. -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > cut and paste: > > set ON_ERROR_ROLLBACK; Should be \set ON_ERROR_ROLLBACK on You can also set this when calling psql like so: psql --set ON_ERROR_ROLLBACK=on But that's getting off-topic now, as we've got the problem narrowed: > INSERT 0 1 This shows the session_replication_role is working as it should. Double check where and how you are setting it; your foreign key problems will go away once it is set correctly. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201102181243 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk1esCsACgkQvJuQZxSWSsh5JgCeK/Mk+e598LAhDsYvNmTCWM8E F+sAoN9YX32TFKF/5YDp3CoNBwfnbnqb =u8rB -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 So I added the on_error_rollback to the script and I get this: ... psql:test.sql:12: ERROR: current transaction is aborted, commands ignored until end of transaction block That isn't right: are you sure you said ON_ERROR_ROLLBACK? It's case-sensitive. Anyway, try this shortened version: cut and paste: set ON_ERROR_ROLLBACK; When I try the below, I get: BEGIN psql:test.sql:3: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "abc_pkey" for table "abc" CREATE TABLE CREATE TABLE SET ?column? --- No error: (1 row) INSERT 0 1 b --- 2 (1 row) ROLLBACK BEGIN; CREATE TEMP TABLE abc (a INT PRIMARY KEY); CREATE TEMP TABLE def (b INT NOT NULL REFERENCES abc(a)); SET session_replication_role = replica; SELECT 'No error:'; INSERT INTO def(b) VALUES (2); SELECT * FROM def; ROLLBACK; - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201102180938 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk1ehJEACgkQvJuQZxSWSsj/5gCgjsQa+nzZz26xQ7c70Bxl5Hs3 AuUAn1uD7MY2BtGR7usl45pC3Yv2pqVS =mLCm -END PGP SIGNATURE- -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > So I added the on_error_rollback to the script and I get this: ... > psql:test.sql:12: ERROR: current transaction is aborted, commands > ignored until end of transaction block That isn't right: are you sure you said ON_ERROR_ROLLBACK? It's case-sensitive. Anyway, try this shortened version: BEGIN; CREATE TEMP TABLE abc (a INT PRIMARY KEY); CREATE TEMP TABLE def (b INT NOT NULL REFERENCES abc(a)); SET session_replication_role = replica; SELECT 'No error:'; INSERT INTO def(b) VALUES (2); SELECT * FROM def; ROLLBACK; - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201102180938 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk1ehJEACgkQvJuQZxSWSsj/5gCgjsQa+nzZz26xQ7c70Bxl5Hs3 AuUAn1uD7MY2BtGR7usl45pC3Yv2pqVS =mLCm -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 psql:test.sql:11: ERROR: current transaction is aborted, commands ignored until end of transaction block Oops my bad, I forgot to tell you I have \set ON_ERROR_ROLLBACK on in my .psqlrc. So you'll need to add that to the top of the script. Or just comment out the first insert and see if the second one works. If it doesn't, something weird is going on with Postgres. If it does, something weird is going on with your script and I would recommend breaking your dump script down into smaller pieces to see what is happening. Most likely session_replication_role is not getting set or is getting reset somewhere. So I added the on_error_rollback to the script and I get this: BEGIN psql:test.sql:4: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "abc_pkey" for table "abc" CREATE TABLE CREATE TABLE ?column? -- Error: (1 row) psql:test.sql:10: ERROR: insert or update on table "def" violates foreign key constraint "def_b_fkey" DETAIL: Key (b)=(1) is not present in table "abc". psql:test.sql:12: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:test.sql:14: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:test.sql:16: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:test.sql:18: ERROR: current transaction is aborted, commands ignored until end of transaction block ROLLBACK - -- Greg Sabino Mullane g...@endpoint.com g...@turnstep.com End Point Corporation 610-983-9073 PGP Key: 0x14964AC8 201102172155 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk1d360ACgkQvJuQZxSWSsidCQCfTnQxp5w6psa3C9NREX0ecZ+j Ft0An2JKofuxVJNwxhVkh4NBTJU3Xcom =fLDa -END PGP SIGNATURE- -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 set local session_replication_role = replica; But that does not seem provide the expected relief. How exactly did this fail? This should absolutely disable all triggers for you, unless you've mucked with the triggers and set them to replica. I received the following error: ERROR: insert or update on table "customer" violates foreign key constraint "$1" Try removing the 'local'; you may be spanning multiple transactions. If this is a script you are feeding directly to psql, you can also add a BEGIN; at the top or just use the -1 argument. I actually manually wrapped the whole thing in a transaction, but I'll give your suggestion a shot. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201102171551 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk1diwUACgkQvJuQZxSWSshcPgCdGuHIe4bINl/BmoKW89YmQscD IdAAnA8bwbzmMKssCga9G0dpSh1GopzD =khQx -END PGP SIGNATURE- -- Geoffrey Myers Myers Consulting Inc. 770.592.1651 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I thought we had tried this before, but with an embedded BEGIN. I get the same result, although I used the -1 switch instead of the BEGIN/COMMIT: psql:backup.txt:2077303: ERROR: insert or update on table "customer" violates foreign key constraint "$1" Hmm..are we running a modern Postgres? 8.3.13 Perhaps see if the following script works with a single error: BEGIN; CREATE TEMP TABLE abc (a INT PRIMARY KEY); CREATE TEMP TABLE def (b INT NOT NULL REFERENCES abc(a)); SELECT 'Error:'; INSERT INTO def(b) VALUES (1); SET session_replication_role = replica; SELECT 'No error:'; INSERT INTO def(b) VALUES (2); SELECT * FROM def; ROLLBACK; I get this: BEGIN psql:test.sql:3: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "abc_pkey" for table "abc" CREATE TABLE CREATE TABLE ?column? -- Error: (1 row) psql:test.sql:9: ERROR: insert or update on table "def" violates foreign key constraint "def_b_fkey" DETAIL: Key (b)=(1) is not present in table "abc". psql:test.sql:11: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:test.sql:13: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:test.sql:15: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:test.sql:17: ERROR: current transaction is aborted, commands ignored until end of transaction block ROLLBACK - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201102171745 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk1dpvQACgkQvJuQZxSWSsjvrgCgmiITSLnGyrBunVZTScc4HKvz Y3IAn1sYG4/BdM6XJpBAVMz6lU1WfUVH =XZcQ -END PGP SIGNATURE- -- Geoffrey Myers Myers Consulting Inc. 770.592.1651 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > psql:test.sql:11: ERROR: current transaction is aborted, commands > ignored until end of transaction block Oops my bad, I forgot to tell you I have \set ON_ERROR_ROLLBACK on in my .psqlrc. So you'll need to add that to the top of the script. Or just comment out the first insert and see if the second one works. If it doesn't, something weird is going on with Postgres. If it does, something weird is going on with your script and I would recommend breaking your dump script down into smaller pieces to see what is happening. Most likely session_replication_role is not getting set or is getting reset somewhere. - -- Greg Sabino Mullane g...@endpoint.com g...@turnstep.com End Point Corporation 610-983-9073 PGP Key: 0x14964AC8 201102172155 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk1d360ACgkQvJuQZxSWSsidCQCfTnQxp5w6psa3C9NREX0ecZ+j Ft0An2JKofuxVJNwxhVkh4NBTJU3Xcom =fLDa -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I thought we had tried this before, but with an embedded BEGIN. I get the same result, although I used the -1 switch instead of the BEGIN/COMMIT: psql:backup.txt:2077303: ERROR: insert or update on table "customer" violates foreign key constraint "$1" Hmm..are we running a modern Postgres? 8.3.13 Perhaps see if the following script works with a single error: BEGIN; CREATE TEMP TABLE abc (a INT PRIMARY KEY); CREATE TEMP TABLE def (b INT NOT NULL REFERENCES abc(a)); SELECT 'Error:'; INSERT INTO def(b) VALUES (1); SET session_replication_role = replica; SELECT 'No error:'; INSERT INTO def(b) VALUES (2); SELECT * FROM def; ROLLBACK; I get this: BEGIN psql:test.sql:3: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "abc_pkey" for table "abc" CREATE TABLE CREATE TABLE ?column? -- Error: (1 row) psql:test.sql:9: ERROR: insert or update on table "def" violates foreign key constraint "def_b_fkey" DETAIL: Key (b)=(1) is not present in table "abc". psql:test.sql:11: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:test.sql:13: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:test.sql:15: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:test.sql:17: ERROR: current transaction is aborted, commands ignored until end of transaction block ROLLBACK -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > I thought we had tried this before, but with an embedded BEGIN. I get > the same result, although I used the -1 switch instead of the BEGIN/COMMIT: > > psql:backup.txt:2077303: ERROR: insert or update on table "customer" > violates foreign key constraint "$1" Hmm..are we running a modern Postgres? Perhaps see if the following script works with a single error: BEGIN; CREATE TEMP TABLE abc (a INT PRIMARY KEY); CREATE TEMP TABLE def (b INT NOT NULL REFERENCES abc(a)); SELECT 'Error:'; INSERT INTO def(b) VALUES (1); SET session_replication_role = replica; SELECT 'No error:'; INSERT INTO def(b) VALUES (2); SELECT * FROM def; ROLLBACK; - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201102171745 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk1dpvQACgkQvJuQZxSWSsjvrgCgmiITSLnGyrBunVZTScc4HKvz Y3IAn1sYG4/BdM6XJpBAVMz6lU1WfUVH =XZcQ -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 set local session_replication_role = replica; But that does not seem provide the expected relief. How exactly did this fail? This should absolutely disable all triggers for you, unless you've mucked with the triggers and set them to replica. I received the following error: ERROR: insert or update on table "customer" violates foreign key constraint "$1" Try removing the 'local'; you may be spanning multiple transactions. If this is a script you are feeding directly to psql, you can also add a BEGIN; at the top or just use the -1 argument. I thought we had tried this before, but with an embedded BEGIN. I get the same result, although I used the -1 switch instead of the BEGIN/COMMIT: psql:backup.txt:2077303: ERROR: insert or update on table "customer" violates foreign key constraint "$1" DETAIL: Key (country,state)=(US,GA) is not present in table "state". psql:backup.txt:2077311: ERROR: current transaction is aborted, commands ignored until end of transaction block . . Just to clarify, I added this to the dump: set session_replication_role = replica; and ran the command: psql -1 -p $TARGETPORT -f $BACKUP -d $DB - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201102171551 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk1diwUACgkQvJuQZxSWSshcPgCdGuHIe4bINl/BmoKW89YmQscD IdAAnA8bwbzmMKssCga9G0dpSh1GopzD =khQx -END PGP SIGNATURE- -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 >>> set local session_replication_role = replica; >>> >>> But that does not seem provide the expected relief. >> >> How exactly did this fail? This should absolutely disable all >> triggers for you, unless you've mucked with the triggers >> and set them to replica. > I received the following error: > > ERROR: insert or update on table "customer" violates foreign key > constraint "$1" Try removing the 'local'; you may be spanning multiple transactions. If this is a script you are feeding directly to psql, you can also add a BEGIN; at the top or just use the -1 argument. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201102171551 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk1diwUACgkQvJuQZxSWSshcPgCdGuHIe4bINl/BmoKW89YmQscD IdAAnA8bwbzmMKssCga9G0dpSh1GopzD =khQx -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 We were trying to accomplish this without having to hack the dump to much. We attempted adding: set local session_replication_role = replica; But that does not seem provide the expected relief. How exactly did this fail? This should absolutely disable all triggers for you, unless you've mucked with the triggers and set them to replica. I received the following error: ERROR: insert or update on table "customer" violates foreign key constraint "$1" - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201102171053 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk1dRKIACgkQvJuQZxSWSsir0wCfQZmZkDrYBYVffyYBGYoqA/RT VRMAoLG497FaRU7gOkpM394UT7xksXzk =f9co -END PGP SIGNATURE- -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > We were trying to accomplish this without having to hack the dump to > much. We attempted adding: > > set local session_replication_role = replica; > > But that does not seem provide the expected relief. How exactly did this fail? This should absolutely disable all triggers for you, unless you've mucked with the triggers and set them to replica. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201102171053 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk1dRKIACgkQvJuQZxSWSsir0wCfQZmZkDrYBYVffyYBGYoqA/RT VRMAoLG497FaRU7gOkpM394UT7xksXzk =f9co -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
On Feb 17, 2011, at 6:59 AM, Geoffrey Myers wrote: >> Unless something very big changed when I wasn't looking, the >> constraints are actually implemented as triggers under the hood. But >> you're right that it'd be cleaner to drop the constraints and re-add >> them than to fool with system triggers. > > We were trying to accomplish this without having to hack the dump to much. > We attempted adding: > > set local session_replication_role = replica; > > But that does not seem provide the expected relief. If your triggers have some simple way of identifying them in a query on pg_trigger, the function below can be altered to easily enable or disable them. John DeSoi, Ph.D. = create or replace function enable_link_clean_triggers(p_enable boolean) returns void as $$ declare v_action text; v_sql text; v_tg record; begin if p_enable then v_action = ' ENABLE TRIGGER '; else v_action = ' DISABLE TRIGGER '; end if; for v_tg in select tgrelid, tgname from pg_trigger where tgname ~ '^tg_link_clean_.+' loop v_sql = 'ALTER TABLE ' || v_tg.tgrelid::regclass::text || v_action || v_tg.tgname || ';'; execute v_sql; end loop; return; end; $$ language plpgsql; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
Andrew Sullivan wrote: On Wed, Feb 16, 2011 at 10:08:53AM -0500, David Johnston wrote: I may be off-track here but triggers do not enforce referential integrity - constraints do. If you need to disable triggers you can do so via the ALTER TABLE command. Unless something very big changed when I wasn't looking, the constraints are actually implemented as triggers under the hood. But you're right that it'd be cleaner to drop the constraints and re-add them than to fool with system triggers. We were trying to accomplish this without having to hack the dump to much. We attempted adding: set local session_replication_role = replica; But that does not seem provide the expected relief. We've got 15 databases we need to convert to UTF-8 and we are trying to get this done the fastest way possible. The reason I think pg_restore works for you is because when a table is built using pg_restore all the data is loaded into all tables BEFORE any constraints are created. I believe that if you did a data-only dump from pg_dump you would have the same integrity problems. Yes. A -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
On Wed, Feb 16, 2011 at 10:08:53AM -0500, David Johnston wrote: > I may be off-track here but triggers do not enforce referential integrity - > constraints do. If you need to disable triggers you can do so via the ALTER > TABLE command. Unless something very big changed when I wasn't looking, the constraints are actually implemented as triggers under the hood. But you're right that it'd be cleaner to drop the constraints and re-add them than to fool with system triggers. > The reason I think pg_restore works for you is because when a table is built > using pg_restore all the data is loaded into all tables BEFORE any > constraints are created. I believe that if you did a data-only dump from > pg_dump you would have the same integrity problems. Yes. A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
I may be off-track here but triggers do not enforce referential integrity - constraints do. If you need to disable triggers you can do so via the ALTER TABLE command. The reason I think pg_restore works for you is because when a table is built using pg_restore all the data is loaded into all tables BEFORE any constraints are created. I believe that if you did a data-only dump from pg_dump you would have the same integrity problems. You can manually get similar behavior by dropping table/column constraints and then re-creating them (and indexes) after the reload is complete. Primary Keys should remain permanently but since you do not want to violate those anyway the problem is not relevant. The only other option to consider is to make all the relevant constraints deferrable - though this may not always be possible. David J -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Geoffrey Myers Sent: Wednesday, February 16, 2011 9:51 AM To: pgsql-general Subject: [GENERAL] disable triggers using psql So, we have a text dump that we used to clean up our data, now we need to reload it into the new database. Problem is, we have some data integrity issues that cause records to fail to load. Before we ran into the data conversion issue we were using 'pg_restore disable_triggers' to get around the data integrity issue. Is there a way to resolve this issue with the psql loading approach? -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers using psql
On Wed, Feb 16, 2011 at 09:50:39AM -0500, Geoffrey Myers wrote: > Is there a way to resolve this issue with the psql loading approach? You can just disable or, depending on your version of Postgres, drop the triggers at the start of the load, load everything up, and then add them again. A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers isolated to transaction only?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers isolated to transaction only?
-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 g...@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- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers isolated to transaction only?
Vick Khera 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers isolated to transaction only?
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 insert trigger modifies, it might be better to simply undo the trigger's effects in the same transaction as the migration. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable Triggers
On Wednesday 09 April 2008 14:56, Greg Sabino Mullane wrote: > > I see the following in the documentation for pg_trigger related > > to tgenabled: "Controls in which session_replication_role modes the > > trigger fires. O = trigger fires in "origin" and "local" modes, > > D = trigger is disabled, R = trigger fires in "replica" mode, A = > > trigger fires always." > > > > My question is: When tgenabled is set to "D", how does that setting > > interact with session_replication_role and, is there a way to use > > tgenabled with a setting of "D" to prevent a particular trigger > > from firing. Using ALTER TABLE to disable the trigger won't work > > because the whole table is locked during the transaction and I only > > want the disabled trigger to apply to the current transaction in the > > current session. > > If you simply want to ignore all triggers, just use a 'replica' role. > When done, switch it back to 'origin' (or your default, which should > be origin). > > If you want to fire only a single trigger, set it to 'always' mode and > switch to 'replica'. If you want to fire all triggers *except* a > certain trigger, set that trigger to replica mode and leave the > session_replication_mode unchanged (default/origin). > > You should be using ALTER TABLE and not worry about changing tgenabled > yourself, in case it wasn't obvious. You should be able to make permanent > changes and then just use session_replication_role to control how it acts > in a particular transaction. Greg, Thanks for your help on this. I'll try to work out something along these lines. I'm inclined to update one of the system tables to accomplish this because that's the way we did it in version 7.4.x. In that case, we were setting reltriggers to 0 in pg_class to turn off all the triggers on a given table, and, in fact, I was doing that at Tom's suggestion for solving the problem in a post to the list long, long, ago, and far, far, away. Again, thanks for taking the time to help :o] > > Here's a quick example: > > SET client_min_messages = 'ERROR'; > DROP SCHEMA IF EXISTS triggertest CASCADE; > SET client_min_messages = 'NOTICE'; > > CREATE SCHEMA triggertest; > > SET SEARCH_PATH = triggertest; > > CREATE TABLE foo(a int); > > INSERT INTO foo VALUES (1); > > CREATE FUNCTION trig1() > RETURNS TRIGGER > LANGUAGE plpgsql > AS $_$ > BEGIN > RAISE NOTICE 'I am trigger one'; > RETURN NULL; > END; > $_$; > > CREATE FUNCTION trig2() > RETURNS TRIGGER > LANGUAGE plpgsql > AS $_$ > BEGIN > RAISE NOTICE 'I am trigger two'; > RETURN NULL; > END; > $_$; > > CREATE FUNCTION trig3() > RETURNS TRIGGER > LANGUAGE plpgsql > AS $_$ > BEGIN > RAISE NOTICE 'I am trigger three'; > RETURN NULL; > END; > $_$; > > CREATE TRIGGER t1 AFTER UPDATE on foo > FOR EACH ROW EXECUTE PROCEDURE trig1(); > > CREATE TRIGGER t2 AFTER UPDATE on foo > FOR EACH ROW EXECUTE PROCEDURE trig2(); > > CREATE TRIGGER t3 AFTER UPDATE on foo > FOR EACH ROW EXECUTE PROCEDURE trig3(); > > UPDATE foo SET a=a; -- all three fire > > ALTER TABLE foo ENABLE ALWAYS TRIGGER t1; > > ALTER TABLE foo ENABLE REPLICA TRIGGER t2; > > UPDATE foo SET a=a; -- two does not fire > > SET session_replication_role TO 'replica'; > > UPDATE foo SET a=a; -- three does not fire > > SET session_replication_role TO DEFAULT; > > UPDATE foo SET a=a; -- two does not fire > > The output of the above yields: > > CREATE TRIGGER > psql:trig.example:53: NOTICE: I am trigger one > psql:trig.example:53: NOTICE: I am trigger two > psql:trig.example:53: NOTICE: I am trigger three > UPDATE 1 > ALTER TABLE > ALTER TABLE > psql:trig.example:59: NOTICE: I am trigger one > psql:trig.example:59: NOTICE: I am trigger three > UPDATE 1 > SET > psql:trig.example:63: NOTICE: I am trigger one > psql:trig.example:63: NOTICE: I am trigger two > UPDATE 1 > SET > psql:trig.example:67: NOTICE: I am trigger one > psql:trig.example:67: NOTICE: I am trigger three > UPDATE 1 > > > -- > Greg Sabino Mullane [EMAIL PROTECTED] > PGP Key: 0x14964AC8 200804091452 > http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -- 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 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable Triggers
"Greg Sabino Mullane" <[EMAIL PROTECTED]> writes: > You should be using ALTER TABLE and not worry about changing tgenabled > yourself, in case it wasn't obvious. Yeah. I had imagined Terry was hacking some backend code to do this, in which case invoking CacheInvalidateRelcache directly might be reasonable. But updating tgenabled directly from client code is Just A Bad Idea. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable Triggers
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable Triggers
Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I see the following in the documentation for pg_trigger related to tgenabled: "Controls in which session_replication_role modes the trigger fires. O = trigger fires in "origin" and "local" modes, D = trigger is disabled, R = trigger fires in "replica" mode, A = trigger fires always." My question is: When tgenabled is set to "D", how does that setting interact with session_replication_role and, is there a way to use tgenabled with a setting of "D" to prevent a particular trigger from firing. Using ALTER TABLE to disable the trigger won't work because the whole table is locked during the transaction and I only want the disabled trigger to apply to the current transaction in the current session. If you simply want to ignore all triggers, just use a 'replica' role. When done, switch it back to 'origin' (or your default, which should be origin). If you want to fire only a single trigger, set it to 'always' mode and switch to 'replica'. If you want to fire all triggers *except* a certain trigger, set that trigger to replica mode and leave the session_replication_mode unchanged (default/origin). You should be using ALTER TABLE and not worry about changing tgenabled yourself, in case it wasn't obvious. You should be able to make permanent changes and then just use session_replication_role to control how it acts in a particular transaction. The issue at hand (I work with the OP), is that our current application disables all triggers quite often. Enter Slony, we want to replicate. So, what we need to do is, disable ALL triggers EXCEPT slony triggers. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable Triggers
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > I see the following in the documentation for pg_trigger related > to tgenabled: "Controls in which session_replication_role modes the > trigger fires. O = trigger fires in "origin" and "local" modes, > D = trigger is disabled, R = trigger fires in "replica" mode, A = > trigger fires always." > My question is: When tgenabled is set to "D", how does that setting > interact with session_replication_role and, is there a way to use > tgenabled with a setting of "D" to prevent a particular trigger > from firing. Using ALTER TABLE to disable the trigger won't work > because the whole table is locked during the transaction and I only > want the disabled trigger to apply to the current transaction in the > current session. If you simply want to ignore all triggers, just use a 'replica' role. When done, switch it back to 'origin' (or your default, which should be origin). If you want to fire only a single trigger, set it to 'always' mode and switch to 'replica'. If you want to fire all triggers *except* a certain trigger, set that trigger to replica mode and leave the session_replication_mode unchanged (default/origin). You should be using ALTER TABLE and not worry about changing tgenabled yourself, in case it wasn't obvious. You should be able to make permanent changes and then just use session_replication_role to control how it acts in a particular transaction. Here's a quick example: SET client_min_messages = 'ERROR'; DROP SCHEMA IF EXISTS triggertest CASCADE; SET client_min_messages = 'NOTICE'; CREATE SCHEMA triggertest; SET SEARCH_PATH = triggertest; CREATE TABLE foo(a int); INSERT INTO foo VALUES (1); CREATE FUNCTION trig1() RETURNS TRIGGER LANGUAGE plpgsql AS $_$ BEGIN RAISE NOTICE 'I am trigger one'; RETURN NULL; END; $_$; CREATE FUNCTION trig2() RETURNS TRIGGER LANGUAGE plpgsql AS $_$ BEGIN RAISE NOTICE 'I am trigger two'; RETURN NULL; END; $_$; CREATE FUNCTION trig3() RETURNS TRIGGER LANGUAGE plpgsql AS $_$ BEGIN RAISE NOTICE 'I am trigger three'; RETURN NULL; END; $_$; CREATE TRIGGER t1 AFTER UPDATE on foo FOR EACH ROW EXECUTE PROCEDURE trig1(); CREATE TRIGGER t2 AFTER UPDATE on foo FOR EACH ROW EXECUTE PROCEDURE trig2(); CREATE TRIGGER t3 AFTER UPDATE on foo FOR EACH ROW EXECUTE PROCEDURE trig3(); UPDATE foo SET a=a; -- all three fire ALTER TABLE foo ENABLE ALWAYS TRIGGER t1; ALTER TABLE foo ENABLE REPLICA TRIGGER t2; UPDATE foo SET a=a; -- two does not fire SET session_replication_role TO 'replica'; UPDATE foo SET a=a; -- three does not fire SET session_replication_role TO DEFAULT; UPDATE foo SET a=a; -- two does not fire The output of the above yields: CREATE TRIGGER psql:trig.example:53: NOTICE: I am trigger one psql:trig.example:53: NOTICE: I am trigger two psql:trig.example:53: NOTICE: I am trigger three UPDATE 1 ALTER TABLE ALTER TABLE psql:trig.example:59: NOTICE: I am trigger one psql:trig.example:59: NOTICE: I am trigger three UPDATE 1 SET psql:trig.example:63: NOTICE: I am trigger one psql:trig.example:63: NOTICE: I am trigger two UPDATE 1 SET psql:trig.example:67: NOTICE: I am trigger one psql:trig.example:67: NOTICE: I am trigger three UPDATE 1 - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200804091452 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkf9EUUACgkQvJuQZxSWSsgrQwCg7Q6ZBLBzzfy5fntxXPI17i8l VTUAoNK++VH2lVj42tstfXM49P7NtCa+ =ex6Z -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable Triggers
On Wednesday 09 April 2008 13:12, Tom Lane wrote: > Terry Lee Tucker <[EMAIL PROTECTED]> writes: > > My question is: When tgenabled is set to "D", how does that setting > > interact with session_replication_role and, is there a way to use > > tgenabled with a setting of "D" to prevent a particular trigger from > > firing. Using ALTER TABLE to disable the trigger won't work because the > > whole table is locked during the transaction and I only want the disabled > > trigger to apply to the current transaction in the current session. > > I'll bet you're missing a relcache flush operation. I don't think an > update on pg_trigger will cause that by itself. > >regards, tom lane Thanks for the response Tom. I hate to be dense, but I really don't have a clue as to what you are saying. I can't find anything in the docs regarding "relcache flush". I have to get this issue resolved as our system uses a Perl process to keep certain columns in certain tables in sync across several databases, so, if you can point me in the right direction, that would be great. -- 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 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable Triggers
Terry Lee Tucker <[EMAIL PROTECTED]> writes: > My question is: When tgenabled is set to "D", how does that setting interact > with session_replication_role and, is there a way to use tgenabled with a > setting of "D" to prevent a particular trigger from firing. Using ALTER TABLE > to disable the trigger won't work because the whole table is locked during > the transaction and I only want the disabled trigger to apply to the current > transaction in the current session. I'll bet you're missing a relcache flush operation. I don't think an update on pg_trigger will cause that by itself. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable Triggers
On Wednesday 09 April 2008 11:00, Greg Sabino Mullane wrote: > > I have a situation where an external process needs to disable the firing > > of triggers on a table. > > ... > > > session_replication_role is set to "origin". I thought this was supposed > > to be fixed in later versions of Postgres (I'm converting from 7.4.19 to > > 8.3.1), so apparently I'm missing something. > > You want: SET session_replication_role to 'replica'; > > -- > Greg Sabino Mullane [EMAIL PROTECTED] > PGP Key: 0x14964AC8 200804091058 > http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 I see the following in the documentation for pg_trigger related to tgenabled: "Controls in which session_replication_role modes the trigger fires. O = trigger fires in "origin" and "local" modes, D = trigger is disabled, R = trigger fires in "replica" mode, A = trigger fires always." My question is: When tgenabled is set to "D", how does that setting interact with session_replication_role and, is there a way to use tgenabled with a setting of "D" to prevent a particular trigger from firing. Using ALTER TABLE to disable the trigger won't work because the whole table is locked during the transaction and I only want the disabled trigger to apply to the current transaction in the current session. TIA -- 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 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable Triggers
On Wednesday 09 April 2008 11:00, Greg Sabino Mullane wrote: > > I have a situation where an external process needs to disable the firing > > of triggers on a table. > > ... > > > session_replication_role is set to "origin". I thought this was supposed > > to be fixed in later versions of Postgres (I'm converting from 7.4.19 to > > 8.3.1), so apparently I'm missing something. > > You want: SET session_replication_role to 'replica'; Thanks for the response Greg. Should the session_replication_role be restored to "origin", when the process is complete? > > -- > Greg Sabino Mullane [EMAIL PROTECTED] > PGP Key: 0x14964AC8 200804091058 > http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -- 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 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable Triggers
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > I have a situation where an external process needs to disable the firing of > triggers on a table. ... > session_replication_role is set to "origin". I thought this was supposed to > be fixed in later versions of Postgres (I'm converting from 7.4.19 to 8.3.1), > so apparently I'm missing something. You want: SET session_replication_role to 'replica'; - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200804091058 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkf82cMACgkQvJuQZxSWSshqbwCfURuaWGtih7HEIrPs3lOCU+2V zN8An3eEH3G/2emX0pl2Z2NmszXB7kiN =cu+o -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable Triggers
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 [EMAIL PROTECTED] www.turbocorp.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Disable Triggers
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Disable Triggers
Tom Lane wrote: Andrew Sullivan <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Disable Triggers
-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 [EMAIL PROTECTED] PGP Key: 0x14964AC8 200802211338 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAke9xUQACgkQvJuQZxSWSsifbACffN6/ohNCwvkvZ10Uvamyg264 nckAnRarfpLgrZYkLe6Q/FSW+edC2hQC =9GqX -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Disable Triggers
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 [EMAIL PROTECTED] www.turbocorp.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Disable Triggers
On Thursday 21 February 2008 12:56, Scott Marlowe wrote: > On Thu, Feb 21, 2008 at 9:20 AM, Terry Lee Tucker <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] www.turbocorp.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Disable Triggers
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Disable Triggers
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Disable Triggers
Andrew Sullivan <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Disable Triggers
On Thu, Feb 21, 2008 at 9:20 AM, Terry Lee Tucker <[EMAIL PROTECTED]> 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... ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Disable Triggers
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 [EMAIL PROTECTED] www.turbocorp.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Disable Triggers
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Disable Triggers
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: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Disable triggers per transaction 8.2.3
On 10/10/07, Henrik <[EMAIL PROTECTED]> wrote: > Hello list, > > I wonder if it is possible to disable triggers for a single transaction. > I know I can disable triggers per table but then I need to disable > all triggers in all recursive tables before doing by query. > > Can I do: > BEGIN TRANSACTION; > DISABLE TRIGGERS; > DELETE FROM tbl_foo WHERE ID > 5; > ENABLE TRIGGERS; > COMMIT; > > Or do I have to do: > BEGIN TRANSACTION; > ALTER TABLE tbl_foo DISABLE TRIGGERS ALL; > ALTER TABLE tbl_foo_bar DISABLE TRIGGERS ALL; > > DELETE FROM tbl_foo WHERE ID > 5; > > ALTER TABLE tbl_foo ENABLE TRIGGERS ALL; > ALTER TABLE tbl_foo_bar ENABLE TRIGGERS ALL; > > COMMIT; > > Or is it even possible? I only want my triggers to be disabled for > the transaction and not the global database. 1. upgrade to 8.2.5 asap 2. disable triggers is possible, but alter acquires an excl lock on the table. so, while you are disabling for you txn only, nobody else does anything until you finish (is that what you want?) 3. there are other strategies to attack this problem for particular situations. merlin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
