Versioning control in PostgreSQL?

[Alex Page]

I haven't used PostgreSQL for nearly a year now, and when I was last
using it there was some discussion on versioning control being
introduced as a feature. Basically, for some data, I'd like to keep
track of who changed it, when, and to what. I know I could include
multiple values in the schema, and have a view to pull out the most
recent, but I was wondering if there was something more automatic that
is a feature of PostgreSQL itself. I've looked around the documentation
with no success. Does anybody know if this is possible?

Alex
--
Mail: Alex Page <al*******@cancer.org.uk>
Real: Systems/Network Assistant, Epidemiology Unit, Oxford
Tel: [phone] (external) / 223 (internal)
PGP: 8868 21D7 3D35 DD77 9D06 BF0A 0746 2DE6 55EA 367E

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQE/nlZwB0Yt5lXqNn4RAl2fAJ4s2O2mrFGmcO5v/Iylx0XrdpdfiACg3P9Q
MmDuUu2UcjGap2OwLB108Zo=
=fEGa
-----END PGP SIGNATURE-----

[Shridhar Daithankar]

On Tuesday 28 October 2003 17:13, Alex Page wrote:

I haven't used PostgreSQL for nearly a year now, and when I was last
using it there was some discussion on versioning control being
introduced as a feature. Basically, for some data, I'd like to keep
track of who changed it, when, and to what. I know I could include
multiple values in the schema, and have a view to pull out the most
recent, but I was wondering if there was something more automatic that
is a feature of PostgreSQL itself. I've looked around the documentation
with no success. Does anybody know if this is possible?

No. It does have multiversion rows but rows updated in previous transaction
are not accessible to users.

The rows will remain on disk eating space till you vacuum. There is no way of
accessing them.

Actually it could be a nice feature if we could access them. For certain
applications, having entire database audited is like dream come true. Of
course it has to provide timestamps/transaction ids as well but in general
that would be pretty handy in some situation.

Alas... that is toooo much fundamental to ask for a feature..:-)

Shridhar
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Ron Johnson]

On Tue, 2003-10-28 at 07:04, Shridhar Daithankar wrote:

On Tuesday 28 October 2003 17:13, Alex Page wrote:

I haven't used PostgreSQL for nearly a year now, and when I was last
using it there was some discussion on versioning control being
introduced as a feature. Basically, for some data, I'd like to keep
track of who changed it, when, and to what. I know I could include
multiple values in the schema, and have a view to pull out the most
recent, but I was wondering if there was something more automatic that
is a feature of PostgreSQL itself. I've looked around the documentation
with no success. Does anybody know if this is possible?

No. It does have multiversion rows but rows updated in previous transaction
are not accessible to users.

The rows will remain on disk eating space till you vacuum. There is no way of
accessing them.

Actually it could be a nice feature if we could access them. For certain
applications, having entire database audited is like dream come true. Of
course it has to provide timestamps/transaction ids as well but in general
that would be pretty handy in some situation.

Alas... that is toooo much fundamental to ask for a feature..:-)

Really? It seems like that one way to do it would be, at commit
time, to write the "before version" to "somewhere else".

--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@cox.net
Jefferson, LA USA

"What's your genius, perfect 20 years too late Monday morning
quarterback answer to how the US should have responded to the
Soviet invasion of Afghanistan? Oh wait, you're just talking crap
- you don't have a real answer, you're just regurgitating crap
from NPR."
http://slashdot.org/comments.pl?sid=76597&cid=6839483
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Shridhar Daithankar]

Ron Johnson wrote:

On Tue, 2003-10-28 at 07:04, Shridhar Daithankar wrote:

Actually it could be a nice feature if we could access them. For certain
applications, having entire database audited is like dream come true. Of
course it has to provide timestamps/transaction ids as well but in general
that would be pretty handy in some situation.

Alas... that is toooo much fundamental to ask for a feature..:-)

Really? It seems like that one way to do it would be, at commit
time, to write the "before version" to "somewhere else".

Well, the original version is stored. Problem is how to make it accessible?

Right now, there are only two types of rows. One updated by most recent
transaction and other is earlier version of same row. Now there could be n
tuples in second catagory.

Postgresql is grounds up designed to discard tuples in second category. It won't
be easy to modify each place so that it could either discard earlier versions or
use them in some sensible fashion.

Shridhar
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Andrew Sullivan]

On Tue, Oct 28, 2003 at 11:43:44AM +0000, Alex Page wrote:

introduced as a feature. Basically, for some data, I'd like to keep
track of who changed it, when, and to what. I know I could include
multiple values in the schema, and have a view to pull out the most

There is a timetravel module in contrib/spi. I'm pretty sure it aims
at reproducing stuff that used to be in the main code, without all
the overhead. It's a place to start, anyway.

A
--
----
Andrew Sullivan 204-4141 Yonge Street
Afilias Canada Toronto, Ontario Canada
<an****@libertyrms.info> M2P 2A8
[phone] x110
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Fernando Nasser]

Alex Page wrote:> I haven't used PostgreSQL for nearly a year now, and when I
was last

using it there was some discussion on versioning control being
introduced as a feature. Basically, for some data, I'd like to keep
track of who changed it, when, and to what. I know I could include
multiple values in the schema, and have a view to pull out the most
recent, but I was wondering if there was something more automatic that
is a feature of PostgreSQL itself. I've looked around the documentation
with no success. Does anybody know if this is possible?

Wouldn't a trigger on update (and insert) that writes to a 'changes' table do
what you want?

--
Fernando Nasser
Red Hat - Toronto E-Mail: fn*****@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario M4P 2C9
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[scott.marlowe]

On Tue, 28 Oct 2003, Alex Page wrote:

I haven't used PostgreSQL for nearly a year now, and when I was last
using it there was some discussion on versioning control being
introduced as a feature. Basically, for some data, I'd like to keep
track of who changed it, when, and to what. I know I could include
multiple values in the schema, and have a view to pull out the most
recent, but I was wondering if there was something more automatic that
is a feature of PostgreSQL itself. I've looked around the documentation
with no success. Does anybody know if this is possible?

There's a nice example code in contrib/spi that might help here.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[elein]

There is an example and discussion of implementing
history tracking of updates to a table. It includes
the discussion of the update rule technique as well.

See http://www.varlena.com/GeneralBits/38.php

elein
el***@varlena.com

On Tue, Oct 28, 2003 at 11:43:44AM +0000, Alex Page wrote:

I haven't used PostgreSQL for nearly a year now, and when I was last
using it there was some discussion on versioning control being
introduced as a feature. Basically, for some data, I'd like to keep
track of who changed it, when, and to what. I know I could include
multiple values in the schema, and have a view to pull out the most
recent, but I was wondering if there was something more automatic that
is a feature of PostgreSQL itself. I've looked around the documentation
with no success. Does anybody know if this is possible?

Alex
--
Mail: Alex Page <al*******@cancer.org.uk>
Real: Systems/Network Assistant, Epidemiology Unit, Oxford
Tel: [phone] (external) / 223 (internal)
PGP: 8868 21D7 3D35 DD77 9D06 BF0A 0746 2DE6 55EA 367E

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)