path: root/src/test/regress/expected/indexing.out

-- Creating an index on a partitioned table makes the partitions
-- automatically get the index
create table idxpart (a int, b int, c text) partition by range (a);
create table idxpart1 partition of idxpart for values from (0) to (10);
create table idxpart2 partition of idxpart for values from (10) to (100)
	partition by range (b);
create table idxpart21 partition of idxpart2 for values from (0) to (100);
create index on idxpart (a);
select relname, relkind, inhparent::regclass
    from pg_class left join pg_index ix on (indexrelid = oid)
	left join pg_inherits on (ix.indexrelid = inhrelid)
	where relname like 'idxpart%' order by relname;
     relname     | relkind |   inhparent    
-----------------+---------+----------------
 idxpart         | p       | 
 idxpart1        | r       | 
 idxpart1_a_idx  | i       | idxpart_a_idx
 idxpart2        | p       | 
 idxpart21       | r       | 
 idxpart21_a_idx | i       | idxpart2_a_idx
 idxpart2_a_idx  | I       | idxpart_a_idx
 idxpart_a_idx   | I       | 
(8 rows)

drop table idxpart;
-- Some unsupported features
create table idxpart (a int, b int, c text) partition by range (a);
create table idxpart1 partition of idxpart for values from (0) to (10);
create unique index on idxpart (a);
ERROR:  cannot create unique index on partitioned table "idxpart"
create index concurrently on idxpart (a);
ERROR:  cannot create index on partitioned table "idxpart" concurrently
drop table idxpart;
-- Verify bugfix with query on indexed partitioned table with no partitions
-- https://postgr.es/m/20180124162006.pmapfiznhgngwtjf@alvherre.pgsql
CREATE TABLE idxpart (col1 INT) PARTITION BY RANGE (col1);
CREATE INDEX ON idxpart (col1);
CREATE TABLE idxpart_two (col2 INT);
SELECT col2 FROM idxpart_two fk LEFT OUTER JOIN idxpart pk ON (col1 = col2);
 col2 
------
(0 rows)

DROP table idxpart, idxpart_two;
-- If a table without index is attached as partition to a table with
-- an index, the index is automatically created
create table idxpart (a int, b int, c text) partition by range (a);
create index idxparti on idxpart (a);
create index idxparti2 on idxpart (b, c);
create table idxpart1 (like idxpart);
\d idxpart1
              Table "public.idxpart1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 
 c      | text    |           |          | 

alter table idxpart attach partition idxpart1 for values from (0) to (10);
\d idxpart1
              Table "public.idxpart1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 
 c      | text    |           |          | 
Partition of: idxpart FOR VALUES FROM (0) TO (10)
Indexes:
    "idxpart1_a_idx" btree (a)
    "idxpart1_b_c_idx" btree (b, c)

drop table idxpart;
-- If a partition already has an index, don't create a duplicative one
create table idxpart (a int, b int) partition by range (a, b);
create table idxpart1 partition of idxpart for values from (0, 0) to (10, 10);
create index on idxpart1 (a, b);
create index on idxpart (a, b);
\d idxpart1
              Table "public.idxpart1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 
Partition of: idxpart FOR VALUES FROM (0, 0) TO (10, 10)
Indexes:
    "idxpart1_a_b_idx" btree (a, b)

select relname, relkind, inhparent::regclass
    from pg_class left join pg_index ix on (indexrelid = oid)
	left join pg_inherits on (ix.indexrelid = inhrelid)
	where relname like 'idxpart%' order by relname;
     relname      | relkind |    inhparent    
------------------+---------+-----------------
 idxpart          | p       | 
 idxpart1         | r       | 
 idxpart1_a_b_idx | i       | idxpart_a_b_idx
 idxpart_a_b_idx  | I       | 
(4 rows)

drop table idxpart;
-- DROP behavior for partitioned indexes
create table idxpart (a int) partition by range (a);
create index on idxpart (a);
create table idxpart1 partition of idxpart for values from (0) to (10);
drop index idxpart1_a_idx;	-- no way
ERROR:  cannot drop index idxpart1_a_idx because index idxpart_a_idx requires it
HINT:  You can drop index idxpart_a_idx instead.
drop index idxpart_a_idx;	-- both indexes go away
select relname, relkind from pg_class
  where relname like 'idxpart%' order by relname;
 relname  | relkind 
----------+---------
 idxpart  | p
 idxpart1 | r
(2 rows)

create index on idxpart (a);
drop table idxpart1;		-- the index on partition goes away too
select relname, relkind from pg_class
  where relname like 'idxpart%' order by relname;
    relname    | relkind 
---------------+---------
 idxpart       | p
 idxpart_a_idx | I
(2 rows)

drop table idxpart;
-- ALTER INDEX .. ATTACH, error cases
create table idxpart (a int, b int) partition by range (a, b);
create table idxpart1 partition of idxpart for values from (0, 0) to (10, 10);
create index idxpart_a_b_idx on only idxpart (a, b);
create index idxpart1_a_b_idx on idxpart1 (a, b);
create index idxpart1_tst1 on idxpart1 (b, a);
create index idxpart1_tst2 on idxpart1 using hash (a);
create index idxpart1_tst3 on idxpart1 (a, b) where a > 10;
alter index idxpart attach partition idxpart1;
ERROR:  "idxpart" is not an index
alter index idxpart_a_b_idx attach partition idxpart1;
ERROR:  "idxpart1" is not an index
alter index idxpart_a_b_idx attach partition idxpart_a_b_idx;
ERROR:  cannot attach index "idxpart_a_b_idx" as a partition of index "idxpart_a_b_idx"
DETAIL:  Index "idxpart_a_b_idx" is not an index on any partition of table "idxpart".
alter index idxpart_a_b_idx attach partition idxpart1_b_idx;
ERROR:  relation "idxpart1_b_idx" does not exist
alter index idxpart_a_b_idx attach partition idxpart1_tst1;
ERROR:  cannot attach index "idxpart1_tst1" as a partition of index "idxpart_a_b_idx"
DETAIL:  The index definitions do not match.
alter index idxpart_a_b_idx attach partition idxpart1_tst2;
ERROR:  cannot attach index "idxpart1_tst2" as a partition of index "idxpart_a_b_idx"
DETAIL:  The index definitions do not match.
alter index idxpart_a_b_idx attach partition idxpart1_tst3;
ERROR:  cannot attach index "idxpart1_tst3" as a partition of index "idxpart_a_b_idx"
DETAIL:  The index definitions do not match.
-- OK
alter index idxpart_a_b_idx attach partition idxpart1_a_b_idx;
alter index idxpart_a_b_idx attach partition idxpart1_a_b_idx; -- quiet
-- reject dupe
create index idxpart1_2_a_b on idxpart1 (a, b);
alter index idxpart_a_b_idx attach partition idxpart1_2_a_b;
ERROR:  cannot attach index "idxpart1_2_a_b" as a partition of index "idxpart_a_b_idx"
DETAIL:  Another index is already attached for partition "idxpart1".
drop table idxpart;
-- make sure everything's gone
select indexrelid::regclass, indrelid::regclass
  from pg_index where indexrelid::regclass::text like 'idxpart%';
 indexrelid | indrelid 
------------+----------
(0 rows)

-- Don't auto-attach incompatible indexes
create table idxpart (a int, b int) partition by range (a);
create table idxpart1 (a int, b int);
create index on idxpart1 using hash (a);
create index on idxpart1 (a) where b > 1;
create index on idxpart1 ((a + 0));
create index on idxpart1 (a, a);
create index on idxpart (a);
alter table idxpart attach partition idxpart1 for values from (0) to (1000);
\d idxpart1
              Table "public.idxpart1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 
Partition of: idxpart FOR VALUES FROM (0) TO (1000)
Indexes:
    "idxpart1_a_a1_idx" btree (a, a)
    "idxpart1_a_idx" hash (a)
    "idxpart1_a_idx1" btree (a) WHERE b > 1
    "idxpart1_a_idx2" btree (a)
    "idxpart1_expr_idx" btree ((a + 0))

drop table idxpart;
-- If CREATE INDEX ONLY, don't create indexes on partitions; and existing
-- indexes on partitions don't change parent.  ALTER INDEX ATTACH can change
-- the parent after the fact.
create table idxpart (a int) partition by range (a);
create table idxpart1 partition of idxpart for values from (0) to (100);
create table idxpart2 partition of idxpart for values from (100) to (1000)
  partition by range (a);
create table idxpart21 partition of idxpart2 for values from (100) to (200);
create table idxpart22 partition of idxpart2 for values from (200) to (300);
create index on idxpart22 (a);
create index on only idxpart2 (a);
create index on idxpart (a);
-- Here we expect that idxpart1 and idxpart2 have a new index, but idxpart21
-- does not; also, idxpart22 is not attached.
\d idxpart1
              Table "public.idxpart1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
Partition of: idxpart FOR VALUES FROM (0) TO (100)
Indexes:
    "idxpart1_a_idx" btree (a)

\d idxpart2
              Table "public.idxpart2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
Partition of: idxpart FOR VALUES FROM (100) TO (1000)
Partition key: RANGE (a)
Indexes:
    "idxpart2_a_idx" btree (a) INVALID
Number of partitions: 2 (Use \d+ to list them.)

\d idxpart21
             Table "public.idxpart21"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
Partition of: idxpart2 FOR VALUES FROM (100) TO (200)

select indexrelid::regclass, indrelid::regclass, inhparent::regclass
  from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)
where indexrelid::regclass::text like 'idxpart%'
  order by indexrelid::regclass::text collate "C";
   indexrelid    | indrelid  |   inhparent   
-----------------+-----------+---------------
 idxpart1_a_idx  | idxpart1  | idxpart_a_idx
 idxpart22_a_idx | idxpart22 | 
 idxpart2_a_idx  | idxpart2  | idxpart_a_idx
 idxpart_a_idx   | idxpart   | 
(4 rows)

alter index idxpart2_a_idx attach partition idxpart22_a_idx;
select indexrelid::regclass, indrelid::regclass, inhparent::regclass
  from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)
where indexrelid::regclass::text like 'idxpart%'
  order by indexrelid::regclass::text collate "C";
   indexrelid    | indrelid  |   inhparent    
-----------------+-----------+----------------
 idxpart1_a_idx  | idxpart1  | idxpart_a_idx
 idxpart22_a_idx | idxpart22 | idxpart2_a_idx
 idxpart2_a_idx  | idxpart2  | idxpart_a_idx
 idxpart_a_idx   | idxpart   | 
(4 rows)

-- attaching idxpart22 is not enough to set idxpart22_a_idx valid ...
alter index idxpart2_a_idx attach partition idxpart22_a_idx;
\d idxpart2
              Table "public.idxpart2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
Partition of: idxpart FOR VALUES FROM (100) TO (1000)
Partition key: RANGE (a)
Indexes:
    "idxpart2_a_idx" btree (a) INVALID
Number of partitions: 2 (Use \d+ to list them.)

-- ... but this one is.
create index on idxpart21 (a);
alter index idxpart2_a_idx attach partition idxpart21_a_idx;
\d idxpart2
              Table "public.idxpart2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
Partition of: idxpart FOR VALUES FROM (100) TO (1000)
Partition key: RANGE (a)
Indexes:
    "idxpart2_a_idx" btree (a)
Number of partitions: 2 (Use \d+ to list them.)

drop table idxpart;
-- When a table is attached a partition and it already has an index, a
-- duplicate index should not get created, but rather the index becomes
-- attached to the parent's index.
create table idxpart (a int, b int, c text) partition by range (a);
create index idxparti on idxpart (a);
create index idxparti2 on idxpart (b, c);
create table idxpart1 (like idxpart including indexes);
\d idxpart1
              Table "public.idxpart1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 
 c      | text    |           |          | 
Indexes:
    "idxpart1_a_idx" btree (a)
    "idxpart1_b_c_idx" btree (b, c)

select relname, relkind, inhparent::regclass
    from pg_class left join pg_index ix on (indexrelid = oid)
	left join pg_inherits on (ix.indexrelid = inhrelid)
	where relname like 'idxpart%' order by relname;
     relname      | relkind | inhparent 
------------------+---------+-----------
 idxpart          | p       | 
 idxpart1         | r       | 
 idxpart1_a_idx   | i       | 
 idxpart1_b_c_idx | i       | 
 idxparti         | I       | 
 idxparti2        | I       | 
(6 rows)

alter table idxpart attach partition idxpart1 for values from (0) to (10);
\d idxpart1
              Table "public.idxpart1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 
 c      | text    |           |          | 
Partition of: idxpart FOR VALUES FROM (0) TO (10)
Indexes:
    "idxpart1_a_idx" btree (a)
    "idxpart1_b_c_idx" btree (b, c)

select relname, relkind, inhparent::regclass
    from pg_class left join pg_index ix on (indexrelid = oid)
	left join pg_inherits on (ix.indexrelid = inhrelid)
	where relname like 'idxpart%' order by relname;
     relname      | relkind | inhparent 
------------------+---------+-----------
 idxpart          | p       | 
 idxpart1         | r       | 
 idxpart1_a_idx   | i       | idxparti
 idxpart1_b_c_idx | i       | idxparti2
 idxparti         | I       | 
 idxparti2        | I       | 
(6 rows)

drop table idxpart;
-- Verify that attaching an invalid index does not mark the parent index valid.
-- On the other hand, attaching a valid index marks not only its direct
-- ancestor valid, but also any indirect ancestor that was only missing the one
-- that was just made valid
create table idxpart (a int, b int) partition by range (a);
create table idxpart1 partition of idxpart for values from (1) to (1000) partition by range (a);
create table idxpart11 partition of idxpart1 for values from (1) to (100);
create index on only idxpart1 (a);
create index on only idxpart (a);
-- this results in two invalid indexes:
select relname, indisvalid from pg_class join pg_index on indexrelid = oid
   where relname like 'idxpart%' order by relname;
    relname     | indisvalid 
----------------+------------
 idxpart1_a_idx | f
 idxpart_a_idx  | f
(2 rows)

-- idxpart1_a_idx is not valid, so idxpart_a_idx should not become valid:
alter index idxpart_a_idx attach partition idxpart1_a_idx;
select relname, indisvalid from pg_class join pg_index on indexrelid = oid
   where relname like 'idxpart%' order by relname;
    relname     | indisvalid 
----------------+------------
 idxpart1_a_idx | f
 idxpart_a_idx  | f
(2 rows)

-- after creating and attaching this, both idxpart1_a_idx and idxpart_a_idx
-- should become valid
create index on idxpart11 (a);
alter index idxpart1_a_idx attach partition idxpart11_a_idx;
select relname, indisvalid from pg_class join pg_index on indexrelid = oid
   where relname like 'idxpart%' order by relname;
     relname     | indisvalid 
-----------------+------------
 idxpart11_a_idx | t
 idxpart1_a_idx  | t
 idxpart_a_idx   | t
(3 rows)

drop table idxpart;
-- verify dependency handling during ALTER TABLE DETACH PARTITION
create table idxpart (a int) partition by range (a);
create table idxpart1 (like idxpart);
create index on idxpart1 (a);
create index on idxpart (a);
create table idxpart2 (like idxpart);
alter table idxpart attach partition idxpart1 for values from (0000) to (1000);
alter table idxpart attach partition idxpart2 for values from (1000) to (2000);
create table idxpart3 partition of idxpart for values from (2000) to (3000);
select relname, relkind from pg_class where relname like 'idxpart%' order by relname;
    relname     | relkind 
----------------+---------
 idxpart        | p
 idxpart1       | r
 idxpart1_a_idx | i
 idxpart2       | r
 idxpart2_a_idx | i
 idxpart3       | r
 idxpart3_a_idx | i
 idxpart_a_idx  | I
(8 rows)

-- a) after detaching partitions, the indexes can be dropped independently
alter table idxpart detach partition idxpart1;
alter table idxpart detach partition idxpart2;
alter table idxpart detach partition idxpart3;
drop index idxpart1_a_idx;
drop index idxpart2_a_idx;
drop index idxpart3_a_idx;
select relname, relkind from pg_class where relname like 'idxpart%' order by relname;
    relname    | relkind 
---------------+---------
 idxpart       | p
 idxpart1      | r
 idxpart2      | r
 idxpart3      | r
 idxpart_a_idx | I
(5 rows)

drop table idxpart, idxpart1, idxpart2, idxpart3;
select relname, relkind from pg_class where relname like 'idxpart%' order by relname;
 relname | relkind 
---------+---------
(0 rows)

create table idxpart (a int) partition by range (a);
create table idxpart1 (like idxpart);
create index on idxpart1 (a);
create index on idxpart (a);
create table idxpart2 (like idxpart);
alter table idxpart attach partition idxpart1 for values from (0000) to (1000);
alter table idxpart attach partition idxpart2 for values from (1000) to (2000);
create table idxpart3 partition of idxpart for values from (2000) to (3000);
-- b) after detaching, dropping the index on parent does not remove the others
select relname, relkind from pg_class where relname like 'idxpart%' order by relname;
    relname     | relkind 
----------------+---------
 idxpart        | p
 idxpart1       | r
 idxpart1_a_idx | i
 idxpart2       | r
 idxpart2_a_idx | i
 idxpart3       | r
 idxpart3_a_idx | i
 idxpart_a_idx  | I
(8 rows)

alter table idxpart detach partition idxpart1;
alter table idxpart detach partition idxpart2;
alter table idxpart detach partition idxpart3;
drop index idxpart_a_idx;
select relname, relkind from pg_class where relname like 'idxpart%' order by relname;
    relname     | relkind 
----------------+---------
 idxpart        | p
 idxpart1       | r
 idxpart1_a_idx | i
 idxpart2       | r
 idxpart2_a_idx | i
 idxpart3       | r
 idxpart3_a_idx | i
(7 rows)

drop table idxpart, idxpart1, idxpart2, idxpart3;
select relname, relkind from pg_class where relname like 'idxpart%' order by relname;
 relname | relkind 
---------+---------
(0 rows)

-- Verify that expression indexes inherit correctly
create table idxpart (a int, b int) partition by range (a);
create table idxpart1 (like idxpart);
create index on idxpart1 ((a + b));
create index on idxpart ((a + b));
create table idxpart2 (like idxpart);
alter table idxpart attach partition idxpart1 for values from (0000) to (1000);
alter table idxpart attach partition idxpart2 for values from (1000) to (2000);
create table idxpart3 partition of idxpart for values from (2000) to (3000);
select relname as child, inhparent::regclass as parent, pg_get_indexdef as childdef
  from pg_class join pg_inherits on inhrelid = oid,
  lateral pg_get_indexdef(pg_class.oid)
  where relkind in ('i', 'I') and relname like 'idxpart%' order by relname;
       child       |      parent      |                              childdef                              
-------------------+------------------+--------------------------------------------------------------------
 idxpart1_expr_idx | idxpart_expr_idx | CREATE INDEX idxpart1_expr_idx ON idxpart1 USING btree (((a + b)))
 idxpart2_expr_idx | idxpart_expr_idx | CREATE INDEX idxpart2_expr_idx ON idxpart2 USING btree (((a + b)))
 idxpart3_expr_idx | idxpart_expr_idx | CREATE INDEX idxpart3_expr_idx ON idxpart3 USING btree (((a + b)))
(3 rows)

drop table idxpart;
-- Verify behavior for collation (mis)matches
create table idxpart (a text) partition by range (a);
create table idxpart1 (like idxpart);
create table idxpart2 (like idxpart);
create index on idxpart2 (a collate "POSIX");
create index on idxpart2 (a);
create index on idxpart2 (a collate "C");
alter table idxpart attach partition idxpart1 for values from ('aaa') to ('bbb');
alter table idxpart attach partition idxpart2 for values from ('bbb') to ('ccc');
create table idxpart3 partition of idxpart for values from ('ccc') to ('ddd');
create index on idxpart (a collate "C");
create table idxpart4 partition of idxpart for values from ('ddd') to ('eee');
select relname as child, inhparent::regclass as parent, pg_get_indexdef as childdef
  from pg_class left join pg_inherits on inhrelid = oid,
  lateral pg_get_indexdef(pg_class.oid)
  where relkind in ('i', 'I') and relname like 'idxpart%' order by relname;
      child      |    parent     |                                childdef                                 
-----------------+---------------+-------------------------------------------------------------------------
 idxpart1_a_idx  | idxpart_a_idx | CREATE INDEX idxpart1_a_idx ON idxpart1 USING btree (a COLLATE "C")
 idxpart2_a_idx  |               | CREATE INDEX idxpart2_a_idx ON idxpart2 USING btree (a COLLATE "POSIX")
 idxpart2_a_idx1 |               | CREATE INDEX idxpart2_a_idx1 ON idxpart2 USING btree (a)
 idxpart2_a_idx2 | idxpart_a_idx | CREATE INDEX idxpart2_a_idx2 ON idxpart2 USING btree (a COLLATE "C")
 idxpart3_a_idx  | idxpart_a_idx | CREATE INDEX idxpart3_a_idx ON idxpart3 USING btree (a COLLATE "C")
 idxpart4_a_idx  | idxpart_a_idx | CREATE INDEX idxpart4_a_idx ON idxpart4 USING btree (a COLLATE "C")
 idxpart_a_idx   |               | CREATE INDEX idxpart_a_idx ON ONLY idxpart USING btree (a COLLATE "C")
(7 rows)

drop table idxpart;
-- Verify behavior for opclass (mis)matches
create table idxpart (a text) partition by range (a);
create table idxpart1 (like idxpart);
create table idxpart2 (like idxpart);
create index on idxpart2 (a);
alter table idxpart attach partition idxpart1 for values from ('aaa') to ('bbb');
alter table idxpart attach partition idxpart2 for values from ('bbb') to ('ccc');
create table idxpart3 partition of idxpart for values from ('ccc') to ('ddd');
create index on idxpart (a text_pattern_ops);
create table idxpart4 partition of idxpart for values from ('ddd') to ('eee');
-- must *not* have attached the index we created on idxpart2
select relname as child, inhparent::regclass as parent, pg_get_indexdef as childdef
  from pg_class left join pg_inherits on inhrelid = oid,
  lateral pg_get_indexdef(pg_class.oid)
  where relkind in ('i', 'I') and relname like 'idxpart%' order by relname;
      child      |    parent     |                                  childdef                                   
-----------------+---------------+-----------------------------------------------------------------------------
 idxpart1_a_idx  | idxpart_a_idx | CREATE INDEX idxpart1_a_idx ON idxpart1 USING btree (a text_pattern_ops)
 idxpart2_a_idx  |               | CREATE INDEX idxpart2_a_idx ON idxpart2 USING btree (a)
 idxpart2_a_idx1 | idxpart_a_idx | CREATE INDEX idxpart2_a_idx1 ON idxpart2 USING btree (a text_pattern_ops)
 idxpart3_a_idx  | idxpart_a_idx | CREATE INDEX idxpart3_a_idx ON idxpart3 USING btree (a text_pattern_ops)
 idxpart4_a_idx  | idxpart_a_idx | CREATE INDEX idxpart4_a_idx ON idxpart4 USING btree (a text_pattern_ops)
 idxpart_a_idx   |               | CREATE INDEX idxpart_a_idx ON ONLY idxpart USING btree (a text_pattern_ops)
(6 rows)

drop index idxpart_a_idx;
create index on only idxpart (a text_pattern_ops);
-- must reject
alter index idxpart_a_idx attach partition idxpart2_a_idx;
ERROR:  cannot attach index "idxpart2_a_idx" as a partition of index "idxpart_a_idx"
DETAIL:  The index definitions do not match.
drop table idxpart;
-- Verify that attaching indexes maps attribute numbers correctly
create table idxpart (col1 int, a int, col2 int, b int) partition by range (a);
create table idxpart1 (b int, col1 int, col2 int, col3 int, a int);
alter table idxpart drop column col1, drop column col2;
alter table idxpart1 drop column col1, drop column col2, drop column col3;
alter table idxpart attach partition idxpart1 for values from (0) to (1000);
create index idxpart_1_idx on only idxpart (b, a);
create index idxpart1_1_idx on idxpart1 (b, a);
create index idxpart1_1b_idx on idxpart1 (b);
-- test expressions and partial-index predicate, too
create index idxpart_2_idx on only idxpart ((b + a)) where a > 1;
create index idxpart1_2_idx on idxpart1 ((b + a)) where a > 1;
create index idxpart1_2b_idx on idxpart1 ((a + b)) where a > 1;
create index idxpart1_2c_idx on idxpart1 ((b + a)) where b > 1;
alter index idxpart_1_idx attach partition idxpart1_1b_idx;	-- fail
ERROR:  cannot attach index "idxpart1_1b_idx" as a partition of index "idxpart_1_idx"
DETAIL:  The index definitions do not match.
alter index idxpart_1_idx attach partition idxpart1_1_idx;
alter index idxpart_2_idx attach partition idxpart1_2b_idx;	-- fail
ERROR:  cannot attach index "idxpart1_2b_idx" as a partition of index "idxpart_2_idx"
DETAIL:  The index definitions do not match.
alter index idxpart_2_idx attach partition idxpart1_2c_idx;	-- fail
ERROR:  cannot attach index "idxpart1_2c_idx" as a partition of index "idxpart_2_idx"
DETAIL:  The index definitions do not match.
alter index idxpart_2_idx attach partition idxpart1_2_idx;	-- ok
select relname as child, inhparent::regclass as parent, pg_get_indexdef as childdef
  from pg_class left join pg_inherits on inhrelid = oid,
  lateral pg_get_indexdef(pg_class.oid)
  where relkind in ('i', 'I') and relname like 'idxpart%' order by relname;
      child      |    parent     |                                     childdef                                     
-----------------+---------------+----------------------------------------------------------------------------------
 idxpart1_1_idx  | idxpart_1_idx | CREATE INDEX idxpart1_1_idx ON idxpart1 USING btree (b, a)
 idxpart1_1b_idx |               | CREATE INDEX idxpart1_1b_idx ON idxpart1 USING btree (b)
 idxpart1_2_idx  | idxpart_2_idx | CREATE INDEX idxpart1_2_idx ON idxpart1 USING btree (((b + a))) WHERE (a > 1)
 idxpart1_2b_idx |               | CREATE INDEX idxpart1_2b_idx ON idxpart1 USING btree (((a + b))) WHERE (a > 1)
 idxpart1_2c_idx |               | CREATE INDEX idxpart1_2c_idx ON idxpart1 USING btree (((b + a))) WHERE (b > 1)
 idxpart_1_idx   |               | CREATE INDEX idxpart_1_idx ON ONLY idxpart USING btree (b, a)
 idxpart_2_idx   |               | CREATE INDEX idxpart_2_idx ON ONLY idxpart USING btree (((b + a))) WHERE (a > 1)
(7 rows)

drop table idxpart;
-- Make sure the partition columns are mapped correctly
create table idxpart (a int, b int, c text) partition by range (a);
create index idxparti on idxpart (a);
create index idxparti2 on idxpart (c, b);
create table idxpart1 (c text, a int, b int);
alter table idxpart attach partition idxpart1 for values from (0) to (10);
create table idxpart2 (c text, a int, b int);
create index on idxpart2 (a);
create index on idxpart2 (c, b);
alter table idxpart attach partition idxpart2 for values from (10) to (20);
select c.relname, pg_get_indexdef(indexrelid)
  from pg_class c join pg_index i on c.oid = i.indexrelid
  where indrelid::regclass::text like 'idxpart%'
  order by indexrelid::regclass::text collate "C";
     relname      |                       pg_get_indexdef                        
------------------+--------------------------------------------------------------
 idxpart1_a_idx   | CREATE INDEX idxpart1_a_idx ON idxpart1 USING btree (a)
 idxpart1_c_b_idx | CREATE INDEX idxpart1_c_b_idx ON idxpart1 USING btree (c, b)
 idxpart2_a_idx   | CREATE INDEX idxpart2_a_idx ON idxpart2 USING btree (a)
 idxpart2_c_b_idx | CREATE INDEX idxpart2_c_b_idx ON idxpart2 USING btree (c, b)
 idxparti         | CREATE INDEX idxparti ON ONLY idxpart USING btree (a)
 idxparti2        | CREATE INDEX idxparti2 ON ONLY idxpart USING btree (c, b)
(6 rows)

drop table idxpart;
-- Verify that columns are mapped correctly in expression indexes
create table idxpart (col1 int, col2 int, a int, b int) partition by range (a);
create table idxpart1 (col2 int, b int, col1 int, a int);
create table idxpart2 (col1 int, col2 int, b int, a int);
alter table idxpart drop column col1, drop column col2;
alter table idxpart1 drop column col1, drop column col2;
alter table idxpart2 drop column col1, drop column col2;
create index on idxpart2 (abs(b));
alter table idxpart attach partition idxpart2 for values from (0) to (1);
create index on idxpart (abs(b));
alter table idxpart attach partition idxpart1 for values from (1) to (2);
select c.relname, pg_get_indexdef(indexrelid)
  from pg_class c join pg_index i on c.oid = i.indexrelid
  where indrelid::regclass::text like 'idxpart%'
  order by indexrelid::regclass::text collate "C";
     relname      |                          pg_get_indexdef                          
------------------+-------------------------------------------------------------------
 idxpart1_abs_idx | CREATE INDEX idxpart1_abs_idx ON idxpart1 USING btree (abs(b))
 idxpart2_abs_idx | CREATE INDEX idxpart2_abs_idx ON idxpart2 USING btree (abs(b))
 idxpart_abs_idx  | CREATE INDEX idxpart_abs_idx ON ONLY idxpart USING btree (abs(b))
(3 rows)

drop table idxpart;
-- Verify that columns are mapped correctly for WHERE in a partial index
create table idxpart (col1 int, a int, col3 int, b int) partition by range (a);
alter table idxpart drop column col1, drop column col3;
create table idxpart1 (col1 int, col2 int, col3 int, col4 int, b int, a int);
alter table idxpart1 drop column col1, drop column col2, drop column col3, drop column col4;
alter table idxpart attach partition idxpart1 for values from (0) to (1000);
create table idxpart2 (col1 int, col2 int, b int, a int);
create index on idxpart2 (a) where b > 1000;
alter table idxpart2 drop column col1, drop column col2;
alter table idxpart attach partition idxpart2 for values from (1000) to (2000);
create index on idxpart (a) where b > 1000;
select c.relname, pg_get_indexdef(indexrelid)
  from pg_class c join pg_index i on c.oid = i.indexrelid
  where indrelid::regclass::text like 'idxpart%'
  order by indexrelid::regclass::text collate "C";
    relname     |                               pg_get_indexdef                               
----------------+-----------------------------------------------------------------------------
 idxpart1_a_idx | CREATE INDEX idxpart1_a_idx ON idxpart1 USING btree (a) WHERE (b > 1000)
 idxpart2_a_idx | CREATE INDEX idxpart2_a_idx ON idxpart2 USING btree (a) WHERE (b > 1000)
 idxpart_a_idx  | CREATE INDEX idxpart_a_idx ON ONLY idxpart USING btree (a) WHERE (b > 1000)
(3 rows)

drop table idxpart;
-- Column number mapping: dropped columns in the partition
create table idxpart1 (drop_1 int, drop_2 int, col_keep int, drop_3 int);
alter table idxpart1 drop column drop_1;
alter table idxpart1 drop column drop_2;
alter table idxpart1 drop column drop_3;
create index on idxpart1 (col_keep);
create table idxpart (col_keep int) partition by range (col_keep);
create index on idxpart (col_keep);
alter table idxpart attach partition idxpart1 for values from (0) to (1000);
\d idxpart
               Table "public.idxpart"
  Column  |  Type   | Collation | Nullable | Default 
----------+---------+-----------+----------+---------
 col_keep | integer |           |          | 
Partition key: RANGE (col_keep)
Indexes:
    "idxpart_col_keep_idx" btree (col_keep)
Number of partitions: 1 (Use \d+ to list them.)

\d idxpart1
               Table "public.idxpart1"
  Column  |  Type   | Collation | Nullable | Default 
----------+---------+-----------+----------+---------
 col_keep | integer |           |          | 
Partition of: idxpart FOR VALUES FROM (0) TO (1000)
Indexes:
    "idxpart1_col_keep_idx" btree (col_keep)

select attrelid::regclass, attname, attnum from pg_attribute
  where attrelid::regclass::text like 'idxpart%' and attnum > 0
  order by attrelid::regclass, attnum;
       attrelid        |           attname            | attnum 
-----------------------+------------------------------+--------
 idxpart1              | ........pg.dropped.1........ |      1
 idxpart1              | ........pg.dropped.2........ |      2
 idxpart1              | col_keep                     |      3
 idxpart1              | ........pg.dropped.4........ |      4
 idxpart1_col_keep_idx | col_keep                     |      1
 idxpart               | col_keep                     |      1
 idxpart_col_keep_idx  | col_keep                     |      1
(7 rows)

drop table idxpart;
-- Column number mapping: dropped columns in the parent table
create table idxpart(drop_1 int, drop_2 int, col_keep int, drop_3 int) partition by range (col_keep);
alter table idxpart drop column drop_1;
alter table idxpart drop column drop_2;
alter table idxpart drop column drop_3;
create table idxpart1 (col_keep int);
create index on idxpart1 (col_keep);
create index on idxpart (col_keep);
alter table idxpart attach partition idxpart1 for values from (0) to (1000);
\d idxpart
               Table "public.idxpart"
  Column  |  Type   | Collation | Nullable | Default 
----------+---------+-----------+----------+---------
 col_keep | integer |           |          | 
Partition key: RANGE (col_keep)
Indexes:
    "idxpart_col_keep_idx" btree (col_keep)
Number of partitions: 1 (Use \d+ to list them.)

\d idxpart1
               Table "public.idxpart1"
  Column  |  Type   | Collation | Nullable | Default 
----------+---------+-----------+----------+---------
 col_keep | integer |           |          | 
Partition of: idxpart FOR VALUES FROM (0) TO (1000)
Indexes:
    "idxpart1_col_keep_idx" btree (col_keep)

select attrelid::regclass, attname, attnum from pg_attribute
  where attrelid::regclass::text like 'idxpart%' and attnum > 0
  order by attrelid::regclass, attnum;
       attrelid        |           attname            | attnum 
-----------------------+------------------------------+--------
 idxpart               | ........pg.dropped.1........ |      1
 idxpart               | ........pg.dropped.2........ |      2
 idxpart               | col_keep                     |      3
 idxpart               | ........pg.dropped.4........ |      4
 idxpart1              | col_keep                     |      1
 idxpart1_col_keep_idx | col_keep                     |      1
 idxpart_col_keep_idx  | col_keep                     |      1
(7 rows)

drop table idxpart;
-- intentionally leave some objects around
create table idxpart (a int) partition by range (a);
create table idxpart1 partition of idxpart for values from (0) to (100);
create table idxpart2 partition of idxpart for values from (100) to (1000)
  partition by range (a);
create table idxpart21 partition of idxpart2 for values from (100) to (200);
create table idxpart22 partition of idxpart2 for values from (200) to (300);
create index on idxpart22 (a);
create index on only idxpart2 (a);
alter index idxpart2_a_idx attach partition idxpart22_a_idx;
create index on idxpart (a);