Thread: Logging failed sql commands trough jdbc?

I'm using pg74.215.jdbc3.jar  with PostgreSQL 8.1.2. I'm not using jdbc
driver for postgres 8 because of prepared transactions wich makes my
postgres log file look like this:

2007-02-19 15:02:51.299 CET [32228] <testDb> SELECTLOG:  duration:
2481.387 ms  statement: EXECUTE <unnamed>  [PREPARE:  select * from
update_message_status($1, $2, $3, $4, $5, $6, $7) as result]

That log makes debugging realy difficult. Therefore I use jdbc driver
for postgres 7.4, because then my postgres log looks like this:

2007-02-19 15:16:24.118 CET [8865] <testDb> SELECTLOG:  duration: 1.280
ms  statement: select * from
update_message_status('b8b8a9a5-a1d0-44d1-99f9-a057b0e589b5', 20) as
result;

Now, If, for instance, update_message_status function fails for some
reason, the log looks like this:

2007-02-19 15:16:24.083 CET [8860] <testDb> SELECTERROR:  duplicate key
violates unique constraint "some_pk"
2007-02-19 15:16:24.083 CET [8860] <testDb> SELECTCONTEXT:  SQL
statement "INSERT INTO ticketing_codes (code_id, code_value,
code_group_id) VALUES ( $1 ,  $2 ,  $3 )"
        PL/pgSQL function "__internal__ticketing_check_code" line 60 at
SQL statement
        PL/pgSQL function "update_message_status" line 25 at assignment

There, again, I can't know what were the parametes that
update_message_status was called with.

Is there a way to force postgres to display those parameters, or to
force JDBC to log all the queries sent to postgres? Even better, can
postgres show the parametars in the 'SELECTCONTEXT' line above (instead
of $1, $2, $3?) I don't need prepared transactions for production
(although I'm not sure about the speed improvement - not that my test
were accurate).

    Mike

    Mike

Hi Mario,

On 2/19/07, Mario Splivalo <mario.splivalo@mobart.hr> wrote:
> I'm using pg74.215.jdbc3.jar  with PostgreSQL 8.1.2. I'm not using jdbc
> driver for postgres 8 because of prepared transactions wich makes my
> postgres log file look like this:
> 2007-02-19 15:02:51.299 CET [32228] <testDb> SELECTLOG:  duration:
> 2481.387 ms  statement: EXECUTE <unnamed>  [PREPARE:  select * from
> update_message_status($1, $2, $3, $4, $5, $6, $7) as result]

In fact, you can use 8.1 driver with ?protocolVersion=2.

> Is there a way to force postgres to display those parameters, or to
> force JDBC to log all the queries sent to postgres? Even better, can
> postgres show the parametars in the 'SELECTCONTEXT' line above (instead
> of $1, $2, $3?) I don't need prepared transactions for production
> (although I'm not sure about the speed improvement - not that my test
> were accurate).

If you can plan a migration to PostgreSQL 8.2, values of the
parameters are now logged so it should solve your problem.

--
Guillaume