PostgreSQL Discussion - Re: How to add an INHERITS to an already populated table

Author

Re: How to add an INHERITS to an already populated table

Richard Huxton

2005-05-31, 7:23 am

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 majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Oleg Bartunov

2005-05-31, 7:23 am

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:
>
> 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

Oleg Bartunov

2005-05-31, 9:23 am

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,1576
9044,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,1576
9044,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:
>
>
> 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 majordomo@postgresql
.org)

David Pradier

2005-05-31, 9:23 am

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

---------------------------(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

Alvaro Herrera

2005-05-31, 9:23 am

On Tue, May 31, 2005 at 04:53:46PM +0400, Oleg Bartunov wrote:
> On Tue, 31 May 2005, Oleg Bartunov wrote:
>
>
> 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,1576
9044,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,1576
9044,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

David Pradier

2005-05-31, 9:23 am

> 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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Sponsored Links