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
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 th
-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 transactio
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
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 sa
I'm planning to split a large table into partitions. During the
migration, all new data will be added to the sub-tables, and I will be
moving the data from the master table to the proper sub tables at the
same time. The trick is that I have an INSERT trigger to keep track
of various counters. I
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
"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
r
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 fig
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 disab
-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 fire
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
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
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
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
-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
Greetings:
I have a situation where an external process needs to disable the firing of
triggers on a table. I have two backend functions which handle this task:
disable_triggers ('table_name')
enable_triggers ('table_name')
These functions are called inside a transaction. The first, stores vario
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 HI
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 "
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 sol
-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 he
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
> > settin
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,
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 repe
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
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
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
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 disablin
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 uti
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
> > replic
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,
gettin
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 i
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 i
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 TRANSACT
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;
61 matches
Mail list logo
