[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 -- [EMAIL PROTECTED] - 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
Re: [GENERAL] How to add an INHERITS to an already populated table
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How to add an INHERITS to an already populated table ?
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 [EMAIL PROTECTED] 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 -- [EMAIL PROTECTED] - 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] How to add an INHERITS to an already populated table
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to add an INHERITS to an already populated table
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] How to add an INHERITS to an already populated table
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) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] How to add an INHERITS to an already populated table
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 -- [EMAIL PROTECTED] - tel: 01.46.47.21.33 - fax: 01.46.47.21.37 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] How to add an INHERITS to an already populated table
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 -- [EMAIL PROTECTED] - tel: 01.46.47.21.33 - fax: 01.46.47.21.37 ---(end of broadcast)--- TIP 8: explain analyze is your friend
