Re: Logging all queries (almost there) - Mailing list pgsql-general

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@yahoo.com.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@akyasociados.com.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@postgresql.org 

______________________________________________________________________

Yahoo! GeoCities: a maneira mais fácil de criar seu web site grátis!
http://br.geocities.yahoo.com/