I haven't given this a lot of thought, so take it with a grain of
salt. But my feeling is that publishing such a detailed log isn't the most
effective way to do this sort of thing. How about, instead, changing the
structure of your database to keep "old" information? Consider, for
example, a simple phone book. You might have the following fields:
id firstname lastname phone fax
just to keep things simple. How about, instead, having two tables:
1.) Records, which ONLY has the id column; and
2.) Data, which has:
id rev firstname lastname phone fax
you can get what you're looking for by simply JOINing Records and
Data. Then, when you want to "change" a record - say, for example, Andrew
Perrin moves from Berkeley to Chapel Hill, thereby changing phones from
510-xxx-xxxx to 919-xxx-xxxx - you actually *add* a new record, with a
higher rev, to Data. So, before:
id: 0
rev: 1
firstname: Andrew
lastname: Perrin
phone: 510-xxx-xxxx
fax:
And after:
id: 0
rev: 1
firstname: Andrew
lastname: Perrin
phone: 510-xxx-xxxx
fax:
id: 0
rev: 2
firstname: Andrew
lastname: Perrin
phone: 919-xxx-xxxx
fax:
SELECTing the highest rev will give you current data; selecting everything
for id 0 sorted by rev will give you the change history.
Just a thought.
Andy Perrin
----------------------------------------------------------------------
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology
(Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill)
andrew_perrin@unc.edu - http://www.unc.edu/~aperrin
On Fri, 23 Mar 2001, Jan Wieck wrote:
> pgsql-sql wrote:
> > Hello Everyone,
> >
> > Here's my simple question.
> >
> > I just want to know/get the recent changes made to a table.
> > Deeper? I wanted the Postgresql server to *publish* every
> > changes made to a table (similar to replication, incremental transfer,
> > etc.).
> > What is the best way to go about it?
> >
> > My idea is to create a trigger for every insert/update that will
> > select the new/updated record and output it to a file. Or better
> > yet, I would like to output the actual sql query to file.
> > Is it possible to output the result of an sql query from a trigger
> > to a file? How?
>
> Putting the SQL query to a file would be my approach too.
>
> The trigger approach lacks the capability to discard changes
> already logged in case of a transaction rollback. Thus, I
> wouldn't buy it.
>
> For the query string logging, alot more is required. Not only
> the queries themself are needed, you'd need to serialize
> snapshot creation, log sequence allocations and the like. And
> the program rolling forward this kind of log into another
> database needs control mechanisms to tell the database that
> it's in this recovery mode and has to ask for those values in
> case it needs them.
>
> You might guess it already, I've been thinking about it for a
> year or so now. And I'm still not past the point to start
> implementing it.
>
> >
> > I would appreciate any answer. Thank you very much.
> > Sherwin
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://www.postgresql.org/search.mpl
> >
>
>
> --
>
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me. #
> #================================================== JanWieck@Yahoo.com #
>
>
>
> _________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>