Problem about partitioned table

From: Mehman Jafarov <mehmanjafarov84(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Cc: pgsql-hackers(at)postgresql(dot)org, support(at)enterprisedb(dot)com, de(at)postgresql(dot)org, contact(at)postgresql(dot)tw, info(at)itpug(dot)org, webmaster(at)itpug(dot)org, majordomo(at)postgresql(dot)org, info(at)postgres(dot)de
Subject: Problem about partitioned table
Date: 2018-10-19 09:03:12
Message-ID: CAMZYErRSQd8jwp2EOFC12_avknpAUm32Rp1qSJcfHe7Ri42aWw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-general pgsql-hackers

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*

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Anthony Bykov 2018-10-19 09:39:56 Re: Optimze usage of immutable functions as relation
Previous Message Kyotaro HORIGUCHI 2018-10-19 08:34:57 Re: Protect syscache from bloating with negative cache entries

Browse pgsql-general by date

  From Date Subject
Next Message ROS Didier 2018-10-19 10:22:05 PostgreSQL 11 and security
Previous Message Clemens Ladisch 2018-10-19 07:04:31 Re: What is the problem with this code?