Re: table versioning approach (not auditing) - Mailing list pgsql-general

On 10/6/14, 6:10 PM, Gavin Flower wrote:

Even if timestamps are used extensively, you'd have to be careful joining on them. You may have information valid at T1 and changing at T3, but the transaction has T2, where T1 < T2 < T3 - the appropriate set of data would be associated with T1, would would not get anywhere trying to find data with a timestamp of T2 (unless you were very lucky!).

Yeah, this is why I think timestamps need to be shunned in favor of explicit pointers. Anyone that thinks timestamps are good enough hasn't thought the problem through completely. :)

I also think there's potential value to storing full transaction information (presumably in a separate table): txid_current(), txid_current_snapshot(), now(), current_user, maybe some other stuff (client IP address?). That way you can tell exactly what created a history record. With appropriate shenanigans you can theoretically determine exactly what other history data would be visible at that time without using pointers (but man would that bu ugly!)

Actually things like phone numbers are tricky.  Sometimes you may want to use the current phone number, and not the one extant at that time (as you want to phone the contact now), or you may still want the old phone number (was the call to a specific number at date/time legitimate & who do we charge the cost of the call too).

Yeah, I'm pretty convinced at this point that history/versioning should be built on top of a schema that always contains the current information, if for no other reason than so you always have a PK that points to what's current in addition to your history PKs.

One of the motivations for having an effective_date, was being able to put changes into the database ahead of time.

Finding the current value uses the same logic a find the value at any other date/time - so you don't need a special schema to distinguish the current state from anything else.  For example:

DROP TABLE IF EXISTS stock;

CREATE TABLE stock
(
    id              text,
    effective_date  timestamptz,
    price           numeric
);

INSERT INTO stock
(
   id,
   effective_date,
   price
)
VALUES
    ('y88', '2014-10-01', 12.0),
    ('x42', '2014-10-01', 12.1),
    ('x42', '2014-10-08', 12.2),
    ('x42', '2014-10-10', 12.3),
    ('x42', '2014-10-16', 12.4),
    ('z42', '2014-10-19', 12.5),
    ('z49', '2014-10-01', 12.6),
    ('z49', '2014-10-30', 12.7),
    ('z77', '2014-10-01', 12.8);
   
CREATE UNIQUE INDEX primary_key ON stock (id ASC, effective_date DESC);
       
SELECT
    s.price
FROM
    stock s
WHERE
        s.id = 'x42'
    AND s.effective_date <= '2014-10-11'
ORDER BY
    s.effective_date DESC
LIMIT 1;

Cheers,
Gavin