Re: log_statement and PREPARE

Tom Lane wrote:
> brian <brian(at)zijn-digital(dot)com> writes:
>
>>But that should mean that my prepared statement that contains an INSERT
>>should be logged, yes? (8.1 issues notwithstanding)
>>I ask because i've set log_statement to 'mod' but am not seeing any
>>of my prepared statements in the log. INSERT, UPDATE, and friends i
>>do see.
>
>
> Hm, works for me:
>
> regression=# create temp table fooey(f1 int);
> CREATE TABLE
> regression=# prepare foo(int) as insert into fooey values($1);
> PREPARE
> regression=# execute foo(42);
> INSERT 0 1
> regression=# show log_statement;
> log_statement
> ---------------
> mod
> (1 row)
>
> in log:
>
> LOG: statement: create temp table fooey(f1 int);
> LOG: statement: prepare foo(int) as insert into fooey values($1);
>
> The same sequence in 8.2 logs:
>
> LOG: statement: create temp table fooey(f1 int);
> LOG: statement: prepare foo(int) as insert into fooey values($1);
> LOG: statement: execute foo(42);
> DETAIL: prepare: prepare foo(int) as insert into fooey values($1);

OK, maybe i *am* confused about PREPARE. The PEAR MDB2 source is doing:

$query = 'PREPARE '.$statement_name.$types_string.' AS '.$query;

which becomes something like:

PREPARE mdb2_statement_pgsqla0e8d35156e904f9581ac790eb917b98 AS ...

So i think i see your point. The "mdb2_statement_pgsql ... " string
identifier is what is then passed along with my data to Pg when
executed. So, quite different than "EXECUTE foo(42)". And this sort of
thing is not logged? Even in 8.2?

On the chance that there really is something amiss, i'll continue ...

I know that the change to postgresql.conf has been recognised because
i've been seeing the regular queries logged since changing log_statement.

The only hint of a prepared statement being logged is when there's an
error. eg:

<2007-09-05 17:35:22 EDT>ERROR: duplicate key violates unique
constraint "auth_member_id_key"
<2007-09-05 17:35:22 EDT>STATEMENT: EXECUTE
mdb2_statement_pgsqla0e8d35156e904f9581ac790eb917b98 (A_HASH_HERE, 5271)

Otherwise, nothing but INSERT, UPDATE, etc. I'm using 8.1.4, btw.

I'll take this over to the PEAR list.

> If what you were looking for was that the *execute* gets logged
> dependent on what the prepared statement was, then you need 8.2.
> All that stuff got rewritten pretty heavily for 8.2 ...

Yeah, i'll upgrade and check that out. Thanks again.
brian