Postgres Pro
Downloads
Home   >   mailing lists

partitioned table - Mailing list pgsql-admin

From Pepe TD Vo
Subject partitioned table
Date
Msg-id 148443228.5959267.1578589603211@mail.yahoo.com
Whole thread Raw
Responses Re: partitioned table  (Michael Lewis <mlewis@entrata.com>)
Re: partitioned table  (Michael Lewis <mlewis@entrata.com>)
List pgsql-admin
Tree view
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.
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

pgsql-admin by date:

Previous
From: bvo
Date:
Subject: misbehavior slave
Next
From: Michael Lewis
Date:
Subject: Re: partitioned table