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

[Rob Richardson]

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:[EMAIL PROTECTED] 
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
<[EMAIL PROTECTED]> 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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Scott Marlowe]

On Fri, Oct 3, 2008 at 1:48 PM, Rob Richardson
<[EMAIL PROTECTED]> 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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Rob Richardson]

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:[EMAIL PROTECTED] 
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
<[EMAIL PROTECTED]> 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.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Scott Marlowe]

On Fri, Oct 3, 2008 at 8:56 AM, Rob Richardson
<[EMAIL PROTECTED]> 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.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Rob Richardson]

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 

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Blazej]

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 <[EMAIL PROTECTED]>:
> 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:[EMAIL PROTECTED]
> 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" <[EMAIL PROTECTED]> 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
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Rob Richardson]

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:[EMAIL PROTECTED] 
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
[EMAIL PROTECTED] Galway Cathedral Recitals:
http://www.galwaycathedral.org/recitals
--

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Raymond O'Donnell]

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
[EMAIL PROTECTED]
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Rob Richardson]

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:[EMAIL PROTECTED] 
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" <[EMAIL PROTECTED]> 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: [GENERAL] How do I save data and then raise an exception?

[Tom Lane]

"Rob Richardson" <[EMAIL PROTECTED]> 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: [GENERAL] How do I save data and then raise an exception?

[Rob Richardson]

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:[EMAIL PROTECTED]
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
<[EMAIL PROTECTED]> 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: [GENERAL] How do I save data and then raise an exception?

[Klint Gore]

Gurjeet Singh wrote:
On Fri, Oct 3, 2008 at 7:14 AM, Alvaro Herrera 
<[EMAIL PROTECTED] > 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: [EMAIL PROTECTED]


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Gurjeet Singh]

On Fri, Oct 3, 2008 at 7:14 AM, Alvaro Herrera
<[EMAIL PROTECTED]>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,
-- 
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB  http://www.enterprisedb.com

Mail sent from my BlackLaptop device

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

[Gurjeet Singh]

On Fri, Oct 3, 2008 at 7:41 AM, Jaime Casanova <[EMAIL PROTECTED]
> wrote:

> On Thu, Oct 2, 2008 at 8:44 PM, Alvaro Herrera
> <[EMAIL PROTECTED]> 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.

-- 
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB  http://www.enterprisedb.com

Mail sent from my BlackLaptop device

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

[Jaime Casanova]

On Thu, Oct 2, 2008 at 8:44 PM, Alvaro Herrera
<[EMAIL PROTECTED]> 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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Alvaro Herrera]

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 Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How do I save data and then raise an exception?

[Rob Richardson]

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