Re: ADD/DROP INHERITS - Mailing list pgsql-hackers
From | Hannu Krosing |
---|---|
Subject | Re: ADD/DROP INHERITS |
Date | |
Msg-id | 1149774753.4537.36.camel@localhost.localdomain Whole thread Raw |
In response to | Re: ADD/DROP INHERITS (Greg Stark <gsstark@mit.edu>) |
Responses |
Re: ADD/DROP INHERITS
|
List | pgsql-hackers |
Ühel kenal päeval, N, 2006-06-08 kell 09:32, kirjutas Greg Stark: > "Zeugswetter Andreas DCP SD" <ZeugswetterA@spardat.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.
pgsql-hackers by date: