From: Jackson, DeJuan <djackson@cpsgroup.com>
To: Remigiusz Sokolowski <rems@gdansk.sprint.pl>
To: Joerg Huettmann <joerg.huettmann@informatik.tu-chemnitz.de>
Cc: pgsql-general@postgreSQL.org, PGSQL SQL <pgsql-sql@postgreSQL.org>
Subject: RE: [GENERAL] Missing features ?
Date: Tue, 2 Feb 1999 13:55:39 -0600
Message-ID: <F10BB1FAF801D111829B0060971D839F639A37@cpsmail> (raw)
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@gdansk.sprint.pl]
> Sent: Monday, February 01, 1999 3:22 AM
> To: Joerg Huettmann
> Cc: pgsql-general@postgreSQL.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: djackson@cpsgroup.com, rems@gdansk.sprint.pl, joerg.huettmann@informatik.tu-chemnitz.de
Subject: Re: [GENERAL] Missing features ?
In-Reply-To: <F10BB1FAF801D111829B0060971D839F639A37@cpsmail>
References: <F10BB1FAF801D111829B0060971D839F639A37@cpsmail>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox