Re: Feature Request: Report additionally error value

On Sat, Nov 28, 2020 at 4:18 PM Eugen Konkov <kes-kes(at)yandex(dot)ru> wrote:

> Hi all.
>
> 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
> CONTEXT: SQL function "accounting_ready" statement 1 [for Statement
> "SELECT COUNT( * ) FROM (WITH
> target_date AS ( SELECT ?::timestamptz ),
> target_order as (
> SELECT
> invoice_range as bill_range,
> o.*
> FROM ( SELECT * FROM "order_bt" WHERE sys_period @> sys_time() ) o
> LEFT JOIN period prd on prd.id = o.period_id
> LEFT JOIN accounting_ready(
> .....
> other 200 lines of query
>
> 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.

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.

Otherwise, yes, sometimes you just need to debug your data (though usually
data is in text format and the error includes the problematic string).

> 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. In short, the text input parsing
routines are decoupled from the queries where they are used.

David J.