"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sat, Nov 28, 2020 at 4:18 PM Eugen Konkov <kes-kes@yandex.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