Home » SQL & PL/SQL » SQL & PL/SQL » How to enforce key uniqueness in a temporal table? (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
| How to enforce key uniqueness in a temporal table? [message #617202] |
Wed, 25 June 2014 23:01  |
 |
SeanBDurkin
Messages: 1
Registered: June 2014
Location: Australia
|
Junior Member |
|
|
What is the best way to enforce key uniqueness in a temporal table? A temporal table is one where all historical states are recorded with a time-span.
For example, we have a Key --> Value association like this ...
create table TEMPORAL_VALUES
(KEY1 varchar2(99) not null,
VALUE1 varchar2(99),
START_PERIOD date not null,
END_PERIOD date not null);
There are two constraints to enforce to do with the temporal nature of the table, to wit:
- For each record we must have END_PERIOD > START_PERIOD. This is the period for which the Key->Value map is valid.
- For each Key, there can't be any overlapping periods. The period includes the moment of the START_PERIOD, but excludes the exact moment of the END_PERIOD.
Constraint enforcement could be done either on row insert/update, or on commit. I don't really care, as long as it is impossible to commit invalid data.
I've been informed that the best practice to enforce constraints like this is to use materialized views instead of triggers. An alternate design pattern might be Kuznetsov's History Table. But Kuznetsov's pattern is for general SQL and may not translate well into Oracle sql.
Please advise on what is the best way to achieve this?
I think that this solution is close, but it doesn't really work because 'on commit' is needed. Oracle doesn't seem capable of creating a materialized view of this complexity which refreshes on commit.
create materialized view OVERLAPPING_VALUES
nologging cache build immediate
refresh complete on demand
as select 'Wrong!'
from
(
select KEY1, END_PERIOD,
lead( START_PERIOD, 1) over (partition by KEY1 order by START_PERIOD) as NEXT_START
from TEMPORAL_VALUES
)
where NEXT_START < END_PERIOD;
alter table OVERLAPPING_VALUES add CHECK( 0 = 1 );
What am I doing wrong? How do I get this work on commit to prevent invalid rows in TEMPORAL_VALUES?
Thanks in advance if you can help,
Faithfully,
Sean B. Durkin
|
|
|
|
|
|
| Re: How to enforce key uniqueness in a temporal table? [message #617242 is a reply to message #617202] |
Thu, 26 June 2014 07:21  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Overlaps are not allowed, but are gaps allowed? Anyway:
SQL> CREATE MATERIALIZED VIEW CATCH_OVERLAPPING_VALUES
2 NOLOGGING
3 CACHE
4 BUILD IMMEDIATE
5 REFRESH COMPLETE
6 ON COMMIT
7 AS
8 SELECT END_PERIOD,
9 LEAD(START_PERIOD) OVER(PARTITION BY KEY1 ORDER BY START_PERIOD) NEXT_START
10 FROM TEMPORAL_VALUES
11 /
Materialized view created.
SQL> -- Clean start
SQL> TRUNCATE TABLE TEMPORAL_VALUES
2 /
Table truncated.
SQL> ALTER TABLE CATCH_OVERLAPPING_VALUES
2 ADD CONSTRAINT CATCH_OVERLAPPING_VALUES_CHK1
3 CHECK(
4 NEXT_START >= END_PERIOD
5 )
6 /
Table altered.
SQL> INSERT
2 INTO TEMPORAL_VALUES
3 VALUES(
4 'KEY1',
5 'VAL1',
6 DATE '2014-01-01',
7 DATE '2014-01-31'
8 )
9 /
1 row created.
SQL> COMMIT
2 /
Commit complete.
SQL> -- No Gap
SQL> INSERT
2 INTO TEMPORAL_VALUES
3 VALUES(
4 'KEY1',
5 'VAL2',
6 DATE '2014-02-01',
7 DATE '2014-02-15'
8 )
9 /
1 row created.
SQL> COMMIT
2 /
Commit complete.
SQL> -- Gap
SQL> INSERT
2 INTO TEMPORAL_VALUES
3 VALUES(
4 'KEY1',
5 'VAL3',
6 DATE '2014-03-01',
7 DATE '2014-03-31'
8 )
9 /
1 row created.
SQL> COMMIT
2 /
Commit complete.
SQL> -- Overlap
SQL> INSERT
2 INTO TEMPORAL_VALUES
3 VALUES(
4 'KEY1',
5 'VAL4',
6 DATE '2014-02-03',
7 DATE '2014-03-25'
8 )
9 /
1 row created.
SQL> COMMIT
2 /
COMMIT
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (SCOTT.CATCH_OVERLAPPING_VALUES_CHK1) violated
SQL>
SY.
|
|
|
|
|
|
|
|
| Re: How to enforce key uniqueness in a temporal table? [message #617268 is a reply to message #617249] |
Thu, 26 June 2014 10:22 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
DrabJay wrote on Thu, 26 June 2014 09:52The identifier of the lock used in the dbms_lock.request call could incorporate the KEY1 value(s) of the affected row(s).
It also depends on if KEY1 can change. If KEY1 can change, then for UPDATE trigger would have to create two locks per rowid - one with :OLD.KEY1 and other with :NEW.KEY1 (if KEY1 changed). And even if KEY1 can't change we would have to capture KEY1 values so we can place KEY1 based lock in statement level trigger.
SY.
[Updated on: Thu, 26 June 2014 10:38] Report message to a moderator |
|
|
|
|
|
| Re: How to enforce key uniqueness in a temporal table? [message #617282 is a reply to message #617276] |
Thu, 26 June 2014 13:47 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
DrabJay wrote on Thu, 26 June 2014 12:59The Trigger approach as in Barbara's solution does not need to included deletes.
Then it will be inconsistent. INSERT and UPDATE will be serialized while DELETE will be not. As a result it will all depend on timing. Table data:
SQL> SELECT *
2 FROM table1
3 /
COL1 D1 D2
---------- --------- ---------
1 01-JAN-14 01-MAR-14
SQL>
Session 1:
SQL> INSERT
2 INTO table1
3 VALUES(
4 1,
5 DATE '2014-04-01',
6 DATE '2014-05-01'
7 )
8 /
1 row created.
SQL>
Session 2 issues INSERT and hangs waiting for Session 1 to release lock:
SQL> INSERT
2 INTO table1
3 VALUES(
4 1,
5 DATE '2014-01-01',
6 DATE '2014-03-01'
7 )
8 /
Session 3:
SQL> DELETE table1
2 WHERE d1 = DATE '2014-01-01'
3 /
1 row deleted.
SQL>
Now it all depends who commits first. If Session 3 commits first and Session 1 later, then Session 2 succeeds. If Session 1 commits first and Session 3 later, then Session 2 fails.
However, if we add DELETE to two triggers, Session 2 will always fail since we serialized the process. I will add ON DELETE to two triggers:
SQL> CREATE OR REPLACE
2 TRIGGER table1_biu
3 BEFORE INSERT
4 OR UPDATE
5 OR DELETE
6 ON table1
7 BEGIN
8 state_pkg.rowids := state_pkg.empty;
9 END table1_biu;
10 /
Trigger created.
SQL> CREATE OR REPLACE
2 TRIGGER table1_aiu
3 AFTER INSERT
4 OR UPDATE
5 OR DELETE
6 ON table1
7 DECLARE
8 v_status INTEGER;
9 v_rec table1%ROWTYPE;
10 v_count NUMBER;
11 BEGIN
12 v_status := DBMS_LOCK.REQUEST(
13 ID => 123,
14 LOCKMODE => DBMS_LOCK.X_MODE,
15 RELEASE_ON_COMMIT => TRUE
16 );
17 FOR i IN 1 .. state_pkg.rowids.COUNT LOOP
18 SELECT *
19 INTO v_rec
20 FROM table1
21 WHERE ROWID = state_pkg.rowids(i);
22 SELECT COUNT(*)
23 INTO v_count
24 FROM table1
25 WHERE ROWID <> state_pkg.rowids(i)
26 AND v_rec.d1 <= d2
27 AND v_rec.d2 >= d1;
28 IF v_count > 0
29 THEN
30 RAISE_APPLICATION_ERROR(
31 -20001,
32 v_rec.d1 || ' to ' || v_rec.d2 || ' overlaps existing values.'
33 );
34 END IF;
35 END LOOP;
36 END table1_aiu;
37 /
Trigger created.
SQL>
Session 1:
SQL> INSERT
2 INTO table1
3 VALUES(
4 1,
5 DATE '2014-04-01',
6 DATE '2014-05-01'
7 )
8 /
1 row created.
SQL>
Session 2 issues INSERT and hangs waiting for Session 1 to release lock:
SQL> INSERT
2 INTO table1
3 VALUES(
4 1,
5 DATE '2014-01-01',
6 DATE '2014-03-01'
7 )
8 /
Session 3 issues DELETE and hangs waiting for Sessions 1 & 2 to release lock:
SQL> DELETE table1
2 WHERE d1 = DATE '2014-01-01'
3 /
1 row deleted.
SQL>
Now only Session 1 can commit/rollback. So it commits. Then Session 2 continues and fails:
SQL> INSERT
2 INTO table1
3 VALUES(
4 1,
5 DATE '2014-04-01',
6 DATE '2014-05-01'
7 )
8 /
INTO table1
*
ERROR at line 2:
ORA-20001: 01-APR-14 to 01-MAY-14 overlaps existing values.
ORA-06512: at "SCOTT.TABLE1_AIU", line 24
ORA-04088: error during execution of trigger 'SCOTT.TABLE1_AIU'
SQL>
And releases the lock so Session 3 proceeds with delete:
SQL> DELETE table1
2 WHERE d1 = DATE '2014-01-01'
3 /
1 row deleted.
SQL>
If we change events and Session 2 does the delete and then Session 3 does the insert, then everything succeeds.
Session 1:
SQL> INSERT
2 INTO table1
3 VALUES(
4 1,
5 DATE '2014-04-01',
6 DATE '2014-05-01'
7 )
8 /
1 row created.
SQL>
Session 2 issues DELETE and hangs waiting for Session 1 to release lock:
SQL> DELETE table1
2 WHERE d1 = DATE '2014-01-01'
3 /
1 row deleted.
SQL>
Session 3 issues INSERT and hangs waiting for Sessions 1 & 2 to release lock:
SQL> INSERT
2 INTO table1
3 VALUES(
4 1,
5 DATE '2014-01-01',
6 DATE '2014-03-01'
7 )
8 /
Again, only Session 1 can commit/rollback. So it commits. Then Session 2 proceeds with delete:
SQL> DELETE table1
2 WHERE d1 = DATE '2014-01-01'
3 /
1 row deleted.
SQL>
Session 3 still hangs waiting for Session 2 to release the lock. And when Session 2 commits lock is released and Session 3 proceeds with INSERT. And now, when conflicting row is gone (deleted and committed) Session 3 succeeds:
SQL> INSERT
2 INTO table1
3 VALUES(
4 1,
5 DATE '2014-01-01',
6 DATE '2014-03-01'
7 )
8 /
1 row created.
SQL>
SY.
[Updated on: Thu, 26 June 2014 13:51] Report message to a moderator |
|
|
|
| Re: How to enforce key uniqueness in a temporal table? [message #617293 is a reply to message #617282] |
Thu, 26 June 2014 16:13 |
 |
DrabJay
Messages: 32
Registered: May 2013
|
Member |
|
|
Although there is a difference in which sessions succeed or fail depending on the order in which they are committed when the delete is not serialized, it is still the case that their schedules are 'correct' as both of them are serializable in that they are equivalent to a serial schedule of the sessions. In both cases the overlap constraint is not violated.
Therefore, the addition of the serialization of the delete is not necessary to enforce the constraint using the Trigger approach.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 17:50:45 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
