Re: [GENERAL] Disabling inheritance with query.

[Edmundo Robles]

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 
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  wrote:
>
>> Edmundo Robles  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.

[Scott Mead]

On Thu, Dec 22, 2016 at 9:51 AM, Edmundo Robles 
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  wrote:
>
>> Edmundo Robles  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 *
http://openscg.com

Re: [GENERAL] Disabling inheritance with query.

[Edmundo Robles]

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  wrote:

> Edmundo Robles  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.

[Jaime Soler]

You should use alter table XX NO INHERIT parent_table;


2016-12-22 9:49 GMT+01:00 Francisco Olarte :

> Edmundo:
>
> On Wed, Dec 21, 2016 at 11:36 PM, Edmundo Robles 
> 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.

[Francisco Olarte]

Edmundo:

On Wed, Dec 21, 2016 at 11:36 PM, Edmundo Robles  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.

[Scott Marlowe]

On Wed, Dec 21, 2016 at 3:36 PM, Edmundo Robles  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.


-- 
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.

[Tom Lane]

Edmundo Robles  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


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