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 KEY, FOREIGN 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
