Re: partitioned indexes and tablespaces

On 2018-Nov-02, Michael Paquier wrote:

> On Thu, Nov 01, 2018 at 09:31:38PM -0300, Alvaro Herrera wrote:

> > 1. When a CREATE INDEX specifies a tablespace, existing partitions get
> > the index in the correct tablespace; however, the parent index itself
> > does not record the tablespace. So when new partitions are created
> > later, they get the index in the default tablespace instead of the
> > specified tablespace. Fix by saving the tablespace in the pg_class row
> > for the parent index.
>
> I may be missing something of course... But partitioned tables don't
> register the tablespace they are on either so as it cannot be used by
> any partitions created on it:

This is not relevant to my case, IMO. Partitioned tables are explicitly
created each time, with their own parameters; if you want to specify the
tablespace in which it is created, you can do so at that point. This is
not the case with partitioned indexes, because they are created
automatically at CREATE TABLE PARTITION OF time, without an option to
specify where each index goes.

> It seems to me that the current behavior is wanted in this case, because
> partitioned tables and partitioned indexes have no physical storage.

Well, I designed the partitioned indexes feature and I can say for
certain that this behavior was not explicitly designed in, but was just
a oversight.

> > 2. ALTER TABLE SET TABLESPACE, applied to the partitioned index, would
> > raise an error indicating that it's not the correct relation kind.
>
> Using the previous example, this does not raise an error:
> alter table aa set tablespace popo;
> However the reference to reltablespace in pg_class is not changed. So I
> would agree with your point to not raise an error and back-patch that,
> but I don't agree with the point of changing reltablespace for a
> partitioned index if that's what you mean.

Same argument here. The pg_class record for the partitioned index
serves to guide the storage of indexes on future partitions, so it is
valuable to have it. Not recording the tablespace (and not allowing it
to be changed afterwards) is a usability fail.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services