Misleading CREATE TABLE error

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

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.

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?

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

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.

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