Обсуждение: partitioned table
I have table and partitioned for year year like this:
CREATE TABLE ecisdrdm.bnft_curr_fact
(bnft_fact_id numeric(38),
bene_cntry_of_brth_id numeric(38),
bene_cntry_of_rsdc_id numeric(38),
bene_cntry_of_ctznshp_id numeric(38),
frm_id numeric(38),
svc_ctr_id numeric(38),
actn_dt_in_id numeric(38),
actn_tm_in_id numeric(38),
src_sys_id numeric(38),
bnft_hist_actn_id numeric(38),
bene_id numeric(38),
bene_end_dt_id numeric(38),
petnr_app_id numeric(38),
atty_id numeric(38),
uscis_emp_id numeric(38),
application_id numeric(38) default -1000000,
rmtr_id numeric(38),
prpr_id numeric(38),
mig_filename varchar(80),
mig_insert_dt timestamp,
mig_modified_dt timestamp
) partition by range (actn_dt_in_id)
TABLESPACE ecisdrdm_data;
CREATE INDEX bnftn_fact_frmid_bmx1 ON ecisdrdm.bnft_curr_fact (frm_id)
TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_attyid_bmx1 ON ecisdrdm.bnft_curr_fact (atty_id)
TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_applicatiid_bti1 ON ecisdrdm.bnft_curr_fact (applicatiON_id)
TABLESPACE ecisdrdm_index;
CREATE INDEX src_sys_id_actn_dt_bmx1 ON ecisdrdm.bnft_curr_fact (src_sys_id, actn_dt_in_id)
TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_bnftfactid_bti1 ON ecisdrdm.bnft_curr_fact (bnft_fact_id)
TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_actndtinid_bmx1 ON ecisdrdm.bnft_curr_fact (actn_dt_in_id)
TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_coposit3_bmx1 ON ecisdrdm.bnft_curr_fact (svc_ctr_id, uscis_emp_id)
TABLESPACE ecisdrdm_index;
CREATE INDEX src_sys_id_actn_dt_saa ON ecisdrdm.bnft_curr_fact (src_sys_id, actn_dt_in_id, applicatiON_id)
TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_beneid_bmx1 ON ecisdrdm.bnft_curr_fact (bene_id)
TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_petnrappid_bti1 ON ecisdrdm.bnft_curr_fact (petnr_app_id)
TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_uscisempid_bmx1 ON ecisdrdm.bnft_curr_fact (uscis_emp_id)
TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_bnfhisactid_bmx1 ON ecisdrdm.bnft_curr_fact (bnft_hist_actn_id)
TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_src_sys_id_bmx1 ON ecisdrdm.bnft_curr_fact (src_sys_id)
TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_benenddtid_bmx1 ON ecisdrdm.bnft_curr_fact (bene_end_dt_id)
TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_prprid_bmx1 ON ecisdrdm.bnft_curr_fact (prpr_id)
TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_svcctrid_bmx1 ON ecisdrdm.bnft_curr_fact (svc_ctr_id)
TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_benctrysdcid_bmx1 ON ecisdrdm.bnft_curr_fact (bene_cntry_of_rsdc_id)
TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_benctrybrtid_bmx1 ON ecisdrdm.bnft_curr_fact (bene_cntry_of_brth_id)
TABLESPACE ecisdrdm_index;
as same as stg_bnft_curr_fact table, it's partitioned too.
when I manually mocking the data into both tables are fine and when I run the procedure, I get errorcode: 42P10 MSG: there is no unique or exclusion constraint matching on the CONFLICT specification
the procedure is
CREATE OR REPLACE FUNCTION ecisdrdm.pr_mig_stg_bnft_curr_fact(
OUT v_ret text)
RETURNS text
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
DECLARE
v_module text = 'pr_mig_stg_bnft_curr_fact ';
host text = inet_server_addr();
errorcode text;
errormsg text;
errormsg_detail text;
errormsg_hint text;
BEGIN
----
-- MERGING: STG_BNFT_CURR_FACT into BNFT_CURR_FACT
----
INSERT INTO ecisdrdm.bnft_curr_fact AS prod (bnft_fact_id, bene_cntry_of_brth_id, bene_cntry_of_rsdc_id,
bene_cntry_of_ctznshp_id, frm_id, svc_ctr_id, actn_dt_in_id, actn_tm_in_id, src_sys_id,
bnft_hist_actn_id, bene_id, bene_end_dt_id, petnr_app_id, atty_id, uscis_emp_id, application_id,
rmtr_id, prpr_id, mig_filename)
SELECT stg.bnft_fact_id, stg.bene_cntry_of_brth_id, stg.bene_cntry_of_rsdc_id,
stg.bene_cntry_of_ctznshp_id, stg.frm_id, stg.svc_ctr_id, stg.actn_dt_in_id, stg.actn_tm_in_id, stg.src_sys_id,
stg.bnft_hist_actn_id, stg.bene_id, stg.bene_end_dt_id, stg.petnr_app_id, stg.atty_id, stg.uscis_emp_id, stg.application_id,
stg.rmtr_id, stg.prpr_id, stg.mig_filename
FROM ecisdrdm.stg_bnft_curr_fact stg
ON CONFLICT ("bnft_fact_id") DO UPDATE
SET (bnft_fact_id, bene_cntry_of_brth_id, bene_cntry_of_rsdc_id,
bene_cntry_of_ctznshp_id, frm_id, svc_ctr_id, actn_dt_in_id, actn_tm_in_id, src_sys_id,
bnft_hist_actn_id, bene_id, bene_end_dt_id, petnr_app_id, atty_id, uscis_emp_id, application_id,
rmtr_id, prpr_id, mig_filename, mig_modified_dt)
=
(SELECT stg.bnft_fact_id, stg.bene_cntry_of_brth_id, stg.bene_cntry_of_rsdc_id,
stg.bene_cntry_of_ctznshp_id, stg.frm_id, stg.svc_ctr_id, stg.actn_dt_in_id, stg.actn_tm_in_id, stg.src_sys_id,
stg.bnft_hist_actn_id, stg.bene_id, stg.bene_end_dt_id, stg.petnr_app_id, stg.atty_id, stg.uscis_emp_id, stg.application_id,
stg.rmtr_id, stg.prpr_id, stg.mig_filename, current_timestamp
FROM ecisdrdm.stg_bnft_curr_fact stg
WHERE prod.application_id = stg.application_id
);
----
-- Set return to "Success" for pr_merge_staging_tables function
----
v_ret := 'Success';
EXCEPTION
WHEN OTHERS THEN
GET STACKED DIAGNOSTICS
errormsg = MESSAGE_TEXT,
errormsg_detail = PG_EXCEPTION_DETAIL,
errormsg_hint = PG_EXCEPTION_HINT;
errorcode := SQLSTATE;
v_ret := concat('ERROR - FUNC: ' || v_module || ' ERRORCODE: ', errorcode, ' MSG: ' || errormsg || ' ', errormsg_detail || ' ', errormsg_hint);
-- NOTE: Only writes to errorlog table if function is called directly
-- If called through the pr_merge_staging_tables function the exception is raised and caught there instead
PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, host, v_module, errorcode, v_ret );
end;
$BODY$;
when I select the table from pgadmin tool, it's not opened and popped up with a blank window with title "index out or range" I do have index on it.
regards,
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success
when I manually mocking the data into both tables are fine and when I run the procedure, I get errorcode: 42P10 MSG: there is no unique or exclusion constraint matching on the CONFLICT specificationthe procedure is
...
INSERT INTO ecisdrdm.bnft_curr_fact AS prod (bnft_fact_id, bene_cntry_of_brth_id, bene_cntry_of_rsdc_id,bene_cntry_of_ctznshp_id, frm_id, svc_ctr_id, actn_dt_in_id, actn_tm_in_id, src_sys_id,bnft_hist_actn_id, bene_id, bene_end_dt_id, petnr_app_id, atty_id, uscis_emp_id, application_id,rmtr_id, prpr_id, mig_filename)SELECT stg.bnft_fact_id, stg.bene_cntry_of_brth_id, stg.bene_cntry_of_rsdc_id,stg.bene_cntry_of_ctznshp_id, stg.frm_id, stg.svc_ctr_id, stg.actn_dt_in_id, stg.actn_tm_in_id, stg.src_sys_id,stg.bnft_hist_actn_id, stg.bene_id, stg.bene_end_dt_id, stg.petnr_app_id, stg.atty_id, stg.uscis_emp_id, stg.application_id,stg.rmtr_id, stg.prpr_id, stg.mig_filenameFROM ecisdrdm.stg_bnft_curr_fact stgON CONFLICT ("bnft_fact_id") DO UPDATESET (bnft_fact_id, bene_cntry_of_brth_id, bene_cntry_of_rsdc_id,
The documentation and the error message explain the issue.
"there is no unique or exclusion constraint matching on the CONFLICT specification"
"The optional
ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error."You have an index, but it is not unique. With partitioning, you cannot create a unique index on a column that is not contained by your partition key. So, you need to re-write to skip the use of ON CONFLICT I expect.
when I manually mocking the data into both tables are fine and when I run the procedure, I get errorcode: 42P10 MSG: there is no unique or exclusion constraint matching on the CONFLICT specificationthe procedure is
...
INSERT INTO ecisdrdm.bnft_curr_fact AS prod (bnft_fact_id, bene_cntry_of_brth_id, bene_cntry_of_rsdc_id,bene_cntry_of_ctznshp_id, frm_id, svc_ctr_id, actn_dt_in_id, actn_tm_in_id, src_sys_id,bnft_hist_actn_id, bene_id, bene_end_dt_id, petnr_app_id, atty_id, uscis_emp_id, application_id,rmtr_id, prpr_id, mig_filename)SELECT stg.bnft_fact_id, stg.bene_cntry_of_brth_id, stg.bene_cntry_of_rsdc_id,stg.bene_cntry_of_ctznshp_id, stg.frm_id, stg.svc_ctr_id, stg.actn_dt_in_id, stg.actn_tm_in_id, stg.src_sys_id,stg.bnft_hist_actn_id, stg.bene_id, stg.bene_end_dt_id, stg.petnr_app_id, stg.atty_id, stg.uscis_emp_id, stg.application_id,stg.rmtr_id, stg.prpr_id, stg.mig_filenameFROM ecisdrdm.stg_bnft_curr_fact stgON CONFLICT ("bnft_fact_id") DO UPDATESET (bnft_fact_id, bene_cntry_of_brth_id, bene_cntry_of_rsdc_id,
The documentation and the error message explain the issue.
"there is no unique or exclusion constraint matching on the CONFLICT specification"
"The optional
ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error."You have an index, but it is not unique. With partitioning, you cannot create a unique index on a column that is not contained by your partition key. So, you need to re-write to skip the use of ON CONFLICT I expect.
I think you might create a block "Begin-Commit", before procedure call.
El 2020-01-09 12:15, Michael Lewis escribió:
>> when I manually mocking the data into both tables are fine and when
>> I run the procedure, I get errorcode: 42P10 MSG: there is no unique
>> or exclusion constraint matching on the CONFLICT specification
>>
>> the procedure is
>
> ...
>
>> INSERT INTO ecisdrdm.bnft_curr_fact AS prod (bnft_fact_id,
>> bene_cntry_of_brth_id, bene_cntry_of_rsdc_id,
>> bene_cntry_of_ctznshp_id, frm_id, svc_ctr_id, actn_dt_in_id,
>> actn_tm_in_id, src_sys_id,
>> bnft_hist_actn_id, bene_id, bene_end_dt_id, petnr_app_id, atty_id,
>> uscis_emp_id, application_id,
>> rmtr_id, prpr_id, mig_filename)
>> SELECT stg.bnft_fact_id, stg.bene_cntry_of_brth_id,
>> stg.bene_cntry_of_rsdc_id,
>> stg.bene_cntry_of_ctznshp_id, stg.frm_id, stg.svc_ctr_id,
>> stg.actn_dt_in_id, stg.actn_tm_in_id, stg.src_sys_id,
>> stg.bnft_hist_actn_id, stg.bene_id, stg.bene_end_dt_id,
>> stg.petnr_app_id, stg.atty_id, stg.uscis_emp_id, stg.application_id,
>>
>> stg.rmtr_id, stg.prpr_id, stg.mig_filename
>> FROM ecisdrdm.stg_bnft_curr_fact stg
>> ON CONFLICT ("bnft_fact_id") DO UPDATE
>> SET (bnft_fact_id, bene_cntry_of_brth_id, bene_cntry_of_rsdc_id,
>
> The documentation and the error message explain the issue.
>
> "there is no unique or exclusion constraint matching on the CONFLICT
> specification"
>
> "The optional ON CONFLICT clause specifies an alternative action to
> raising a unique violation or exclusion constraint violation error."
>
> -https://www.postgresql.org/docs/current/sql-insert.html
>
> You have an index, but it is not unique. With partitioning, you cannot
> create a unique index on a column that is not contained by your
> partition key. So, you need to re-write to skip the use of ON CONFLICT
> I expect.
I think you might create a block "Begin-Commit", before procedure call.
El 2020-01-09 12:15, Michael Lewis escribió:
>> when I manually mocking the data into both tables are fine and when
>> I run the procedure, I get errorcode: 42P10 MSG: there is no unique
>> or exclusion constraint matching on the CONFLICT specification
>>
>> the procedure is
>
> ...
>
>> INSERT INTO ecisdrdm.bnft_curr_fact AS prod (bnft_fact_id,
>> bene_cntry_of_brth_id, bene_cntry_of_rsdc_id,
>> bene_cntry_of_ctznshp_id, frm_id, svc_ctr_id, actn_dt_in_id,
>> actn_tm_in_id, src_sys_id,
>> bnft_hist_actn_id, bene_id, bene_end_dt_id, petnr_app_id, atty_id,
>> uscis_emp_id, application_id,
>> rmtr_id, prpr_id, mig_filename)
>> SELECT stg.bnft_fact_id, stg.bene_cntry_of_brth_id,
>> stg.bene_cntry_of_rsdc_id,
>> stg.bene_cntry_of_ctznshp_id, stg.frm_id, stg.svc_ctr_id,
>> stg.actn_dt_in_id, stg.actn_tm_in_id, stg.src_sys_id,
>> stg.bnft_hist_actn_id, stg.bene_id, stg.bene_end_dt_id,
>> stg.petnr_app_id, stg.atty_id, stg.uscis_emp_id, stg.application_id,
>>
>> stg.rmtr_id, stg.prpr_id, stg.mig_filename
>> FROM ecisdrdm.stg_bnft_curr_fact stg
>> ON CONFLICT ("bnft_fact_id") DO UPDATE
>> SET (bnft_fact_id, bene_cntry_of_brth_id, bene_cntry_of_rsdc_id,
>
> The documentation and the error message explain the issue.
>
> "there is no unique or exclusion constraint matching on the CONFLICT
> specification"
>
> "The optional ON CONFLICT clause specifies an alternative action to
> raising a unique violation or exclusion constraint violation error."
>
> -https://www.postgresql.org/docs/current/sql-insert.html
>
> You have an index, but it is not unique. With partitioning, you cannot
> create a unique index on a column that is not contained by your
> partition key. So, you need to re-write to skip the use of ON CONFLICT
> I expect.