473,381 Members | 1,482 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,381 software developers and data experts.

Bitemporal tables sequenced unique triggers

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
0 2949

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

Similar topics

2
by: Ori | last post by:
Hi, I have two tables such that in each table I need to make sure that column x in table A and column y in table B have a unique values meaning that a user cannot insert a value to column A if...
4
by: stacdab | last post by:
We have a partitioned view with 4 underlying tables. The view and each of the underlying tables are in seperate databases on the same server. Inserts and deletes on the view work fine. We then...
10
by: heromull | last post by:
We have an asp.net app with about 200 data entry forms. Customers may enter data into any number of forms. Each form's data is persisted in a corresponding sql table. When data entry is...
1
by: Christoph Graf | last post by:
Hi everybody! As far as I have seen you can inherit from a table and get its columns. Is there a possibility to also inherit a tables triggers? When I simply derive a table from another I...
2
by: Rune Froysa | last post by:
I have one table with columns that are used as foreign-keys from several other tables. Sometimes deletion from this table takes +5 seconds for a single row. Are there any utilities that can be...
1
by: Ruediger Herrmann | last post by:
Hello again, as I am new to PostgreSQL (great pice of softwork btw) this probably won't be the last question. I access PostgreSQL from Java via the PostgreSQL JDBC driver. I am currently...
6
by: heyvinay | last post by:
I have transaction table where the rows entered into the transaction can come a result of changes that take place if four different tables. So the situation is as follows: Transaction Table...
3
by: meeraguna | last post by:
We have 20 -30 normalized tables in our dartabase . Also we have 4 tables where we store the calculated data fron those normalised tables. The Reason we have these 4 denormalised tables is when we...
4
by: --CELKO-- | last post by:
I need to convert a bunch of DB2 triggers to Oracle. Is there any kind of tools for this?
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.