Thread: Trigger disable for table

Trigger disable for table

From
Frank Durstewitz
Date:
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

Re: Trigger disable for table

From
"A. Kretschmer"
Date:
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

Re: Trigger disable for table

From
Terry Lee Tucker
Date:
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

Re: Trigger disable for table

From
"Frank Durstewitz, Emporis GmbH"
Date:
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


How do I save data and then raise an exception?

From
"Rob Richardson"
Date:
Greetings!
 
The people who originally wrote the system I'm trying to work with did not know as much as they should have about working with databases, so I'm stuck with the following situation:
 
The applicaton is written in C++ (MS Visual C++ 6, Windows XP, in case it matters).  At one point, a required check was not performed before data was saved.  I cannot change this part of the C++ code, so I have to perform the check in the database, and the insert query has to fail so that the application will see that something bad happened.  However, there's another query that gets performed before the one I have to check.  If the check fails, the earlier query has to be undone.  The only way I know to intentionally fail a query is to raise an exception.  However, raising an exception causes all earlier database changes to be undone.  How can I avoid that?
 
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?
 
Thanks very much!
 
RobR

Re: How do I save data and then raise an exception?

From
Alvaro Herrera
Date:
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.

Re: How do I save data and then raise an exception?

From
"Jaime Casanova"
Date:
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

Re: How do I save data and then raise an exception?

From
"Gurjeet Singh"
Date:
On Fri, Oct 3, 2008 at 7:41 AM, Jaime Casanova <jcasanov@systemguards.com.ec> wrote:
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?

NOTICE wouldn't rollback any part of the transaction! OP needs mixed COMMIT success in the same transaction.

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: How do I save data and then raise an exception?

From
"Gurjeet Singh"
Date:
On Fri, Oct 3, 2008 at 7:14 AM, 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.

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.

Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: How do I save data and then raise an exception?

From
Klint Gore
Date:
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


Re: How do I save data and then raise an exception?

From
"Rob Richardson"
Date:
RAISE NOTICE won't help (I don't think) because the notice isn't visible from inside the C++ application, so the user won't know that a problem occured. 
 
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.  We haven't tracked that down yet, but this job would fix that too.
 
Now all I have to do is learn how to set up a job.  I only know about them from overhearing colleagues talking.
 
RobR


From: Jaime Casanova [mailto:jcasanov@systemguards.com.ec]
Sent: Thu 10/2/2008 10:11 PM
To: Alvaro Herrera
Cc: Rob Richardson; pgsql-general@postgresql.org
Subject: Re: [GENERAL] How do I save data and then raise an exception?

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

Re: How do I save data and then raise an exception?

From
Tom Lane
Date:
"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

Re: How do I save data and then raise an exception?

From
"Rob Richardson"
Date:
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

Re: How do I save data and then raise an exception?

From
Raymond O'Donnell
Date:
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
------------------------------------------------------------------

Re: How do I save data and then raise an exception?

From
"Rob Richardson"
Date:
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
------------------------------------------------------------------

Re: How do I save data and then raise an exception?

From
Blazej
Date:
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
>

Re: How do I save data and then raise an exception? -- solved

From
"Rob Richardson"
Date:
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

Re: How do I save data and then raise an exception?

From
"Scott Marlowe"
Date:
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.

Re: How do I save data and then raise an exception?

From
"Rob Richardson"
Date:
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.

Re: How do I save data and then raise an exception?

From
"Scott Marlowe"
Date:
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

Re: How do I save data and then raise an exception?

From
"Rob Richardson"
Date:
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