Re: [HACKERS] Logging statements and parameter values

From: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>, Ted Powell <ted(at)psg(dot)com>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>, Postgres general mailing list <pgsql-general(at)postgresql(dot)org>
Subject: Re: [HACKERS] Logging statements and parameter values
Date: 2006-02-06 12:28:13
Message-ID: 1139228893.24321.207.camel@coppola.muc.ecircle.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Simon,

For me the usage pattern would be: log all params, bind time values, on
the same log line as "log_min_duration" entries. That's what I need to
know which are the non-performant queries, and it also helps on
occasions to identify application problems.

In any case all your plans sound very good, can't wait to have it
working :-)

Thanks,
Csaba.

On Mon, 2006-02-06 at 12:43, Simon Riggs wrote:
> 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
>
>
> ---------------------------(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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sergey Karin 2006-02-06 12:43:30 change current user in pl/pgsql function
Previous Message Simon Riggs 2006-02-06 11:43:43 Re: Logging statements and parameter values

Browse pgsql-hackers by date

  From Date Subject
Next Message Simon Riggs 2006-02-06 13:40:37 Re: [HACKERS] Logging statements and parameter values
Previous Message Simon Riggs 2006-02-06 11:43:43 Re: Logging statements and parameter values