Thread: SELECT statements in log files

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.

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.

    Mario
--
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr

"I can do it quick, I can do it cheap, I can do it well. Pick any two."

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

On Mar 03 06:20, Simon Riggs wrote:
> On Fri, 2006-03-03 at 12:19 +0100, Mario Splivalo wrote:
> > 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.

IIRC, (in the Python side) both psycopg and pygresql modules don't
support parameter usage yet. But with 5.1.0RC1 release of PHP, there're
new functions available with parameter utilization.


Regards.