Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id E6A099F924D
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  6 Nov 2007 08:50:30 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 59782-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  6 Nov 2007 08:50:26 -0400 (AST)
Received: from mercury.eclipse.kcom.com (mercury.karoo.kcom.com
	[212.50.160.61])
	by postgresql.org (Postfix) with ESMTP id 9C3B69F9243
	for <pgsql-sql@postgresql.org>; Tue,  6 Nov 2007 08:50:25 -0400 (AST)
X-IronPort-AV: E=Sophos;i="4.21,378,1188774000"; d="scan'208";a="169459908"
Received: from unknown (HELO [192.168.0.3]) ([81.168.109.141])
	by mercury.eclipse.kcom.com with ESMTP; 06 Nov 2007 12:50:22 +0000
From: Keith Carr <klinux@carrhome.eclipse.co.uk>
To: pgsql-sql@postgresql.org
Subject: Bitemporal - problem with correalated subquery?
Date: Tue, 6 Nov 2007 12:51:45 +0000
User-Agent: KMail/1.9.5
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200711061251.45332.klinux@carrhome.eclipse.co.uk>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200711/22
X-Sequence-Number: 29550

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:

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)
);


Here is the function/trigger I seem to be having trouble with (although there are others which maintain the integrity of the data - meaning records cannot overlap):

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 trigger/function (P_O_integrity) that does not work properly. Specifically it is the following part:

-- 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))))


This can be rewritten as follows:

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))));

And if run on the data below, should pull out customer_no's '2' and '3'.
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 <=

The arrows indicate where there are gaps.

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 | 2000-01-01 | 9999-12-31

The 2nd and 3rd lines should have not been able to be inserted with the trigger as they "bridge" records with gaps in the Customers table.

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.
It seems to be returning B2 from the NOT EXISTS correlated sub query rather than B?!?

Thanks for any help you can give.
Keith