Re: Disabling inheritance with query.

On Thu, Dec 22, 2016 at 9:51 AM, Edmundo Robles <edmundo(at)sw-argos(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Edmundo Robles <edmundo(at)sw-argos(dot)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>*
http://openscg.com