Re: Feature Request: Report additionally error value

I format query to read it more easy. See attachment

Tuesday, December 8, 2020, 3:43:13 PM, you wrote:

Hello Tom,

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

Another case posted below.

Here in database are values which could not construct correct daterange.
There are thousands of rows. It is hard to find even a way or make an assumption
how to get record which cause this error.

It will be very impressive if database could report the line at database where error is occur.
Via HINT for example.
If this is not possible, then current cursor position or raw data dump in debug mode.
Probably some memory buffers or so.

Any additional info would be helpful.

Thank you.

DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::Pg::st execute failed: ERROR: range lower bound must be less than or equal to range upper bound?CONTEXT: SQL function "accounting_ready" statement 1 [for Statement "SELECT COUNT( * ) FROM (( WITH?target_date AS ( SELECT ?::timestamptz ),?target_order as (? SELECT? usage_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(? CASE WHEN prd.INTERVAL = '1 mon' THEN date_trunc( 'month', o.docdate ) ELSE o.docdate END,? prd.INTERVAL, (SELECT * FROM target_date)? ) acc ON true? WHERE FALSE? OR ? = 0? OR ? = 1 AND o.id = ?? AND acc.usage AND EXISTS (? SELECT * FROM order_bt prev_order? WHERE sys_period @> sys_time() AND? prev_order.id = o.id AND prev_order.app_period && acc.usage_range? )? AND o.app_period && acc.usage_range? OR ? = 2 AND o.agreement_id = ? and o.period_id = ?? AND acc.usage AND EXISTS (? SELECT * FROM order_bt prev_order? WHERE sys_period @> sys_time() AND? prev_order.id = o.id AND prev_order.app_period && acc.usage_range? )? AND o.app_period && acc.usage_range? OR ? = 3?),?USAGE AS ( SELECT? (ocd.o).id as order_id,? (dense_rank() over (PARTITION BY o.agreement_id, o.id ORDER BY (ocd.ic).consumed_period )) as group_id,? (ocd.c).id as detail_id,? (ocd.c).service_type_id as service_type,? (ocd.c).resource_type_id as detail_type,? (ocd.c).amount as detail_amount,? (ocd.c).allocated_resource_id as resource_id,? null as resource_uuid,? null as resource_desc,? rt.unit as resource_unit,? -- count changes. Logic is next: How many times configration for this order is met at this period? count(*) OVER (PARTITION BY (ocd.o).id, (ocd.c).id ) as consumed_count,? (ocd.ic).consumed as consumed_days,? null as consumed_amount,? null as consumed_last,?? a.id as agreement_id,? coalesce( substring( a.docn from '^A?(.*?)(?:\s*-[^-]*)?$' ), a.docn ) as agreement,? a.docdate as docdate,?? pkg.id as package_id,? pkg.link_1c_id as package_1c_id,? coalesce( pkg.display, pkg.name ) as package,?? coalesce( st.display, st.name, rt.display, rt.name ) as sr_name,? COALESCE( (ocd.p).label, rt.LABEL ) as unit_label,?? coalesce( (ocd.c).sort_order, pd.sort_order ) as sort_order,?? ocd.item_price AS price,? -- We want to display QTY for resources too? coalesce( ocd.item_qty, (ocd.c).amount/rt.unit ) AS qty,? 0 AS month_length,? 0 AS days_count,? o.bill_range,? lower( (ocd.ic).consumed_period ) as consumed_from,? upper( (ocd.ic).consumed_period ) -interval '1sec' as consumed_till,??? ocd.item_suma,?? 0 as discount,? (sum( ocd.item_suma ) OVER( PARTITION BY (ocd.o).id, (ocd.ic).consumed_period ))::numeric( 10, 2) AS group_suma,? (sum( ocd.item_cost ) OVER( PARTITION BY (ocd.o).id, (ocd.ic).consumed_period ))::numeric( 10, 2) AS order_cost?FROM target_order o?LEFT JOIN order_cost_details( o.bill_range ) ocd? ON (ocd.o).id = o.id AND (ocd.ic).consumed_period && o.app_period? LEFT JOIN agreement a ON a.id = o.agreement_id? LEFT JOIN package pkg ON pkg.id = o.package_id? LEFT JOIN package_detail pd ON pd.package_id = (ocd.o).package_id? AND pd.resource_type_id IS NOT DISTINCT FROM (ocd.c).resource_type_id? AND pd.service_type_id IS NOT DISTINCT FROM (ocd.c).service_type_id? LEFT JOIN resource_type rt ON rt.id = (ocd.c).resource_type_id? LEFT JOIN service_type st on st.id = (ocd.c).service_type_id?)??SELECT *,? (group_suma/6) ::numeric( 10, 2 ) as group_nds,? (SELECT sum(x) from (SELECT sum( DISTINCT group_suma ) AS x FROM usage sub_u WHERE sub_u.agreement_id = usage.agreement_id GROUP BY agreement_id, order_id) t) as total_suma,? (SELECT sum(x) from (SELECT sum( DISTINCT (group_suma/6)::numeric( 10, 2 ) ) AS x FROM usage sub_u WHERE sub_u.agreement_id = usage.agreement_id GROUP BY agreement_id, order_id) t) as total_nds?FROM usage?where ? <> 3 OR consumed_count > 1?ORDER BY? /* put order first then allocated resource without Order */? agreement,? order_id,? bill_range,? group_id,? sort_order nulls last,? detail_type nulls last,? price desc nulls last,? detail_amount desc,? service_type nulls last,? detail_id?)?) "me"" with ParamValues: 1='2020-08-01', 2='2', 3='2', 4=undef, 5='2', 6='3493', 7='10', 8='2', 9='2'] at /home/kes/work/projects/tucha/monkeyman/lib/MaitreD/Controller/Cart.pm line 828

--
Best regards,
Eugen Konkov

--
Best regards,
Eugen Konkov