RE: [GENERAL] Missing features ?

This feature was depreciated because of it's high overhead verses it's
utility. In other words not enough people were using it to make it
worth the cost in everyone's database. There are many way that you can
design the same type of system in your own database. I suggest using
inheritance and RULES. Example (assuming id is PRIMARY KEY):

CREATE TABLE stuff (
id INT,
t TEXT,
d DATETIME DEFAULT(DATETIME(NOW()))
);
CREATE UNIQUE INDEX stuff_pkey ON stuff(id);
CREATE TABLE old_stuff () INHERITS (stuff);
CREATE RULE del_stuff AS ON DELETE TO stuff DO
INSERT INTO old_stuff SELECT old.*;
CREATE RULE up_stuff AS ON UPDATE TO stuff DO
INSERT INTO old_stuff SELECT old.*;
INSERT INTO stuff (id, t) VALUES (1, 'Hello there.');
INSERT INTO stuff (id, t) VALUES (2, 'Hi there.');
INSERT INTO stuff (id, t) VALUES (3, 'Hello there again.');
INSERT INTO stuff (id, t) VALUES (4, 'Hi there once more.');
INSERT INTO stuff (id, t) VALUES (5, 'Hello there.');
UPDATE stuff SET d=now(), t='Hi' WHERE t='Hello there.';
DELETE FROM stuff WHERE id=2;
DELETE FROM stuff WHERE id=5;
-- current snapshot
SELECT * from stuff;
-- all that have ever existed (most recent)
SELECT * from stuff* s1 WHERE d = (SELECT MAX(d) FROM stuff* s2 WHERE
s1.id = s2.id);
-- records @ or before '2/2/1999 13:50:00'
SELECT * from stuff* s1 WHERE d = (SELECT MAX(d) FROM stuff* s2 WHERE
s1.id = s2.id and s2.d <= '2/2/1999 13:50:00');

I think you get the idea. If you want to store the date an item was
delete/updated rather than when it was inserted the rules become:
CREATE RULE del_stuff AS ON DELETE TO stuff DO
INSERT INTO old_stuff SELECT old.id, old.t, datetime(now());
CREATE RULE up_stuff AS ON UPDATE TO stuff DO
INSERT INTO old_stuff SELECT old.id, old.t, datetime(now());

Hope this helps,
-DEJ

> -----Original Message-----
> From: Remigiusz Sokolowski [mailto:rems(at)gdansk(dot)sprint(dot)pl]
> Sent: Monday, February 01, 1999 3:22 AM
> To: Joerg Huettmann
> Cc: pgsql-general(at)postgreSQL(dot)org
> Subject: Re: [GENERAL] Missing features ?
>
>
> > Hi!
> >
> > I'm looking for a kind of history or archive-feature in
> Postgresql like in the
> > older postgres versions.
> > There it was possible by using Tmin and Tmax to recover old
> states of a
> > database.
> > Unfortunatly I didn't find something about database backup
> and recovery with
> > postgresql in the manual.
> >
> > Can anybody help me with this problem?
> >
> > Thanks...Joerg
>
> hi!
> You probably mean Time Travel chapter in postgres manual.
> I don't know if this is possible with actual versions of
> Postgres, but You
> can try it
> Rem