Search Postgresql Archives

Re: Disabling inheritance with query.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux