On Sat, Nov 28, 2020 at 4:18 PM Eugen Konkov <kes-kes@yandex.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.
