ALTER INDEX fails on partitioned index

On 2019-Jan-05, Justin Pryzby wrote:

12dev and 11.1:

postgres=# CREATE TABLE t(i int)PARTITION BY RANGE(i);
postgres=# CREATE INDEX ON t(i) WITH(fillfactor=11);
postgres=# ALTER INDEX t_i_idx SET (fillfactor=12);
ERROR: 42809: "t_i_idx" is not a table, view, materialized view, or index
LOCATION: ATWrongRelkindError, tablecmds.c:5031

I can't see that's deliberate,

Well, I deliberately ignored that aspect of the report at the time as it
seemed to me (per discussion in thread [1]/messages/by-id/CAH2-WzkOKptQiE51Bh4_xeEHhaBwHkZkGtKizrFMgEkfUuRRQg@mail.gmail.com) that this behavior was
intentional. However, if I think in terms of things like
pages_per_range in BRIN indexes, this decision seems to be a mistake,
because surely we should propagate that value to children.

[1]: /messages/by-id/CAH2-WzkOKptQiE51Bh4_xeEHhaBwHkZkGtKizrFMgEkfUuRRQg@mail.gmail.com

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

On Mon, Jan 07, 2019 at 04:23:30PM -0300, Alvaro Herrera wrote:

On 2019-Jan-05, Justin Pryzby wrote:

12dev and 11.1:

postgres=# CREATE TABLE t(i int)PARTITION BY RANGE(i);
postgres=# CREATE INDEX ON t(i) WITH(fillfactor=11);
postgres=# ALTER INDEX t_i_idx SET (fillfactor=12);
ERROR: 42809: "t_i_idx" is not a table, view, materialized view, or index
LOCATION: ATWrongRelkindError, tablecmds.c:5031

I can't see that's deliberate,

Well, I deliberately ignored that aspect of the report at the time as it
seemed to me (per discussion in thread [1]) that this behavior was
intentional. However, if I think in terms of things like
pages_per_range in BRIN indexes, this decision seems to be a mistake,
because surely we should propagate that value to children.

[1] /messages/by-id/CAH2-WzkOKptQiE51Bh4_xeEHhaBwHkZkGtKizrFMgEkfUuRRQg@mail.gmail.com

I don't see any discussion regarding ALTER (?)

Actually, I ran into this while trying to set pages_per_range.
But shouldn't it also work for fillfactor ?

Thanks,
Justin

On Mon, Jan 07, 2019 at 01:34:08PM -0600, Justin Pryzby wrote:

I don't see any discussion regarding ALTER (?)

Actually, I ran into this while trying to set pages_per_range.
But shouldn't it also work for fillfactor ?

Like ALTER TABLE, the take for ALTER INDEX is that we are still
lacking a ALTER INDEX ONLY flavor which would apply only to single
partitioned indexes instead of applying it down to a full set of
partitions below the partitioned entry on which the DDL is defined.
That would be useful for SET STATISTICS as well. So Alvaro's decision
looks right to me as of what has been done in v11.
--
Michael

On 2019-Jan-05, Justin Pryzby wrote:

12dev and 11.1:

postgres=# CREATE TABLE t(i int)PARTITION BY RANGE(i);
postgres=# CREATE INDEX ON t(i) WITH(fillfactor=11);
postgres=# ALTER INDEX t_i_idx SET (fillfactor=12);
ERROR: 42809: "t_i_idx" is not a table, view, materialized view, or index
LOCATION: ATWrongRelkindError, tablecmds.c:5031

I can't see that's deliberate,

So do you have a proposed patch?

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