By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,441 Members | 979 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,441 IT Pros & Developers. It's quick & easy.

Bitemporal tables sequenced unique triggers

P: 1
Hi there, This is my first time posting in here and I'm hoping somebody can point out where I am going wrong?

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
  1. CREATE TABLE Customers
  2. (customer_no INTEGER NOT NULL,
  3.  customer_name CHAR(30) NOT NULL,
  4.  vt_begin DATE DEFAULT CURRENT_DATE,
  5.  vt_end DATE DEFAULT DATE '9999-12-31',
  6.  tt_start DATE DEFAULT CURRENT_DATE,
  7.  tt_stop DATE DEFAULT DATE '9999-12-31',
  8.    CONSTRAINT Cust_VTdates_correct
  9.      CHECK (vt_begin <= vt_end),
  10.    CONSTRAINT Cust_ttdates_correct
  11.      CHECK (tt_start <= tt_stop),
  12.    PRIMARY KEY (customer_no, vt_begin, vt_end, tt_start, tt_stop)
  13. );
  14.  
  15.  
  16. CREATE TABLE Properties
  17. (
  18.  prop_no INTEGER NOT NULL PRIMARY KEY,
  19.  prop_name CHAR(20) NOT NULL
  20. );
  21.  
  22.  
  23. CREATE TABLE Prop_Owner
  24. (
  25.  prop_no INTEGER NOT NULL
  26.    REFERENCES Properties (prop_no),
  27.  customer_no INTEGER NOT NULL,
  28.  vt_begin DATE DEFAULT CURRENT_DATE,
  29.  vt_end DATE DEFAULT DATE '9999-12-31',
  30.  tt_start DATE DEFAULT CURRENT_DATE,
  31.  tt_stop DATE DEFAULT DATE '9999-12-31',
  32.    CONSTRAINT PropOwner_VTdates_correct
  33.      CHECK (vt_begin <= vt_end),
  34.    CONSTRAINT PropOwner_ttdates_correct
  35.      CHECK (tt_start <= tt_stop),
  36.    PRIMARY KEY (prop_no, customer_no, vt_begin, vt_end, tt_start, tt_stop)
  37. );
  38.  
Here are the functions and triggers:
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION Propowner_Seq_Uniq_VT_TT() RETURNS TRIGGER AS
  2. $$
  3. DECLARE vald INTEGER;
  4. BEGIN
  5.  SELECT 1 INTO vald
  6.  WHERE NOT EXISTS
  7.     (SELECT C1.customer_no
  8.      FROM Customers AS C1
  9.      WHERE 1
  10.       < (SELECT COUNT(customer_no)
  11.          FROM Customers AS C2
  12.          WHERE C1.customer_no = C2.customer_no
  13.            AND C1.vt_begin < C2.vt_end AND C2.vt_begin < C1.vt_end
  14.            AND C1.tt_start < C2.tt_stop AND C2.tt_start < C1.tt_stop
  15.   ));
  16.   IF NOT FOUND THEN
  17.        RAISE EXCEPTION 'NOPE! nobody can have 2 records for the same vt and tt times';
  18.   END IF;
  19.   RETURN NULL;
  20. END;
  21. $$
  22. LANGUAGE plpgsql;
  23.  
  24. CREATE TRIGGER Propowner_Seq_Uniq_VT_TT
  25. AFTER INSERT OR UPDATE OR DELETE ON Customers
  26.     FOR EACH ROW EXECUTE PROCEDURE Propowner_Seq_Uniq_VT_TT();
  27.  
  28.  
  29. CREATE OR REPLACE FUNCTION Propown_SeqUnq_VT_TT() RETURNS TRIGGER AS
  30. $$
  31. DECLARE vald INTEGER;
  32. BEGIN
  33.  SELECT 1 INTO vald
  34.  WHERE NOT EXISTS
  35.     (SELECT PO1.customer_no
  36.      FROM Prop_Owner AS PO1
  37.      WHERE 1
  38.       < (SELECT COUNT(customer_no)
  39.          FROM Prop_Owner AS PO2
  40.          WHERE PO1.customer_no = PO2.customer_no
  41.            AND PO1.vt_begin < PO2.vt_end AND PO2.vt_begin < PO1.vt_end
  42.            AND PO1.tt_start < PO2.tt_stop AND PO2.tt_start < PO1.tt_stop
  43.   ));
  44.   IF NOT FOUND THEN
  45.        RAISE EXCEPTION 'NOPE! no property_customer combination can have 2 records for the same vt and tt times';
  46.   END IF;
  47.   RETURN NULL;
  48. END;
  49. $$
  50. LANGUAGE plpgsql;
  51.  
  52. CREATE TRIGGER Propown_SeqUnq_VT_TT
  53. AFTER INSERT OR UPDATE OR DELETE ON Prop_Owner
  54.     FOR EACH ROW EXECUTE PROCEDURE Propown_SeqUnq_VT_TT();
  55.  
  56.  
  57. CREATE OR REPLACE FUNCTION P_O_integrity() RETURNS TRIGGER AS
  58. $$
  59. DECLARE vald INTEGER;
  60. BEGIN
  61.  SELECT 1 INTO vald
  62.  WHERE NOT EXISTS
  63.   (SELECT *
  64.    FROM Prop_Owner AS A
  65. -- there was a row valid in <ReferencedTable> when A started
  66.    WHERE NOT EXISTS
  67.     (SELECT *
  68.      FROM Customers AS B
  69.      WHERE A.customer_no = B.customer_no
  70.        AND B.vt_begin <= A.vt_begin AND A.vt_begin < B.vt_end
  71.        AND B.tt_start <= A.tt_start AND A.tt_start < B.tt_stop)
  72. -- there was a row valid in <ReferencedTable> when A ended
  73.    OR NOT EXISTS
  74.     (SELECT *
  75.      FROM Customers AS B
  76.      WHERE A.customer_no = B.customer_no
  77.        AND B.vt_begin < A.vt_end AND A.vt_end <= B.vt_end
  78.        AND B.tt_start < A.tt_stop AND A.tt_stop <= B.tt_stop)
  79. -- there are no gaps in <ReferencedTable> during A's period of validity
  80.    OR EXISTS
  81.     (SELECT *
  82.      FROM Customers AS B
  83.      WHERE A.customer_no = B.customer_no
  84.        AND ((A.vt_begin < B.vt_end AND B.vt_end < A.vt_end)
  85.                     OR (A.tt_start < B.tt_stop AND B.tt_stop < A.tt_stop))
  86.        AND NOT EXISTS
  87.            (SELECT *
  88.         FROM Customers AS B2
  89.         WHERE B2.customer_no = B.customer_no
  90.           AND ((B2.vt_begin <= B.vt_end AND B.vt_end < B2.vt_end)
  91.                           OR (B2.tt_start <= B.tt_stop AND B.tt_stop < B2.tt_stop))))
  92.   );
  93.  IF NOT FOUND THEN
  94.        RAISE EXCEPTION 'Referential integrity breached. No covering Foreign Key';
  95.  END IF;
  96. RETURN NULL;
  97. END;
  98. $$
  99. LANGUAGE plpgsql;
  100.  
  101.  
  102. CREATE TRIGGER P_O_integrity
  103. AFTER INSERT OR UPDATE OR DELETE  ON Prop_Owner
  104.     FOR EACH ROW EXECUTE PROCEDURE P_O_integrity();
  105.  

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
  1. -- there are no gaps in <ReferencedTable> during A's period of validity
  2.    OR EXISTS
  3.     (SELECT *
  4.      FROM Customers AS B
  5.      WHERE A.customer_no = B.customer_no
  6.        AND ((A.vt_begin < B.vt_end AND B.vt_end < A.vt_end)
  7.                     OR (A.tt_start < B.tt_stop AND B.tt_stop < A.tt_stop))
  8.        AND NOT EXISTS
  9.            (SELECT *
  10.         FROM Customers AS B2
  11.         WHERE B2.customer_no = B.customer_no
  12.           AND ((B2.vt_begin <= B.vt_end AND B.vt_end < B2.vt_end)
  13.                           OR (B2.tt_start <= B.tt_stop AND B.tt_stop < B2.tt_stop))))
  14.  
This can be rewritten as follows:
Expand|Select|Wrap|Line Numbers
  1. SELECT customer_no
  2. FROM Prop_Owner AS A
  3. WHERE EXISTS
  4.   (SELECT customer_no
  5.    FROM Customers AS B
  6.    WHERE A.customer_no = B.customer_no
  7.      AND ((A.vt_begin < B.vt_end AND B.vt_end < A.vt_end)
  8.               OR (A.tt_start < B.tt_stop AND B.tt_stop < A.tt_stop))
  9.      AND NOT EXISTS
  10.        (SELECT customer_no
  11.         FROM Customers AS B2
  12.         WHERE B2.customer_no = B.customer_no
  13.           AND ((B2.vt_begin <= B.vt_end AND B.vt_end < B2.vt_end)
  14.                    OR (B2.tt_start <= B.tt_stop AND B.tt_stop < B2.tt_stop))));
  15.  
But does not seem to select any of the rows in which there are gaps in Customers during the validity of Prop_Owner

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
Nov 5 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.