Re: Audit trail
I'd recommend using rules to improve your efficiency for queries affect more
than one row..
Josh.
Show quoted text
On March 13, 2003 09:33 am, James Gregory wrote:
Hi all,
I am wondering if anyone has any good solutions for having postgres
leave an audit trail. Specifically I would like it to copy rows to a
ghost table without constraints every time a row is modified, deleted
etc. I don't really want to have to do the work of creating said ghost
tables, but if that's the only way, then that's ok.I know about Audit Trail, and it seems ok, but it's not suitable here
because:1. It doesn't copy whole rows
2. It requires that your tables adhere to a particular structure (I'm
new in this company and I'm slowly migrating tables to a structure that
is close enough that it could be made to work, but for now I'm stuck
with what's here)Is it best to write some triggers or has someone already done this
stuff?Thanks,
James.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Import Notes
Reply to msg id not found: 1047566000.13365.30.camel@pirate.bridge.anchor.net.auReference msg id not found: 1047566000.13365.30.camel@pirate.bridge.anchor.net.au
I am wondering if anyone has any good solutions for having postgres
leave an audit trail.
I had 2 tables that needed a simple audit trail. It was very easy to
set up using php.
A few lines of code within transactions:
When a row is created, log it.
When any changes happen, log them, who did them and why.
I never felt I needed all the info for each row - just the changes.
Jeff
Import Notes
Reply to msg id not found: 1047566000.13365.30.camel@pirate.bridge.anchor.net.au | Resolved by subject fallback
James Gregory wrote:
I considered that. Trouble is that I'd have to write this code in 5 or 6
different languages, one of which is pl/pgsql. Likewise I'd need to test
all those different implementations (and it would be impractical to
retrofit this to the pl/pgsql stuff).So yeah, I really need the database to do it itself.
There is no "native" audit feature built in to Postgres, but it is easy
enough to implement using audit tables and plpgsql function triggers on
the application tables. See:
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/triggers.html
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/plpgsql-trigger.html
Joe
Import Notes
Reply to msg id not found: 1047569172.13365.34.camel@pirate.bridge.anchor.net.au
On Wed, 12 Mar 2003, Joshua Moore-Oliva wrote:
I'd recommend using rules to improve your efficiency for queries affect more
than one row..Josh.
I agree with Josh that rules are an easy way to accomplish what you are
after. The best example I've found on using rules is
http://www.postgresql.org/docs/aw_pgsql_book/node124.html
Take Care,
James
->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<
James Thompson 138 Cardwell Hall Manhattan, Ks 66506 785-532-0561
Kansas State University Department of Mathematics
->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<
On Thu, 2003-03-13 at 04:18, Jeff Fitzmyers wrote:
I am wondering if anyone has any good solutions for having postgres
leave an audit trail.I had 2 tables that needed a simple audit trail. It was very easy to
set up using php.
I considered that. Trouble is that I'd have to write this code in 5 or 6
different languages, one of which is pl/pgsql. Likewise I'd need to test
all those different implementations (and it would be impractical to
retrofit this to the pl/pgsql stuff).
So yeah, I really need the database to do it itself.
Thanks,
James.
Look at the /contrib/spi package, which implements "time travel" as well
as other features. It basically does what you're talking about. I'm not
sure if it's a good fit or not though.
On Wed, 12 Mar 2003, Joshua Moore-Oliva wrote:
Show quoted text
I'd recommend using rules to improve your efficiency for queries affect more
than one row..Josh.
On March 13, 2003 09:33 am, James Gregory wrote:
Hi all,
I am wondering if anyone has any good solutions for having postgres
leave an audit trail. Specifically I would like it to copy rows to a
ghost table without constraints every time a row is modified, deleted
etc. I don't really want to have to do the work of creating said ghost
tables, but if that's the only way, then that's ok.I know about Audit Trail, and it seems ok, but it's not suitable here
because:1. It doesn't copy whole rows
2. It requires that your tables adhere to a particular structure (I'm
new in this company and I'm slowly migrating tables to a structure that
is close enough that it could be made to work, but for now I'm stuck
with what's here)Is it best to write some triggers or has someone already done this
stuff?Thanks,
James.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster