Thread: Misleading CREATE TABLE error

Misleading CREATE TABLE error

From
Thom Brown
Date:
Hi,

I found the following error message misleading:

test=# create table cows2 (LIKE cows);
ERROR:  inherited relation "cows" is not a table
STATEMENT:  create table cows2 (LIKE cows);

I'm not trying to inherit a relation, I'm trying to base a table on
it.  As it happens, "cows" is a foreign table, which *is* a table,
just not a regular table.  It might be useful to add support to clone
foreign tables into regular tables, the use-case being that you may
wish to import all the data locally into a table of the same
structure.  But the gripe here is the suggestion that the relation
would have been inherited, which would actually be achieved using
INHERITS.

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Misleading CREATE TABLE error

From
Robert Haas
Date:
On Tue, Nov 8, 2011 at 4:49 PM, Thom Brown <thom@linux.com> wrote:
> I found the following error message misleading:
>
> test=# create table cows2 (LIKE cows);
> ERROR:  inherited relation "cows" is not a table
> STATEMENT:  create table cows2 (LIKE cows);
>
> I'm not trying to inherit a relation, I'm trying to base a table on
> it.  As it happens, "cows" is a foreign table, which *is* a table,
> just not a regular table.  It might be useful to add support to clone
> foreign tables into regular tables, the use-case being that you may
> wish to import all the data locally into a table of the same
> structure.  But the gripe here is the suggestion that the relation
> would have been inherited, which would actually be achieved using
> INHERITS.

Interesting.  I agree that there's no obvious reason why that
shouldn't be allowed to work.  Could be useful with views, too.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Misleading CREATE TABLE error

From
Peter Eisentraut
Date:
On ons, 2011-11-09 at 12:00 -0500, Robert Haas wrote:
> On Tue, Nov 8, 2011 at 4:49 PM, Thom Brown <thom@linux.com> wrote:
> > I found the following error message misleading:
> >
> > test=# create table cows2 (LIKE cows);
> > ERROR:  inherited relation "cows" is not a table
> > STATEMENT:  create table cows2 (LIKE cows);
> >
> > I'm not trying to inherit a relation, I'm trying to base a table on
> > it.  As it happens, "cows" is a foreign table, which *is* a table,
> > just not a regular table.  It might be useful to add support to clone
> > foreign tables into regular tables, the use-case being that you may
> > wish to import all the data locally into a table of the same
> > structure.  But the gripe here is the suggestion that the relation
> > would have been inherited, which would actually be achieved using
> > INHERITS.
> 
> Interesting.  I agree that there's no obvious reason why that
> shouldn't be allowed to work.  Could be useful with views, too.

I recently came across a situation where LIKE with a composite type
might have been useful.




Re: Misleading CREATE TABLE error

From
Peter Eisentraut
Date:
On tis, 2011-11-08 at 21:49 +0000, Thom Brown wrote:
> I found the following error message misleading:
>
> test=# create table cows2 (LIKE cows);
> ERROR:  inherited relation "cows" is not a table
> STATEMENT:  create table cows2 (LIKE cows);
>
> I'm not trying to inherit a relation, I'm trying to base a table on
> it.

It's not only the error message that's misleading, but the whole code,
because the entire code for CREATE TABLE ... (LIKE ...) claims to do
"inheritance" based on an ancient understanding of the SQL standard.  I
know this has confused me many times already, so I decided to clean this
up and rename all the internal parser structures, split up the
regression tests for real inheritance and CREATE TABLE LIKE, and adjust
the error messages.  Patch attached.

> As it happens, "cows" is a foreign table, which *is* a table,
> just not a regular table.  It might be useful to add support to clone
> foreign tables into regular tables, the use-case being that you may
> wish to import all the data locally into a table of the same
> structure.

This is easy to fix, and I mangled it into my big renaming patch, which
I shouldn't have.  Anyway, one question that's perhaps worth discussing
is whether we should allow and disallow the various INCLUDING options
depending on the relation type.  For example, views don't have indexes,
so should we disallow INCLUDING INDEXES or just assume they don't have
any?

Attachment

Re: Misleading CREATE TABLE error

From
Thom Brown
Date:
On 27 December 2011 20:16, Peter Eisentraut <peter_e@gmx.net> wrote:
> It's not only the error message that's misleading, but the whole code,
> because the entire code for CREATE TABLE ... (LIKE ...) claims to do
> "inheritance" based on an ancient understanding of the SQL standard.  I
> know this has confused me many times already, so I decided to clean this
> up and rename all the internal parser structures, split up the
> regression tests for real inheritance and CREATE TABLE LIKE, and adjust
> the error messages.  Patch attached.

Thanks for the patch. +1 for changing "parent" to "source" in the
docs.  The patch doesn't apply cleanly for me for some reason though.

> Anyway, one question that's perhaps worth discussing
> is whether we should allow and disallow the various INCLUDING options
> depending on the relation type.  For example, views don't have indexes,
> so should we disallow INCLUDING INDEXES or just assume they don't have
> any?

I'd personally prefer the latter, primarily because it won't create
another syntax variation with no discernable benefit.

--
Thom


Re: Misleading CREATE TABLE error

From
Peter Eisentraut
Date:
On tis, 2011-11-29 at 06:33 +0200, Peter Eisentraut wrote:
> > > I'm not trying to inherit a relation, I'm trying to base a table on
> > > it.  As it happens, "cows" is a foreign table, which *is* a table,
> > > just not a regular table.  It might be useful to add support to clone
> > > foreign tables into regular tables, the use-case being that you may
> > > wish to import all the data locally into a table of the same
> > > structure.  But the gripe here is the suggestion that the relation
> > > would have been inherited, which would actually be achieved using
> > > INHERITS.
> >
> > Interesting.  I agree that there's no obvious reason why that
> > shouldn't be allowed to work.  Could be useful with views, too.
>
> I recently came across a situation where LIKE with a composite type
> might have been useful.
>
This was the last piece of the puzzle that was missing in this area, for
which I have now developed a fix.  The problem was that
parserOpenTable() rejected composite types.  But the only thing that was
really adding over using relation_open() directly was nicer error
pointers.  So I removed a few levels of indirection there, and
integrated the error pointer support directly into
transformTableLikeClause().  This also has the advantage that the "...
is not a table, view, or ..." message now has error pointer support.


Attachment

Re: Misleading CREATE TABLE error

From
Robert Haas
Date:
On Fri, Feb 24, 2012 at 4:03 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
> On tis, 2011-11-29 at 06:33 +0200, Peter Eisentraut wrote:
>> > > I'm not trying to inherit a relation, I'm trying to base a table on
>> > > it.  As it happens, "cows" is a foreign table, which *is* a table,
>> > > just not a regular table.  It might be useful to add support to clone
>> > > foreign tables into regular tables, the use-case being that you may
>> > > wish to import all the data locally into a table of the same
>> > > structure.  But the gripe here is the suggestion that the relation
>> > > would have been inherited, which would actually be achieved using
>> > > INHERITS.
>> >
>> > Interesting.  I agree that there's no obvious reason why that
>> > shouldn't be allowed to work.  Could be useful with views, too.
>>
>> I recently came across a situation where LIKE with a composite type
>> might have been useful.
>>
> This was the last piece of the puzzle that was missing in this area, for
> which I have now developed a fix.  The problem was that
> parserOpenTable() rejected composite types.  But the only thing that was
> really adding over using relation_open() directly was nicer error
> pointers.  So I removed a few levels of indirection there, and
> integrated the error pointer support directly into
> transformTableLikeClause().  This also has the advantage that the "...
> is not a table, view, or ..." message now has error pointer support.

Looks reasonable.  The only thing you didn't copy from
parserOpenTable() is the special error-handling for CTEs, but AFAICS
that's irrelevant here.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company