Thread: How to add an INHERITS to an already populated table ?
Hi everybody, is it possible to add some inheritance lively, without doing a dump/restore ? Some bits of information to explain why I'd like to do that : I've got those big tables, without correct constraints, sometimes even without foreign keys et with sometimes some problems of data corruption, coming from the application part. What I'd really like to do is to add to every important table some inheritance to a table we have which contains the following information : (created_by, modified_by, date_of_creation, date_of_modification). And of course, I can't stop the server. Best regards, David -- dpradier@apartia.fr - tel: 01.46.47.21.33 - fax: 01.46.47.21.37
David Pradier wrote: > Hi everybody, > > is it possible to add some inheritance lively, without doing a > dump/restore ? Not AFAIK. Easiest solution is probably to script some ALTER TABLE ADD COLUMN commands. -- Richard Huxton Archonet Ltd
Hi. Recently I have tried to do the same thing and I coudn't include inheritence in existing tables. After a half of day of frustration, I have got an idea. I have successfully done it by using EMS PostgreSQL Manager Lite (you can download it from the net). There is an option "Duplicate" in EMS Manager, by which you can duplicate any table with all properties except foreign keys. During that process you can modify code (before Commit) so you can include inheritence in your new table. Then you delete original table, rename new table to old name and recreate foreign key... Well, I'm a newbie, so maybe someone more experienced offer you some better advice. If not, this will work... Bye. ----- Original Message ----- From: "David Pradier" <dpradier@apartia.fr> To: <pgsql-general@postgresql.org> Sent: Tuesday, May 31, 2005 10:10 AM Subject: [GENERAL] How to add an INHERITS to an already populated table ? > Hi everybody, > > is it possible to add some inheritance lively, without doing a > dump/restore ? > > Some bits of information to explain why I'd like to do that : > I've got those big tables, without correct constraints, sometimes even > without foreign keys et with sometimes some problems of data corruption, > coming from the application part. > What I'd really like to do is to add to every important table some > inheritance to a table we have which contains the following information : > (created_by, modified_by, date_of_creation, date_of_modification). > And of course, I can't stop the server. > > Best regards, > David > > -- > dpradier@apartia.fr - tel: 01.46.47.21.33 - fax: 01.46.47.21.37 > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
look on pg_inherits table and pg_class.relhassubclass. More info http://www.pgsql.ru/db/mw/msg.html?mid=2044343 On Tue, 31 May 2005, Richard Huxton wrote: > David Pradier wrote: >> Hi everybody, >> >> is it possible to add some inheritance lively, without doing a >> dump/restore ? > > Not AFAIK. Easiest solution is probably to script some ALTER TABLE ADD COLUMN > commands. > > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
On Tue, 31 May 2005, Oleg Bartunov wrote: > look on pg_inherits table and pg_class.relhassubclass. > More info http://www.pgsql.ru/db/mw/msg.html?mid=2044343 > example: create table t (i int4); create table t1 (i int4); create table t2 (i int4); -- mark 't' has children tables update pg_class set relhassubclass='t' where relname='t'; -- get oid of child table 't1' select relfilenode from pg_class where relname='t1'; -- get oid of parent table select relfilenode from pg_class where relname='t'; -- add inheritance t-t1 insert into pg_inherits values(15769046,15769044,1); -- get oid of child table 't2' select relfilenode from pg_class where relname='t2'; -- add inheritance t-t2 insert into pg_inherits values(15769048,15769044,1); --test test=# explain analyze select * from t; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Result (cost=0.00..88.20 rows=5820 width=8) (actual time=0.057..0.057 rows=0 loops=1) -> Append (cost=0.00..88.20 rows=5820 width=8) (actual time=0.044..0.044 rows=0 loops=1) -> Seq Scan on t (cost=0.00..29.40 rows=1940 width=8) (actual time=0.008..0.008 rows=0 loops=1) -> Seq Scan on t1 t (cost=0.00..29.40 rows=1940 width=8) (actual time=0.007..0.007 rows=0 loops=1) -> Seq Scan on t2 t (cost=0.00..29.40 rows=1940 width=8) (actual time=0.006..0.006 rows=0 loops=1) Total runtime: 0.171 ms (6 rows) -- check if alter table works alter table t add column x real; test=# \d t Table "public.t" Column | Type | Modifiers --------+---------+----------- i | integer | x | real | test=# \d t1 Table "public.t1" Column | Type | Modifiers --------+---------+----------- i | integer | x | real | Inherits: t test=# \d t2 Table "public.t2" Column | Type | Modifiers --------+---------+----------- i | integer | x | real | Inherits: t > > On Tue, 31 May 2005, Richard Huxton wrote: > >> David Pradier wrote: >>> Hi everybody, >>> >>> is it possible to add some inheritance lively, without doing a >>> dump/restore ? >> >> Not AFAIK. Easiest solution is probably to script some ALTER TABLE ADD >> COLUMN commands. >> >> > > Regards, > Oleg > _____________________________________________________________ > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > Sternberg Astronomical Institute, Moscow University (Russia) > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(095)939-16-83, +007(095)939-23-83 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Well, it seems very promising ! I think I'll make some tests and do it your way asap. Thanks a lot ! Thanks to everybody else, too. Best regards, David -- dpradier@apartia.fr - tel: 01.46.47.21.33 - fax: 01.46.47.21.37
On Tue, May 31, 2005 at 04:53:46PM +0400, Oleg Bartunov wrote: > On Tue, 31 May 2005, Oleg Bartunov wrote: > > >look on pg_inherits table and pg_class.relhassubclass. > >More info http://www.pgsql.ru/db/mw/msg.html?mid=2044343 > > > > example: > > create table t (i int4); > create table t1 (i int4); > create table t2 (i int4); > > -- mark 't' has children tables > update pg_class set relhassubclass='t' where relname='t'; > -- get oid of child table 't1' > select relfilenode from pg_class where relname='t1'; > -- get oid of parent table > select relfilenode from pg_class where relname='t'; > -- add inheritance t-t1 > insert into pg_inherits values(15769046,15769044,1); > -- get oid of child table 't2' > select relfilenode from pg_class where relname='t2'; > -- add inheritance t-t2 > insert into pg_inherits values(15769048,15769044,1); Please note that the inheritance is not fully set -- if you discover strange behavior e.g. when altering any of the tables, don't be surprised. In particular, you should set the attislocal and attinhcount attributes in pg_attribute for the child tables; also pg_depend entries are missing. I don't know what else. Also you definitely shouldn't be using relfilenode, but the real Oid of the table (relfilenode is the filename only, not the internal identifier of the table). -- Alvaro Herrera (<alvherre[a]surnet.cl>) Essentially, you're proposing Kevlar shoes as a solution for the problem that you want to walk around carrying a loaded gun aimed at your foot. (Tom Lane)
> Please note that the inheritance is not fully set -- if you discover > strange behavior e.g. when altering any of the tables, don't be > surprised. In particular, you should set the attislocal and attinhcount > attributes in pg_attribute for the child tables; also pg_depend entries > are missing. I don't know what else. > > Also you definitely shouldn't be using relfilenode, but the real Oid of > the table (relfilenode is the filename only, not the internal identifier > of the table). I think I'll make a script and propose it for revision to the list before using it on any real database. David -- dpradier@apartia.fr - tel: 01.46.47.21.33 - fax: 01.46.47.21.37