From: | Christophe Pettus <xof(at)thebuild(dot)com> |
---|---|
To: | Rumpi Gravenstein <rgravens(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Indexes mysteriously change to ON ONLY |
Date: | 2023-01-27 21:07:08 |
Message-ID: | 9251FE93-3C6E-4821-A007-0EDFAC8E7E72@thebuild.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> On Jan 27, 2023, at 13:01, Rumpi Gravenstein <rgravens(at)gmail(dot)com> wrote:
>
> We are using the pg_indexes view (indexdef) to retrieve the index definition.
This is as expected. Once the index is created on the partitioned set of tables, the index on the *root* table will be ON ONLY that table; the child tables appear separately:
xof=# create table t (i bigint) partition by range(i);
CREATE TABLE
xof=# create table t001 partition of t for values from (1) to (2);
CREATE TABLE
xof=# create index on t(i);
CREATE INDEX
xof=# select * from pg_indexes where tablename = 't';
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+-----------+------------+-------------------------------------------------------
public | t | t_i_idx | | CREATE INDEX t_i_idx ON ONLY public.t USING btree (i)
(1 row)
xof=# select * from pg_indexes where tablename = 't001';
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+------------+------------+--------------------------------------------------------
public | t001 | t001_i_idx | | CREATE INDEX t001_i_idx ON public.t001 USING btree (i)
(1 row)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2023-01-27 21:16:24 | Re: Indexes mysteriously change to ON ONLY |
Previous Message | Rumpi Gravenstein | 2023-01-27 21:01:31 | Re: Indexes mysteriously change to ON ONLY |