Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-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

pgsql-hackers by date

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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group