From: Justin Pryzby <pryzby@telsasoft.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Alexey Bashtanov <bashtanov@imap.cc>
Cc: Franck Verrot <franck@verrot.fr>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [PATCH]: Allow errors in parameter values to be reported during the BIND phase itself..
Date: Mon, 15 Mar 2021 17:49:45 -0500
Message-ID: <20210315224945.GB29463@telsasoft.com> (raw)
In-Reply-To: <3087634.1615848349@sss.pgh.pa.us>
References: <20210104170939.GH9712@telsasoft.com>
<3087634.1615848349@sss.pgh.pa.us>
On Mon, Mar 15, 2021 at 06:45:49PM -0400, Tom Lane wrote:
> Justin Pryzby <pryzby@telsasoft.com> writes:
> > For example:
> > $ python3.5 -c "import pg; db=pg.DB(); q = db.query(\"SET log_parameter_max_length_on_error=-1;\"); db.prepare('p', 'SELECT \$1::smallint'); db.query_prepared('p',66666);"
> > 2021-01-03 02:21:04.547 CST [20157] ERROR: value "66666" is out of range for type smallint
> > 2021-01-03 02:21:04.547 CST [20157] CONTEXT: unnamed portal with parameters: $1 = '66666'
> > 2021-01-03 02:21:04.547 CST [20157] STATEMENT: SELECT $1::smallint
>
> > When there are many bind params, this can be useful to determine which is out
> > of range. Think 900 int/smallint columns, or less-wide tables being inserted
> > multiple rows at a time with VALUES(),(),()...
>
> > Of course, this isn't as good as showing the column name, so I might pursue
> > Tom's suggestion for that at some point.
>
> I started to look at this, and immediately began to wonder where is the
> previous discussion you're evidently referring to. Can you dig up an
> archives link?
I think I was referring to this (from the commit message).
https://www.postgresql.org/message-id/flat/CANfkH5k-6nNt-4cSv1vPB80nq2BZCzhFVR5O4VznYbsX0wZmow@mail....
Also, I looked through the original thread, and found this was discussed at the
time:
https://www.postgresql.org/message-id/b1b68453-9756-bd92-306e-a29fc5ad7cd7%402ndquadrant.com> >> ERROR: value "62812" is out of range for type smallint
> >> STATEMENT: SELECT abalance FROM pgbench_accounts WHERE aid = $1;
> >>
> >> (In this case the error message contains the parameter value, so it's
> >> not a very practical case, but it should work, it seems.)
> > I guess this error occurred /while/ binding, so the parameters probably
> > weren't yet all bound by the time of error reporting.
> > That's why the error message came without parameters.
>
> I see. But I think that could be fixed. Change exec_bind_message() to
> loop over the parameters twice: once to save them away, once to actually
> process them. I think the case of a faulty input value is probably very
> common, so it would be confusing if that didn't work.
https://www.postgresql.org/message-id/resend/20191205231550.GA28677%40alvherre.pgsql> One problem I noticed is that we don't log parameters when
> exec_bind_message fetches the parameter values. So the very first
> example problem in testlibpq5 fails to print the values of any
> parameters previously seen. I don't think this is a real problem in
> practice. You still get the unparseable value in the error message from
> the input function, so not having the errdetail() does not seem very
> important.
I see that as a deficiency (as Peter did), so I'm requesting to improve that
now. It's not a bugfix, though.
--
Justin
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-hackers@postgresql.org
Cc: pryzby@telsasoft.com, tgl@sss.pgh.pa.us, alvherre@alvh.no-ip.org, bashtanov@imap.cc, franck@verrot.fr
Subject: Re: [PATCH]: Allow errors in parameter values to be reported during the BIND phase itself..
In-Reply-To: <20210315224945.GB29463@telsasoft.com>
* 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