Re: Disabling inheritance with query.

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(at)sw-argos(dot)com>
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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Edmundo Robles <edmundo(at)sw-argos(dot)com> 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
>>
>
>