When a cascaded column drop is removing the last column, drop the table
instead.  Regression tests via domains.

Reported by Tim Knowles.

-- 
  Rod Taylor
26 Sep 2002 21:00:16 -0400
Rod Taylor <rbt@rbt.ca>
Cascaded Column Drop
Rod Taylor <rbt@rbt.ca> writes:
> When a cascaded column drop is removing the last column, drop the table
> instead.  Regression tests via domains.

Is that a good idea, or should we refuse the drop entirely?  A table
drop zaps a lot more stuff than a column drop.

What I was actually wondering about after reading Tim's report was
whether we could support zero-column tables, which would eliminate the
need for the special case altogether.  I have not looked to see how
extensive are the places that assume tuples have > 0 columns ...

			regards, tom lane

Thu, 26 Sep 2002 23:47:08 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Cascaded Column Drop 
Tom Lane wrote:
> Rod Taylor <rbt@rbt.ca> writes:
> > When a cascaded column drop is removing the last column, drop the table
> > instead.  Regression tests via domains.
> 
> Is that a good idea, or should we refuse the drop entirely?  A table
> drop zaps a lot more stuff than a column drop.

I think we should refuse the drop.  It is just too strange.  You can
suggest if they want the column dropped, just drop the table.

> What I was actually wondering about after reading Tim's report was
> whether we could support zero-column tables, which would eliminate the
> need for the special case altogether.  I have not looked to see how
> extensive are the places that assume tuples have > 0 columns ...

Zero-width tables do sound interesting.  Is it somehow non-relational?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 27 Sep 2002 00:00:47 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Cascaded Column Drop
Bruce Momjian wrote:
> Tom Lane wrote:
>>What I was actually wondering about after reading Tim's report was
>>whether we could support zero-column tables, which would eliminate the
>>need for the special case altogether.  I have not looked to see how
>>extensive are the places that assume tuples have > 0 columns ...
> 
> Zero-width tables do sound interesting.  Is it somehow non-relational?
> 

I can see a zero column table as a transition state maybe, but what else would 
they be useful for?

Joe


Thu, 26 Sep 2002 21:11:28 -0700
Joe Conway <mail@joeconway.com>
Re: Cascaded Column Drop
Joe Conway wrote:
> Bruce Momjian wrote:
> > Tom Lane wrote:
> >>What I was actually wondering about after reading Tim's report was
> >>whether we could support zero-column tables, which would eliminate the
> >>need for the special case altogether.  I have not looked to see how
> >>extensive are the places that assume tuples have > 0 columns ...
> > 
> > Zero-width tables do sound interesting.  Is it somehow non-relational?
> > 
> 
> I can see a zero column table as a transition state maybe, but what else would 
> they be useful for?

It's the /dev/null of the SQL world, but I'm not sure what that means. :-)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 27 Sep 2002 00:16:54 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Cascaded Column Drop
Bruce Momjian dijo: 

> Tom Lane wrote:
> > Rod Taylor <rbt@rbt.ca> writes:
> > > When a cascaded column drop is removing the last column, drop the table
> > > instead.  Regression tests via domains.
> > 
> > Is that a good idea, or should we refuse the drop entirely?  A table
> > drop zaps a lot more stuff than a column drop.
> 
> I think we should refuse the drop.  It is just too strange.  You can
> suggest if they want the column dropped, just drop the table.

Yeah... you can't have triggers, you can't have constraints.  Hey, you
can create a view using it, and possibly you can inherit the table...
but what's that good for?

But think about the inheritance case again: suppose

create table p (f1 int);
create table c (f2 int) inherits (p);

Now you just change your mind and want to drop p but not c.  You can't
do it because f1 is the last column on it, and c inherits it.  So a way
to drop the last column inherited (thus freeing the dependency on p)
makes c independent, and you can drop p.

But note that this drop of p is not just drop-cascade: the inheritance
tree has to get out of the dependency info first (it's not drop-restrict
either, is it?)

-- 
Alvaro Herrera (<alvherre[a]atentus.com>)
"La espina, desde que nace, ya pincha" (Proverbio africano)


Fri, 27 Sep 2002 00:18:46 -0400 (CLT)
Alvaro Herrera <alvherre@atentus.com>
Re: Cascaded Column Drop
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> What I was actually wondering about after reading Tim's report was
>> whether we could support zero-column tables, which would eliminate the
>> need for the special case altogether.  I have not looked to see how
>> extensive are the places that assume tuples have > 0 columns ...

> Zero-width tables do sound interesting.  Is it somehow non-relational?

Dunno.  I wasn't really thinking that zero-width tables are all that
useful by themselves, but it does seem natural that you should be able
to redefine a column by
	ALTER TABLE mytab DROP COLUMN foo;
	ALTER TABLE mytab ADD COLUMN foo ...;
even if foo is the only column in mytab.  Right now we reject that.

			regards, tom lane

Fri, 27 Sep 2002 00:28:56 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Cascaded Column Drop 
> > Is that a good idea, or should we refuse the drop entirely?  A table
> > drop zaps a lot more stuff than a column drop.
> 
> I think we should refuse the drop.  It is just too strange.  You can
> suggest if they want the column dropped, just drop the table.

Leaving a zero-width table would be best, even if its not so useful.  I
don't like rejecting a CASCADE as it kinda defeats the purpose of having
CASCADE.

The patch may not work right in complex cases anyway.  It seems the
switch to remove the table should be earlier.

-- 
  Rod Taylor


27 Sep 2002 00:29:21 -0400
Rod Taylor <rbt@rbt.ca>
Re: Cascaded Column Drop
Alvaro Herrera <alvherre@atentus.com> writes:
> But think about the inheritance case again: suppose

> create table p (f1 int);
> create table c (f2 int) inherits (p);

> Now you just change your mind and want to drop p but not c.  You can't
> do it because f1 is the last column on it, and c inherits it.  So a way
> to drop the last column inherited (thus freeing the dependency on p)
> makes c independent, and you can drop p.

Hmm, no I don't think so.  Parent-to-child dependence is a property of
the two tables, not of their columns, and should not go away just
because you reduce the parent to zero columns.  I would expect that if
I dropped p.f1 (assuming this were allowed) and then added p.g1, that
c would also now have c.g1.  So the parent/child relationship outlives
any specific column ... IMHO anyway.

			regards, tom lane

Fri, 27 Sep 2002 01:43:37 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Cascaded Column Drop 
Rod Taylor <rbt@rbt.ca> writes:
> Leaving a zero-width table would be best, even if its not so useful.  I
> don't like rejecting a CASCADE as it kinda defeats the purpose of having
> CASCADE.

I did something about this --- as of CVS tip, you can do

regression=# create table foo (f1 int);
CREATE TABLE
regression=# alter table foo drop column f1;
ALTER TABLE
regression=# select * from foo;

--
(0 rows)

I fixed the places that were exposed by the regression tests as not
coping with zero-column tables, but it is likely that there are some
more places that will give odd errors with such a table.  Feel free
to beat on it.

psql seems to have some minor issues with a zero-column select.
You can do this:

regression=# insert into foo default values;
INSERT 720976 1
regression=# select * from foo;

--
(1 row)

regression=# insert into foo default values;
INSERT 720977 1
regression=# select * from foo;

--
(2 rows)

regression=# 

Seems like nothing's being printed for an empty row.

			regards, tom lane

Sat, 28 Sep 2002 16:38:29 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [PATCHES] Cascaded Column Drop 
On Sat, 2002-09-28 at 16:38, Tom Lane wrote:
> Rod Taylor <rbt@rbt.ca> writes:
> > Leaving a zero-width table would be best, even if its not so useful.  I
> > don't like rejecting a CASCADE as it kinda defeats the purpose of having
> > CASCADE.
> 
> I did something about this --- as of CVS tip, you can do
> 
> regression=# create table foo (f1 int);
> CREATE TABLE
> regression=# alter table foo drop column f1;
> ALTER TABLE
> regression=# select * from foo;

Which of course would dump as 'create table foo ();'.

I don't think relcache would like a table without any columns, which is
why the above is rejected.


Anyway, should pg_dump ignore the table entirely?  Or do we try to allow
create table () without any attributes?

-- 
  Rod Taylor


28 Sep 2002 22:19:50 -0400
Rod Taylor <rbt@rbt.ca>
Re: [PATCHES] Cascaded Column Drop
Rod Taylor <rbt@rbt.ca> writes:
>> I did something about this --- as of CVS tip, you can do
>> 
>> regression=# create table foo (f1 int);
>> CREATE TABLE
>> regression=# alter table foo drop column f1;
>> ALTER TABLE
>> regression=# select * from foo;

> Which of course would dump as 'create table foo ();'.

True.  I didn't say that everything would be happy with it ;-).  I think
that a zero-column table is only useful as a transient state, and so I'm
happy as long as the backend doesn't core dump.

> I don't think relcache would like a table without any columns, which is
> why the above is rejected.

Relcache doesn't seem to have a problem with it.

> Anyway, should pg_dump ignore the table entirely?  Or do we try to allow
> create table () without any attributes?

I feel no strong need to do either.  But it likely would only take
removal of this error check:

regression=# create table foo ();
ERROR:  DefineRelation: please inherit from a relation or define an attribute

at least as far as the backend goes.

			regards, tom lane

Sat, 28 Sep 2002 22:41:50 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [PATCHES] Cascaded Column Drop 

> regression=# create table foo ();
> ERROR:  DefineRelation: please inherit from a relation or define an attribute
> 
> at least as far as the backend goes.

Found in relcache.c earlier:
	AssertArg(natts > 0);

Didn't look too hard to see what it protects, because it's more effort
than it's worth.

-- 
  Rod Taylor


28 Sep 2002 22:50:32 -0400
Rod Taylor <rbt@rbt.ca>
Re: [PATCHES] Cascaded Column Drop
Rod Taylor <rbt@rbt.ca> writes:
> Found in relcache.c earlier:
> 	AssertArg(natts > 0);

Okay, one other place to change ... there are probably more such ...

			regards, tom lane

Sat, 28 Sep 2002 22:58:33 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [PATCHES] Cascaded Column Drop 