Thread: Versioning control in PostgreSQL?

Versioning control in PostgreSQL?

From
Alex Page
Date:
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 <alex.page@cancer.org.uk>
Real: Systems/Network Assistant, Epidemiology Unit, Oxford
Tel:  01865 302 223 (external) / 223 (internal)
PGP:  8868 21D7 3D35 DD77 9D06  BF0A 0746 2DE6 55EA 367E

Attachment

Re: Versioning control in PostgreSQL?

From
Shridhar Daithankar
Date:
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


Re: Versioning control in PostgreSQL?

From
Ron Johnson
Date:
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. ron.l.johnson@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


Re: Versioning control in PostgreSQL?

From
Shridhar Daithankar
Date:
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


Re: Versioning control in PostgreSQL?

From
Andrew Sullivan
Date:
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
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Re: Versioning control in PostgreSQL?

From
Fernando Nasser
Date:
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:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9


Re: Versioning control in PostgreSQL?

From
"scott.marlowe"
Date:
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.


Re: Versioning control in PostgreSQL?

From
elein
Date:
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
elein@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 <alex.page@cancer.org.uk>
> Real: Systems/Network Assistant, Epidemiology Unit, Oxford
> Tel:  01865 302 223 (external) / 223 (internal)
> PGP:  8868 21D7 3D35 DD77 9D06  BF0A 0746 2DE6 55EA 367E