db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-2041) Trigger should register a dependency on tables and columns used in its body
Date Tue, 12 Nov 2013 16:53:17 GMT

     [ https://issues.apache.org/jira/browse/DERBY-2041?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

Knut Anders Hatlen updated DERBY-2041:

    Attachment: register-dependencies.diff

It looks like using the same approach as CREATE VIEW might work.

The attached patch, register-dependencies.diff, makes CreateTriggerNode register dependencies
the same way as CreateViewNode, and pass them on to CreateTriggerConstantAction, which stores
those dependencies in SYS.SYSDEPENDS.

Those changes alone were enough to make DROP TABLE behave as expected. Additionally, TriggerDescriptor.prepareToInvalidate()
had to be told to raise an error if some other kinds of SQL objects were dropped, such as
functions and views.

DROP TYPE, DROP SEQUENCE and DROP DERBY AGGREGATE already had the expected behaviour, as they
piggybacked on the trigger's SPS and had SPSDescriptor.prepareToInvalidate() raise an error
if such a dependency was about to be dropped. Unfortunately, that creates a rather cryptic
error message:

ERROR X0Y24: Operation 'DROP DERBY AGGREGATE' cannot be performed on object 'INTMODE' because
STATEMENT 'TRIGGERACTN_5ddba7b4-0142-4c61-18e0-000058ee2be7_c6f5a764-0142-4c61-18e0-000058ee2be7'
is dependent on that object.

The patch moves that error handling from SPSDescriptor to TriggerDescriptor, which makes the
error message clearer:

ERROR X0Y25: Operation 'DROP DERBY AGGREGATE' cannot be performed on object 'INTMODE' because
TRIGGER 'TR_INTMODE' is dependent on that object.

The patch adds a new test case to TriggerTest which verifies that dropping of procedures,
functions, table functions, synonyms, sequences, aggregates, views, tables, columns and UDTs
works as expected.

Currently, many of the existing regression tests fail with the patch. The failures seem to
be mainly because the tests attempt to drop a table while there's still a trigger that references
it. Those failures are expected, and the tests need to be updated.

One thing to consider, is how we expect the behaviour to be on upgrade and downgrade.

The current behaviour of the patch is as following:

# The trigger's dependencies are registered when the trigger is created, so existing triggers
will not automatically get the new dependency tracking on upgrade.
# CREATE TRIGGER registers the dependencies even if the database is in soft-upgrade mode.
This means the dependencies are still registered after downgrade, but the old version is only
partially capable of using the dependencies. (Since it doesn't have the TriggerDescriptor
updates, it still won't detect dependencies for example in DROP FUNCTION and DROP VIEW, but
it will be able to detect them with DROP TABLE.)
# After upgrade (soft or hard) dropping UDTs, aggregates or sequences will no longer detect
dependencies on triggers created with the old version. (Since the trigger dependencies were
not registered at CREATE TRIGGER time, and the code that piggybacked on the SPS dependencies
is not in the new version.)

I guess #1 is OK, since existing triggers won't behave worse than before. It might be possible
to create some upgrade code that reconstructs the dependencies on upgrade, but that may be
more complexity than it's worth.

#2 is probably OK too, as it makes the behaviour after downgrade more correct, even though
it still misses a few cases.

#3 means that the trigger's dependencies on UDTs, aggregates and sequences are lost on upgrade,
and the trigger needs to be dropped and recreated with the new version in order to make the
dependency tracking as good as it was in the old version. We should try to find a better way
to handle this.

I can see two quick solutions to that problem:

# Move the invalidation logic for those three types of SQL objects back from TriggerDescriptor
to SPSDescriptor. That means the cryptic error messages are back, but it also means the behaviour
does not change on upgrade. (Apart from the messages being somewhat cryptic, the behaviour
was correct before upgrade.)
# Have the invalidation logic both in TriggerDescriptor and SPSDescriptor. In that case, we'll
have the cryptic message when we drop a UDT/aggregate/sequence used in one of the existing
triggers. And if we drop one that is used in a trigger created with the new version, we'll
get *both* the cryptic message and the one that shows the actual trigger name, in two SQLExceptions
that are linked to each other.

I guess we could live with either of those solutions even though both are suboptimal.

Another option is to keep the logic in SPSDescriptor.prepareToInvalidate() and add some logic
to make it look up the actual trigger name in the system tables. But I think that would probably
be too much complexity for too little gain in these edge cases.

> Trigger should register a dependency on tables and columns used in its body
> ---------------------------------------------------------------------------
>                 Key: DERBY-2041
>                 URL: https://issues.apache.org/jira/browse/DERBY-2041
>             Project: Derby
>          Issue Type: Bug
>          Components: Documentation, SQL
>    Affects Versions:
>            Reporter: Bryan Pendleton
>            Assignee: Knut Anders Hatlen
>            Priority: Minor
>              Labels: derby_triage10_11
>         Attachments: register-dependencies.diff
> A trigger registers a dependency in the Dependency Manager for those columns which cause
the firing of the trigger, but does not register a dependency on tables and columns which
are used in the body of the trigger. This means that the trigger may unexpectedly become invalid
due to a change to one of those tables and columns in its body, and the user may be surprised
that Derby did not warn them of this problem when the underlying table/column change was made.
> An example of this problem is as follows:
> create table t1 (c1 int);
> create table t2 (c2 int);
> create trigger tr1 after update of c1 on t1  for each row mode db2sql insert into t2
(c2) values (1);
> With this set of definitions, Derby will warn/prevent the user about changes to table
t1 and column c1, but will not warn the user about changes to table t2 and column c2. If the
user drops or renames t2 or c2, the trigger will then give an error the next time it fires.
> It seems like it would be an improvement for the trigger to record this dependency on
table t2 and column c2.

This message was sent by Atlassian JIRA

View raw message