error-free disabling of individual child partition tables

On Mon, May 22, 2006 at 02:25:09PM -0400, April Lorenzen wrote:

It comes up repeatedly that I need to load fresh data into a new
table, build indexes and vacuum - then drop the old table and
substitute the new.

Why are you vacuuming a brand new table? Just run analyze.

Postgresql partioning makes this all almost possible: the master
automatically SELECTs from all the child tables - so it automatically
recoginize the new ones, and no loss when the old ones are dropped.

How is that any better than:

BEGIN;
-- Don't do the drop right now, because it might take awhile
ALTER TABLE tablename RENAME TO delete_tablename;
ALTER TABLE temporary_tablename RENAME TO tablename;
COMMIT;
DROP delete_tablename;

Just one thing would make it near perfect: if I could keep the master
from being able to SELECT from the new child table while I'm COPYing
data into the new child, building the index and vacuuming it - without
sending an error back to the user who is querying the master table.

I tried a CHECK constraint, a RULE - SELECT rule can't INSTEAD DO
NOTHING - #postgresql channel people say there's nothing that can do
this... revoking privs or changing the owner of the new child results
in an error back to the user who queries the master table. Tried
creating a dummy table with no records and making a rule for the child
to INSTEAD select from the dummy table but that was not allowed
because it would turn my non-empty child table into a view.

Thank you,

- April

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Jim C. Nasby wrote:

BEGIN;
-- Don't do the drop right now, because it might take awhile
ALTER TABLE tablename RENAME TO delete_tablename;
ALTER TABLE temporary_tablename RENAME TO tablename;
COMMIT;
DROP delete_tablename;

What if there are dependencies? Might be better to have a view, which
everything depends on, and change the view definition from one table to
another back and forth.

cheers

andrew

On Mon, May 22, 2006 at 03:28:39PM -0400, Andrew Dunstan wrote:

Jim C. Nasby wrote:

BEGIN;
-- Don't do the drop right now, because it might take awhile
ALTER TABLE tablename RENAME TO delete_tablename;
ALTER TABLE temporary_tablename RENAME TO tablename;
COMMIT;
DROP delete_tablename;

What if there are dependencies? Might be better to have a view, which
everything depends on, and change the view definition from one table to
another back and forth.

What dependencies would there be that a view would solve? You can't
define RI on a view AFAIK...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Jim C. Nasby wrote:

On Mon, May 22, 2006 at 03:28:39PM -0400, Andrew Dunstan wrote:

Jim C. Nasby wrote:

BEGIN;
-- Don't do the drop right now, because it might take awhile
ALTER TABLE tablename RENAME TO delete_tablename;
ALTER TABLE temporary_tablename RENAME TO tablename;
COMMIT;
DROP delete_tablename;

What if there are dependencies? Might be better to have a view, which
everything depends on, and change the view definition from one table to
another back and forth.

What dependencies would there be that a view would solve? You can't
define RI on a view AFAIK...

functions, rules, other views ... RI is not the only source of dependencies.

you would do this:

< load table_a >
create or replace view v as select * from table_a;
drop if exists table table_b;

next time around swap table_a and table_b, or you could write some
plpgsql or plperl to do it nicely for you.

cheers

andrew

On Mon, 2006-05-22 at 14:25 -0400, April Lorenzen wrote:

Postgresql partioning makes this all almost possible: the master
automatically SELECTs from all the child tables - so it automatically
recoginize the new ones, and no loss when the old ones are dropped.

Just one thing would make it near perfect: if I could keep the master
from being able to SELECT from the new child table while I'm COPYing
data into the new child, building the index and vacuuming it - without
sending an error back to the user who is querying the master table.

There is not currently any way to make an existing table become a child
table of another table. I propose a TODO item to allow this:

ALTER TABLE childN INHERITS ( parent1, ... );

This would only succeed if all of the columns that would have been
inherited from all parent tables already exist with the same name and
datatype, nullability and default values. Additional columns would be
allowed in the child table.

This would also allow you to use CREATE TABLE AS SELECT and then move
that table underneath a parent.

We don't need a disinherit do we?

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

Simon Riggs wrote:

There is not currently any way to make an existing table become a child
table of another table. I propose a TODO item to allow this:

ALTER TABLE childN INHERITS ( parent1, ... );

This would only succeed if all of the columns that would have been
inherited from all parent tables already exist with the same name and
datatype, nullability and default values. Additional columns would be
allowed in the child table.

This would also allow you to use CREATE TABLE AS SELECT and then move
that table underneath a parent.

We don't need a disinherit do we?

O, yes, I think we do. I can imagine that the ability to swap a table
out of a set without deleting it could be very useful (e.g. you might
move it in as a child of an archive table). These two would add markedly
to the usefulness of inheritance as a partitioning mechanism.

cheers

andrew

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

Added to TODO:

o Add ALTER TABLE tab ADD/DROP INHERITS parent

Sounds good, more inline with add/drop constraint.

pg_attribute.attislocal has to be set to 'false' for ADD,

and

attislocal: If you set this to False, you wouldn't be able to set it
back again.

Just curious, why is that ?

pg_attribute.attinhcount adjusted appropriately

Do we have a plan on what to do with RI constraints on the parent
when doing DROP INHERTITS ?
Seems all FK tables would need to be checked for keys in the local
table.
(Not sure whether we have PK on tab*, but it seems you could do it when
the
partitioning column is part of the PK)

Andreas

Ühel kenal päeval, K, 2006-05-24 kell 09:56, kirjutas Zeugswetter
Andreas DCP SD:

Added to TODO:

o Add ALTER TABLE tab ADD/DROP INHERITS parent

Sounds good, more inline with add/drop constraint.

pg_attribute.attislocal has to be set to 'false' for ADD,

and

attislocal: If you set this to False, you wouldn't be able to set it
back again.

Just curious, why is that ?

pg_attribute.attinhcount adjusted appropriately

Do we have a plan on what to do with RI constraints on the parent
when doing DROP INHERTITS ?

I think this is out of scope of this discussion, as we do not currently
support RI over inherited tables. IOW RI is always on one table ONLY.

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

Ü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 children who 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