Hi!

When setting log_statement = all, and using JDBC PreparedStatements, I get
$n in the log where the real arguments used to be in previous versions of
postgresql:

postgres[30059]: [97-1] LOG: statement: INSERT INTO group_data
(this_group_id, item_text, link_path) VALUES ($1, $2, $3)


I really need to know the *real* arguments... How can I get them? Is it a
bug?

/Palle


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Palle Girgensohn <girgen@pingpong.net> writes:

> When setting log_statement = all, and using JDBC PreparedStatements, I get $n
> in the log where the real arguments used to be in previous versions of
> postgresql:
>
> postgres[30059]: [97-1] LOG: statement: INSERT INTO group_data (this_group_id,
> item_text, link_path) VALUES ($1, $2, $3)
>
> I really need to know the *real* arguments... How can I get them? Is it a bug?

The bug was that prepared statements didn't work properly in the past. That is
the statement you're actually running.

You might want to look into JDBC options to disable use of prepared
statements. The old emulation code must still be there in case it runs against
a <=7.4 database so perhaps there's an option to use it.

--
greg


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

>>postgres[30059]: [97-1] LOG: statement: INSERT INTO group_data (this_group_id,
>>item_text, link_path) VALUES ($1, $2, $3)
>>
>>I really need to know the *real* arguments... How can I get them? Is it a bug?
>
>
> The bug was that prepared statements didn't work properly in the past. That is
> the statement you're actually running.
>
> You might want to look into JDBC options to disable use of prepared
> statements. The old emulation code must still be there in case it runs against
> a <=7.4 database so perhaps there's an option to use it.

I think he has a really excellent point. It should log the parameters
as well.

Chris

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Christopher Kings-Lynne wrote:
> I think he has a really excellent point. It should log the parameters
> as well.

neilc=# prepare foo(int, int) as select $1 + $2;
PREPARE
neilc=# execute foo(5, 10);
....
neilc=# execute foo(15, 20);
....

% tail /usr/local/pgsql/postmaster.log
LOG: statement: prepare foo(int, int) as select $1 + $2;
LOG: statement: execute foo(5, 10);
LOG: statement: execute foo(15, 20);

-Neil

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

At 2005-04-07 12:14:19 +1000, neilc@samurai.com wrote:
>
> % tail /usr/local/pgsql/postmaster.log
> LOG: statement: prepare foo(int, int) as select $1 + $2;
> LOG: statement: execute foo(5, 10);
> LOG: statement: execute foo(15, 20);

If you send a v3 protocol execute message instead of an SQL EXECUTE
statement, the parameters don't get logged.

-- ams

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Thu, Apr 07, 2005 at 12:14:19PM +1000, Neil Conway wrote:
> Christopher Kings-Lynne wrote:
> >I think he has a really excellent point. It should log the parameters
> >as well.
>
> neilc=# prepare foo(int, int) as select $1 + $2;
> PREPARE
> neilc=# execute foo(5, 10);
> ...
> neilc=# execute foo(15, 20);
> ...
>
> % tail /usr/local/pgsql/postmaster.log
> LOG: statement: prepare foo(int, int) as select $1 + $2;
> LOG: statement: execute foo(5, 10);
> LOG: statement: execute foo(15, 20);

Yeah, but I think he mentioned JDBC which (I think) uses the low-level
protocol and probably doesn't log the parameters as well (I notice that
his example has INSERT as the query, not PREPARE nor EXECUTE.)

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"I call it GNU/Linux. Except the GNU/ is silent." (Ben Reiter)

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Neil Conway wrote:
> Christopher Kings-Lynne wrote:
>
>> I think he has a really excellent point. It should log the parameters
>> as well.
>
>
> neilc=# prepare foo(int, int) as select $1 + $2;
> PREPARE
> neilc=# execute foo(5, 10);
> ...
> neilc=# execute foo(15, 20);
> ...
>
> % tail /usr/local/pgsql/postmaster.log
> LOG: statement: prepare foo(int, int) as select $1 + $2;
> LOG: statement: execute foo(5, 10);
> LOG: statement: execute foo(15, 20);

Query-level EXECUTE is logged, but Bind/Execute via the V3 extended
query protocol (which is what the JDBC driver does) isn't.

In fact, the logging for the extended query protocol really sucks: the
server logs only the Parse, and is silent about Bind/Execute, so there
are all sorts of strange cases where your statement logs do not reflect
what was actually executed at all. For example, the JDBC driver issues a
Parse (but no Execute!) when an application asks for type metadata from
a query, and it can issue multiple Bind/Executes for a single Parse.

I've raised this before on -hackers but haven't had time to do anything
about it myself yet.

-O

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Oliver Jowett wrote:
> Query-level EXECUTE is logged, but Bind/Execute via the V3 extended
> query protocol (which is what the JDBC driver does) isn't.

Ah, I see. Yes, that certainly needs to be fixed.

-Neil

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Greg Stark wrote:
> Palle Girgensohn <girgen@pingpong.net> writes:
>
>>When setting log_statement = all, and using JDBC PreparedStatements, I get $n
>>in the log where the real arguments used to be in previous versions of
>>postgresql:

> You might want to look into JDBC options to disable use of prepared
> statements. The old emulation code must still be there in case it runs against
> a <=7.4 database so perhaps there's an option to use it.

You can do this by appending '?protocolVersion=2' to the JDBC URL you
use (or '&protocolVersion=2' if you already have other URL parameters).

If you do this you also lose any features that need V3 protocol support
(e.g. query parameter metadata and some resultset metadata).

-O

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Oliver Jowett <oliver@opencloud.com> writes:
> In fact, the logging for the extended query protocol really sucks:

Without doubt. Someone has to sit down and think about exactly what
we should log, where when and how ... proposals welcome ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org