Postgres Pro
Downloads
Home   >   mailing lists

Thread: change format of logging statements?

change format of logging statements?

From
Matthew Hixson
Date:
Postgres 8.2.0 is logging statements with variables like $1, $2, etc.
and then on the next line saying:

DETAIL:  parameters: $1 = '100', $2 = '100', $3 = '1003'

Is it possible to get statements logged with the parameters placed
into the actual query statement so that its more convenient to copy
and paste them into psql while debugging?  The reason for this is
that Hibernate is creating the queries and I'd like to see exactly
what those queries are returning.
   Thanks,
    -M@

Re: change format of logging statements?

From
Alvaro Herrera
Date:
Matthew Hixson wrote:
> Postgres 8.2.0 is logging statements with variables like $1, $2, etc.
> and then on the next line saying:
>
> DETAIL:  parameters: $1 = '100', $2 = '100', $3 = '1003'
>
> Is it possible to get statements logged with the parameters placed
> into the actual query statement so that its more convenient to copy
> and paste them into psql while debugging?  The reason for this is
> that Hibernate is creating the queries and I'd like to see exactly
> what those queries are returning.

Not really.  You could probably use PREPARE <the query as first logged>
and EXECUTE with the given paramters instead of interpolating the
parameters in the query itself.  It might be easier.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: change format of logging statements?

From
Matthew Hixson
Date:
With 8.2.4 is it possible to get Postgres to log incoming SQL
statements the same as they look when written?  Instead of:

DEBUG: insert into foo (name) values ($1);
DETAIL: parameters: $1 = 'stan'

I'd like to see:

DEBUG: insert into foo (name) values ('stan');

This would be extremely helpful when debugging complex Hibernate
generated queries.  I could just copy&paste the query into a psql
session and begin playing with it.
   Thanks,
    -M@

Re: change format of logging statements?

From
Tom Lane
Date:
Matthew Hixson <hixson@poindextrose.org> writes:
> Instead of:
> DEBUG: insert into foo (name) values ($1);
> DETAIL: parameters: $1 = 'stan'
> I'd like to see:
> DEBUG: insert into foo (name) values ('stan');

Don't hold your breath.  That would require a great deal more smarts
in the logging code (and a great deal more cycles expended) than it
has now.

            regards, tom lane

Re: change format of logging statements?

From
Alvaro Herrera
Date:
Tom Lane wrote:
> Matthew Hixson <hixson@poindextrose.org> writes:
> > Instead of:
> > DEBUG: insert into foo (name) values ($1);
> > DETAIL: parameters: $1 = 'stan'
> > I'd like to see:
> > DEBUG: insert into foo (name) values ('stan');
>
> Don't hold your breath.  That would require a great deal more smarts
> in the logging code (and a great deal more cycles expended) than it
> has now.

That said, you can use explain on these things, though you must do a bit
more work:

alvherre=# prepare foo as insert into foo (name) values ($1);
PREPARE
alvherre=# explain execute foo('stan');
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0)
(1 ligne)

The benefit is that this will use the same plan that Hibernate would be
using, whereas simply expanding the literal in the query would possibly not.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support