Re: alter index WITH ( storage_parameter = value [, ... ] ) for partition index.

another case where I got error like partition table index is not a index is
given below.

postgres=# create table part(a int, constraint part_pk primary key(a))
PARTITION BY RANGE(a);
CREATE TABLE
postgres=# create table part_p1 partition of part for values from
(minvalue) to (0);
CREATE TABLE
postgres=# create table part_p2 partition of part for values from (0) to
(maxvalue);
CREATE TABLE
postgres=# create EXTENSION if not exists pgstattuple;
CREATE EXTENSION
postgres=# select pgstatindex('part_p1_pkey');
pgstatindex
------------------------------
(3,0,8192,0,0,0,0,0,NaN,NaN)
(1 row)

postgres=# select pgstatindex('part_pk');
ERROR: relation "part_pk" is not a btree index
postgres=#

Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation

On Wed, Jun 27, 2018 at 3:12 PM, Rajkumar Raghuwanshi <
rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com> wrote:

> Hi,
>
> I have created partition table index with some storage_parameter like
> example given below, I am not able to reset/modify it from partition table.
> Is this fine.
>
> postgres=# create table part(a int) PARTITION BY RANGE(a);
> CREATE TABLE
> postgres=# create table part_p partition of part for values from
> (minvalue) to (maxvalue);
> CREATE TABLE
> postgres=# create index part_idx on part(a) with (fillfactor = '14');
> CREATE INDEX
> postgres=# \d part
> Table "public.part"
> Column | Type | Collation | Nullable | Default
> --------+---------+-----------+----------+---------
> a | integer | | |
> Partition key: RANGE (a)
> Indexes:
> "part_idx" btree (a) WITH (fillfactor='14')
> Number of partitions: 1 (Use \d+ to list them.)
>
> postgres=# \d part_p
> Table "public.part_p"
> Column | Type | Collation | Nullable | Default
> --------+---------+-----------+----------+---------
> a | integer | | |
> Partition of: part FOR VALUES FROM (MINVALUE) TO (MAXVALUE)
> Indexes:
> "part_p_a_idx" btree (a) WITH (fillfactor='14')
>
>
> *postgres=# alter index part_idx reset (fillfactor);ERROR: "part_idx" is
> not a table, view, materialized view, or index*
> postgres=# alter index part_p_a_idx reset (fillfactor);
> ALTER INDEX
> postgres=# \d+ part
> Table "public.part"
> Column | Type | Collation | Nullable | Default | Storage | Stats
> target | Description
> --------+---------+-----------+----------+---------+--------
> -+--------------+-------------
> a | integer | | | | plain
> | |
> Partition key: RANGE (a)
> Indexes:
> * "part_idx" btree (a) WITH (fillfactor='14')*
> Partitions: part_p FOR VALUES FROM (MINVALUE) TO (MAXVALUE)
>
> postgres=# \d part_p
> Table "public.part_p"
> Column | Type | Collation | Nullable | Default
> --------+---------+-----------+----------+---------
> a | integer | | |
> Partition of: part FOR VALUES FROM (MINVALUE) TO (MAXVALUE)
> Indexes:
> "part_p_a_idx" btree (a)
>
> Thanks & Regards,
> Rajkumar Raghuwanshi
> QMG, EnterpriseDB Corporation
>