 | |
[Info-Ingres] Rules vs. table structure..who wins?
comp.databases.ingres comp.databases.ingres
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
[Info-Ingres] Rules vs. table structure..who wins? -
04-21-2010
, 06:17 AM
#2
| |||
| |||
|
#3
| |||
| |||
|
|
From the SQL Guide: |
"When an error occurs in a database procedure, the behavior of the DBMS
Server depends on whether the procedure was invoked by a rule or
executed directly (using EXECUTE PROCEDURE).
If the procedure was invoked by a rule, an error has the following
effects:
.. The procedure is terminated.
.. Those statements in the procedure which have been executed are rolled
back.
.. The statement that fired the rule is rolled back.
If the procedure was executed directly, an error has the following
effects:
.. All statements in the procedure up to the point of the error are
rolled back.
.. The procedure continues execution with the statement following the
statement that caused the error.
.. Parameters passed by reference are not updated."
So the error behaviour you're seeing is the documented behaviour.
Regards
Paul
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Martin Bowes
Sent: 21 April 2010 12:17
To: Ingres and related product discussion forum
Subject: [Info-Ingres] Rules vs. table structure..who wins?
Hi All,
We all know that when a table has multiple rules acting on it that the
order of firing for the rules cannot be garunteed.
But what about this...
Consider a table with a rules fired procedure and a unique index. (There
is no unique constraint)
An action occurs that causes the rule to fire.
The RFP then performs an act that causes a uniqueness violation in the
index.
Whose error processing takes over?
At the moment it appears to me that the index wins...and generates an
appropriate error message, and the rule is stopped dead. The rules error
processing never gets to fire off.
Is this the correct action?
I would have thought the rfps error processing would have been triggered
with the error code being passed back and available from iierrornumber
as per usual.
Martin Bowes
#4
| |||
| |||
|
#5
| |||
| |||
|
#6
| |||
| |||
|
|
Of course we can postulate that being documented doesn't make it "correct". I can easily imagine a procedure that wants to test for duplicates and perform some other action, even in a rule. We need to distinguish between real DBMS errors (out of disk space, log full) and application semantic errors such as "duplicate key". Consider I want to do this: insert into table values () if (duplicate) then do something else. With the current error handling system, I have to write needlessly more complex code select count(*) from ... where somecriteria if thecount == 0 then insert into table values () else do something else (I have not tested this. Does the procedure >>really<< fail in this case?) -- daryl.monge (AT) ingres (DOT) com ------------------------------------------------------------------------ daryl.monge (AT) ingres (DOT) com's Profile: http://community.ingres.com/forum/member.php?userid=736 View this thread: http://community.ingres.com/forum/sh...ad.php?t=11923 _______________________________________________ Info-Ingres mailing list Info-Ingres (AT) kettleriverconsulting (DOT) com http://ext-cando.kettleriverconsulti...fo/info-ingres |
--
Jeremy Peel
#7
| |||
| |||
|
#8
| |||
| |||
|
|
On first glance it certainly seems to me that the SQL statement in the procedure should receive the error and be allowed to process it internal |
Quote:
|
to the rule. Stopping the entire rule/procedure process seem like it would |
Quote:
|
significant affect the flexibility of the procedure author to handle problems. |
You probably want to be careful there. The RFP failed on a significant
constraint error. If it was allowed to continue and do its own error
processing....what would happen if I didn't process the error and then
run more queries which worked, what then would be the error status of
the RFP?
BTW. I just tried an experiment in 9.2.0 where I got the RFP to directly
execute a sub procedure.
I hoped that the DEP would get to do the error processing ... but it was
smarter than me and the RFP rule on error processing was still applied.
The DEP never got to do the error code stuff. Whis is probably for the
best.
In this particular case, I could rewrite my RFP to check before its
attempt at the update to see if the coast is clear.
An alternative would be to consider a BEFORE trigger.
Marty
#9
| |||
| |||
|
|
On first glance it certainly seems to me that the SQL statement in the procedure should receive the error and be allowed to process it internal to the rule. Stopping the entire rule/procedure process seem like it would significant affect the flexibility of the procedure author to handle problems. |
You probably want to be careful there. The RFP failed on a significant constraint error. If it was allowed to continue and do its own error processing....what would happen if I didn't process the error and then run more queries which worked, what then would be the error status of the RFP?
BTW. I just tried an experiment in 9.2.0 where I got the RFP to directly execute a sub procedure.
I hoped that the DEP would get to do the error processing ... but it was smarter than me and the RFP rule on error processing was still applied. TheDEP never got to do the error code stuff. Whis is probably for the best.
In this particular case, I could rewrite my RFP to check before its attempt at the update to see if the coast is clear.
An alternative would be to consider a BEFORE trigger.
Marty
__________________________________________________ ____________
This message has been scanned for all viruses by BTnet VirusScreen.
The service is delivered in partnership with MessageLabs.
This service does not scan any password protected or encrypted
attachments.
__________________________________________________ ____________
This message has been checked for all viruses by BTnet VirusScreen.
The service is delivered in partnership with MessageLabs and does not scan any password protected or encrypted attachments.
Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of the company. The contents of this email are confidential to the addressee and may also be privileged. If you are not the addressee of this email, you must not copy, forward, disclose or otherwise use it, or any part of it, for anypurpose, nor disclose its contents to any other person. If you have received this email in error please notify the sender. Please be aware that any email sent to, or received from, this address may be monitored for quality control, staff training or security purposes. Although the company scans all outgoing email and attachments for viruses, neither the sender nor the company accepts any responsibility for viruses and it remains the responsibility of the recipient to scan email and attachments (if any) for viruses.
#10
| |||
| |||
|
|
How do other databases handle errors in rule(trigger) fired db procedures? |
It must be so, else user code could subvert the atomic guarantees made by
the RDBMS. If the procedure code could catch and "deal with" RI errors,
how could the server continue to ensure said RI? At some point, it's not
up to the code.
The only right way to prevent RI errors in procedural code is to verify
preconditions *before* affecting the data.
--jkl
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.