Re: BUG #3921: CREATE TABLE / INCLUDING INDEXES fails with permission denied

"Andrew Gilligan" <andy(at)tcpd(dot)net> writes:
> test=> CREATE TABLE t2 (LIKE t1 INCLUDING INDEXES);
> ERROR: permission denied for tablespace pg_default

How annoying :-(. We could work around this particular manifestation
with a patch to make generateClonedIndexStmt() not set index->tableSpace
if the source index is in the database's default tablespace. However,
it seems to me there are a number of definitional oddities in this area,
so I'm bouncing this over to -hackers for some discussion.

1. DefineIndex() treats an omitted tablespace clause differently from
explicitly specifying the tablespace that is the database's default:
if you explicitly specify the space then you must have permissions on
it, otherwise you don't need any. (This is the same behavior as in
DefineRelation incidentally.) Maybe this isn't such a hot idea, and
we should treat the two cases interchangeably?

2. Because heap_create() replaces MyDatabaseTableSpace with zero before
storing reltablespace into pg_class, it is impossible to tell after the
fact whether a table or index was created with an explicit tablespace
specification that happened to match the DB's default, or whether the
tablespace clause was omitted. This seems inconsistent with point 1,
if we decide to maintain the current behavior that the cases are not
treated identically. However we can't just change this, because of the
way that CREATE DATABASE works.

3. Should LIKE INCLUDING INDEXES even try to copy the source index's
tablespace? It doesn't try to copy the table's tablespace. (However,
the new table's tablespace can be specified elsewhere in the command,
whereas there's noplace to put a per-index tablespace spec.) IIRC
it was reported as a bug that LIKE didn't copy index tablespaces, and
we just "fixed" that without much thought, but maybe the point needs
more careful consideration.

Thoughts?

regards, tom lane