Обсуждение: Misleading CREATE TABLE error
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
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