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

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

From

Rajkumar Raghuwanshi

Date:

27 June 2018, 15:42:14

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

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

From

Rajkumar Raghuwanshi

Date:

28 June 2018, 12:21:23

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.raghuwanshi@enterprisedb.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

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

From

Michael Paquier

Date:

28 June 2018, 12:37:00

On Thu, Jun 28, 2018 at 11:51:23AM +0530, Rajkumar Raghuwanshi wrote:
> postgres=# select pgstatindex('part_pk');
> ERROR:  relation "part_pk" is not a btree index

This error message is intentional.  Please see bef5fcc and its related
thread:
https://www.postgresql.org/message-id/CAH2-WzkOKptQiE51Bh4_xeEHhaBwHkZkGtKizrFMgEkfUuRRQg@mail.gmail.com
--
Michael

Attachment

signature.asc

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

From

Rajkumar Raghuwanshi

Date:

28 June 2018, 12:38:40

On Thu, Jun 28, 2018 at 12:07 PM, Michael Paquier <michael@paquier.xyz> wrote:

On Thu, Jun 28, 2018 at 11:51:23AM +0530, Rajkumar Raghuwanshi wrote:
> postgres=# select pgstatindex('part_pk');
> ERROR: relation "part_pk" is not a btree index

This error message is intentional. Please see bef5fcc and its related
thread:
https://www.postgresql.org/message-id/CAH2-WzkOKptQiE51Bh4_xeEHhaBwHkZkGtKizrFMgEkfUuRRQg@mail.gmail.com

Thanks, Sorry I missed thread.

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

From

Robert Haas

Date:

03 July 2018, 00:18:41

On Wed, Jun 27, 2018 at 5:42 AM, Rajkumar Raghuwanshi
<rajkumar.raghuwanshi@enterprisedb.com> wrote:
> postgres=# alter index part_idx reset (fillfactor);
> ERROR:  "part_idx" is not a table, view, materialized view, or index

I don't know whether that should work, but it seems like the error
message needs improvement, at the least.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company