At 04:08 PM 12/12/2002 -0500, Tom Lane wrote:
>Should we remove this error check, thereby effectively making
>zero-column tables first-class citizens?

I should wait 2 minutes before hitting 'send'.

The other option is to disallow the steps that resulted in the zero-column 
table in the first place. This is probably more rational.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/


Fri, 13 Dec 2002 14:11:05 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Creating a zero-column table
Philip Warner <pjw@rhyme.com.au> writes:
> At 04:08 PM 12/12/2002 -0500, Tom Lane wrote:
>> Should we remove this error check, thereby effectively making
>> zero-column tables first-class citizens?

> The other option is to disallow the steps that resulted in the zero-column 
> table in the first place. This is probably more rational.

That was where we were a couple months ago: DROP COLUMN would refuse to
let you drop the last column of a table.  But that has much worse
problems than whether we are able to deliver a "helpful" error message.
For example:
	Amy does CREATE TABLE foo(f1 beths_type);
	Beth now cannot drop her type beths_type.
In most circles this would be called a denial of service.

			regards, tom lane

Fri, 13 Dec 2002 00:31:07 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Creating a zero-column table 
At 12:31 AM 13/12/2002 -0500, Tom Lane wrote:
>         Amy does CREATE TABLE foo(f1 beths_type);
>         Beth now cannot drop her type beths_type.
>In most circles this would be called a denial of service.

Seems like a feature - if beth made the type public, she has to deal with 
fame. I don't see it as different from preventing a dropdb when it is being 
accessed, or unmounting a disk while it is being accessed. But I resume 
this argument has already been had...


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/


Fri, 13 Dec 2002 18:31:38 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Creating a zero-column table 
Philip Warner <pjw@rhyme.com.au> writes:
> At 12:31 AM 13/12/2002 -0500, Tom Lane wrote:
>> Amy does CREATE TABLE foo(f1 beths_type);
>> Beth now cannot drop her type beths_type.
>> In most circles this would be called a denial of service.

> Seems like a feature - if beth made the type public, she has to deal with 
> fame.

But in every other context, Beth has the unconditional right to drop her
type: if it's not the only column in Amy's table, Beth can drop her type
and Amy's column along with it.

Basically, the no-zero-column-tables restriction for deletion was
removed because it creates more weird corner cases than it prevents.
I still agree with that decision.  What we're seeing here is that the
corresponding restriction during table creation also creates weird
corner cases.

			regards, tom lane

Fri, 13 Dec 2002 02:56:38 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Creating a zero-column table 
At 02:56 AM 13/12/2002 -0500, Tom Lane wrote:
>if it's not the only column in Amy's table, Beth can drop her type
>and Amy's column along with it.

I keep forgetting PG's inheritance features. In a non-inheritance system, I 
would vote for forcing a one column table to be dropped. For PG, I think 
you are right.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/


Fri, 13 Dec 2002 19:43:37 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Creating a zero-column table 
On Fri, 2002-12-13 at 03:43, Philip Warner wrote:
> At 02:56 AM 13/12/2002 -0500, Tom Lane wrote:
> >if it's not the only column in Amy's table, Beth can drop her type
> >and Amy's column along with it.
> 
> I keep forgetting PG's inheritance features. In a non-inheritance system, I 
> would vote for forcing a one column table to be dropped. For PG, I think 
> you are right.
> 

Just out of curiosity, do any of the SQL specs deal with 0 column
tables? I can't recall any dbms supporting a create table command that
didn't require at least 1 column.

Robert Treat



13 Dec 2002 09:44:26 -0500
Robert Treat <xzilla@users.sourceforge.net>
Re: Creating a zero-column table
Robert Treat <xzilla@users.sourceforge.net> writes:
> Just out of curiosity, do any of the SQL specs deal with 0 column
> tables? I can't recall any dbms supporting a create table command that
> didn't require at least 1 column.

Actually, in SQL92 11.17 <drop column definition> I find

         3) C shall be a column of T and C shall not be the only column of
            T.

SQL99 has the same rule.  So they indeed prefer the no-drop wart to the
table-with-zero-columns wart.  I have to disagree with that conclusion
though ...

			regards, tom lane

Fri, 13 Dec 2002 09:59:39 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Creating a zero-column table 