You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Yip Ng (JIRA)" <de...@db.apache.org> on 2006/08/08 11:35:15 UTC
[jira] Updated: (DERBY-1652) Update trigger updating the same rows
as the original update does not throw an exception ERROR 54038: "Maximum
depth of nested triggers was exceeded" as it should
[ http://issues.apache.org/jira/browse/DERBY-1652?page=all ]
Yip Ng updated DERBY-1652:
--------------------------
Attachment: derby1652-10.1.3-stat.txt
derby1652-10.1.3-diff.txt
Attaching patch for DERBY-1652. The patch is for the 10.1 codebase. A similar patch is needed for 10.2, which I'll post as soon as derbyall completes.
The cause of the problem is that the trigger descriptor is created after the stored prepared statement(SPS) has been compiled, so the compiled form of the SPS is not aware of that its trigger action can fire on the trigger table itself. Hence, the constant action was not generated correctly.
During upgrade, the SPSs are invalidated at database boot time. The SPS will be recompile when it is being invoked and the recompilation at this point will of course detect the relevent trigger and generate the correct constant action for the SPS and produce the expected behavior when the SPS is executed - throwing an error when it exceeds the trigger's maximum depth in the above case.
The simplest solution without introducing another revalidation of the SPS is to create the trigger descriptor first before compiling the SPS. I ran derbyall and had to go over the testcases which have the wrong master outputs and I have corrected them on this patch. Appreciate if someone can review this.
On a side note, I ran derbyall on a *clean* 10.1 codeline and I see the following
failures:
derbyall/derbyall.fail:unit/T_Diagnosticable.unit
derbyall/derbyall.fail:i18n/urlLocale.sql
derbyall/derbyall.fail:i18n/messageLocale.sql
derbyall/derbyall.fail:i18n/iepnegativetests_ES.sql
derbyall/derbynetclientmats/derbynetmats.fail:derbynet/sysinfo.java
derbyall/derbynetmats/derbynetmats.fail:derbynet/sysinfo.java
Is this a known problem?
> Update trigger updating the same rows as the original update does not throw an exception ERROR 54038: "Maximum depth of nested triggers was exceeded" as it should
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1652
> URL: http://issues.apache.org/jira/browse/DERBY-1652
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.0.2.0
> Reporter: Kathey Marsden
> Assigned To: Yip Ng
> Fix For: 10.1.3.2
>
> Attachments: derby1652-10.1.3-diff.txt, derby1652-10.1.3-stat.txt
>
>
> Execution of an update trigger that updates the same row as the original update will recurse forever and exceed the maximum nesting level of 16 so should throw the exception:
> ERROR 54038: "Maximum depth of nested triggers was exceeded"
> However, it does not always throw the exception. For example:
> CREATE TABLE "TEST" (
>
> "TESTID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START
> WITH 1,
> INCREMENT BY 1),
>
> "INFO" INTEGER NOT NULL,
>
> "TIMESTAMP" TIMESTAMP NOT NULL DEFAULT
> '1980-01-01-00.00.00.000000'
> );
>
> CREATE TRIGGER UPDATE_TEST
> AFTER UPDATE ON TEST
> REFERENCING OLD AS OLD
> FOR EACH ROW MODE DB2SQL
> UPDATE TEST SET TIMESTAMP = CURRENT_TIMESTAMP WHERE
> TESTID = OLD.TESTID;
> INSERT INTO TEST (INFO) VALUES
> (1),
> (2),
> (3);
> UPDATE TEST SET INFO = 1 WHERE TESTID = 2;
> Does not throw an exception:
> However, If the derby jars are updated to a new version, the correct exception is thrown.
> Replace derby jars with new version
> Execute the following in ij:
> UPDATE TEST SET INFO = 1 WHERE TESTID = 2;
> ERROR 54038: Maximum depth of nested triggers was exceeded.
> Note: This issue stemmed from the Invalid issue, DERBY-1603, because a user hit the exception after upgrade and thought the exception after upgrade, not the lack of exception before upgrade was the problem. This may be a common user error, so we need a release note to help mitigate the issue. I will add one shortly after confirming the correct trigger syntax.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira