Обсуждение: Re: error-free disabling of individual child partition
> > table of another table. I propose a TODO item to allow this: > > > > ALTER TABLE childN INHERITS ( parent1, ... ); > > We don't need a disinherit do we? I propose: ALTER TABLE childN INHERITS (); Thus I also think, that the list should be complete, and is not an addition to existing inheritance. > > O, yes, I think we do. I can imagine that the ability to swap a table Agreed. Simon, were you testing how many ppl read to the end :-) Andreas
On Tue, 2006-05-23 at 09:49 +0200, Zeugswetter Andreas DCP SD wrote: > > > table of another table. I propose a TODO item to allow this: > > > > > > ALTER TABLE childN INHERITS ( parent1, ... ); > > > > We don't need a disinherit do we? > > I propose: ALTER TABLE childN INHERITS (); > Thus I also think, that the list should be complete, and is not an > addition > to existing inheritance. Sounds good; an absolute rather than a relative approach. Avoids new keywords. Implementation is simpler too: - check that we have all required merged attributes (if any) - remove any inheritance that isn't on the list If the table is already INHERITS (x) and we specify INHERITS (x) then its a no-op that returns success. > > O, yes, I think we do. I can imagine that the ability to swap a table > > Agreed. Simon, were you testing how many ppl read to the end :-) Just seeing who cared. DROP TABLE removes inheritance too, in a Keyser Soze manner. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Ühel kenal päeval, T, 2006-05-23 kell 10:51, kirjutas Simon Riggs: > On Tue, 2006-05-23 at 09:49 +0200, Zeugswetter Andreas DCP SD wrote: > > > > table of another table. I propose a TODO item to allow this: > > > > > > > > ALTER TABLE childN INHERITS ( parent1, ... ); > > > > > > We don't need a disinherit do we? > > > > I propose: ALTER TABLE childN INHERITS (); > > Thus I also think, that the list should be complete, and is not an > > addition > > to existing inheritance. > > Sounds good; an absolute rather than a relative approach. Avoids new > keywords. And also allows you move a partition from live to archive table in one command. Brilliant :) > Implementation is simpler too: > - check that we have all required merged attributes (if any) > - remove any inheritance that isn't on the list > > If the table is already INHERITS (x) and we specify INHERITS (x) then > its a no-op that returns success. > > > > O, yes, I think we do. I can imagine that the ability to swap a table > > > > Agreed. Simon, were you testing how many ppl read to the end :-) > > Just seeing who cared. DROP TABLE removes inheritance too, in a Keyser > Soze manner. Just fyi - I care too . -- ---------------- 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
"Zeugswetter Andreas DCP SD" <ZeugswetterA@spardat.at> writes:
>>> We don't need a disinherit do we?
> I propose: ALTER TABLE childN INHERITS ();
> Thus I also think, that the list should be complete, and is not an
> addition to existing inheritance.
Don't like that at all: it seems far too error-prone.
regards, tom lane
Ühel kenal päeval, T, 2006-05-23 kell 09:37, kirjutas Tom Lane: > "Zeugswetter Andreas DCP SD" <ZeugswetterA@spardat.at> writes: > >>> We don't need a disinherit do we? > > > I propose: ALTER TABLE childN INHERITS (); > > Thus I also think, that the list should be complete, and is not an > > addition to existing inheritance. > > Don't like that at all: it seems far too error-prone. In what way ? Do you mean that it will be easy for the user to make errors, od do yuo think that it will be hard to implement in a robust way ? In the first case, I'd propose following syntax ALTER TABLE childN ALTER INHERITS DROP (parent); ALTER TABLE childN ALTER INHERITS ADD (parent); With this syntax reparenting would need an explicit transaction and two "ALTER TABLE ... ALTER INHERITS ..." commands, but it is (arguably) harder to make mistakes. -- ---------------- 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
Hannu Krosing <hannu@skype.net> writes:
> Ühel kenal päeval, T, 2006-05-23 kell 09:37, kirjutas Tom Lane:
>>> I propose: ALTER TABLE childN INHERITS ();
>>> Thus I also think, that the list should be complete, and is not an
>>> addition to existing inheritance.
>>
>> Don't like that at all: it seems far too error-prone.
> In what way ?
It seems like it'd be awfully easy to unintentionally disinherit a child
table from a parent.
In a situation where you're only using single inheritance, it hardly
matters of course, but for multiple inheritance it just seems like a
way to shoot yourself in the foot. ISTM it'd be safer to have an
explicit disinherit-from-this-parent operation.
> In the first case, I'd propose following syntax
> ALTER TABLE childN ALTER INHERITS DROP (parent);
> ALTER TABLE childN ALTER INHERITS ADD (parent);
I could live with that. Do we need the parens?
regards, tom lane
On Tue, 2006-05-23 at 09:37 -0400, Tom Lane wrote: > "Zeugswetter Andreas DCP SD" <ZeugswetterA@spardat.at> writes: > >>> We don't need a disinherit do we? > > > I propose: ALTER TABLE childN INHERITS (); > > Thus I also think, that the list should be complete, and is not an > > addition to existing inheritance. > > Don't like that at all: it seems far too error-prone. What types of error do you think its prone to? Can you say what you would prefer? As ever, not that worried about syntax, but I would like to get agreement on a specific way forward now we're discussing this. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
> ALTER TABLE childN ALTER INHERITS DROP (parent); > ALTER TABLE childN ALTER INHERITS ADD (parent); Wouldn't it be possible to allow the ADD/DROP to happen in the same statement, like: ALTER TABLE childN ALTER INHERITS DROP crt_parent ADD new_parent; or: ALTER TABLE childN ALTER INHERITS DROP crt_parent, ALTER INHERITS ADD new_parent; That would still make it one statement, but more explicit. And it would eliminate the need for parenthesis (I assume they were needed for supplying more than 1 table to inherit/disinherit). Cheers, Csaba.
On Tue, 2006-05-23 at 16:29 +0200, Csaba Nagy wrote: > > ALTER TABLE childN ALTER INHERITS DROP (parent); > > ALTER TABLE childN ALTER INHERITS ADD (parent); > > Wouldn't it be possible to allow the ADD/DROP to happen in the same > statement, like: > > ALTER TABLE childN ALTER INHERITS DROP crt_parent ADD new_parent; > > or: > > ALTER TABLE childN ALTER INHERITS DROP crt_parent, ALTER INHERITS ADD > new_parent; > > That would still make it one statement, but more explicit. And it would > eliminate the need for parenthesis (I assume they were needed for > supplying more than 1 table to inherit/disinherit). Sounds good. Do we need the ALTER keyword? That isn't used anywhere apart from manipulating columns. i.e. ALTER TABLE childN INHERITS DROP old_parent; ALTER TABLE childN INHERITS ADD new_parent; -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Ühel kenal päeval, T, 2006-05-23 kell 15:59, kirjutas Simon Riggs: > On Tue, 2006-05-23 at 16:29 +0200, Csaba Nagy wrote: > > > ALTER TABLE childN ALTER INHERITS DROP (parent); > > > ALTER TABLE childN ALTER INHERITS ADD (parent); > > > > Wouldn't it be possible to allow the ADD/DROP to happen in the same > > statement, like: > > > > ALTER TABLE childN ALTER INHERITS DROP crt_parent ADD new_parent; > > > > or: > > > > ALTER TABLE childN ALTER INHERITS DROP crt_parent, ALTER INHERITS ADD > > new_parent; > > > > That would still make it one statement, but more explicit. And it would > > eliminate the need for parenthesis (I assume they were needed for > > supplying more than 1 table to inherit/disinherit). > > Sounds good. > > Do we need the ALTER keyword? Probably not. > That isn't used anywhere apart from > manipulating columns. i.e. > > ALTER TABLE childN INHERITS DROP old_parent; > ALTER TABLE childN INHERITS ADD new_parent; For me "DROP INHERITS oldtable" sounds better than "INHERITS DROP oldtable" , but it may be just me :) -- ---------------- 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 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.
On Tue, 2006-05-23 at 18:19 +0300, Hannu Krosing wrote: > For me "DROP INHERITS oldtable" sounds better than "INHERITS DROP > oldtable" , but it may be just me :) Agreed, so proposal is now ALTER TABLE childN DROP INHERITS old_parent; ALTER TABLE childN ADD INHERITS new_parent; Going once; going twice... -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Simon Riggs <simon@2ndquadrant.com> writes:
> Do we need the ALTER keyword? That isn't used anywhere apart from
> manipulating columns. i.e.
> ALTER TABLE childN INHERITS DROP old_parent;
> ALTER TABLE childN INHERITS ADD new_parent;
At that point it seems like it'd read more naturally the other way
round:
ALTER TABLE childN DROP INHERITS old_parent;
ALTER TABLE childN ADD INHERITS new_parent;
although I'm not sure if this would create a parser conflict against
ADD/DROP COLUMN.
regards, tom lane
On Tue, 2006-05-23 at 11:31 -0400, Tom Lane wrote: > At that point it seems like it'd read more naturally the other way > round: > > ALTER TABLE childN DROP INHERITS old_parent; > ALTER TABLE childN ADD INHERITS new_parent; > > although I'm not sure if this would create a parser conflict against > ADD/DROP COLUMN. Behaviour would be: - If you DROP INHERITS this simply removes the link to the parent. All existing columns, constraints etc are retained. You can DROP inheritance on a table that is itself a parent; its children are unaffected. - If you ADD INHERITS this will fail if it would do the equivalent of possibly multiple ADD COLUMNs. You can ADD inheritance onto a table that is itself a parent; its children are unaffected. - The table data is not scanned at all for either ADD or DROP INHERITS - You cannot ADD INHERITS if the table being added as parent is already one of the inheritance set of the target table (i.e. no loops) - ADD/DROP are opposites; you can use the other one to undo an action taken in haste, error etc - Once DROP INHERITS has committed no changes are propagated down from parent to former child. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Simon Riggs <simon@2ndquadrant.com> writes:
> - ADD/DROP are opposites; you can use the other one to undo an action
> taken in haste, error etc
It's not going to be that easy. What exactly will happen to the child
table's attislocal/attinhcount settings, and why, during ADD or DROP?
regards, tom lane
On Tue, 2006-05-23 at 13:17 -0400, Tom Lane wrote: > Simon Riggs <simon@2ndquadrant.com> writes: > > - ADD/DROP are opposites; you can use the other one to undo an action > > taken in haste, error etc > > It's not going to be that easy. What exactly will happen to the child > table's attislocal/attinhcount settings, and why, during ADD or DROP? Never is round here ;-) attislocal: If you set this to False, you wouldn't be able to set it back again. If you leave it as it is, you'd never be able to recursively drop a column. If you change it, you'll never be able to stop someone from dropping a previously defined local column. Proposal: 1. attislocal is not touched. That means if you want to create a new partition you do this: CREATE TABLE newChild () INHERITS (template); ... do some processing ... ALTER TABLE newChild ADD INHERITS parent; or this: CREATE TABLE newChild () INHERITS (parent); ALTER TABLE newChild DROP INHERITS parent; ... do some processing ... ALTER TABLE newChild ADD INHERITS parent; Neither of which I like. 2. attislocal is always set False when an appropriate ADD INHERITS is actioned. Not ever set back again. attinhcount changes as appropriate - up for ADDs and down for DROPs. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Added to TODO:
o Add ALTER TABLE tab ADD/DROP INHERITS parent
pg_attribute.attislocal has to be set to 'false' for ADD, and pg_attribute.attinhcount adjusted
appropriately
---------------------------------------------------------------------------
Simon Riggs wrote:
> On Tue, 2006-05-23 at 13:17 -0400, Tom Lane wrote:
> > Simon Riggs <simon@2ndquadrant.com> writes:
> > > - ADD/DROP are opposites; you can use the other one to undo an action
> > > taken in haste, error etc
> >
> > It's not going to be that easy. What exactly will happen to the child
> > table's attislocal/attinhcount settings, and why, during ADD or DROP?
>
> Never is round here ;-)
>
> attislocal: If you set this to False, you wouldn't be able to set it
> back again. If you leave it as it is, you'd never be able to recursively
> drop a column. If you change it, you'll never be able to stop someone
> from dropping a previously defined local column.
> Proposal:
> 1. attislocal is not touched.
> That means if you want to create a new partition you do this:
>
> CREATE TABLE newChild () INHERITS (template);
>
> ... do some processing ...
>
> ALTER TABLE newChild ADD INHERITS parent;
>
> or this:
>
> CREATE TABLE newChild () INHERITS (parent);
> ALTER TABLE newChild DROP INHERITS parent;
>
> ... do some processing ...
>
> ALTER TABLE newChild ADD INHERITS parent;
>
> Neither of which I like.
>
> 2. attislocal is always set False when an appropriate ADD INHERITS is
> actioned. Not ever set back again.
>
> attinhcount changes as appropriate - up for ADDs and down for DROPs.
>
> --
> Simon Riggs
> EnterpriseDB http://www.enterprisedb.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
-- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Ühel kenal päeval, T, 2006-05-23 kell 18:48, kirjutas Simon Riggs: > > 2. attislocal is always set False when an appropriate ADD INHERITS is > actioned. Not ever set back again. Why "never set back again" ? I'd guess that it should be set back to true when it is not an inherited column anymore, that is when its attinhcount reaches zero. > attinhcount changes as appropriate - up for ADDs and down for DROPs. > -- ---------------- 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
On Wed, 2006-05-24 at 11:17 +0300, Hannu Krosing wrote: > Ühel kenal päeval, T, 2006-05-23 kell 18:48, kirjutas Simon Riggs: > > > > > 2. attislocal is always set False when an appropriate ADD INHERITS is > > actioned. Not ever set back again. > > Why "never set back again" ? I'd guess that it should be set back to > true when it is not an inherited column anymore, that is when its > attinhcount reaches zero. Because you have no record of whether it was created locally or inherited when originally created. And: do you care? Why? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Simon Riggs <simon@2ndquadrant.com> writes:
> On Wed, 2006-05-24 at 11:17 +0300, Hannu Krosing wrote:
>> Why "never set back again" ? I'd guess that it should be set back to
>> true when it is not an inherited column anymore, that is when its
>> attinhcount reaches zero.
> Because you have no record of whether it was created locally or
> inherited when originally created. And: do you care? Why?
The invariant is supposed to be that every attribute has attislocal
true or attinhcount > 0 (or both). Otherwise it has no justification
to exist. I see in the regression database that someone has broken
this invariant; it looks like LIKE inheritance is misimplemented.
I'm going to insist on a fix for that ;-)
I think that the correct behavior for add/drop is:
* ADD INHERITS increments attinhcount for every column found to match
a column of the parent. Nothing happens to attislocal.
* DROP INHERITS decrements attinhcount for every column found to match
a column of the parent. Set attislocal true if attinhcount thereby
goes to zero.
This makes ADD followed by DROP a certain no-op (if attinhcount was
zero before the ADD, then attislocal must have been true already).
However, DROP followed by ADD is not a no-op since we might wind up
with attislocal true in a column that wasn't that way before. This
seems like a relatively minor thing though. The alternative would
be to delete the child column when it has no definition sources
left; which would be self-consistent but I don't think it's the
behavior we want for this.
You could also imagine clearing attislocal during ADD, but that
just changes which case isn't a complete no-op, so I don't see
any great attraction to it.
regards, tom lane
Ühel kenal päeval, K, 2006-05-24 kell 09:40, kirjutas Tom Lane: > Simon Riggs <simon@2ndquadrant.com> writes: > > On Wed, 2006-05-24 at 11:17 +0300, Hannu Krosing wrote: > >> Why "never set back again" ? I'd guess that it should be set back to > >> true when it is not an inherited column anymore, that is when its > >> attinhcount reaches zero. > > > Because you have no record of whether it was created locally or > > inherited when originally created. And: do you care? Why? > > The invariant is supposed to be that every attribute has attislocal > true or attinhcount > 0 (or both). In what case does it have both ? Or is it so that 1) islocal means that column is not inherited from any parent 2) attinhcount is the number of direct childrenwho inherit this attribute In this case it should be possible to keep both "right" and make both ADD+DROP and DROP+ADD invariants. > Otherwise it has no justification > to exist. I see in the regression database that someone has broken > this invariant; it looks like LIKE inheritance is misimplemented. I don't think that LIKE inheritance is inheritance at all, rather it is a create-time macro. > I'm going to insist on a fix for that ;-) Agreed. -- ---------------- 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
Hannu Krosing wrote: > Ühel kenal päeval, K, 2006-05-24 kell 09:40, kirjutas Tom Lane: > > Simon Riggs <simon@2ndquadrant.com> writes: > > > On Wed, 2006-05-24 at 11:17 +0300, Hannu Krosing wrote: > > >> Why "never set back again" ? I'd guess that it should be set back to > > >> true when it is not an inherited column anymore, that is when its > > >> attinhcount reaches zero. > > > > > Because you have no record of whether it was created locally or > > > inherited when originally created. And: do you care? Why? > > > > The invariant is supposed to be that every attribute has attislocal > > true or attinhcount > 0 (or both). > > In what case does it have both ? create table parent (foo int); create table child (foo int) inherits (parent); In the child, the column is local but it's also inherited from parent. So if you drop the column from the parent, it should be kept in the child. > > Otherwise it has no justification > > to exist. I see in the regression database that someone has broken > > this invariant; it looks like LIKE inheritance is misimplemented. > > I don't think that LIKE inheritance is inheritance at all, rather it is > a create-time macro. In that case the columns should be marked attislocal. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Hannu Krosing wrote: > I don't think that LIKE inheritance is inheritance at all, rather it is > a create-time macro. > > > Right. It's actually quite useful. I'd like to see it made available in a couple of other contexts, such as CREATE TYPE and the type expression needed when calling a function that returns a RECORD or SETOF RECORD. cheers andrew
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Hannu Krosing wrote:
>> I don't think that LIKE inheritance is inheritance at all, rather it is
>> a create-time macro.
> In that case the columns should be marked attislocal.
Right.
regards, tom lane
On Wed, 2006-05-24 at 09:40 -0400, Tom Lane wrote: > Simon Riggs <simon@2ndquadrant.com> writes: > > On Wed, 2006-05-24 at 11:17 +0300, Hannu Krosing wrote: > >> Why "never set back again" ? I'd guess that it should be set back to > >> true when it is not an inherited column anymore, that is when its > >> attinhcount reaches zero. > > > Because you have no record of whether it was created locally or > > inherited when originally created. And: do you care? Why? > > The invariant is supposed to be that every attribute has attislocal > true or attinhcount > 0 (or both). Otherwise it has no justification > to exist. I see in the regression database that someone has broken > this invariant; it looks like LIKE inheritance is misimplemented. > I'm going to insist on a fix for that ;-) > > I think that the correct behavior for add/drop is: > > * ADD INHERITS increments attinhcount for every column found to match > a column of the parent. Nothing happens to attislocal. > > * DROP INHERITS decrements attinhcount for every column found to match > a column of the parent. Set attislocal true if attinhcount thereby > goes to zero. > > This makes ADD followed by DROP a certain no-op (if attinhcount was > zero before the ADD, then attislocal must have been true already). > However, DROP followed by ADD is not a no-op since we might wind up > with attislocal true in a column that wasn't that way before. This > seems like a relatively minor thing though. The alternative would > be to delete the child column when it has no definition sources > left; which would be self-consistent but I don't think it's the > behavior we want for this. Sounds good. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com