Re: ADD/DROP INHERITS - Mailing list pgsql-hackers

Hannu Krosing <hannu@skype.net> writes:

> Do you mean that in newer versions ALTER TABLE ADD COLUMN will change
> existing data without asking me ?
> 
> That would be evil!
> 
> Even worse if ALTER TABLE ALTER COLUMN SET DEFAULT would do the same.

postgres=# alter table test add b integer default 1;
ALTER TABLE
postgres=# select * from test;a | b 
---+---0 | 1
(1 row)

> > 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.
> 
> I would be *really*, *really*, *really* annoyed if an op that I expected
> to take less than 1 sec takes 5 hours and then forces me to spend
> another 10 hours on VACUUM FULL+REINDEX or CLUSTER to get performance
> back.

I forget whether the developer managed to get it working without doing any
table rewriting. In theory the table just needs to know that records that are
"missing" that column in the null bitmap should behave as if they have the
default value. But I seem to recall some headaches with that approach.

> > 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.
> 
> If DROP partition keeps defaults, and ADD does not change them then DROP
> +ADD is a NOOP.
>
> > 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.

It does make DROP+ADD a noop which is why I'm suggesting it. I'm just noting
that it makes a second reason why:

CREATE TABLE foo (a integer) INHERITS (bar);

and:

CREATE TABLE foo (a integer);
ALTER TABLE foo INHERIT bar;

are not equivalent. Since in the first case a will acquire any defaults on a
from bar whereas in the second case it will remain with a default of NULL.


> constraints should match, that is a child table should already have all
> the constraints of parent, but may have more.

Well even that is a problem. You can drop an inherited constraint from a
child. So this would mean you wouldn't be able to re-add that partition back.

Come to think of it it's pretty strange that you can drop an inherited
constraint from a child. And doing an experiment it seems you can also DROP
NOT NULL on a child which is also pretty strange.

I don't see how to block these operations though unless we either search
parent classes for constraints to check at run-time or add additional
dependency records to block dropping these things.



> > 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.
> 
> nono. the ADD/DROP INHERITS should not do any data checking, just
> comparison of metainfo. the partitions could be huge and having to check
> data inside them would negate most of the usefullness for ADD/DROP
> INHERITS.

I agree that it's important to be possible to add/drop partitions in constant
time. That's the whole advantage of partitioned tables. However it might be
reasonable to support *additional* operations that aren't necessary for
partitioned tables but make sense for other applications even if these
operations are more expensive.

But it seems the priority right now is clearly on partitioned tables and these
other operations are for another day.

-- 
greg