Re: Feature Request: Report additionally error value

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Sat, Nov 28, 2020 at 4:18 PM Eugen Konkov <kes-kes(at)yandex(dot)ru> wrote:
>> I often fall into error like this:
>> DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::Pg::st
>> execute failed: ERROR: timestamp out of range
>> Would be nice if here also will be reported error value.

> In this specific situation timestamp input does report the problematic text
> when text is provided (timestamp_in) but both the output and
> "receive/binary" routines simply provide the reported error.

A quick look through the source code says that the places where we report
that message without providing a value are mostly places where
timestamp2tm or the like has failed. That means we *can't* produce a
value that will mean anything to a human; the range checks on timestamps
are closely associated with the limitations of the calendar conversion
code.

> Unless you are doing some kind of ETL with that query I'd bet money
> whatever perl is sending along for that input parameter is being sent in
> binary and is incompatible with PostgreSQL's allowed timestamp range.

Yeah. It's not that easy to get an out-of-range timestamp into Postgres,
so a garbage value being sent in binary seems like a pretty likely
explanation. It's not the only explanation, but you'd have to be doing a
fairly out-of-the-ordinary calculation, like

# select now() + interval '1000000 years';
ERROR: timestamp out of range

>> Also would be useful if PG point at query where this bad value was
>> calculated or occur.

> This is not the first time we've seen this request and it usually ends up
> getting stalled because its non-trivial to implement and thus isn't
> feasible for the benefit it brings.

I do still have ambitions to make that happen, but you're right that it's
a major undertaking. Don't hold your breath.

regards, tom lane