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] [Commented] (DERBY-6665) Violation of deferred constraints not detected when conglomerates are shared
Date Thu, 17 Jul 2014 08:22:04 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6665?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14064705#comment-14064705
] 

Knut Anders Hatlen commented on DERBY-6665:
-------------------------------------------

The key of the {{HashMap<Long, ValidationInfo> deferredHashTables}} field in {{GenericLanguageConnectionContext}}
is:
- CHECK constraint: The conglomerate number of the heap conglomerate of the table on which
the constraint is defined.
- UNIQUE constraint: The conglomerate number of the B-tree conglomerate that backs the constraint.
- FOREIGN KEY constraint: The conglomerate number of the B-tree conglomerate that backs the
constraint.

If we change the key to a UUID, we have these choices:
- CHECK constraint: Either the UUID of the table descriptor or the UUID of the heap conglomerate.
- UNIQUE constraint: Either the UUID of the constraint or the UUID of the B-tree conglomerate.
- FOREIGN KEY constraint: Either the UUID of the constraint or the UUID of the B-tree conglomerate.

Functionally, I don't think it matters much which UUID we choose, as long as we're consistent.
They all seem to be allocated uniquely for logical conglomerates rather than physical conglomerates.
It sounds cleaner, though, to use the table/constraint UUIDs and not deal with the conglomerate
ids at all.

One more important difference between the conglomerate numbers that we currently hash on,
and the UUIDs, is that the UUIDs do not change when SYSCS_COMPRESS_TABLE is called. There
is a fair amount of code to handle the case where compression has changed the conglomerate
numbers during the transaction, and I believe that code will be redundant if we switch to
UUIDs.

> Violation of deferred constraints not detected when conglomerates are shared
> ----------------------------------------------------------------------------
>
>                 Key: DERBY-6665
>                 URL: https://issues.apache.org/jira/browse/DERBY-6665
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.11.0.0
>            Reporter: Knut Anders Hatlen
>            Assignee: Knut Anders Hatlen
>
> See the following script:
> {noformat}
> ij version 10.11
> ij> connect 'jdbc:derby:memory:db;create=true';
> ij> create table t1(x int primary key);
> 0 rows inserted/updated/deleted
> ij> create table t2(x int primary key);
> 0 rows inserted/updated/deleted
> ij> create table t3(x int, constraint fk1 foreign key (x) references t1 initially
deferred, constraint fk2 foreign key (x) references t2 initially deferred);
> 0 rows inserted/updated/deleted
> ij> insert into t1 values 1;
> 1 row inserted/updated/deleted
> ij> autocommit off;
> ij> insert into t3 values 1;
> 1 row inserted/updated/deleted
> ij> insert into t2 values 1;
> 1 row inserted/updated/deleted
> ij> delete from t1;
> 1 row inserted/updated/deleted
> ij> commit;
> ij> select * from t1;
> X          
> -----------
> 0 rows selected
> ij> select * from t2;
> X          
> -----------
> 1          
> 1 row selected
> ij> select * from t3;
> X          
> -----------
> 1          
> 1 row selected
> {noformat}
> Since T3.X contains a value (1) that is not present in T1, the foreign key FK1 is violated,
and the COMMIT statement should have failed.



--
This message was sent by Atlassian JIRA
(v6.2#6252)

Mime
View raw message