Search Postgresql Archives

Problem about partitioned table

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

 



Hi everyone, 

I have a problem with partitioned table in PostgreSql. 
Actually I use the version 10. I created the partitioned table in test environment but face some problems with partitioned table constraint.
I google all about it last week and from the official site I get that version 11 will be released and that feature will be supported as I understand it.
From version 11 documentation
"Add support for PRIMARY KEYFOREIGN KEY, indexes, and triggers on partitioned tables"
I install and configure yesterday as new 11 version released. And test it. Unfortunately I didn't achieve again.
Neither I don't understand the new feature nor this case is actually not supported.
Please help me about the problem.

In my test environment CASE is like that (I used the declarative partitioning)

I have a er_doc_to_user_relation table before. And I partitioned this table by list with column state.
I have  created two partitions as following 
       CREATE TABLE xx.er_doc_to_user_state_1_3
PARTITION OF xx.er_doc_to_user_relation (oid,created_date,state,status,updated_date,branch_oid,state_update_date,user_position,
fk_action_owner,fk_action_owner_org,fk_document,fk_flow,fk_org,fk_prew_doc_user_rel,fk_user,fk_workflow,fk_action_login_type)
FOR VALUES IN (1,3);
CREATE TABLE xx.er_doc_to_user_state_2_4_9
PARTITION OF xx.er_doc_to_user_relation (oid,created_date,state,status,updated_date,branch_oid,state_update_date,user_position,
fk_action_owner,fk_action_owner_org,fk_document,fk_flow,fk_org,fk_prew_doc_user_rel,fk_user,fk_workflow,fk_action_login_type)
FOR VALUES IN (2,4,9);
After that I have created constraints and indexes for each partition manually. Everything is OK until here.
When I try to create constraint in another table which references er_doc_to_user_relation table.
Case 1: Try to create foreign key constraint reference to parent table er_doc_to_user_relation.
              ALTER TABLE xx.er_doc_workflow_action
                   ADD CONSTRAINT fk_doc_work_act FOREIGN KEY (fk_to_user_doc_rel)
                  REFERENCES xx.er_doc_to_user_relation(oid) MATCH SIMPLE
                  ON UPDATE NO ACTION
                 ON DELETE NO ACTION;
         Following error occurred:
                  ERROR: cannot reference partitioned table "er_doc_to_user_relation"
SQL state: 42809

 Because it is not supported so I try the second case as following.

Case 2: Try to create foreign key constraint  reference to each partitioned table separately (er_doc_to_user_state_1_3, er_doc_to_user_state_2_4_9).  
              ALTER TABLE xx.er_doc_workflow_action
                   ADD CONSTRAINT fk_doc_work_act_1_3 FOREIGN KEY (fk_to_user_doc_rel)
                  REFERENCES xx.er_doc_to_user_state_1_3(oid) MATCH SIMPLE
                 ON UPDATE NO ACTION
                 ON DELETE NO ACTION;
             Following error occurred:
                  ERROR: insert or update on table "er_doc_workflow_action" violates foreign key constraint "fk_doc_work_act_1_3"
DETAIL: Key (fk_to_user_doc_rel)=(3hjbzok1mn100g) is not present in table "er_doc_to_user_state_1_3". SQL state: 23503

I think this error is normal because oid which is referenced is in other partitioned table so it can't validate all data.
  If I try to create foreign key constraint on second partition again same error will be occurred due to same reason.

  Note: I want to create constraint only one-to-one column (fk_to_user_doc_rel - oid)

BIG QUESTION IS THAT 

How can I solve this problem?  What is your recommendations? 

PLEASE HELP ME !!!

-- 
Best Regards,
Mehman Jafarov
DBA Aministrator at CyberNet LLC


[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