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*
