Thread: Creating a zero-column table

Creating a zero-column table

From
Tom Lane
Date:
I was bemused to notice that pg_dump is currently unable to dump the
regression database.  The domain regression test leaves an empty table
(one with zero columns), which causes pg_dump to produce

--
-- TOC entry 172 (OID 675837)
-- Name: domnotnull; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE domnotnull (
);


This is rejected on restore:

ERROR:  DefineRelation: please inherit from a relation or define an attribute

I believe that the table would be correctly restored if we simply
removed that error check in DefineRelation.  On the other hand, for
ordinary hand-entered CREATE TABLE commands it seems like a useful
error check.

Should we remove this error check, thereby effectively making
zero-column tables first-class citizens?  If not, what shall we say to
people who complain that their dump is broken?
        regards, tom lane


Re: Creating a zero-column table

From
Hannu Krosing
Date:
Tom Lane kirjutas R, 13.12.2002 kell 02:08:
> I was bemused to notice that pg_dump is currently unable to dump the
> regression database.  The domain regression test leaves an empty table
> (one with zero columns), which causes pg_dump to produce
> 
> --
> -- TOC entry 172 (OID 675837)
> -- Name: domnotnull; Type: TABLE; Schema: public; Owner: postgres
> --
> 
> CREATE TABLE domnotnull (
> );
> 
> 
> This is rejected on restore:
> 
> ERROR:  DefineRelation: please inherit from a relation or define an attribute
> 
> I believe that the table would be correctly restored if we simply
> removed that error check in DefineRelation.  On the other hand, for
> ordinary hand-entered CREATE TABLE commands it seems like a useful
> error check.
> 
> Should we remove this error check, thereby effectively making
> zero-column tables first-class citizens? 

I would vote for removing the check. I see no reason why one should not
be able to define a zero-column table. While we cant currently do
anything useful (except select oid :) with it now, it does not mean that
it would not serve as a valid base table for inheritance hierarchies in
future. 

I'm probably going to propose an implicit zero-column base table for all
user defined tables (say any_table) so that one can get a list of all
tuple ids in all tables by doing a simple 
"select tableoid,oid from any_table". This will of course not be very
useful for tables with no oids and where there is no index on oid.


-- 
Hannu Krosing <hannu@tm.ee>


Re: Creating a zero-column table

From
Philip Warner
Date:
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?

It's a bit daft, but I suspect it's the way to go. There has to be a 
non-zero chance that a future version of pg_dump may want to add attributes 
to tables in a piecemeal fashion (cyclic FKs? Yuk).



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



Re: Creating a zero-column table

From
Philip Warner
Date:
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   |/



Re: Creating a zero-column table

From
Tom Lane
Date:
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


Re: Creating a zero-column table

From
Philip Warner
Date:
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   |/



Re: Creating a zero-column table

From
Tom Lane
Date:
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


Re: Creating a zero-column table

From
Philip Warner
Date:
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   |/



Re: Creating a zero-column table

From
Robert Treat
Date:
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




Re: Creating a zero-column table

From
Tom Lane
Date:
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


Re: Creating a zero-column table

From
Peter Eisentraut
Date:
Tom Lane writes:

> Should we remove this error check, thereby effectively making
> zero-column tables first-class citizens?

Yes.

-- 
Peter Eisentraut   peter_e@gmx.net



Re: Creating a zero-column table

From
Robert Treat
Date:
On Fri, 2002-12-13 at 19:27, Peter Eisentraut wrote:
> Tom Lane writes:
> 
> > Should we remove this error check, thereby effectively making
> > zero-column tables first-class citizens?
> 
> Yes.
> 

I've never been one for actively going against the sql standards (which
this case would seem to do) so if it is done lets make sure we add
documentation to point out that we aren't compliant on this issue.

Robert Treat




Re: Creating a zero-column table

From
Peter Eisentraut
Date:
Robert Treat writes:

> I've never been one for actively going against the sql standards (which
> this case would seem to do) so if it is done lets make sure we add
> documentation to point out that we aren't compliant on this issue.

This could be a reasonable place to hook in the "SQL flagger"
(non-conforming syntax checker), which a conforming SQL implementation
needs to have anyhow.

-- 
Peter Eisentraut   peter_e@gmx.net