Re: DROP COLUMN misbehaviour with multiple inheritance - Mailing list pgsql-hackers

On Mon, 2002-09-23 at 18:41, Tom Lane wrote:
> Hannu Krosing <hannu@tm.ee> writes:
> > Alvaro Herrera kirjutas E, 23.09.2002 kell 10:30:
> >> The former drops f1 from c, while the latter does not.  It's
> >> inconsistent.
> 
> > But this is what _should_ happen.
> 
> On what grounds do you claim that?  I agree with Alvaro: it's
> inconsistent to have ONLY produce different effects depending on
> the order in which you issue the commands.

Sorry it took some time thin down my thoughts ;)

As the three following sets of commands ( should ) yield exactly the
same database schema (as visible to user):

1) --------------------------------
create table p1 (f1 int, g1 int);
create table p2 (f1 int, h1 int);
create table c () inherits(p1, p2);
drop column p2.f1; -- this DROP is in fact implicitly ONLY
2) --------------------------------
create table p1 (f1 int, g1 int);
create table p2 (f1 int, h1 int);
create table c () inherits(p1, p2);
drop only column p2.f1;
3) --------------------------------
create table p1 (f1 int, g1 int);
create table p2 (h1 int);
create table c () inherits(p1, p2);
-----------------------------------

For this schema, no matter how we arrived at it

DROP COLUMN p1.f1;

should be different from

DROP ONLY COLUMN p1.f1;



But the ONLY modifier was implicit for all the _non-final_ DROPs

We could carve it out for users by _requiring_ ONLY if the column
dropped is multiply inherited, but that would cut off the possibility
that it is multiply inherited in some children and not in some other,
i.e you could not have drop column automatically remove c13.f1 but keep
c12.f1 for the following schema.

create table p1 (f1 int, g1 int);
create table p2 (f1 int, h1 int);
create table c12 () inherits(p1, p2);
create table p3 (i1 int);
create table c13 () inherits(p1, p3);


So I'd suggest we just postulate that for multiple inheritance dropping
any columns still inherited from other peers will be implicitly "DROP
ONLY" _as far as it concerns this child_ .

then it would be clear why we have different behaviour for

drop ONLY column p1.f1;
drop column p2.f1;

and

drop ONLY column p2.f1; <-- this ONLY is implicit for c by virtue of                           p1.f1 being still
around
drop ONLY column p1.f1;


> > It is quite unreasonable to expect that order of commands makes no
> > difference.
> 
> Why?
> 
> I'll agree that it's not an overriding argument, but it is something
> to shoot for if we can.  And I'm not seeing the argument on the other
> side.

Just to reiterate:

1. All ALTER TABLE MyTable DROP COLUMN commands assume implicit ONLY
when dropping columns multiply inherited from MyTable.

2. Making the final DROP implicitly NOT-ONLY in case there have been
other DROPs of same column from other parents would make it
non-deterministic if columns from child tables will be dropped when
using DROP ONLY on a schema you dont know the full history for.

2.a It will probably also not be pg_dump-transparent, ie doing
dump/reload between first and second drop column will get you different
results.

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