Re: DROP COLUMN misbehaviour with multiple inheritance

Tom Lane kirjutas P, 29.09.2002 kell 04:00:
> Alvaro Herrera <alvherre(at)atentus(dot)com> writes:
> > I have this almost ready. The thing I don't have quite clear yet is
> > what to do with attislocal. IMHO it should not be touched in any case,
> > but Hannu thinks that for symmetry it should be reset in some cases.

I'd propose that ADD ONLY would pull topmost attislocal up (reset it
from the (grand)child) whereas plain ADD would leave attislocal alone.

The use of ONLY with this meaning is for the symmetry with DROP ONLY.

> My feeling would be to leave it alone in all cases. If I have
>
> create table p (f1 int);
> create table c (f2 text) inherits (p);
>
> I would find it quite surprising if I could destroy c.f2 by adding
> and then dropping p.f2.

This should depend on weather you drop ONLY

Or are you also be surprised by this behaviour of DELETE CASCADE :)

hannu=# create table c(i int);
CREATE TABLE
hannu=# insert into c values(1);
INSERT 41595 1
hannu=# insert into c values(2);
INSERT 41596 1
hannu=# create table p (pk int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'p_pkey'
for table 'p'
CREATE TABLE
hannu=# insert into p values(1);
INSERT 41601 1
hannu=# insert into p values(2);
INSERT 41602 1
hannu=# alter table c add constraint fk foreign key (i)
hannu-# references p on delete cascade;
NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
ALTER TABLE
hannu=# delete from p where pk=2;
DELETE 1
hannu=# select * from c;
i
---
1
(1 row)

Surprise: Where did i=2 go ??

What you are proposing is IMHO equivalent to making FOREIGN KEYs ON
DELETE CASCADE behaviour dependant on weather the foreign key was
created initially or added afterwards.

> > Also, what do you mean by conflicts on defaults? I don't think the
> > parent should take into consideration what the defaults are for its
> > children. Same for constraints.
>
> Well, the rules will probably have to be different for this case than
> they are when creating a child below an existing parent. In particular,
> if the ADD COLUMN operation is trying to create constraints (including
> a simple NOT NULL), I'm inclined to fail rather than merge if the
> existing child column does not already have matching constraints.
> It would seem surprising to me that creating a parent column in this
> way could allow the formerly free-standing child column to suddenly
> have constraints it didn't have before. Also, you'd have to scan the
> child rows to see whether they all meet the constraint, which would
> be slow. For example, if you wanted to do
>
> alter table p add column f2 text not null;
>
> in the above example, I think it is reasonable to insist that you first
> do
>
> alter table c alter column f2 set not null;

To this I strongly agree.

-----------------
Hannu