I wondered if anyone had considered allowing inheritence children to have
different column types than the parent (and each other).
I'm thinking of the trivial (?) case of smallint/int/bigint. Reason is that
when we load data which exceeds the theshold for the current data type we have
to promote the column, rewriting the table, which can take a very long time,
and use very large amount of space. We've had to start uninheriting all but
the most recent children before ALTERing to make it more reasonable (and then
separately ALTER+reinherit each child) - it's especially painful when a key
column grows beyond "int", and many tables need to be altered all at once..
It seems to me this is what would happen if one were to UNION ALL the children,
although I see the plan differs with differering type:
pryzbyj=# create table ii(i bigint);
pryzbyj=# create table i(i int);
pryzbyj=# explain SELECT * FROM ii UNION ALL SELECT * FROM i;
Append (cost=0.00..110.80 rows=4540 width=6)
-> Seq Scan on ii (cost=0.00..31.40 rows=2140 width=8)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..58.00 rows=2400 width=4)
-> Seq Scan on i (cost=0.00..34.00 rows=2400 width=4)
pryzbyj=# alter table i ALTER i TYPE bigint;
ALTER TABLE
pryzbyj=# explain SELECT * FROM ii UNION ALL SELECT * FROM i;
Append (cost=0.00..62.80 rows=4280 width=8)
-> Seq Scan on ii (cost=0.00..31.40 rows=2140 width=8)
-> Seq Scan on i (cost=0.00..31.40 rows=2140 width=8)
If it were allowed for children to have int columns with differing widths, then
to promote int column, we would uninherit the historic children, ALTER the
parent (and most recent tables), and then reinherit the children (unless ALTER
on its own avoided rewriting tables in such a case).
Justin
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
