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