Search Postgresql Archives

Possible bug: could not open relation with OID [numbers] SQL State: XX000

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux