Re: [PATCH]: Allow errors in parameter values to be reported during the BIND phase itself..

On Mon, Mar 15, 2021 at 06:45:49PM -0400, Tom Lane wrote:
> Justin Pryzby <pryzby(at)telsasoft(dot)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(at)mail(dot)gmail(dot)com

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