Re: SELECT statements in log files

On Fri, 2006-03-03 at 12:19 +0100, Mario Splivalo wrote:
> We're using JDBC to connect to postgres server. I've setup logging so
> that any query that executes over 100ms gets logged. But, the log file
> looks like this:
>
>
> 2006-03-03 12:17:08 CET [31591] <octopussy2> SELECTLOG: duration:
> 253.307 ms statement: EXECUTE <unnamed> [PREPARE: select * from
> create_mt_sms_message($1, $2, $3, $4, $5, $6, $7, $8, $9) as result]
>
> Now, I guess this happened when we switched to the fresh JDBC driver for
> postgres 8.1. Before that I could see the actuall values for the $1, $2,
> $3 parameters...
>
> Is this a known 'issue', or am I doing something wrong here? Is there a
> way to see actuall values using new JDBC driver?
>
> The java client code didn't change a bit. We just switched to
> postgres8.1, and upgraded the JDBC driver used by tomcat.

The new driver switched from non-prepared statements to prepared
statements. The logging is different in each case and is useful because
you can compare similar SQL statements more easily than before. You
should hopefully have noticed a performance improvement also with
prepared statements.

Logging parameter values is likely to be there for 8.2, but I don't
think that is likely to be back-ported to 8.1.

Could you say how you'd *like* it to work in detail, so we can get some
opinions on how best to implement this. I've posted an initial design
about a month ago on -general but without much response.

> When I connect to postgres from PHP or Python or using just psql, I can
> see full SELECT querries logged, with the actuall values for the
> parameters.

Those interfaces do not use prepared statements, so are not logged in
the same way.

Best Regards, Simon Riggs