Thread: Creating a zero-column table
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
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>
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 |/
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 |/
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
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 |/
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
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 |/
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
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
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
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
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