Обсуждение: Tuple changes from relfilenodes

Hello,

Just want to confirm something. I heard that data files containing tables also keep information on tuple(row) changes so is it true and if so how to get this data from the files?

*ukasz Brodziak<lukasz.brodziak@hotmail.com> wrote:

> Just want to confirm something. I heard that data files containing
> tables also keep information on tuple(row) changes so is it true
> and if so how to get this data from the files?

Are you looking for statistics or the actual prior versions of rows?

-Kevin

I'm looking for actual versions of row data. What I want to achieve as a final result is a kind of data change history.

> Date: Fri, 27 Aug 2010 08:25:26 -0500
> From: Kevin.Grittner@wicourts.gov
> To: lukasz.brodziak@hotmail.com; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Tuple changes from relfilenodes
> Are you looking for statistics or the actual prior versions of rows?
>
> -Kevin
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

*ukasz Brodziak<lukasz.brodziak@hotmail.com> wrote:
> From: Kevin.Grittner@wicourts.gov

>> Are you looking for statistics or the actual prior versions of
>> rows?

> I'm looking for actual versions of row data. What I want to
> achieve as a final result is a kind of data change history.

Well, unless you are suppressing the vacuum behavior which normally
occurs, your history won't go back very far; and if you do suppress
vacuums, performance will begin to fall of fairly quickly.

Tuples representing old versions of rows may be eliminated as soon
as there is no transaction which would still be able to see them.

-Kevin

On 8/27/10 9:48 AM, £ukasz Brodziak wrote:
> I'm looking for actual versions of row data. What I want to achieve as a final result is a kind of data change
history.

What about a before-update trigger and an history table?

Craig

>  > Date: Fri, 27 Aug 2010 08:25:26 -0500
>  > From: Kevin.Grittner@wicourts.gov
>  > To: lukasz.brodziak@hotmail.com; pgsql-admin@postgresql.org
>  > Subject: Re: [ADMIN] Tuple changes from relfilenodes
>  > Are you looking for statistics or the actual prior versions of rows?
>  >
>  > -Kevin
>  >
>  > --
>  > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
>  > To make changes to your subscription:
>  > http://www.postgresql.org/mailpref/pgsql-admin

What do You mean by history table? Creating such table is out of the question.

> Date: Fri, 27 Aug 2010 10:04:39 -0700
> From: craig_james@emolecules.com
> To:
> Subject: Re: [ADMIN] Tuple changes from relfilenodes
>
> On 8/27/10 9:48 AM, ?ukasz Brodziak wrote:
> > I'm looking for actual versions of row data. What I want to achieve as a final result is a kind of data change history.
>
> What about a before-update trigger and an history table?
>
> Craig

>> Are you looking for statistics or the actual prior versions of
>> rows?
>
> I'm looking for actual versions of row data. What I want to
> achieve as a final result is a kind of data change history.

We just use ON DELETE rules on the tables where we want to maintain a history. In most cases, we just copy the old
recordto the archive table which has an additional timestamp column with a default of NOW() to indicate the date of
archival.In one case, where the table schema is large, changes are "frequent" and the changes are often minimal, we
onlyarchive a small subset of data based on what is changing. If the changes affect columns outside of the small
subset,then we archive the whole record. 

Bill MacArthur

* Make that ON DELETE and ON UPDATE :)

I get the idea but I can't change the structure of the database by adding anything. I have a database from which I have to retrieve some history data for recovery purposes.
> Date: Fri, 27 Aug 2010 13:11:47 -0400
> From: webmaster@dhs-club.com
> To: lukasz.brodziak@hotmail.com; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Tuple changes from relfilenodes
>
> >> Are you looking for statistics or the actual prior versions of
> >> rows?
> >
> > I'm looking for actual versions of row data. What I want to
> > achieve as a final result is a kind of data change history.
>
> We just use ON DELETE rules on the tables where we want to maintain a history. In most cases, we just copy the old record to the archive table which has an additional timestamp column with a default of NOW() to indicate the date of archival. In one case, where the table schema is large, changes are "frequent" and the changes are often minimal, we only archive a small subset of data based on what is changing. If the changes affect columns outside of the small subset, then we archive the whole record.
>
> Bill MacArthur
>

On 8/27/10 10:09 AM, Lukasz Brodziak wrote:
> What do You mean by history table? Creating such table is out of the question.

You asked for "actual versions of row data" and a "data change history."  How can you expect to get a history without
thehistory being stored somewhere?  The previous answers to your question already said that the old versions of data
canbe erased almost immediately, so if you really need the history, you have to do it yourself. 

Why is that out of the question?  Perhaps a more detailed explanation of what you are trying to achieve would help.

Craig

>
>  > Date: Fri, 27 Aug 2010 10:04:39 -0700
>  > From: craig_james@emolecules.com
>  > To:
>  > Subject: Re: [ADMIN] Tuple changes from relfilenodes
>  >
>  > On 8/27/10 9:48 AM, ?ukasz Brodziak wrote:
>  > > I'm looking for actual versions of row data. What I want to achieve as a final result is a kind of data change
history.
>  >
>  > What about a before-update trigger and an history table?
>  >
>  > Craig

*ukasz Brodziak<lukasz.brodziak@hotmail.com> wrote:

> I have a database from which I have to retrieve some history data
> for recovery purposes.

Ouch.

A quick search on pgfoundry shows a couple projects which might
help.  (I've never used either of them.)  Perhaps others will have
more ideas.

http://pgfoundry.org/projects/pgstatpage/

http://pgfoundry.org/projects/pg-rdump/

-Kevin

>> Are you looking for statistics or the actual prior versions of
>> rows?
>
> I'm looking for actual versions of row data. What I want to
> achieve as a final result is a kind of data change history.

We just use ON DELETE rules on the tables where we want to maintain a history. In most cases, we just copy the old
recordto the archive table which has an additional timestamp column with a default of NOW() to indicate the date of
archival.In one case, where the table schema is large, changes are "frequent" and the changes are often minimal, we
onlyarchive a small subset of data based on what is changing. If the changes affect columns outside of the small
subset,then we archive the whole record. 

Bill MacArthur

Kevin Grittner wrote:
> *ukasz Brodziak<lukasz.brodziak@hotmail.com> wrote:
> > From: Kevin.Grittner@wicourts.gov
>
> >> Are you looking for statistics or the actual prior versions of
> >> rows?
>
> > I'm looking for actual versions of row data. What I want to
> > achieve as a final result is a kind of data change history.
>
> Well, unless you are suppressing the vacuum behavior which normally
> occurs, your history won't go back very far; and if you do suppress
> vacuums, performance will begin to fall of fairly quickly.
>
> Tuples representing old versions of rows may be eliminated as soon
> as there is no transaction which would still be able to see them.

Well, single-page vacuums are even more likely to remove old data, and
you can't turn that off.  I am working on am MVCC talk for PG/West that
will show how agressive those single-page (HOT and non-HOT) vacuums are.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +