Re: ADD/DROP INHERITS

"Zeugswetter Andreas DCP SD" <ZeugswetterA(at)spardat(dot)at> writes:

> Imho the op should only choose that path if he wants to fill the table
> before adding the inheritance. It makes no sense to add columns with default
> values to existing rows of the child table, especially when you inherit the
> defaults from the parent.

We already have ALTER TABLE ADD COLUMN working for columns with defaults, so I
think that horse has left the barn. It was awfully annoying for users when
that feature was missing. Any non-linearities in the user interface like this
end up being surprises and annoyances for users.

In any case there's a separate problem with defaults. We want to guarantee
that you can DROP a partition and then re-ADD it and the result should be a
noop at least from the user's perspective. We can't do that unless I
compromise on my idea that adding a child after the fact should be equivalent
to creating it with the parent in the definition.

When creating a table with the parent in the definition CREATE TABLE will copy
the parent's default if the default in the child is NULL:

postgres=# create table b (i integer default null) inherits (a);
NOTICE: merging column "i" with inherited definition
CREATE TABLE
postgres=# \d b
Table "public.b"
Column | Type | Modifiers
--------+---------+-----------
i | integer | default 2
Inherits: a

The problem is that it's possible to fiddle with the defaults after the table
is created, including dropping a default. If you drop the default and then
DROP-ADD the partition it would be a problem if the default magically
reappeared.

The only way to allow DROP then ADD to be a noop would be to accept whatever
the DEFAULT is on the child table without complaint. And I'm not just saying
that because it's the easiest for me to implement :)

This is already a factor for NOT NULL constraints too. When adding a parent
after the fact your NULLable column can magically become NOT NULL if the
parent is NOT NULL. But for adding a partition after the fact we can't just
change the column to NOT NULL because there may already be NULL rows in the
table.

We could do a pass-3 check for the NOT NULL constraint but if we're not doing
other schema changes then it makes more sense to just refuse to add such a
table.

--
greg