Re: Logging statements and parameter values

On Mon, 2006-01-30 at 17:19 -0500, Bruce Momjian wrote:
> Ted Powell wrote:
> > On Mon, Jan 30, 2006 at 04:31:29PM -0500, Bruce Momjian wrote:
> > >
> > > 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. [...]
> >
> > 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.

Some more detailed thoughts:

1. Do we want to log parameters at Bind time or at Execution time? Bind
is easier and more correct, but might look a little strange in the log
since the parameters would be logged before the execution appears. IMHO
Bind time is more correct. That would mean we have a separate line for
logged parameters, e.g.
parameters: p1=111 p2=hshssh p3=47000.5

2. Should we save them until end of execution, so we can output them on
the same line as log_min_duration_statement queries? Sounds easier but
the meaning might be more confused.

3. Do we want to log parameters that are used for planning, but no
others? Sometimes yes, sometimes no, I think.

Sounds like we need:
- a log_parameters GUC with settings of: "none", "plan" and "all".
- output log messages at Bind time on a separate log line, which would
replace the existing "statement: [protocol] <BIND>" message with
"(portalname) parameters: p1=111 p2=hshssh p3=47000.5"
- portalname would be blank if we aren't using named portals

While we're discussing logging, I also want to be able to set
log_min_duration_statement on a user by user basis (i,e, for individual
applications). We set this to superuser-only for valid security reasons,
but I'd like to have the ability for the superuser to relax that
restriction for short periods, or even permanently on development
servers. That sounds like another GUC: log_security = on
which would enforce SUSET/USERSET control (and would need to be a SIGHUP
parameter).

Best Regards, Simon Riggs