Disabling inheritance with query.

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

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

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.

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

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

You should use alter table XX NO INHERIT parent_table;

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

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:

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 <http://openscg.com&gt;*
http://openscg.com

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: