Thread: Versioning control in PostgreSQL?
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
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
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
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
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
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
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.
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