Thread: [GENERAL] inheritence children with integer columns of differing width

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

Justin Pryzby <pryzby@telsasoft.com> writes:
> I wondered if anyone had considered allowing inheritence children to have
> different column types than the parent (and each other).

No, and it's not sane to consider it.

> I'm thinking of the trivial (?) case of smallint/int/bigint.

What's trivial about that?  Consider

       select * from parent_table where somefunc(intcol);

If somefunc takes integer, this is fine, but what are we supposed to do
if the corresponding column in the child is bigint?  Throw error for any
row where the value doesn't fit in int?  If so, what's the point of
letting the child column type be different?

            regards, tom lane