Sorry the full message is this
I want to do that because, I have a partitioned table (big_table like master and child like ...t201610,t201611,t201612...) and others t1,t2,t3,t4 have foreign keys reference to big_table and i had many trobules at insert data, reading the doc:
"A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. "
So i have two choices:
1. The obvious and boring is create triggers between tables to simulate foreign key behavior. but each insert in T1..T4 must read the whole data in child tables thinking on 20 million of records and growing each minute. So this option maybe is not the best choice.
2. Another is create child tables for t1..t4 like t1_201610,t2_201611,... t4_201612,.... and create the foreign keys referencing the right table. that sounds better for t1,t3 and t4 those tables are historical no problem with that, but t2 must be have the last information.
if i do a query on t2 i will have many records from child tables of t2 instead the last record. Yes, i could write a query to get the last record, but that query is hardcoded inside a program, and i don't have the code :( ,
The creation of child_tables must be automatic at insert data , so when the new child is created i must disable the inherits for all childs of t2 and set the inhertis to the new t2_child
On Thu, Dec 22, 2016 at 8:51 AM, Edmundo Robles <edmundo@xxxxxxxxxxxx> wrote:
I want to do that because, I have a partitioned table (big_table) and others (t1,t2,t3,t4) have foreign keys reference to big_table and i had many trobules at insert data, reading the doc:"A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. "On Wed, Dec 21, 2016 at 4:58 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:Edmundo Robles <edmundo@xxxxxxxxxxxx> writes:
> i need disable inheritance from many tables in a query like
> "delete from pg_inherits where inhparent=20473" instead alter table ...
> but is safe? which is the risk for database if i delete it?
This seems really dangerous. You're certainly missing the pg_depend
linkages, not to mention attribute inheritance counts in pg_attribute,
and there may be other things I'm not remembering offhand.
Why can't you use the normal ALTER TABLE approach?
regards, tom lane
