I am currently trying to use Bitemporal tables. By this I mean a table with a valid times and transaction times. These topics are covered by Joe Celko and Richard Snodgrass in their respective books.
I have developed a simple schema to test the relevant constraints which are required to keep all the valid times and transaction times in order and to make sure they don't overlap. This is shown below and is done using a similar schema of tables for Customers, Properties and Prop_Owners as Richard Snodgrass does in his book.
Of course these constrains are not possible in Postgres, so I have made them as functions and then created triggers for them.
Everything seems to be working except for my function/trigger that maintains the referential integrity between the Prop_Owner and Customers tables when there is a "gap" in the Customers valid time or transaction time.
I am using Postgres 8.1 on Suse10.2
vt = valid time
tt = transaction time
Here is the schema:
Expand|Select|Wrap|Line Numbers
- CREATE TABLE Customers
- (customer_no INTEGER NOT NULL,
- customer_name CHAR(30) NOT NULL,
- vt_begin DATE DEFAULT CURRENT_DATE,
- vt_end DATE DEFAULT DATE '9999-12-31',
- tt_start DATE DEFAULT CURRENT_DATE,
- tt_stop DATE DEFAULT DATE '9999-12-31',
- CONSTRAINT Cust_VTdates_correct
- CHECK (vt_begin <= vt_end),
- CONSTRAINT Cust_ttdates_correct
- CHECK (tt_start <= tt_stop),
- PRIMARY KEY (customer_no, vt_begin, vt_end, tt_start, tt_stop)
- );
- CREATE TABLE Properties
- (
- prop_no INTEGER NOT NULL PRIMARY KEY,
- prop_name CHAR(20) NOT NULL
- );
- CREATE TABLE Prop_Owner
- (
- prop_no INTEGER NOT NULL
- REFERENCES Properties (prop_no),
- customer_no INTEGER NOT NULL,
- vt_begin DATE DEFAULT CURRENT_DATE,
- vt_end DATE DEFAULT DATE '9999-12-31',
- tt_start DATE DEFAULT CURRENT_DATE,
- tt_stop DATE DEFAULT DATE '9999-12-31',
- CONSTRAINT PropOwner_VTdates_correct
- CHECK (vt_begin <= vt_end),
- CONSTRAINT PropOwner_ttdates_correct
- CHECK (tt_start <= tt_stop),
- PRIMARY KEY (prop_no, customer_no, vt_begin, vt_end, tt_start, tt_stop)
- );
Expand|Select|Wrap|Line Numbers
- CREATE OR REPLACE FUNCTION Propowner_Seq_Uniq_VT_TT() RETURNS TRIGGER AS
- $$
- DECLARE vald INTEGER;
- BEGIN
- SELECT 1 INTO vald
- WHERE NOT EXISTS
- (SELECT C1.customer_no
- FROM Customers AS C1
- WHERE 1
- < (SELECT COUNT(customer_no)
- FROM Customers AS C2
- WHERE C1.customer_no = C2.customer_no
- AND C1.vt_begin < C2.vt_end AND C2.vt_begin < C1.vt_end
- AND C1.tt_start < C2.tt_stop AND C2.tt_start < C1.tt_stop
- ));
- IF NOT FOUND THEN
- RAISE EXCEPTION 'NOPE! nobody can have 2 records for the same vt and tt times';
- END IF;
- RETURN NULL;
- END;
- $$
- LANGUAGE plpgsql;
- CREATE TRIGGER Propowner_Seq_Uniq_VT_TT
- AFTER INSERT OR UPDATE OR DELETE ON Customers
- FOR EACH ROW EXECUTE PROCEDURE Propowner_Seq_Uniq_VT_TT();
- CREATE OR REPLACE FUNCTION Propown_SeqUnq_VT_TT() RETURNS TRIGGER AS
- $$
- DECLARE vald INTEGER;
- BEGIN
- SELECT 1 INTO vald
- WHERE NOT EXISTS
- (SELECT PO1.customer_no
- FROM Prop_Owner AS PO1
- WHERE 1
- < (SELECT COUNT(customer_no)
- FROM Prop_Owner AS PO2
- WHERE PO1.customer_no = PO2.customer_no
- AND PO1.vt_begin < PO2.vt_end AND PO2.vt_begin < PO1.vt_end
- AND PO1.tt_start < PO2.tt_stop AND PO2.tt_start < PO1.tt_stop
- ));
- IF NOT FOUND THEN
- RAISE EXCEPTION 'NOPE! no property_customer combination can have 2 records for the same vt and tt times';
- END IF;
- RETURN NULL;
- END;
- $$
- LANGUAGE plpgsql;
- CREATE TRIGGER Propown_SeqUnq_VT_TT
- AFTER INSERT OR UPDATE OR DELETE ON Prop_Owner
- FOR EACH ROW EXECUTE PROCEDURE Propown_SeqUnq_VT_TT();
- CREATE OR REPLACE FUNCTION P_O_integrity() RETURNS TRIGGER AS
- $$
- DECLARE vald INTEGER;
- BEGIN
- SELECT 1 INTO vald
- WHERE NOT EXISTS
- (SELECT *
- FROM Prop_Owner AS A
- -- there was a row valid in <ReferencedTable> when A started
- WHERE NOT EXISTS
- (SELECT *
- FROM Customers AS B
- WHERE A.customer_no = B.customer_no
- AND B.vt_begin <= A.vt_begin AND A.vt_begin < B.vt_end
- AND B.tt_start <= A.tt_start AND A.tt_start < B.tt_stop)
- -- there was a row valid in <ReferencedTable> when A ended
- OR NOT EXISTS
- (SELECT *
- FROM Customers AS B
- WHERE A.customer_no = B.customer_no
- AND B.vt_begin < A.vt_end AND A.vt_end <= B.vt_end
- AND B.tt_start < A.tt_stop AND A.tt_stop <= B.tt_stop)
- -- there are no gaps in <ReferencedTable> during A's period of validity
- OR EXISTS
- (SELECT *
- FROM Customers AS B
- WHERE A.customer_no = B.customer_no
- AND ((A.vt_begin < B.vt_end AND B.vt_end < A.vt_end)
- OR (A.tt_start < B.tt_stop AND B.tt_stop < A.tt_stop))
- AND NOT EXISTS
- (SELECT *
- FROM Customers AS B2
- WHERE B2.customer_no = B.customer_no
- AND ((B2.vt_begin <= B.vt_end AND B.vt_end < B2.vt_end)
- OR (B2.tt_start <= B.tt_stop AND B.tt_stop < B2.tt_stop))))
- );
- IF NOT FOUND THEN
- RAISE EXCEPTION 'Referential integrity breached. No covering Foreign Key';
- END IF;
- RETURN NULL;
- END;
- $$
- LANGUAGE plpgsql;
- CREATE TRIGGER P_O_integrity
- AFTER INSERT OR UPDATE OR DELETE ON Prop_Owner
- FOR EACH ROW EXECUTE PROCEDURE P_O_integrity();
It is this last trigger/function (P_O_integrity) that does not work properly. Specifically it is the following part:
Expand|Select|Wrap|Line Numbers
- -- there are no gaps in <ReferencedTable> during A's period of validity
- OR EXISTS
- (SELECT *
- FROM Customers AS B
- WHERE A.customer_no = B.customer_no
- AND ((A.vt_begin < B.vt_end AND B.vt_end < A.vt_end)
- OR (A.tt_start < B.tt_stop AND B.tt_stop < A.tt_stop))
- AND NOT EXISTS
- (SELECT *
- FROM Customers AS B2
- WHERE B2.customer_no = B.customer_no
- AND ((B2.vt_begin <= B.vt_end AND B.vt_end < B2.vt_end)
- OR (B2.tt_start <= B.tt_stop AND B.tt_stop < B2.tt_stop))))
Expand|Select|Wrap|Line Numbers
- SELECT customer_no
- FROM Prop_Owner AS A
- WHERE EXISTS
- (SELECT customer_no
- FROM Customers AS B
- WHERE A.customer_no = B.customer_no
- AND ((A.vt_begin < B.vt_end AND B.vt_end < A.vt_end)
- OR (A.tt_start < B.tt_stop AND B.tt_stop < A.tt_stop))
- AND NOT EXISTS
- (SELECT customer_no
- FROM Customers AS B2
- WHERE B2.customer_no = B.customer_no
- AND ((B2.vt_begin <= B.vt_end AND B.vt_end < B2.vt_end)
- OR (B2.tt_start <= B.tt_stop AND B.tt_stop < B2.tt_stop))));
The data I used is as follows:
Customers:
customer_no |customer_name | vt_begin | vt_end | tt_start | tt_stop
-------------+--------------------------------+------------+------------+------------+------------
1 | keith | 2006-01-01 | 9999-12-31 | 2006-01-01 | 2006-12-31
1 | keith | 2006-01-01 | 2006-12-31 | 2006-12-31 | 9999-12-31
1 | keith | 2006-12-31 | 9999-12-31 | 2006-12-31 | 2007-12-31
1 | keith | 2006-12-31 | 2007-12-31 | 2007-12-31 | 9999-12-31
2 | simon | 2004-01-01 | 9999-12-31 | 2004-01-01 | 2004-12-01
2 | simon | 2004-01-01 | 2004-12-31 | 2004-12-01 | 9999-12-31
2 | simon | 2004-12-31 | 9999-12-31 | 2004-12-15 | 9999-12-31
3 | john | 2000-01-01 | 9999-12-31 | 2000-01-01 | 2001-01-01
3 | john | 2000-01-01 | 2001-01-01 | 2001-01-01 | 9999-12-31
3 | john | 2002-01-01 | 9999-12-31 | 2002-01-01 | 9999-12-31
Properties:
prop_no | prop_name
---------+----------------------
1 | house
2 | flat
3 | penthouse
Prop_Owner:
prop_no | customer_no | vt_begin | vt_end | tt_start | tt_stop
---------+-------------+------------+------------+------------+------------
1 | 1 | 2006-02-01 | 9999-12-31 | 2006-02-01 | 2006-12-01
2 | 2 | 2004-02-01 | 9999-12-31 | 2004-01-01 | 2004-12-25
3 | 3 | 2000-02-01 | 9999-12-31 | 2003-01-01 | 9999-12-31
I hope this makes sense and that someone can explain why the last part of P_O_integrity is not selecting the records covered with gaps correctly.
Thanks for any help you can give.
Keith