Thread: Trigger disable for table

Hi list.

A fairly complex problem:

- Table A has a before insert/update trigger, which look up table B and
use field C from table B.
- Table B has a after insert/update trigger, which update table A with
field C.

The update on table B triggers the trigger from table A, so the same
thing is done twice.
Can one avoid to fire the trigger on table A, when updates are made to
table B, because i know all fields already and can build the update sql
for table A, so no need to call the trigger on table A?

My idea is to have it like
...
IF NEW.published = TRUE THEN
    ALTER TABLE a DISABLE TRIGGER mytrigger USER;
    (do update here)
    ALTER TABLE a ENABLE TRIGGER mytrigger USER;
...

Will a construct like this disable the trigger only inside the this
function or is the trigger disabled outside (visiblility?) the function,
too, which is unacceptable.

(Hmm, sounds very confused, and so i am...)

A helping hand on this topic is well accepted :-)

Thanks, Frank

am  Thu, dem 02.10.2008, um 12:26:20 +0200 mailte Frank Durstewitz folgendes:
> My idea is to have it like
> ...
> IF NEW.published = TRUE THEN
>    ALTER TABLE a DISABLE TRIGGER mytrigger USER;
>    (do update here)
>    ALTER TABLE a ENABLE TRIGGER mytrigger USER;
> ...
>
> Will a construct like this disable the trigger only inside the this
> function or is the trigger disabled outside (visiblility?) the function,
> too, which is unacceptable.

I think, you can do that, but an 'ALTER TABLE' produce a
AccessExclusiveLock on this table.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

On Thursday 02 October 2008 06:26, Frank Durstewitz wrote:
> Hi list.
>
> A fairly complex problem:
>
> - Table A has a before insert/update trigger, which look up table B and
> use field C from table B.
> - Table B has a after insert/update trigger, which update table A with
> field C.
>
> The update on table B triggers the trigger from table A, so the same
> thing is done twice.
> Can one avoid to fire the trigger on table A, when updates are made to
> table B, because i know all fields already and can build the update sql
> for table A, so no need to call the trigger on table A?
>
> My idea is to have it like
> ...
> IF NEW.published = TRUE THEN
>     ALTER TABLE a DISABLE TRIGGER mytrigger USER;
>     (do update here)
>     ALTER TABLE a ENABLE TRIGGER mytrigger USER;
> ...
>
> Will a construct like this disable the trigger only inside the this
> function or is the trigger disabled outside (visiblility?) the function,
> too, which is unacceptable.
>
> (Hmm, sounds very confused, and so i am...)
>
> A helping hand on this topic is well accepted :-)
>
> Thanks, Frank

This should work but, if I remember correctly, it will lock table A. If that
is OK in your environment, then go for it. It is not in ours. We have a table
that we called override and when we want to override the firing of a certain
trigger, we put code in that trigger that checks the override table for the
existence of a record matching the trigger name and some other criteria. If
we find it, we simply return from the trigger at that point. The trigger on
table B would be responsible for inserting the record into override and then
deleting the record after the update is done. We've build wrapper functions
to make the inserts and deletes to override easy.

HTH...

--
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812  Fax: (336) 372-6812  Cell: (336) 404-6987
terry@turbocorp.com
www.turbocorp.com

Andreas and Terry,

thanks for answering and pointing me in the direction. Unfortunately i
found out, that even without the trigger the updates take too much time
for interactive applications. So i go for a batch-update.

Kindly regards, Frank

Rob Richardson wrote:

> Here's what I need to do:
>
> IF query_check_fails THEN
>     UPDATE some_table SET some_value = 0 WHERE some_condition_is_true;
>     RAISE EXCEPTION 'Look, you idiot, do it right next time!';
> END;
>
> I need the update to work, but I need to raise the exception so the C++
> code recognizes the error.  How can I do both?

You need an autonomous transaction, which Postgres does not support
directly but you can implement using dblink or a plperl function that
connects back to the database.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Thu, Oct 2, 2008 at 8:44 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Rob Richardson wrote:
>
>> Here's what I need to do:
>>
>> IF query_check_fails THEN
>>     UPDATE some_table SET some_value = 0 WHERE some_condition_is_true;
>>     RAISE EXCEPTION 'Look, you idiot, do it right next time!';
>> END;
>>
>> I need the update to work, but I need to raise the exception so the C++
>> code recognizes the error.  How can I do both?
>
> You need an autonomous transaction, which Postgres does not support
> directly but you can implement using dblink or a plperl function that
> connects back to the database.
>

what about RAISE NOTICE?

--
regards,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Gurjeet Singh wrote:
> On Fri, Oct 3, 2008 at 7:14 AM, Alvaro Herrera
> <alvherre@commandprompt.com <mailto:alvherre@commandprompt.com>> wrote:
>
>     Rob Richardson wrote:
>
>     > Here's what I need to do:
>     >
>     > IF query_check_fails THEN
>     >     UPDATE some_table SET some_value = 0 WHERE
>     some_condition_is_true;
>     >     RAISE EXCEPTION 'Look, you idiot, do it right next time!';
>     > END;
>     >
>     > I need the update to work, but I need to raise the exception so
>     the C++
>     > code recognizes the error.  How can I do both?
>
>     You need an autonomous transaction, which Postgres does not support
>     directly but you can implement using dblink or a plperl function that
>     connects back to the database.
>
>
> I was also going to suggest that but did not, because autonomous
> transaction won't help here! The data has been INSERTed or UPDATEd in
> this transaction, and hence won't be visible to the autonomous
> transaction, because the main transaction hasn't committed yet.

Autonomous transactions in the oracle sense would do the job perfectly.
http://www.oracle-base.com/articles/misc/AutonomousTransactions.php

Faking that example with dblink isn't going to fly with PG - the select
with 10 rows before the rollback is never going to see 10.

For Rob's need though, running his update thru dblink it should do the
job.  If the data he's fixing with the update statement was in the same
transaction, then the update wouldn't be needed at all and the whole
thing could just rollback.  You have to assume that by the point where
Rob's code fires, the bad data is already committed.  That update needs
to commit to undo that previous transaction, but he still needs to get
the 3rd party app to know that something went horribly wrong with its
insert.

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au

"Rob Richardson" <Rob.Richardson@rad-con.com> writes:
> I think I just came up with a thoroughly ugly idea.  The database
> supports an annealing shop, in which coils are assigned to charges.
> After the check fails, I end up with coils assigned to a charge that
> does not exist.  I could set up a job that runs every minute and
> checks all coils with status "Assigned" to make sure that the
> associated charges actually exist.  That would fix another recurring
> problem, in which a user intentionally deletes a charge but the
> charge's coils stay assigned to that charge.

Why don't you have a foreign key constraint from coils to charges?

            regards, tom lane

That's how it should have been done, but it wasn't.  It's too late to
change it now.  If I make any change to the C++ code, I run into a
horrible case of DLL Hell.  I told my bosses that if we change any C++
code at that site, we have to change all of it.  So I need a pure
database solution.  Or maybe something else.  Now I'm thinking of a
Python script, of which there are several running on site.

RobR


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, October 03, 2008 8:47 AM
To: Rob Richardson
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How do I save data and then raise an exception?

"Rob Richardson" <Rob.Richardson@rad-con.com> writes:
> I think I just came up with a thoroughly ugly idea.  The database
> supports an annealing shop, in which coils are assigned to charges.
> After the check fails, I end up with coils assigned to a charge that
> does not exist.  I could set up a job that runs every minute and
> checks all coils with status "Assigned" to make sure that the
> associated charges actually exist.  That would fix another recurring
> problem, in which a user intentionally deletes a charge but the
> charge's coils stay assigned to that charge.

Why don't you have a foreign key constraint from coils to charges?

            regards, tom lane

On 03/10/2008 14:52, Rob Richardson wrote:
> That's how it should have been done, but it wasn't.  It's too late to
> change it now.  If I make any change to the C++ code, I run into a

But setting up a foreign key constrain is something you do in the
database, not in the app - can't you do that?

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

No, because the application first saves the coils and then saves the
charge.  Setting up the foreign key constraint would prevent any coils
from being saved because the charge record would never exist at the time
the coil record was created.

RobR, who would love to hear some of the music in Galway Cathedral!

-----Original Message-----
From: Raymond O'Donnell [mailto:rod@iol.ie]
Sent: Friday, October 03, 2008 10:53 AM
To: Rob Richardson
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How do I save data and then raise an exception?

On 03/10/2008 14:52, Rob Richardson wrote:
> That's how it should have been done, but it wasn't.  It's too late to
> change it now.  If I make any change to the C++ code, I run into a

But setting up a foreign key constrain is something you do in the
database, not in the app - can't you do that?

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie Galway Cathedral Recitals:
http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Maybe try pltclu - you may use socket (TCP/IP) or to write data to OS
filesystem. I was doing in pltclu very similar things.

Write what you concrete watnts (in points), then I will try to write
you scripts.

Regards
Blazej


2008/10/3 Rob Richardson <Rob.Richardson@rad-con.com>:
> That's how it should have been done, but it wasn't.  It's too late to
> change it now.  If I make any change to the C++ code, I run into a
> horrible case of DLL Hell.  I told my bosses that if we change any C++
> code at that site, we have to change all of it.  So I need a pure
> database solution.  Or maybe something else.  Now I'm thinking of a
> Python script, of which there are several running on site.
>
> RobR
>
>
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Friday, October 03, 2008 8:47 AM
> To: Rob Richardson
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] How do I save data and then raise an exception?
>
> "Rob Richardson" <Rob.Richardson@rad-con.com> writes:
>> I think I just came up with a thoroughly ugly idea.  The database
>> supports an annealing shop, in which coils are assigned to charges.
>> After the check fails, I end up with coils assigned to a charge that
>> does not exist.  I could set up a job that runs every minute and
>> checks all coils with status "Assigned" to make sure that the
>> associated charges actually exist.  That would fix another recurring
>> problem, in which a user intentionally deletes a charge but the
>> charge's coils stay assigned to that charge.
>
> Why don't you have a foreign key constraint from coils to charges?
>
>                        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
>

My thanks to all who contributed thoughts about my question.  I have put
a two-part solution into place.  The trigger function that fires when
charges are inserted raises the exception, but leaves the possibility of
dangling coils (coils with the numbers of charges that do not exist in
the database).  A Python script running under the control of a service
checks every minute for dangling coils and removes their charge numbers.
This is not ideal, but it works.  It has the added benefit of handling
dangling coils that have shown up from another (as yet unidentified)
cause occasionally.

RobR

On Fri, Oct 3, 2008 at 8:56 AM, Rob Richardson
<Rob.Richardson@rad-con.com> wrote:
> No, because the application first saves the coils and then saves the
> charge.  Setting up the foreign key constraint would prevent any coils
> from being saved because the charge record would never exist at the time
> the coil record was created.

You should be able to set your constraints to deferrable initially
deferred and get proper behaviour even with that update system.

I didn't see anything in the documentation about deferred constraints.
Can you point to someplace where I can read about them?

Thank you!

RobR


-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Friday, October 03, 2008 2:59 PM
To: Rob Richardson
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How do I save data and then raise an exception?

On Fri, Oct 3, 2008 at 8:56 AM, Rob Richardson
<Rob.Richardson@rad-con.com> wrote:
> No, because the application first saves the coils and then saves the
> charge.  Setting up the foreign key constraint would prevent any coils

> from being saved because the charge record would never exist at the
> time the coil record was created.

You should be able to set your constraints to deferrable initially
deferred and get proper behaviour even with that update system.

On Fri, Oct 3, 2008 at 1:48 PM, Rob Richardson
<Rob.Richardson@rad-con.com> wrote:
> I didn't see anything in the documentation about deferred constraints.
> Can you point to someplace where I can read about them?

http://www.postgresql.org/docs/8.3/static/sql-createtable.html

Thank you, Scott.  That's interesting to know about.  It doesn't solve
my initial problem, though, because the C++ application was written
without transactions.  (Yet another illustration of the lack of database
knowledge on the part of the initial developers.)

RobR

-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Friday, October 03, 2008 4:59 PM
To: Rob Richardson
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How do I save data and then raise an exception?

On Fri, Oct 3, 2008 at 1:48 PM, Rob Richardson
<Rob.Richardson@rad-con.com> wrote:
> I didn't see anything in the documentation about deferred constraints.
> Can you point to someplace where I can read about them?

http://www.postgresql.org/docs/8.3/static/sql-createtable.html