Re: ADD/DROP INHERITS

Ühel kenal päeval, K, 2006-06-07 kell 15:33, kirjutas Greg Stark:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>
> > I thought we had agreed that the semantics of ADD INHERITS would be to
> > reject the command if the child wasn't already suitable to be a child
> > of the parent. Not to modify it by adding columns or constraints or
> > whatever. For the proposed uses of ADD INHERITS (in particular,
> > linking and unlinking partition tables) an incompatibility in schema
> > almost certainly means you made a mistake, and you don't really want
> > the system helpfully "fixing" your table to match the parent.
>
> I didn't see any discussion like that and I find it pretty surprising.

I'm pretty sure that what was discussed was just attaching/detaching
child tables into inheritance chains with no table alterations.

Maybe it was never mentioned explicitly, but that was how I understood
the discussion.

> Personally I would have agreed. For partitioned tables you certainly don't
> want it to create new columns without warning you.

Exactly!

> But that's entirely inconsistent with the way inherited tables work in
> general. It seems to go against the grain of Postgres's general style to
> implement just the use case that's useful for a particular application rather
> than keep the features logically consistent with each other.

There are too many conflicting definitions of "logically consistent", so
doing the bare minimum is the best way to avoid the whole problem.

> Perhaps there should be an option when issuing the ADD INHERITS to indicate
> whether you want it to create new columns or only match existing columns. That
> would also give me a convenient excuse to skip all those NOTICEs about merging
> column definitions.

nonono! the whole pg inheritance/partitioning thing is still quite
low-level and ADD/DEL INHERITS is the wrong place to start fixing it.

> Actually I think in the long term for partitioned tables Postgres will have to
> implement a special syntax just like Oracle and other databases. The user
> doesn't really want to have to manually manage all the partitions as tables.
> That imposes a lot of extra work to have to define the tables with the right
> syntax, maintain the constraints properly, etc.

Yes. Maybe. But this is something that requires much more thought and
planning than adding the simplest possible ADD/DELETE INHERITS.

> For the user it would be better to have a single property of the partitioned
> table that specified the partition key. Then when adding a partition you would
> only have to specify the key range it covers, not write an arbitrary
> constraint from scratch. Nor would you have to create an empty table with the
> proper definition first then add it in.

Don't try to solve too many problems at once. Starting with just a
possibility to move suitable ready-made partitions in and out of
inheritance chain solves a really big problem. No need to try to
obfuscate it with extra functionality, at least not initially.

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

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