From: Bruce Momjian <bruce@momjian.us>
To: Nigel Bishop <Nigel.Bishop@ioko.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Getting bind variables from log file
Date: Mon, 20 Nov 2006 16:28:38 -0500 (EST)
Message-ID: <200611202128.kAKLScT05820@momjian.us> (raw)
In-Reply-To: <72558D9A7573814BB4082A28843E03D403B27E7B@intyodc01.uk.ioko365.com>
Nigel Bishop wrote:
> Hi,
>
>
>
> PG 8.1.4
>
> O/S Solaris 8
>
>
>
> I was wondering whether it's possible to get the value of the bind
> variables in the PG log file
>
Yes, but only in 8.2beta.
---------------------------------------------------------------------------
>
>
> We have an app that's failing:
>
>
>
> 2006-11-15 12:50:15 GMTSTATEMENT: BEGIN
>
> 2006-11-15 12:50:15 GMTLOG: 00000: statement: PREPARE <unnamed> AS
> SELECT displayname, stringvalue, datatype, siteid, datereceived,
>
> file, intvalue FROM file_head INNER JOIN file_body USING (fileid) INNER
> JOIN metric_references USING (reference) WHERE datereceived
>
> BETWEEN DATE $1 AND DATE $2 AND siteid IN ($3) AND reference IN ($4)
> ORDER BY datereceived, displayname, file, siteid
>
> 2006-11-15 12:50:15 GMTLOCATION: exec_parse_message, postgres.c:1153
>
> 2006-11-15 12:50:15 GMTSTATEMENT: SELECT displayname, stringvalue,
> datatype, siteid, datereceived, file, intvalue FROM file_head IN
>
> NER JOIN file_body USING (fileid) INNER JOIN metric_references USING
> (reference) WHERE datereceived BETWEEN DATE $1 AND DATE $2 AND
>
> siteid IN ($3) AND reference IN ($4) ORDER BY datereceived,
> displayname, file, siteid
>
> 2006-11-15 12:50:15 GMTDEBUG: 00000: StartTransactionCommand
>
> 2006-11-15 12:50:15 GMTLOCATION: start_xact_command, postgres.c:1983
>
> 2006-11-15 12:50:15 GMTSTATEMENT: SELECT displayname, stringvalue,
> datatype, siteid, datereceived, file, intvalue FROM file_head IN
>
> NER JOIN file_body USING (fileid) INNER JOIN metric_references USING
> (reference) WHERE datereceived BETWEEN DATE $1 AND DATE $2 AND
>
> siteid IN ($3) AND reference IN ($4) ORDER BY datereceived,
> displayname, file, siteid
>
> 2006-11-15 12:50:15 GMTERROR: 42601: syntax error at or near "$1" at
> character 211
>
> 2006-11-15 12:50:15 GMTLOCATION: yyerror, scan.l:770
>
> 2006-11-15 12:50:15 GMTSTATEMENT: SELECT displayname, stringvalue,
> datatype, siteid, datereceived, file, intvalue FROM file_head IN
>
> NER JOIN file_body USING (fileid) INNER JOIN metric_references USING
> (reference) WHERE datereceived BETWEEN DATE $1 AND DATE $2 AND
>
> siteid IN ($3) AND reference IN ($4) ORDER BY datereceived,
> displayname, file, siteid
>
> 2006-11-15 12:50:15 GMTDEBUG: 00000: StartTransactionCommand
>
> 2006-11-15 12:50:15 GMTLOCATION: start_xact_command, postgres.c:1983
>
> 2006-11-15 12:50:15 GMTDEBUG: 00000: StartTransaction
>
>
>
> Running the SQL at the prompt is fine using the date format 'YYYY-MM-DD'
>
>
>
> I set the following params in the conf file and although if kicks out a
> lot of info it doesn't show the values for the bind variables
>
>
>
> client_min_messages = debug5
>
> log_min_messages = debug5
>
> log_error_verbosity = verbose
>
> log_min_error_statement = debug5
>
> log_min_duration_statement = 0
>
> log_statement = 'all'
>
>
>
> Can someone point out where I'm going wrong?
>
>
>
> Thanks,
>
>
>
> Nigel
>
>
>
>
>
>
>
> Communications on or through ioko's computer systems may be monitored or recorded to secure effective system operation and for other lawful purposes.
>
> Unless otherwise agreed expressly in writing, this communication is to be treated as confidential and the information in it may not be used or disclosed except for the purpose for which it has been sent. If you have reason to believe that you are not the intended recipient of this communication, please contact the sender immediately. No employee is authorised to conclude any binding agreement on behalf of ioko with another party by e-mail without prior express written confirmation.
>
> ioko365 Ltd. VAT reg 656 2443 31. Reg no 3048367. All rights reserved.
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: bruce@momjian.us, Nigel.Bishop@ioko.com
Subject: Re: Getting bind variables from log file
In-Reply-To: <200611202128.kAKLScT05820@momjian.us>
References: <72558D9A7573814BB4082A28843E03D403B27E7B@intyodc01.uk.ioko365.com> <200611202128.kAKLScT05820@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox