Thread: 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
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 ajs@crankycanuck.ca
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-owner@postgresql.org [mailto:pgsql-general-owner@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
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 ajs@crankycanuck.ca
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
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
toeasily 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;
-----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 greg@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-----
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 greg@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
-----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 greg@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-----
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 greg@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
-----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 greg@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-----
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
-----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 greg@endpoint.com greg@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-----
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 greg@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
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 greg@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
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 greg@endpoint.com greg@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
-----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 greg@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-----
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 greg@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
-----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 greg@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-----
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
-----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 greg@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-----
lists@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.
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 greg@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
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 greg@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
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 greg@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
-----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 greg@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-----
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 greg@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