Re: Feature Request: Report additionally error value

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