Date: Mon, 15 Mar 2021 17:49:45 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>,
	Alexey Bashtanov <bashtanov@imap.cc>,
	Franck Verrot <franck@verrot.fr>, pgsql-hackers@postgresql.org
Subject: Re: [PATCH]: Allow errors in parameter values to be reported during
 the BIND phase itself..
Message-ID: <20210315224945.GB29463@telsasoft.com>
References: <20210104170939.GH9712@telsasoft.com>
 <3087634.1615848349@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <3087634.1615848349@sss.pgh.pa.us>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20210315224945.GB29463%40telsasoft.com>
Precedence: bulk

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.gmail.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