Getting bind variables from log file

This is a multi-part message in MIME format.

------_=_NextPart_001_01C70CAC.582C6792
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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

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 =3D debug5

log_min_messages =3D debug5

log_error_verbosity =3D verbose

log_min_error_statement =3D debug5

log_min_duration_statement =3D 0

log_statement =3D '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 purpos=
es.

Unless otherwise agreed expressly in writing, this communication is to be=
treated as confidential and the information in it may not be used or dis=
closed except for the purpose for which it has been sent. If you have rea=
son to believe that you are not the intended recipient of this communicat=
ion, 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.

------_=_NextPart_001_01C70CAC.582C6792
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:o=3D"urn:schemas-microsoft-com:office:office" xmlns:w=3D"urn:=
schemas-microsoft-com:office:word" xmlns=3D"http://www.w3.org/TR/REC-html=
40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)">
<style>

</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

Hi,<o:p></o:p>

<o:p> </o:p>

PG 8.1.4<o:p></o:p>

O/S Solaris 8<o:p></o:p>

<o:p> </o:p>

I was wondering whether it’s possible to=
get
the value of the bind variables in the PG log file<o:p></o:p></fon=
t>

<o:p> </o:p>

We have an app that’s failing:<o:p></o:p=
>

<o:p> </o:p>

2006-11-15 12:50:15 GMTSTATEMENT: BEGIN<=
o:p></o:p>

2006-11-15 12:50:15 GMTLOG: 00000: state=
ment:
PREPARE <unnamed> AS SELECT displayname, stringvalue, datatype, sit=
eid,
datereceived,<o:p></o:p>

 file, intvalue FROM file_head INNER JOIN=

file_body USING (fileid) INNER JOIN metric_references USING (reference) W=
HERE
datereceived<o:p></o:p>

 BETWEEN DATE $1 AND DATE $2 AND siteid I=
N
($3) AND reference IN ($4) ORDER BY datereceived, displayname, file=
,
siteid<o:p></o:p>

2006-11-15 12:50:15 GMTLOCATION:
exec_parse_message, postgres.c:1153<o:p></o:p>

2006-11-15 12:50:15 GMTSTATEMENT: SELECT=

displayname, stringvalue, datatype, siteid, datereceived, file, intvalue =
FROM
file_head IN<o:p></o:p>

NER JOIN file_body USING (fileid) INNER JOIN
metric_references USING (reference) WHERE datereceived BETWEEN DATE $1 AN=
D DATE
$2 AND<o:p></o:p>

siteid IN ($3) AND reference IN ($4) ORD=
ER BY
datereceived, displayname, file, siteid<o:p></o:p>

2006-11-15 12:50:15 GMTDEBUG: 00000:
StartTransactionCommand<o:p></o:p>

2006-11-15 12:50:15 GMTLOCATION:
start_xact_command, postgres.c:1983<o:p></o:p>

2006-11-15 12:50:15 GMTSTATEMENT: <sp=
an
style=3D'font-weight:bold'>SELECT displayname, stringvalue, datatype, sit=
eid,
datereceived, file, intvalue FROM file_head IN<o:p></o:p></spa=
n>

NER JOIN file_body USI=
NG
(fileid) INNER JOIN metric_references USING (reference) WHERE datereceive=
d
BETWEEN DATE $1 AND DATE $2 AND<o:p></o:p>

siteid IN ($3) A=
ND
reference IN ($4) ORDER BY datereceived, displayname, file, siteid<o:p></=
o:p>

2006-11-15 12:50:15
GMTERROR: 42601: syntax error at or near "$1" at characte=
r 211<o:p></o:p>

2006-11-15 12:50:15 GMTLOCATION: yyerror=
,
scan.l:770<o:p></o:p>

2006-11-15 12:50:15 GMTSTATEMENT: SELECT=

displayname, stringvalue, datatype, siteid, datereceived, file, intvalue =
FROM
file_head IN<o:p></o:p>

NER JOIN file_body USING (fileid) INNER JOIN
metric_references USING (reference) WHERE datereceived BETWEEN DATE $1 AN=
D DATE
$2 AND<o:p></o:p>

siteid IN ($3) AND reference IN ($4) ORD=
ER BY
datereceived, displayname, file, siteid<o:p></o:p>

2006-11-15 12:50:15 GMTDEBUG: 00000:
StartTransactionCommand<o:p></o:p>

2006-11-15 12:50:15 GMTLOCATION:
start_xact_command, postgres.c:1983<o:p></o:p>

2006-11-15 12:50:15 GMTDEBUG: 00000:
StartTransaction<o:p></o:p>

<o:p> </o:p>

Running the SQL at the prompt is fine using th=
e date
format ‘YYYY-MM-DD’<o:p></o:p>

<o:p> </o:p>

I set the following params in the conf file an=
d
although if kicks out a lot of info it doesn’t show the values for =
the
bind variables<o:p></o:p>

<o:p> </o:p>

client_min_messages =3D debug5<o:p></o:p></spa=
n>

log_min_messages =3D
debug5 &=
nbsp;
<o:p></o:p>

log_error_verbosity =3D verbose<o:p></o:p></sp=
an>

log_min_error_statement =3D
debug5 <o:p></o:p>

log_min_duration_statement =3D 0<o:p></o:p></s=
pan>

log_statement =3D 'all'<o:p></o:p></fon=
t>

<o:p> </o:p>

Can someone point out where I’m going wr=
ong?<o:p></o:p>

<o:p> </o:p>

Thanks,<o:p></o:p>

<o:p> </o:p>

Nigel<o:p></o:p>

<o:=
p> </o:p>

<o:p> </o:p>=


</div>

 Communications on or through ioko's computer systems may be monit=
ored or recorded to secure effective system operation and for other lawfu=
l purposes. Unless otherwise agreed expressly in writing, this com=
munication 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 se=
nt. If you have reason to believe that you are not the intended recipient=
of this communication, please contact the sender immediately. No employe=
e is authorised to conclude any binding agreement on behalf of ioko with =
another party by e-mail without prior express written confirmation. <b=
r>ioko365 Ltd. VAT reg 656 2443 31. Reg no 3048367. All rights reserved.<=
/body>

</html>

------_=_NextPart_001_01C70CAC.582C6792--

Nigel Bishop [ Mo, 20 November 2006 15:01 ] [ ID #1541791 ]

Re: Getting bind variables from log file

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 [at] momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Bruce Momjian [ Mo, 20 November 2006 22:28 ] [ ID #1541792 ]

Re: Getting bind variables from log file

OK Thanks Bruce

-----Original Message-----
From: Bruce Momjian [mailto:bruce [at] momjian.us]
Sent: Monday, November 20, 2006 9:29 PM
To: Nigel Bishop
Cc: pgsql-admin [at] postgresql.org
Subject: Re: [ADMIN] Getting bind variables from log file

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 =3D debug5
>
> log_min_messages =3D debug5
>
> log_error_verbosity =3D verbose
>
> log_min_error_statement =3D debug5
>
> log_min_duration_statement =3D 0
>
> log_statement =3D '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 [at] momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Nigel Bishop [ Di, 21 November 2006 15:48 ] [ ID #1543039 ]