Hey all,
First off: PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc
(Debian 6.3.0-18) 6.3.0 20170516, 64-bit
I have something going on, and i'm not sure what is causing it. I
recently upgraded our development environment to PG10, and the error
in the subject appeared with one of my analytical functions.
It creates some temporary tables, joins them together, and then spits
out a result. If I run it for one "contract_id", it'll work just
fine, then I run it for another similar "contract_id", it'll throw the
error in the subject.
I attached the function.
Any help would be appreciated.
Thanks,
-Adam
-- Function: gosimple.contract_exposure_direct(uuid, boolean, boolean, uuid[])
-- DROP FUNCTION gosimple.contract_exposure_direct(uuid, boolean, boolean, uuid[]);
CREATE OR REPLACE FUNCTION gosimple.contract_exposure_direct(
IN p_contract_id uuid,
IN p_stacked_ind boolean,
IN p_limit_actual_ind boolean,
IN p_valid_companies uuid[])
RETURNS TABLE(direct_contract_ids uuid[], direct_rates numeric[], company_id uuid, company_name text, company_number text, product_id uuid, product_name text, product_number text, uom_type_id uuid, uom_type_description text, this_direct_rate numeric, this_estimated_quantity numeric, this_spend numeric, other_direct_rate numeric, total_direct_rate numeric, total_spend numeric, target_rate numeric, claim_ids uuid[], claim_amounts numeric[], total_claim_volume numeric, total_claim_amount numeric) AS
$BODY$
BEGIN
DROP TABLE IF EXISTS tmp_params;
DROP TABLE IF EXISTS tmp_valid_companies;
DROP TABLE IF EXISTS direct_ids;
DROP TABLE IF EXISTS tmp_price;
DROP TABLE IF EXISTS direct_info;
DROP TABLE IF EXISTS tmp_rates;
DROP TABLE IF EXISTS tmp_base;
DROP TABLE IF EXISTS contract_actual_direct;
IF p_contract_id IS NULL
THEN
RAISE EXCEPTION 'p_contract_id cannot be null.';
END IF;
IF p_valid_companies IS NOT NULL AND p_limit_actual_ind = true
THEN
RAISE EXCEPTION 'Cannot use p_valid_companies and p_limit_actual_ind together.';
END IF;
CREATE TEMPORARY TABLE tmp_params AS
SELECT cf.contractee_company_id, cf.contractee_grouping_id, crv.date_range
FROM contract_amend_version cav
INNER JOIN contract_renew_version crv
ON cav.contract_renew_version_id = crv.contract_renew_version_id
INNER JOIN contract_family cf
ON crv.contract_family_id = cf.contract_family_id
WHERE true
AND cav.contract_amend_version_id = p_contract_id
GROUP BY 1, 2, 3;
RAISE NOTICE 'tmp_params created: %', clock_timestamp();
CREATE TEMPORARY TABLE contract_actual_direct AS
SELECT cad.contract_id, cad.product_id, cad.company_id, cad.claim_ids, cad.claim_amounts, cad.total_claim_volume, cad.total_claim_amount
FROM gosimple.contract_actual_direct(p_contract_id, p_valid_companies) cad;
ANALYZE contract_actual_direct;
RAISE NOTICE 'contract_actual_direct created: %', clock_timestamp();
CREATE TEMPORARY TABLE tmp_valid_companies AS
SELECT DISTINCT unnest(p_valid_companies) as company_id
WHERE p_valid_companies IS NOT NULL
UNION ALL
SELECT DISTINCT contract_actual_direct.company_id
FROM contract_actual_direct
WHERE p_limit_actual_ind = true
UNION ALL
SELECT DISTINCT contractee_view_hierarchy.company_id
FROM contractee_view_hierarchy
WHERE contract_id = p_contract_id
AND p_valid_companies IS NULL
AND p_limit_actual_ind = false;
ANALYZE tmp_valid_companies;
RAISE NOTICE 'tmp_valid_companies created: %', clock_timestamp();
CREATE TEMPORARY TABLE direct_ids AS
SELECT p_contract_id::uuid as contract_id
UNION ALL
SELECT cav.contract_amend_version_id as contract_id
FROM contract_amend_version cav
INNER JOIN contract_renew_version crv
ON cav.contract_renew_version_id = crv.contract_renew_version_id
INNER JOIN contract_family cf
ON crv.contract_family_id = cf.contract_family_id
INNER JOIN tmp_params
ON true
WHERE true
AND cf.contractee_company_id IS NOT DISTINCT FROM tmp_params.contractee_company_id
AND cf.contractee_grouping_id IS NOT DISTINCT FROM tmp_params.contractee_grouping_id
AND crv.date_range && tmp_params.date_range
AND cav.contract_state IN ('APPROVED', 'ACTIVE', 'EXPIRED')
AND cav.contract_amend_version_id != p_contract_id
AND EXISTS (
SELECT 1
FROM tmp_valid_companies tvc
INNER JOIN contractee_view_hierarchy conv
ON tvc.company_id = conv.company_id
WHERE true
AND cav.contract_amend_version_id = conv.contract_id
)
AND p_stacked_ind = true;
ANALYZE direct_ids;
RAISE NOTICE 'direct_ids created: %', clock_timestamp();
CREATE TEMPORARY TABLE tmp_price (
identifier uuid -- IN
, contract_id uuid
, company_id uuid -- IN
, product_id uuid -- IN
, target_uom_type_id uuid -- IN
, resolve_date date -- IN
, cost_basis_type enum.cost_basis_type -- IN
, price_id uuid -- OUT
, price numeric -- OUT
);
INSERT INTO tmp_price(identifier, contract_id, company_id, product_id, target_uom_type_id, resolve_date, cost_basis_type)
SELECT gen_random_uuid()
, cp.contract_id
, vcmp.company_id
, cp.product_id
, cp.uom_type_id
, lower(crv.date_range) + ((upper(crv.date_range) - 1) - lower(crv.date_range)) / 2
, cf.cost_basis_type
FROM contract_product cp
INNER JOIN contract_amend_version cav
ON cp.contract_id = cav.contract_amend_version_id
INNER JOIN contract_renew_version crv
ON cav.contract_renew_version_id = crv.contract_renew_version_id
INNER JOIN contract_family cf
ON crv.contract_family_id = cf.contract_family_id
CROSS JOIN tmp_valid_companies vcmp
WHERE true
AND EXISTS (
SELECT 1
FROM direct_ids di
WHERE true
AND cp.contract_id = di.contract_id
)
AND cp.rebate_direct_type != 'NONE'
AND (cp.rebate_direct_type = 'FIXED_PRICE'::enum.rebate_value_type OR cp.rebate_direct_decimal_model = 'PERCENT'::enum.decimal_model);
PERFORM gosimple.get_price();
RAISE NOTICE 'tmp_price created: %', clock_timestamp();
CREATE TEMPORARY TABLE direct_info AS
SELECT x.contract_item_id, gen_random_uuid() as identifier, x.product_id, x.estimated_quantity, x.price, x.uom_type_id, x.company_ids
FROM (
SELECT cp.contract_item_id, cp.product_id, cp.estimated_quantity, cpp.price, cp.uom_type_id, array_agg(tvc.company_id) company_ids
FROM contract_item_view cp
CROSS JOIN tmp_valid_companies tvc
LEFT JOIN tmp_price cpp
ON cp.contract_id = cpp.contract_id
AND cp.product_id = cpp.product_id
AND tvc.company_id = cpp.company_id
WHERE true
AND ((cp.rebate_direct_type = 'FIXED_PRICE') OR (cp.rebate_direct_value != 0 AND cp.rebate_direct_type = 'FIXED_RATE'))
AND EXISTS (
SELECT 1
FROM direct_ids dcid
WHERE true
AND dcid.contract_id = cp.contract_id
)
GROUP BY 1, 2, 3, 4, 5
) x;
ANALYZE direct_info;
RAISE NOTICE 'direct_row created: %', clock_timestamp();
CREATE TEMPORARY TABLE tmp_rates AS
SELECT cder.source_row_id as identifier, cder.rate::numeric as direct_rate, cder.explicit_zero_rate_ind
FROM gosimple.calculate_contract_item_direct_rebate_rate((
SELECT array_agg(row(
di.contract_item_id, di.identifier, di.product_id, null, di.estimated_quantity, di.price, di.uom_type_id
)::gosimple.in_calculate_contract_item_rebate_rate)
FROM direct_info di
)) cder;
ANALYZE tmp_rates;
RAISE NOTICE 'tmp_rates created: %', clock_timestamp();
CREATE TEMPORARY TABLE tmp_base AS
SELECT ci.contract_id, di.product_id, unnest(di.company_ids) as company_id, tr.direct_rate, ci.estimated_quantity, ci.uom_type_id
FROM tmp_rates tr
INNER JOIN direct_info di
USING (identifier)
INNER JOIN contract_item_view ci
USING (contract_item_id, product_id)
WHERE true
AND (ci.contract_id IS NOT DISTINCT FROM p_contract_id OR (ci.contract_id IS DISTINCT FROM p_contract_id AND tr.explicit_zero_rate_ind = false));
ANALYZE tmp_base;
RAISE NOTICE 'tmp_base created: %', clock_timestamp();
RETURN QUERY SELECT
array_agg(tb.contract_id) FILTER (WHERE tb.contract_id IS DISTINCT FROM p_contract_id AND tb.direct_rate IS NOT NULL) as direct_contract_ids
, array_agg(tb.direct_rate) FILTER (WHERE tb.contract_id IS DISTINCT FROM p_contract_id AND tb.direct_rate IS NOT NULL) as direct_rates
, cmp.company_id
, cmp.company_name::text
, cmp.company_number::text
, pr.product_id
, pr.product_name::text
, pr.product_number::text
, ut.uom_type_id
, ut.uom_type_description::text
, sum(tb.direct_rate) FILTER (WHERE tb.contract_id IS NOT DISTINCT FROM p_contract_id) as this_direct_rate
, sum(tb.estimated_quantity) FILTER (WHERE tb.contract_id IS NOT DISTINCT FROM p_contract_id) / cc.valid_company_count as this_estimated_quantity
, sum(tb.direct_rate) FILTER (WHERE tb.contract_id IS NOT DISTINCT FROM p_contract_id) * sum(tb.estimated_quantity) FILTER (WHERE tb.contract_id IS NOT DISTINCT FROM p_contract_id) / cc.valid_company_count as this_spend
, sum(tb.direct_rate) FILTER (WHERE tb.contract_id IS DISTINCT FROM p_contract_id) as other_direct_rate
, sum(tb.direct_rate) as total_direct_rate
, sum(tb.direct_rate) * sum(tb.estimated_quantity) FILTER (WHERE tb.contract_id IS NOT DISTINCT FROM p_contract_id) / cc.valid_company_count as total_spend
, null::numeric as target_rate
, max(cad.claim_ids) claim_ids
, max(cad.claim_amounts) claim_amounts
, sum(cad.total_claim_volume) total_claim_volume
, sum(cad.total_claim_amount) total_claim_amount
FROM tmp_base tb
INNER JOIN product pr
ON tb.product_id = pr.product_id
INNER JOIN uom_type ut
ON tb.uom_type_id = ut.uom_type_id
INNER JOIN company cmp
ON tb.company_id = cmp.company_id
LEFT JOIN (
SELECT count(distinct tmp_valid_companies.company_id) as valid_company_count
FROM tmp_valid_companies
) as cc
ON true
LEFT JOIN contract_actual_direct cad
ON tb.contract_id = cad.contract_id
AND tb.product_id = cad.product_id
AND tb.company_id = cad.company_id
GROUP BY
cmp.company_id
, cmp.company_name
, cmp.company_number
, pr.product_id
, pr.product_name
, pr.product_number
, ut.uom_type_id
, ut.uom_type_description
, cc.valid_company_count;
RAISE NOTICE 'query done: %', clock_timestamp();
DROP TABLE IF EXISTS tmp_params;
DROP TABLE IF EXISTS tmp_valid_companies;
DROP TABLE IF EXISTS direct_ids;
DROP TABLE IF EXISTS tmp_price;
DROP TABLE IF EXISTS direct_info;
DROP TABLE IF EXISTS tmp_rates;
DROP TABLE IF EXISTS tmp_base;
DROP TABLE IF EXISTS contract_actual_direct;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 1000
ROWS 100;
ALTER FUNCTION gosimple.contract_exposure_direct(uuid, boolean, boolean, uuid[])
OWNER TO root;
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
