Thread: [GENERAL] Disabling inheritance with query.
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?
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
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.
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.
- 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
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
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
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