PostgreSQL Discussion - Logging statements and parameter values

Author

Logging statements and parameter values

Ted Powell

2006-01-27, 7:23 am

Our development group needs to have the option of logging all SQL
statements including substituted parameter values. Getting output in the
form:
... WHERE contact.login_con = $1 AND company.login_co = $2

was no problem, but nothing that I tried turning on in the config file
yielded values for $1 and $2.

Digging into the source for 8.1.1 brought me to this code in
..../backend/tcop/postgres.c (lines 1449+)

/* We need to output the parameter values someday */
if (log_statement == LOGSTMT_ALL)
ereport(LOG,
(errmsg("statement: <BIND> %s", portal_name)));

/*
* Fetch parameters, if any, and store in the portal's memory context.
*/
if (numParams > 0)

It seems to me that a point near the bottom of the loop over parameters
(1564+)
params[i].kind = PARAM_NUM;
params[i].id = i + 1;
params[i].ptype = ptype;
params[i].isnull = isNull;

i++;
}

(params[i].value is set in a couple of places higher up in the loop)
would be a good place to log each parameter, but...

Has this not been done simply because nobody has gotten around to it, or
are there pitfalls? Although I've been using PostgreSQL for several years,
this is my first venture into its source code beyond watching it build.

Also, the Datum params[i].value, does it necessarily hold displayable
text, even when its content is the output of a binary input converter?
Is there a utility log routine somewhere that I can simply feed a
Datum to?

--
Ted Powell <ted@psg.com> http://psg.com/~ted/
GPL code ... It's the difference between
owning your own home and just renting. --PJ

---------------------------(end of broadcast)---------------------------
TIP 1: 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

Tom Lane

2006-01-27, 11:23 am

Ted Powell <ted@theplace.enposte.net> writes:
> Has this not been done simply because nobody has gotten around to it, or
> are there pitfalls?

What are you going to do with binary parameter values? Calling the
type's output converter is possible but not very pleasant.

> Also, the Datum params[i].value, does it necessarily hold displayable
> text, even when its content is the output of a binary input converter?

Datums are guaranteed *not* to be displayable text.

regards, tom lane

---------------------------(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

2006-01-30, 8:24 pm

I assume it is this TODO:

* Allow protocol-level BIND parameter values to be logged

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

Ted Powell wrote:
> Our development group needs to have the option of logging all SQL
> statements including substituted parameter values. Getting output in the
> form:
> ... WHERE contact.login_con = $1 AND company.login_co = $2
>
> was no problem, but nothing that I tried turning on in the config file
> yielded values for $1 and $2.
>
> Digging into the source for 8.1.1 brought me to this code in
> .../backend/tcop/postgres.c (lines 1449+)
>
> /* We need to output the parameter values someday */
> if (log_statement == LOGSTMT_ALL)
> ereport(LOG,
> (errmsg("statement: <BIND> %s", portal_name)));
>
> /*
> * Fetch parameters, if any, and store in the portal's memory context.
> */
> if (numParams > 0)
>
> It seems to me that a point near the bottom of the loop over parameters
> (1564+)
> params[i].kind = PARAM_NUM;
> params[i].id = i + 1;
> params[i].ptype = ptype;
> params[i].isnull = isNull;
>
> i++;
> }
>
> (params[i].value is set in a couple of places higher up in the loop)
> would be a good place to log each parameter, but...
>
> Has this not been done simply because nobody has gotten around to it, or
> are there pitfalls? Although I've been using PostgreSQL for several years,
> this is my first venture into its source code beyond watching it build.
>
> Also, the Datum params[i].value, does it necessarily hold displayable
> text, even when its content is the output of a binary input converter?
> Is there a utility log routine somewhere that I can simply feed a
> Datum to?
>
>
> --
> Ted Powell <ted@psg.com> http://psg.com/~ted/
> GPL code ... It's the difference between
> owning your own home and just renting. --PJ
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: 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
>

--
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

Ted Powell

2006-01-30, 8:24 pm

On Mon, Jan 30, 2006 at 04:31:29PM -0500, Bruce Momjian wrote:[color=darkred
]
>
> I assume it is this TODO:
>
> * Allow protocol-level BIND parameter values to be logged
>
>
> ---------------------------------------------------------------------------
>
> Ted Powell wrote:

That's it! (I should have thought to look in the TODO.)

Has any design work been done on this?

--
Ted Powell <ted@psg.com> http://psg.com/~ted/
GPL code ... It's the difference between
owning your own home and just renting. --PJ

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

Bruce Momjian

2006-01-30, 8:24 pm

Ted Powell wrote:
> On Mon, Jan 30, 2006 at 04:31:29PM -0500, Bruce Momjian wrote:
>
> That's it! (I should have thought to look in the TODO.)
>
> Has any design work been done on this?

No. I am with Simon Riggs today at my house and I asked him, hoping he
can get it done for 8.2. I don't think it is very hard.

--
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

Ted Powell

2006-01-30, 8:24 pm

On Mon, Jan 30, 2006 at 05:19:23PM -0500, Bruce Momjian wrote:
> [...]
> [...]
>
> No. I am with Simon Riggs today at my house and I asked him, hoping he
> can get it done for 8.2. I don't think it is very hard.

Various things have been pushed on my stack since I posted about this.
When it gets near the top again, I'll check back. Thanks for the response.

--
Ted Powell <ted@psg.com> http://psg.com/~ted/
GPL code ... It's the difference between
owning your own home and just renting. --PJ

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

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

Sponsored Links