Re: ADD/DROP INHERITS

Ühel kenal päeval, N, 2006-06-08 kell 09:32, kirjutas Greg Stark:
> "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.

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.

Soon we will be as bad as MS Word !

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

And making such radical changes even between major versions should be
stricty forbidden.

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

sure. it should not magically appear.

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

exactly. that would be the correct behaviour. even for NULL default.

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

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

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

--
----------------
Hannu Krosing
Database Architect
Skype Techshould benologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com

NOTICE: This communication contains privileged or other confidential
information. If you have received it in error, please advise the sender
by reply email and immediately delete the message and any attachments
without copying or disclosing the contents.