"sniffing" postgres queries

You might be able to get enough information looking at the raw
frontend/backend protocol exchange (http://www.postgresql.org/docs/
8.2/static/protocol.html) using something like tcpflow or Ethereal.

On Mar 20, 2007, at 5:45 PM, Anton Melser wrote:

I am having real trouble with a project that is a little out of my
league and I just can't find out how to find which table the app is
getting its values from (it's a long story...). I know what values I
am looking for, and would like to log everything (even if only for one
call of a web page...) that comes in and everything that goes out. Is
this possible at the db level? Even debug5 doesn't seem to give me the
result of a query... If it is only possible at the driver level (I did
something vaguely similar with the odbc driver I think), then any
pointers for a jdbc2 on 8.1.4?

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

"Anton Melser" <melser.anton@gmail.com> writes:

I am having real trouble with a project that is a little out of my
league and I just can't find out how to find which table the app is
getting its values from (it's a long story...). I know what values I
am looking for, and would like to log everything (even if only for one
call of a web page...) that comes in and everything that goes out. Is
this possible at the db level? Even debug5 doesn't seem to give me the
result of a query... If it is only possible at the driver level (I did
something vaguely similar with the odbc driver I think), then any
pointers for a jdbc2 on 8.1.4?

Doesn't log_statement = ALL get the job done for you? If you need to
correlate queries with results then I guess not; there is no provision
for logging query results. The nearby suggestion to use a packet
sniffer is probably your only recourse for that. I'd suggest that you
may also need to set the driver to use protocol version 2, so that all
the queries come in in plain text --- otherwise you'll have some
difficulty interpreting the packet streams ...

regards, tom lane

On 21/03/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Anton Melser" <melser.anton@gmail.com> writes:

I am having real trouble with a project that is a little out of my
league and I just can't find out how to find which table the app is
getting its values from (it's a long story...). I know what values I
am looking for, and would like to log everything (even if only for one
call of a web page...) that comes in and everything that goes out. Is
this possible at the db level? Even debug5 doesn't seem to give me the
result of a query... If it is only possible at the driver level (I did
something vaguely similar with the odbc driver I think), then any
pointers for a jdbc2 on 8.1.4?

Doesn't log_statement = ALL get the job done for you? If you need to
correlate queries with results then I guess not; there is no provision
for logging query results. The nearby suggestion to use a packet
sniffer is probably your only recourse for that. I'd suggest that you
may also need to set the driver to use protocol version 2, so that all
the queries come in in plain text --- otherwise you'll have some
difficulty interpreting the packet streams ...

Thanks both for your replies. I remember now that it was indeed
ethereal that we used last time (for the odbc problem). I will
probably try and avoid this solution for the moment...
Thanks for your help.
Cheers
Anton