Horton

Thread

  1. Misleading CREATE TABLE error

    Thom Brown <thom@linux.com> — 2011-11-08T21:49:27Z

    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

  2. Re: Misleading CREATE TABLE error

    Robert Haas <robertmhaas@gmail.com> — 2011-11-09T17:00:34Z

    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

  3. Re: Misleading CREATE TABLE error

    Peter Eisentraut <peter_e@gmx.net> — 2011-11-29T04:33:10Z

    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.

  4. Re: Misleading CREATE TABLE error

    Peter Eisentraut <peter_e@gmx.net> — 2011-12-27T20:16:13Z

    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?

  5. Re: Misleading CREATE TABLE error

    Thom Brown <thom@linux.com> — 2011-12-28T02:00:38Z

    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

  6. Re: Misleading CREATE TABLE error

    Peter Eisentraut <peter_e@gmx.net> — 2012-02-24T09:03:12Z

    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.

  7. Re: Misleading CREATE TABLE error

    Robert Haas <robertmhaas@gmail.com> — 2012-02-27T00:10:03Z

    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