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 replic
-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.ou
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_replicatio
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_replicatio
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 pro
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:
>
-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 SE
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 pro
-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
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
-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
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 tha
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 trigge
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 "custome
-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 scri
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 "custome
-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
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 trigge
-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
>> a
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
-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 sho
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 trig
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
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
...@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
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
--
An
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
27 matches
Mail list logo
