Re: Logging all queries (almost there)

I don't know if there is a better way, but you could enable
log_connections and log_pid.
This way, you will get in your log file something like this:

2004-01-28 10:39:28 [79227] LOG: connection authorized: user=admin
database=test
...
2004-01-28 10:39:33 [79227] LOG: statement: select * from people;
...
2004-01-28 10:39:35 [79227] LOG: statement: delete from people where
name like 'f%';
...

you could use grep to filter connections (and their pids), and the you
could grep again to find out what queries each pid executed.

If you are interested only in a particular table, then you could use a
trigger AFTER INSERT OR UPDATE OR DELETE (not select), and there you
could code a function to register what changes are being made to your
table.

hope this helps :)

On Wed, 2004-01-28 at 09:36, MaRCeLO PeReiRA wrote:

> Hi All,
>
> I have set [log_statement=true] in the postgresql.conf
> file and now I have all the statements logged. But,
> there is a problem:
>
> Once I have more than one database, I had to know in
> which database a statement was executed on.
>
> The log file is full of: "select * from stuff", but as
> I have more than one database, how can I know where
> the [stuff] table is??????
>
> Regards,
>
> Marcelo
>
> --- MaRCeLO PeReiRA <gandalf_mp(at)yahoo(dot)com(dot)br>
> escreveu: > Hi Franco,
> >
> > If I set 'log_statement=true', will *all* the
> > queries
> > sent to the backend be logged???
> >
> > In which table will it be used to do this log??
> >
> > Regards,
> >
> > Marcelo
> >
> > --- Franco Bruno Borghesi
> > <franco(at)akyasociados(dot)com(dot)ar> escreveu: > you can set
> > the log_statement parameter to true in
> > > the configuration
> > > file ($PGDATA/postgresql.conf).
> > >
> > > On Tue, 2004-01-27 at 16:39, MaRCeLO PeReiRA
> > wrote:
> > >
> > > > Hi All,
> > > >
> > > > Is there any way to log (save) "all" the queries
> > > > (select, insert, updade, everything) executed on
> > a
> > > > database, in a table??
> > > >
> > > > Regards,
> > > >
> > > > Marcelo
> > > >
> > > >
> > >
> >
> ______________________________________________________________________
> > > >
> > > > Yahoo! GeoCities: a maneira mais fácil de criar
> > > seu web site grátis!
> > > > http://br.geocities.yahoo.com/
> > > >
> > > > ---------------------------(end of
> > > broadcast)---------------------------
> > > > TIP 4: Don't 'kill -9' the postmaster
> > > >
> > >
> >
> > > ATTACHMENT part 2 application/pgp-signature
> > name=signature.asc
> >
> >
> >
> ______________________________________________________________________
> >
> > Yahoo! GeoCities: a maneira mais fácil de criar seu
> > web site grátis!
> > http://br.geocities.yahoo.com/
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to
> majordomo(at)postgresql(dot)org
>
> ______________________________________________________________________
>
> Yahoo! GeoCities: a maneira mais fácil de criar seu web site grátis!
> http://br.geocities.yahoo.com/
>