Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2007
    Posts
    6

    Unanswered: Bitemporal tables sequenced unique triggers - SQL Problem

    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:

    Code:
    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 are the functions and triggers:
    Code:
    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:
    Code:
    -- 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:
    Code:
    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))));
    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

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    I'm not going to be able to look at your code for several weeks, but, have you referenced this pg user's (apparently) similar approach?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    eek, thats a lot to go through. I will try and look at it more later

    Have you broken up the queries and tried the smaller parts out to see if they work as expected?
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  4. #4
    Join Date
    Nov 2007
    Posts
    6
    Quote Originally Posted by loquin
    I'm not going to be able to look at your code for several weeks, but, have you referenced this pg user's (apparently) similar approach?
    Hi Loquin,
    Thanks for your reply. Just had a look at the artical/previous work - and I already have that part working perfectly (where I just want to make sure that things in the same table do not overlap). This I have accomplished with the first two functions/triggers I listed.
    The part I am having trouble with is where I go a step further - and make sure that a 'period' in the referencing table cannot reference a period in the referenced table for which it is not valid. For example - say you have a customer who is valid on your organisation/database and then leaves and then returns. I might have another table that references this - but I would want to make sure that I cannot mistakenly enter a period that bridges the 'gap' between the two periods in the first table.
    So the only trouble I have is with the 3rd funtion/trigger (P_O_integrity). In fact - I have most of this working - so it will make sure there was a relevant 'record' at the beginning and the end of the period for which the referencing period is valid - it is just the final part which is causing problems - where it makes sure that there are no gaps in the referenced table for the period of the referencing tables validity.
    Most of my code comes from Richard Snodgrass' book 'Developing Time orientated Applications in SQL' (which is out of print but can be downloaded from here: http://www.cs.arizona.edu/~rts/tdbbook.pdf) It is code fragment 5.21 I am using.
    In fact I am following his example in this so I cannot see why it is not working. I feel maybe it is more how Postgres in interpreting the very final section as I have broken out above.
    Thanks for taking the time to look at this, it is much appreciated

  5. #5
    Join Date
    Nov 2007
    Posts
    6
    Quote Originally Posted by amthomas
    eek, thats a lot to go through. I will try and look at it more later

    Have you broken up the queries and tried the smaller parts out to see if they work as expected?
    Hi amthomas,
    Yes, broken it down and both parts work as expected. Put them together and it doesn't?!? Strange....
    So this part (as it only this part not working right):
    Code:
    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))));
    I have broken down into:
    Code:
    SELECT *
    FROM Prop_Owner AS A
    WHERE 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
    Code:
    SELECT *
       FROM Customers AS B
       WHERE 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)));
    Which both return results (because I have put in rows in Prop_Owner that 'mistakenly' reference periods in Customers with gaps) as expected.
    However put them together as above and it returns nothing. It is like the inner part of the correlated subquery is returning B2 and so the outher part has nothing to work with??!?
    Thanks for any words of wisdom you may have

  6. #6
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    Code:
    SELECT customer_no
       FROM Customers AS B, Prop_Owner A
       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)))
    Does that work for you? hehe.. I have not used exist much so I am pretty slow following this.

    It is my understanding that it is going to give you customer_no that do not exist based on the conditions of the subquery. You could also replace (SELECT customer_no with... (SELECT 1...) and it should still operate fine.

    ugh.. I hate messing with time limitations :P haha. Sorry.. I am not being very helpful.
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  7. #7
    Join Date
    Nov 2007
    Posts
    6
    Guess what Amthomas, I tried exactly the same thing by flattening the code out as you did and it still did not work.
    It is though the value of B2 from the inner NOT EXISTS is being passed back to the outer EXISTS rather than B. For it to work B needs to be passed back.

    Your understanding of the query is quite correct - that it will return a result when the referenced table is not valid at either the valid or transaction time in the referencing table.

    I have been trying to get this to work for a few days and I am starting to hate time limitations also! But they are so good for integrity!
    Thanks again for you help

  8. #8
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    sorry, going to do another drive by again

    Do this:
    Code:
    SELECT customer_no
       FROM Customers AS B, Prop_Owner A
       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 for any results in that that do not meet the results in

    Code:
    SELECT customer_no
            FROM Customers AS B2
            WHERE B2.customer_no = B.customer_no
              AND ((B2.vt_begin <= B2.vt_end AND B2.vt_end < B2.vt_end)
                       OR (B2.tt_start <= B2.tt_stop AND B2.tt_stop < B2.tt_stop))
    Should not be in the final result. So if part 1 returns correctly and part 2 returns correctly then the results should be part 1 minus results for part 2. To me, it seems like the results for each would be identical which would mean the results cancel each other out. But... I think I am missing something
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  9. #9
    Join Date
    Nov 2007
    Posts
    6
    Hi amthomas,

    Sorry for my slowness in getting back. I wanted to test some stuff before I posted again. so here goes...........

    The original code I was having trouble with (as posted above):
    Code:
    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))));
    Actually needs to be written like this to work correctly:
    Code:
    SELECT *
       FROM Prop_Owner AS A
       WHERE 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 1
    		= (SELECT COUNT(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)))))
    (to do with the number of matching rows)

    However - I then started thinking some more, because this only worked correctly with VT (valid time) and did not with TT (Transaction Time). Then I realised - there are a LOT of permutations to how these <referenced> TT's and VT's could meet up and the <referencing> VT's and TT's overlap them!! So this function would quickly become bloated with a lot of UNIONS I think and therefore slow database performance if used on a lot of tables!

    So there is a different tack.........
    Instead of trying to figure out all gaps - DO NOT ALLOW gaps in VT and TT in the <referenced> table! This makes sense to do anyway - a customer should have a detail changed immediately from one thing to another - they do not "dissapear" for a while - even it the TT's are different to the VT's.

    So to stop gaps in the <referenced> table i have written this function/trigger for it:
    Code:
    CREATE OR REPLACE FUNCTION Customer_Contiguous() RETURNS TRIGGER AS
    $$
    DECLARE val INTEGER;
    BEGIN
     SELECT 1 INTO val
     WHERE NOT EXISTS
      (SELECT *
         FROM Customers AS C1
        WHERE EXISTS
             (SELECT *
                FROM Customers AS C2
               WHERE C1.vt_end < C2.vt_begin
                 AND ((C1.tt_start < C2.tt_stop) AND (C2.tt_start < C1.tt_stop))
                 AND C1.customer_no = C2.customer_no
                 AND (NOT EXISTS
                       (SELECT *
                          FROM Customers AS C3
                         WHERE C3.customer_no = C1.customer_no
                           AND (C3.tt_stop = DATE '9999-12-31')
                           AND (((C3.vt_begin <= C1.vt_end) AND (C1.vt_end < C3.vt_end))
                                OR ((C3.vt_begin < C2.vt_begin) AND (C2.vt_begin <= C3.vt_end))))))
              OR EXISTS
                (SELECT *
                   FROM Customers AS C2
                  WHERE C1.tt_stop < C2.tt_start
                    AND ((C1.vt_begin < C2.vt_end) AND (C2.vt_begin < C1.vt_end))
                    AND C1.customer_no = C2.customer_no
                    AND (NOT EXISTS
                           (SELECT *
                              FROM Customers AS C3
                             WHERE C3.customer_no = C1.customer_no
                               AND (C3.vt_end = DATE '9999-12-31')
                               AND (((C3.tt_start <= C1.tt_stop) AND (C1.tt_stop < C3.tt_stop))
                                    OR ((C3.tt_start < C2.tt_start) AND (C2.tt_start <= C3.tt_stop))))))
      );
     IF NOT FOUND THEN
           RAISE EXCEPTION 'Contiguous history breached';
     END IF;
    RETURN NULL;
    END;
    $$
    LANGUAGE plpgsql;
    
    
    CREATE TRIGGER Customer_Contiguous
    AFTER INSERT OR UPDATE OR DELETE  ON Customers
    	FOR EACH ROW EXECUTE PROCEDURE P_O_integrity();
    And now the <referencing> table may now use the vastly simplified function/trigger:
    Code:
    CREATE OR REPLACE FUNCTION P_O_integrity() RETURNS TRIGGER AS
    $$
    DECLARE val INTEGER;
    BEGIN
     SELECT 1 INTO val
     WHERE NOT EXISTS
      (SELECT *
         FROM Prop_Owner AS A
        WHERE A.tt_stop = DATE '9999-12-31'
          AND ((NOT EXISTS
                  (SELECT *
                     FROM Customers AS B
                    WHERE A.customer_no = B.customer_no
                      AND (B.tt_stop = DATE '9999-12-31')
                      AND (B.vt_begin <= A.vt_begin)
                      AND (A.vt_begin < B.vt_end)))
              OR (NOT EXISTS
                    (SELECT *
                       FROM Customers AS B
                      WHERE A.customer_no = B.customer_no
                        AND (B.tt_stop = DATE '9999-12-31')
                        AND (B.vt_begin < A.vt_end)
                        AND (A.vt_end <= B.vt_end))))
      );
     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();
    I think I have to test these fully - but they seem to do the job.

    In my head though I think it would still be nice to achieve the first solution I was after (so it could detect gaps) - however it might take a lot of thinking! However I I like these sorts of projects! (I must be mad)
    Maybe this can be a "pet" project of mine?

    Thanks for all your help in this
    Keith

  10. #10
    Join Date
    Nov 2007
    Posts
    6
    Hi amthomas,

    Sorry for my slowness in getting back. I wanted to test some stuff before I posted again. so here goes...........

    The original code I was having trouble with (as posted above):
    Code:
    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))));
    Actually needs to be written like this to work correctly:
    Code:
    SELECT *
       FROM Prop_Owner AS A
       WHERE 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 1
    		= (SELECT COUNT(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)))))
    (to do with the number of matching rows)

    However - I then started thinking some more, because this only worked correctly with VT (valid time) and did not with TT (Transaction Time). Then I realised - there are a LOT of permutations to how these <referenced> TT's and VT's could meet up and the <referencing> VT's and TT's overlap them!! So this function would quickly become bloated with a lot of UNIONS I think and therefore slow database performance if used on a lot of tables!

    So there is a different tack.........
    Instead of trying to figure out all gaps - DO NOT ALLOW gaps in VT and TT in the <referenced> table! This makes sense to do anyway - a customer should have a detail changed immediately from one thing to another - they do not "dissapear" for a while - even it the TT's are different to the VT's.

    So to stop gaps in the <referenced> table i have written this function/trigger for it:
    Code:
    CREATE OR REPLACE FUNCTION Customer_Contiguous() RETURNS TRIGGER AS
    $$
    DECLARE val INTEGER;
    BEGIN
     SELECT 1 INTO val
     WHERE NOT EXISTS
      (SELECT *
         FROM Customers AS C1
        WHERE EXISTS
             (SELECT *
                FROM Customers AS C2
               WHERE C1.vt_end < C2.vt_begin
                 AND ((C1.tt_start < C2.tt_stop) AND (C2.tt_start < C1.tt_stop))
                 AND C1.customer_no = C2.customer_no
                 AND (NOT EXISTS
                       (SELECT *
                          FROM Customers AS C3
                         WHERE C3.customer_no = C1.customer_no
                           AND (C3.tt_stop = DATE '9999-12-31')
                           AND (((C3.vt_begin <= C1.vt_end) AND (C1.vt_end < C3.vt_end))
                                OR ((C3.vt_begin < C2.vt_begin) AND (C2.vt_begin <= C3.vt_end))))))
              OR EXISTS
                (SELECT *
                   FROM Customers AS C2
                  WHERE C1.tt_stop < C2.tt_start
                    AND ((C1.vt_begin < C2.vt_end) AND (C2.vt_begin < C1.vt_end))
                    AND C1.customer_no = C2.customer_no
                    AND (NOT EXISTS
                           (SELECT *
                              FROM Customers AS C3
                             WHERE C3.customer_no = C1.customer_no
                               AND (C3.vt_end = DATE '9999-12-31')
                               AND (((C3.tt_start <= C1.tt_stop) AND (C1.tt_stop < C3.tt_stop))
                                    OR ((C3.tt_start < C2.tt_start) AND (C2.tt_start <= C3.tt_stop))))))
      );
     IF NOT FOUND THEN
           RAISE EXCEPTION 'Contiguous history breached';
     END IF;
    RETURN NULL;
    END;
    $$
    LANGUAGE plpgsql;
    
    
    CREATE TRIGGER Customer_Contiguous
    AFTER INSERT OR UPDATE OR DELETE  ON Customers
    	FOR EACH ROW EXECUTE PROCEDURE P_O_integrity();
    And now the <referencing> table may now use the vastly simplified function/trigger:
    Code:
    CREATE OR REPLACE FUNCTION P_O_integrity() RETURNS TRIGGER AS
    $$
    DECLARE val INTEGER;
    BEGIN
     SELECT 1 INTO val
     WHERE NOT EXISTS
      (SELECT *
         FROM Prop_Owner AS A
        WHERE A.tt_stop = DATE '9999-12-31'
          AND ((NOT EXISTS
                  (SELECT *
                     FROM Customers AS B
                    WHERE A.customer_no = B.customer_no
                      AND (B.tt_stop = DATE '9999-12-31')
                      AND (B.vt_begin <= A.vt_begin)
                      AND (A.vt_begin < B.vt_end)))
              OR (NOT EXISTS
                    (SELECT *
                       FROM Customers AS B
                      WHERE A.customer_no = B.customer_no
                        AND (B.tt_stop = DATE '9999-12-31')
                        AND (B.vt_begin < A.vt_end)
                        AND (A.vt_end <= B.vt_end))))
      );
     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();
    I think I have to test these fully - but they seem to do the job.

    In my head though I think it would still be nice to achieve the first solution I was after (so it could detect gaps) - however it might take a lot of thinking! However I I like these sorts of projects! (I must be mad)
    Maybe this can be a "pet" project of mine?

    Thanks for all your help in this
    Keith

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •