Thread: [GENERAL] Disabling inheritance with query.

[GENERAL] Disabling inheritance with query.

From
Edmundo Robles
Date:
Hi! 

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?

Re: [GENERAL] Disabling inheritance with query.

From
Tom Lane
Date:
Edmundo Robles <edmundo@sw-argos.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


Re: [GENERAL] Disabling inheritance with query.

From
Scott Marlowe
Date:
On Wed, Dec 21, 2016 at 3:36 PM, Edmundo Robles <edmundo@sw-argos.com> wrote:
> Hi!
>
> 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?

You could change the source query to use the only keyword:

delete from only parenttable where ...

OR you could write a rule or trigger that rewrote the query to have
the only keyword in it under certain circumstances.


Re: [GENERAL] Disabling inheritance with query.

From
Francisco Olarte
Date:
Edmundo:

On Wed, Dec 21, 2016 at 11:36 PM, Edmundo Robles <edmundo@sw-argos.com> wrote:
> 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?

Dangers of touching the catalog directly have already been pointed by
TL, along with the question of why isn't normal ALTER TABLE ok.

If it is because there are a lot of childs, I would like to point a
simple script ( if you are fluent in any scripting language, or even
in SQL ) can be used to automatically generate a bunch of alter table
commands. Even a simple text editor will do ( turn your query above
into something generating a bunch of table names, edit it ). Or just
try something like ( beware, untested )

with childs as (select relname from pg_class, pg_inherits where
pg_class.oid=inhrelid and inhparent='20473)
SELECT 'ALTER TABLE ' || relname || ' rest of alter table command;'
from childs ;

And feed the result back to the server using your favorite tool (
quoting maybe needed, schema names may be needed, YMMV ).

Francisco Olarte.


Re: [GENERAL] Disabling inheritance with query.

From
Jaime Soler
Date:
You should use alter table XX NO INHERIT parent_table;


2016-12-22 9:49 GMT+01:00 Francisco Olarte <folarte@peoplecall.com>:
Edmundo:

On Wed, Dec 21, 2016 at 11:36 PM, Edmundo Robles <edmundo@sw-argos.com> wrote:
> 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?

Dangers of touching the catalog directly have already been pointed by
TL, along with the question of why isn't normal ALTER TABLE ok.

If it is because there are a lot of childs, I would like to point a
simple script ( if you are fluent in any scripting language, or even
in SQL ) can be used to automatically generate a bunch of alter table
commands. Even a simple text editor will do ( turn your query above
into something generating a bunch of table names, edit it ). Or just
try something like ( beware, untested )

with childs as (select relname from pg_class, pg_inherits where
pg_class.oid=inhrelid and inhparent='20473)
SELECT 'ALTER TABLE ' || relname || ' rest of alter table command;'
from childs ;

And feed the result back to the server using your favorite tool (
quoting maybe needed, schema names may be needed, YMMV ).

Francisco Olarte.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Disabling inheritance with query.

From
Edmundo Robles
Date:
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@sss.pgh.pa.us> wrote:
Edmundo Robles <edmundo@sw-argos.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

Re: [GENERAL] Disabling inheritance with query.

From
Scott Mead
Date:


On Thu, Dec 22, 2016 at 9:51 AM, Edmundo Robles <edmundo@sw-argos.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@sss.pgh.pa.us> wrote:
Edmundo Robles <edmundo@sw-argos.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?

If you need to do it from many tables, you could write a script to generate the ALTER TABLE statements....

select 'ALTER TABLE ' || schemaname ||'.' || psut.relname || ' NO INHERIT ' || pc.relname ||';'
  from pg_stat_user_tables psut, pg_class pc, pg_inherits pi
 where pi.inhrelid = psut.relid
   AND pi.inhparent = pc.oid
   AND pi.inhparent = 20473;

I wouldn't manually hit the catalogs, but, this will write all of the ALTER TABLE statements that you need.
 

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




--
--
Scott Mead
Sr. Architect
OpenSCG

Re: [GENERAL] Disabling inheritance with query.

From
Edmundo Robles
Date:
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@sw-argos.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@sss.pgh.pa.us> wrote:
Edmundo Robles <edmundo@sw-argos.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