From: Tom Lane <tgl@sss.pgh.pa.us>
To: Natalie Wenz <nataliewenz@ebureau.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Cc: Joshua D. Drake <jd@commandprompt.com>
Subject: Re: Inspect values of prepared statements
Date: Mon, 02 May 2016 17:40:59 -0400
Message-ID: <14231.1462225259@sss.pgh.pa.us> (raw)
In-Reply-To: <4DD34523-B75E-4409-A640-2704B0ED2C3E@ebureau.com>
References: <26FFD5EA-0317-497F-9CCA-BC80C6A1CA6C@ebureau.com>
<5727943C.4050008@commandprompt.com>
<1B7B1A44-6B19-46AF-8ED8-74098F43B45C@ebureau.com>
<57279740.9080300@commandprompt.com>
<4DD34523-B75E-4409-A640-2704B0ED2C3E@ebureau.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Natalie Wenz <nataliewenz@ebureau.com> writes:
> So, I tried turning statement logging on and that didn't give me any more detail. I think that is because the query I want to inspect is generating an error (which I failed to mention in my original question).
> ERROR: invalid input syntax for type double precision: "A"
> STATEMENT: insert into x (a,b,c,d,e,f) VALUES ($1,$2,$3,$4,$5,$6)
> columns c, d, e are all double precision, so I'm not sure which one has the bad data. If I knew what values $1 and $2 were, I could pinpoint the record that is generating the error.
Ah. You're out of luck on that, I'm afraid, because what the
parameter-value logging code does is to print from the converted internal
forms of the parameters. If you get an error while converting the values
to internal form, which is evidently what happened here, no log for you.
Also, that conversion happens on-the-fly as each Bind message field is
read. Thus, for example, if the bad input is for parameter $2 then we
simply can't print the remaining parameter values, because we have not
seen them yet. (This could be fixed, perhaps, but it would involve a
lot of restructuring and probably slow things down. Also it wouldn't
work for parameters arriving in binary form; which is probably what led
us to make the choice to do parameter logging this way.)
What I think we could do without too much trouble or overhead is to set
up errcontext data so that a failure like that could come with a CONTEXT
line like
ERROR: invalid input syntax for type double precision: "A"
STATEMENT: insert into x (a,b,c,d,e,f) VALUES ($1,$2,$3,$4,$5,$6)
CONTEXT: reading parameter $2 for statement "foo"
I'm curious whether that would be enough information to solve your
problem.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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 using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, nataliewenz@ebureau.com, jd@commandprompt.com
Subject: Re: Inspect values of prepared statements
In-Reply-To: <14231.1462225259@sss.pgh.pa.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