Getting bind variables from log file - Mailing list pgsql-admin

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 = 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