PostgreSQL Discussion - Prepared query logging omits bind values

Author

Prepared query logging omits bind values

Jon Jensen

2005-09-23, 8:23 pm

PostgreSQL folks,

It appears that even with the most verbose logging options enabled,
PostgreSQL logs prepared queries like this:

select * from transactions where code = $1

And never logs the bind values that correspond to $1, etc.

This instance I noticed is under PostgreSQL 7.4, both with fairly
default logging settings and with these settings in an attempt to
maximize the detail level:

log_min_messages = debug5
log_error_verbosity = verbose
log_min_error_statem
ent = debug5
log_min_duration_sta
tement = -1
log_connections = true
log_disconnections = true
log_duration = true
log_statement = 'all'
log_hostname = false

I checked the docs for Pg 7.4 and 8.0, and didn't see any indication that
this is configurable.

Is it true that there is no option to log prepared query bind values? If
so, would a patch that enables this, either always or via a configuration
option, be welcome? Without the bind values, I am unable to retrace events
and discover the causes of problems.

Thanks,
Jon

--
Jon Jensen
End Point Corporation
http://www.endpoint.com/

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

Bruce Momjian

2005-09-23, 8:23 pm

We are working on improve this for 8.1. I assume you are talking about
protocol prepare rather than the PREPARE statement, which should be
logged.

Even in 8.1 we don't log the bind values, but we should.

---------------------------------------------------------------------------

Jon Jensen wrote:
> PostgreSQL folks,
>
> It appears that even with the most verbose logging options enabled,
> PostgreSQL logs prepared queries like this:
>
> select * from transactions where code = $1
>
> And never logs the bind values that correspond to $1, etc.
>
> This instance I noticed is under PostgreSQL 7.4, both with fairly
> default logging settings and with these settings in an attempt to
> maximize the detail level:
>
> log_min_messages = debug5
> log_error_verbosity = verbose
> log_min_error_statem
ent = debug5
> log_min_duration_sta
tement = -1
> log_connections = true
> log_disconnections = true
> log_duration = true
> log_statement = 'all'
> log_hostname = false
>
> I checked the docs for Pg 7.4 and 8.0, and didn't see any indication that
> this is configurable.
>
> Is it true that there is no option to log prepared query bind values? If
> so, would a patch that enables this, either always or via a configuration
> option, be welcome? Without the bind values, I am unable to retrace events
> and discover the causes of problems.
>
> Thanks,
> Jon
>
> --
> Jon Jensen
> End Point Corporation
> http://www.endpoint.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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

Jon Jensen

2005-09-23, 8:23 pm

On Fri, 23 Sep 2005, Bruce Momjian wrote:

>
> We are working on improve this for 8.1. I assume you are talking about
> protocol prepare rather than the PREPARE statement, which should be
> logged.

Yes, I mean protocol prepare. I assume the PREPARE/EXECUTE statements are
fully logged, since the data is in the statements themselves, whereas
with protocol prepare the data is out of band.

> Even in 8.1 we don't log the bind values, but we should.

Is this something that could go into 8.1, or is it too late for that? In
either case, is someone already working on a patch for this?

Thanks,
Jon

--
Jon Jensen
End Point Corporation
http://www.endpoint.com/

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Bruce Momjian

2005-09-23, 8:23 pm

Jon Jensen wrote:
> On Fri, 23 Sep 2005, Bruce Momjian wrote:
>
>
> Yes, I mean protocol prepare. I assume the PREPARE/EXECUTE statements are
> fully logged, since the data is in the statements themselves, whereas
> with protocol prepare the data is out of band.

Right.

>
> Is this something that could go into 8.1, or is it too late for that? In
> either case, is someone already working on a patch for this?

Simon Riggs is working on a patch to output the prepare and execute
phases, but it does not output the BIND parameters. I asked for that
addition yesterday, but I doubt this will make it into 8.1.

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Sponsored Links