Quelle indexing.out Sprache: unbekannt

Spracherkennung für: .out vermutete Sprache: Unknown {[0] [0] [0]} [Methode: Schwerpunktbildung, einfache Gewichte, sechs Dimensionen]

-- 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);
-- relhassubclass of a partitioned index is false before creating any partition.
-- It will be set after the first partition is created.
create index idxpart_idx on idxpart (a);
select relhassubclass from pg_class where relname = 'idxpart_idx';
relhassubclass
----------------
f
(1 row)

-- Check that partitioned indexes are present in pg_indexes.
select indexdef from pg_indexes where indexname like 'idxpart_idx%';
                            indexdef
-----------------------------------------------------------------
CREATE INDEX idxpart_idx ON ONLY public.idxpart USING btree (a)
(1 row)

drop index idxpart_idx;
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);
-- Even with partitions, relhassubclass should not be set if a partitioned
-- index is created only on the parent.
create index idxpart_idx on only idxpart(a);
select relhassubclass from pg_class where relname = 'idxpart_idx';
relhassubclass
----------------
f
(1 row)

drop index idxpart_idx;
create index on idxpart (a);
select relname, relkind, relhassubclass, 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 | relhassubclass |   inhparent
-----------------+---------+----------------+----------------
idxpart         | p       | t              |
idxpart1        | r       | f              |
idxpart1_a_idx  | i       | f              | idxpart_a_idx
idxpart2        | p       | t              |
idxpart21       | r       | f              |
idxpart21_a_idx | i       | f              | idxpart2_a_idx
idxpart2_a_idx  | I       | t              | idxpart_a_idx
idxpart_a_idx   | I       | t              |
(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 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;
-- Verify bugfix with index rewrite on ALTER TABLE / SET DATA TYPE
-- https://postgr.es/m/CAKcux6mxNCGsgATwf5CGMF8g4WSupCXicCVMeKUTuWbyxHOMsQ@mail.gmail.com
CREATE TABLE idxpart (a INT, b TEXT, c INT) PARTITION BY RANGE(a);
CREATE TABLE idxpart1 PARTITION OF idxpart FOR VALUES FROM (MINVALUE) TO (MAXVALUE);
CREATE INDEX partidx_abc_idx ON idxpart (a, b, c);
INSERT INTO idxpart (a, b, c) SELECT i, i, i FROM generate_series(1, 50) i;
ALTER TABLE idxpart ALTER COLUMN c TYPE numeric;
DROP TABLE idxpart;
-- 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)

\d+ idxpart1_a_idx
                 Index "public.idxpart1_a_idx"
Column |  Type   | Key? | Definition | Storage | Stats target
--------+---------+------+------------+---------+--------------
a      | integer | yes  | a          | plain   |
Partition of: idxparti
No partition constraint
btree, for table "public.idxpart1"

\d+ idxpart1_b_c_idx
                Index "public.idxpart1_b_c_idx"
Column |  Type   | Key? | Definition | Storage  | Stats target
--------+---------+------+------------+----------+--------------
b      | integer | yes  | b          | plain    |
c      | text    | yes  | c          | extended |
Partition of: idxparti2
No partition constraint
btree, for table "public.idxpart1"

-- Forbid ALTER TABLE when attaching or detaching an index to a partition.
create index idxpart_c on only idxpart (c);
create index idxpart1_c on idxpart1 (c);
alter table idxpart_c attach partition idxpart1_c for values from (10) to (20);
ERROR:  "idxpart_c" is not a partitioned table
alter index idxpart_c attach partition idxpart1_c;
select relname, relpartbound from pg_class
  where relname in ('idxpart_c', 'idxpart1_c')
  order by relname;
  relname   | relpartbound
------------+--------------
idxpart1_c |
idxpart_c  |
(2 rows)

alter table idxpart_c detach partition idxpart1_c;
ERROR:  ALTER action DETACH PARTITION cannot be performed on relation "idxpart_c"
DETAIL:  This operation is not supported for partitioned indexes.
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, relhassubclass, 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 | relhassubclass |    inhparent
------------------+---------+----------------+-----------------
idxpart          | p       | t              |
idxpart1         | r       | f              |
idxpart1_a_b_idx | i       | f              | idxpart_a_b_idx
idxpart_a_b_idx  | I       | t              |
(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 concurrently idxpart_a_idx; -- unsupported
ERROR:  cannot drop partitioned index "idxpart_a_idx" concurrently
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;
-- DROP behavior with temporary partitioned indexes
create temp table idxpart_temp (a int) partition by range (a);
create index on idxpart_temp(a);
create temp table idxpart1_temp partition of idxpart_temp
  for values from (0) to (10);
drop index idxpart1_temp_a_idx; -- error
ERROR:  cannot drop index idxpart1_temp_a_idx because index idxpart_temp_a_idx requires it
HINT:  You can drop index idxpart_temp_a_idx instead.
-- non-concurrent drop is enforced here, so it is a valid case.
drop index concurrently idxpart_temp_a_idx;
select relname, relkind from pg_class
  where relname like 'idxpart_temp%' order by relname;
   relname    | relkind
--------------+---------
idxpart_temp | p
(1 row)

drop table idxpart_temp;
-- 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
        Partitioned 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
        Partitioned 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
        Partitioned 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, d bool) 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    |           |          |
d      | boolean |           |          |
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    |           |          |
d      | boolean |           |          |
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)

-- While here, also check matching when creating an index after the fact.
create index on idxpart1 ((a+b)) where d = true;
\d idxpart1
              Table "public.idxpart1"
Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a      | integer |           |          |
b      | integer |           |          |
c      | text    |           |          |
d      | boolean |           |          |
Partition of: idxpart FOR VALUES FROM (0) TO (10)
Indexes:
    "idxpart1_a_idx" btree (a)
    "idxpart1_b_c_idx" btree (b, c)
    "idxpart1_expr_idx" btree ((a + b)) WHERE d = true

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
idxpart1_expr_idx | i       |
idxparti          | I       |
idxparti2         | I       |
(7 rows)

create index idxparti3 on idxpart ((a+b)) where d = true;
\d idxpart1
              Table "public.idxpart1"
Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a      | integer |           |          |
b      | integer |           |          |
c      | text    |           |          |
d      | boolean |           |          |
Partition of: idxpart FOR VALUES FROM (0) TO (10)
Indexes:
    "idxpart1_a_idx" btree (a)
    "idxpart1_b_c_idx" btree (b, c)
    "idxpart1_expr_idx" btree ((a + b)) WHERE d = true

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
idxpart1_expr_idx | i       | idxparti3
idxparti          | I       |
idxparti2         | I       |
idxparti3         | I       |
(8 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 that re-attaching an already-attached partition index can
-- validate the parent index if it was still invalid, including
-- indirect ancestors in subpartitions.
create table idxpart (a int, b int) partition by range (a);
create table idxpart1 partition of idxpart for values from (0) to (1000) partition by range (a);
create table idxpart11 partition of idxpart1 for values from (0) to (500);
-- Partitioned table with no partitions
create table idxpart2 partition of idxpart for values from (1000) to (2000) partition by range (a);
-- create parent indexes
create index on only idxpart ((a/b));
create index on only idxpart1 ((a/b));
create index on only idxpart2 ((a/b));
-- fail, leaves behind an invalid index on the leaf partition
insert into idxpart11 values (1, 0);
create index concurrently on idxpart11 ((a/b));
ERROR:  division by zero
select relname, indisvalid from pg_class join pg_index on indexrelid = oid
   where relname like 'idxpart%' order by relname;
      relname       | indisvalid
--------------------+------------
idxpart11_expr_idx | f
idxpart1_expr_idx  | f
idxpart2_expr_idx  | t
idxpart_expr_idx   | f
(4 rows)

-- attach the indexes; parents stay invalid
alter index idxpart1_expr_idx attach partition idxpart11_expr_idx;
alter index idxpart_expr_idx attach partition idxpart1_expr_idx;
alter index idxpart_expr_idx attach partition idxpart2_expr_idx;
select relname, indisvalid from pg_class join pg_index on indexrelid = oid
   where relname like 'idxpart%' order by relname;
      relname       | indisvalid
--------------------+------------
idxpart11_expr_idx | f
idxpart1_expr_idx  | f
idxpart2_expr_idx  | t
idxpart_expr_idx   | f
(4 rows)

-- fix the index on the leaf partition
delete from idxpart11 where b = 0;
reindex index concurrently idxpart11_expr_idx;
-- reattach the leaf partition index; parents should now be valid
alter index idxpart1_expr_idx attach partition idxpart11_expr_idx;
select relname, indisvalid from pg_class join pg_index on indexrelid = oid
   where relname like 'idxpart%' order by relname;
      relname       | indisvalid
--------------------+------------
idxpart11_expr_idx | t
idxpart1_expr_idx  | t
idxpart2_expr_idx  | t
idxpart_expr_idx   | t
(4 rows)

drop table idxpart;
-- Verify that re-attaching does not validate the parent when another
-- child index is still invalid.
create table idxpart (a int, b int) partition by range (a);
create table idxpart1 partition of idxpart for values from (0) to (500);
create table idxpart2 partition of idxpart for values from (500) to (1000);
create index on only idxpart ((a/b));
-- create invalid indexes on both children
insert into idxpart1 values (1, 0);
insert into idxpart2 values (501, 0);
create index concurrently on idxpart1 ((a/b));
ERROR:  division by zero
create index concurrently on idxpart2 ((a/b));
ERROR:  division by zero
select relname, indisvalid from pg_class join pg_index on indexrelid = oid
   where relname like 'idxpart%' order by relname;
      relname      | indisvalid
-------------------+------------
idxpart1_expr_idx | f
idxpart2_expr_idx | f
idxpart_expr_idx  | f
(3 rows)

-- attach both; parent stays invalid
alter index idxpart_expr_idx attach partition idxpart1_expr_idx;
alter index idxpart_expr_idx attach partition idxpart2_expr_idx;
select relname, indisvalid from pg_class join pg_index on indexrelid = oid
   where relname like 'idxpart%' order by relname;
      relname      | indisvalid
-------------------+------------
idxpart1_expr_idx | f
idxpart2_expr_idx | f
idxpart_expr_idx  | f
(3 rows)

-- fix only idxpart1's index, leave idxpart2's still invalid
delete from idxpart1 where b = 0;
reindex index concurrently idxpart1_expr_idx;
-- re-attach the fixed child; parent should stay invalid
alter index idxpart_expr_idx attach partition idxpart1_expr_idx;
select relname, indisvalid from pg_class join pg_index on indexrelid = oid
   where relname like 'idxpart%' order by relname;
      relname      | indisvalid
-------------------+------------
idxpart1_expr_idx | t
idxpart2_expr_idx | f
idxpart_expr_idx  | f
(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)

create table idxpart (a int, b int, c int) partition by range(a);
create index on idxpart(c);
create table idxpart1 partition of idxpart for values from (0) to (250);
create table idxpart2 partition of idxpart for values from (250) to (500);
alter table idxpart detach partition idxpart2;
\d idxpart2
              Table "public.idxpart2"
Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a      | integer |           |          |
b      | integer |           |          |
c      | integer |           |          |
Indexes:
    "idxpart2_c_idx" btree (c)

alter table idxpart2 drop column c;
\d idxpart2
              Table "public.idxpart2"
Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a      | integer |           |          |
b      | integer |           |          |

drop table idxpart, idxpart2;
-- 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 public.idxpart1 USING btree (((a + b)))
idxpart2_expr_idx | idxpart_expr_idx | CREATE INDEX idxpart2_expr_idx ON public.idxpart2 USING btree (((a + b)))
idxpart3_expr_idx | idxpart_expr_idx | CREATE INDEX idxpart3_expr_idx ON public.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 public.idxpart1 USING btree (a COLLATE "C")
idxpart2_a_idx  |               | CREATE INDEX idxpart2_a_idx ON public.idxpart2 USING btree (a COLLATE "POSIX")
idxpart2_a_idx1 |               | CREATE INDEX idxpart2_a_idx1 ON public.idxpart2 USING btree (a)
idxpart2_a_idx2 | idxpart_a_idx | CREATE INDEX idxpart2_a_idx2 ON public.idxpart2 USING btree (a COLLATE "C")
idxpart3_a_idx  | idxpart_a_idx | CREATE INDEX idxpart3_a_idx ON public.idxpart3 USING btree (a COLLATE "C")
idxpart4_a_idx  | idxpart_a_idx | CREATE INDEX idxpart4_a_idx ON public.idxpart4 USING btree (a COLLATE "C")
idxpart_a_idx   |               | CREATE INDEX idxpart_a_idx ON ONLY public.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 public.idxpart1 USING btree (a text_pattern_ops)
idxpart2_a_idx  |               | CREATE INDEX idxpart2_a_idx ON public.idxpart2 USING btree (a)
idxpart2_a_idx1 | idxpart_a_idx | CREATE INDEX idxpart2_a_idx1 ON public.idxpart2 USING btree (a text_pattern_ops)
idxpart3_a_idx  | idxpart_a_idx | CREATE INDEX idxpart3_a_idx ON public.idxpart3 USING btree (a text_pattern_ops)
idxpart4_a_idx  | idxpart_a_idx | CREATE INDEX idxpart4_a_idx ON public.idxpart4 USING btree (a text_pattern_ops)
idxpart_a_idx   |               | CREATE INDEX idxpart_a_idx ON ONLY public.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 public.idxpart1 USING btree (b, a)
idxpart1_1b_idx |               | CREATE INDEX idxpart1_1b_idx ON public.idxpart1 USING btree (b)
idxpart1_2_idx  | idxpart_2_idx | CREATE INDEX idxpart1_2_idx ON public.idxpart1 USING btree (((b + a))) WHERE (a > 1)
idxpart1_2b_idx |               | CREATE INDEX idxpart1_2b_idx ON public.idxpart1 USING btree (((a + b))) WHERE (a > 1)
idxpart1_2c_idx |               | CREATE INDEX idxpart1_2c_idx ON public.idxpart1 USING btree (((b + a))) WHERE (b > 1)
idxpart_1_idx   |               | CREATE INDEX idxpart_1_idx ON ONLY public.idxpart USING btree (b, a)
idxpart_2_idx   |               | CREATE INDEX idxpart_2_idx ON ONLY public.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 public.idxpart1 USING btree (a)
idxpart1_c_b_idx | CREATE INDEX idxpart1_c_b_idx ON public.idxpart1 USING btree (c, b)
idxpart2_a_idx   | CREATE INDEX idxpart2_a_idx ON public.idxpart2 USING btree (a)
idxpart2_c_b_idx | CREATE INDEX idxpart2_c_b_idx ON public.idxpart2 USING btree (c, b)
idxparti         | CREATE INDEX idxparti ON ONLY public.idxpart USING btree (a)
idxparti2        | CREATE INDEX idxparti2 ON ONLY public.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));
create index on idxpart ((b + 1));
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 public.idxpart1 USING btree (abs(b))
idxpart1_expr_idx | CREATE INDEX idxpart1_expr_idx ON public.idxpart1 USING btree (((b + 1)))
idxpart2_abs_idx  | CREATE INDEX idxpart2_abs_idx ON public.idxpart2 USING btree (abs(b))
idxpart2_expr_idx | CREATE INDEX idxpart2_expr_idx ON public.idxpart2 USING btree (((b + 1)))
idxpart_abs_idx   | CREATE INDEX idxpart_abs_idx ON ONLY public.idxpart USING btree (abs(b))
idxpart_expr_idx  | CREATE INDEX idxpart_expr_idx ON ONLY public.idxpart USING btree (((b + 1)))
(6 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 public.idxpart1 USING btree (a) WHERE (b > 1000)
idxpart2_a_idx | CREATE INDEX idxpart2_a_idx ON public.idxpart2 USING btree (a) WHERE (b > 1000)
idxpart_a_idx  | CREATE INDEX idxpart_a_idx ON ONLY public.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
         Partitioned 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
         Partitioned 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;
--
-- Constraint-related indexes
--
-- Verify that it works to add primary key / unique to partitioned tables
create table idxpart (a int primary key, b int) partition by range (a);
\d idxpart
        Partitioned table "public.idxpart"
Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a      | integer |           | not null |
b      | integer |           |          |
Partition key: RANGE (a)
Indexes:
    "idxpart_pkey" PRIMARY KEY, btree (a)
Number of partitions: 0

-- multiple primary key on child should fail
create table failpart partition of idxpart (b primary key) for values from (0) to (100);
ERROR:  multiple primary keys for table "failpart" are not allowed
drop table idxpart;
-- primary key on child is okay if there's no PK in the parent, though
create table idxpart (a int) partition by range (a);
create table idxpart1pk partition of idxpart (a primary key) for values from (0) to (100);
\d idxpart1pk
             Table "public.idxpart1pk"
Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a      | integer |           | not null |
Partition of: idxpart FOR VALUES FROM (0) TO (100)
Indexes:
    "idxpart1pk_pkey" PRIMARY KEY, btree (a)

drop table idxpart;
-- Failing to use the full partition key is not allowed
create table idxpart (a int unique, b int) partition by range (a, b);
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  UNIQUE constraint on table "idxpart" lacks column "b" which is part of the partition key.
create table idxpart (a int, b int unique) partition by range (a, b);
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  UNIQUE constraint on table "idxpart" lacks column "a" which is part of the partition key.
create table idxpart (a int primary key, b int) partition by range (b, a);
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "idxpart" lacks column "b" which is part of the partition key.
create table idxpart (a int, b int primary key) partition by range (b, a);
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "idxpart" lacks column "a" which is part of the partition key.
-- OK if you use them in some other order
create table idxpart (a int, b int, c text, primary key  (a, b, c)) partition by range (b, c, a);
drop table idxpart;
-- OK to add an exclusion constraint if partitioning by its equal column
create table idxpart (a int4range, exclude USING GIST (a with = )) partition by range (a);
drop table idxpart;
-- OK more than one equal column
create table idxpart (a int4range, b int4range, exclude USING GIST (a with =, b with =)) partition by range (a, b);
drop table idxpart;
-- OK with more than one equal column: constraint is a proper superset of partition key
create table idxpart (a int4range, b int4range, exclude USING GIST (a with =, b with =)) partition by range (a);
drop table idxpart;
-- Not OK more than one equal column: partition keys are a proper superset of constraint
create table idxpart (a int4range, b int4range, exclude USING GIST (a with = )) partition by range (a, b);
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  EXCLUDE constraint on table "idxpart" lacks column "b" which is part of the partition key.
-- Not OK with just -|-
create table idxpart (a int4range, exclude USING GIST (a with -|- )) partition by range (a);
ERROR:  cannot match partition key to index on column "a" using non-equal operator "-|-"
-- OK with equals and &&, and equals is the partition key
create table idxpart (a int4range, b int4range, exclude USING GIST (a with =, b with &&)) partition by range (a);
drop table idxpart;
-- Not OK with equals and &&, and equals is not the partition key
create table idxpart (a int4range, b int4range, c int4range, exclude USING GIST (b with =, c with &&)) partition by range (a);
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  EXCLUDE constraint on table "idxpart" lacks column "a" which is part of the partition key.
-- OK more than one equal column and a && column
create table idxpart (a int4range, b int4range, c int4range, exclude USING GIST (a with =, b with =, c with &&)) partition by range (a, b);
drop table idxpart;
-- no expressions in partition key for PK/UNIQUE
create table idxpart (a int primary key, b int) partition by range ((b + a));
ERROR:  unsupported PRIMARY KEY constraint with partition key definition
DETAIL:  PRIMARY KEY constraints cannot be used when partition keys include expressions.
create table idxpart (a int unique, b int) partition by range ((b + a));
ERROR:  unsupported UNIQUE constraint with partition key definition
DETAIL:  UNIQUE constraints cannot be used when partition keys include expressions.
-- use ALTER TABLE to add a primary key
create table idxpart (a int, b int, c text) partition by range (a, b);
alter table idxpart add primary key (a); -- not an incomplete one though
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "idxpart" lacks column "b" which is part of the partition key.
alter table idxpart add primary key (a, b); -- this works
\d idxpart
        Partitioned table "public.idxpart"
Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a      | integer |           | not null |
b      | integer |           | not null |
c      | text    |           |          |
Partition key: RANGE (a, b)
Indexes:
    "idxpart_pkey" PRIMARY KEY, btree (a, b)
Number of partitions: 0

create table idxpart1 partition of idxpart for values from (0, 0) to (1000, 1000);
\d idxpart1
              Table "public.idxpart1"
Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a      | integer |           | not null |
b      | integer |           | not null |
c      | text    |           |          |
Partition of: idxpart FOR VALUES FROM (0, 0) TO (1000, 1000)
Indexes:
    "idxpart1_pkey" PRIMARY KEY, btree (a, b)

drop table idxpart;
-- use ALTER TABLE to add a unique constraint
create table idxpart (a int, b int) partition by range (a, b);
alter table idxpart add unique (a);   -- not an incomplete one though
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  UNIQUE constraint on table "idxpart" lacks column "b" which is part of the partition key.
alter table idxpart add unique (b, a);  -- this works
\d idxpart
        Partitioned table "public.idxpart"
Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a      | integer |           |          |
b      | integer |           |          |
Partition key: RANGE (a, b)
Indexes:
    "idxpart_b_a_key" UNIQUE CONSTRAINT, btree (b, a)
Number of partitions: 0

drop table idxpart;
-- Exclusion constraints can be added if partitioning by their equal column
create table idxpart (a int4range, b int4range) partition by range (a);
alter table idxpart add exclude USING GIST (a with =);
drop table idxpart;
-- OK more than one equal column
create table idxpart (a int4range, b int4range) partition by range (a, b);
alter table idxpart add exclude USING GIST (a with =, b with =);
drop table idxpart;
-- OK with more than one equal column: constraint is a proper superset of partition key
create table idxpart (a int4range, b int4range) partition by range (a);
alter table idxpart add exclude USING GIST (a with =, b with =);
drop table idxpart;
-- Not OK more than one equal column: partition keys are a proper superset of constraint
create table idxpart (a int4range, b int4range) partition by range (a, b);
alter table idxpart add exclude USING GIST (a with =);
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  EXCLUDE constraint on table "idxpart" lacks column "b" which is part of the partition key.
drop table idxpart;
-- Not OK with just -|-
create table idxpart (a int4range, b int4range) partition by range (a, b);
alter table idxpart add exclude USING GIST (a with -|-);
ERROR:  cannot match partition key to index on column "a" using non-equal operator "-|-"
drop table idxpart;
-- OK with equals and &&, and equals is the partition key
create table idxpart (a int4range, b int4range) partition by range (a);
alter table idxpart add exclude USING GIST (a with =, b with &&);
drop table idxpart;
-- Not OK with equals and &&, and equals is not the partition key
create table idxpart (a int4range, b int4range, c int4range) partition by range (a);
alter table idxpart add exclude USING GIST (b with =, c with &&);
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  EXCLUDE constraint on table "idxpart" lacks column "a" which is part of the partition key.
drop table idxpart;
-- OK more than one equal column and a && column
create table idxpart (a int4range, b int4range, c int4range) partition by range (a, b);
alter table idxpart add exclude USING GIST (a with =, b with =, c with &&);
drop table idxpart;
-- When (sub)partitions are created, they also contain the constraint
create table idxpart (a int, b int, primary key (a, b)) partition by range (a, b);
create table idxpart1 partition of idxpart for values from (1, 1) to (10, 10);
create table idxpart2 partition of idxpart for values from (10, 10) to (20, 20)
  partition by range (b);
create table idxpart21 partition of idxpart2 for values from (10) to (15);
create table idxpart22 partition of idxpart2 for values from (15) to (20);
create table idxpart3 (b int not null, a int not null);
alter table idxpart attach partition idxpart3 for values from (20, 20) to (30, 30);
select conname, contype, conrelid::regclass, conindid::regclass, conkey
  from pg_constraint where conrelid::regclass::text like 'idxpart%'
  order by conrelid::regclass::text, conname;
       conname       | contype | conrelid  |    conindid    | conkey
---------------------+---------+-----------+----------------+--------
idxpart_a_not_null  | n       | idxpart   | -              | {1}
idxpart_b_not_null  | n       | idxpart   | -              | {2}
idxpart_pkey        | p       | idxpart   | idxpart_pkey   | {1,2}
idxpart1_pkey       | p       | idxpart1  | idxpart1_pkey  | {1,2}
idxpart_a_not_null  | n       | idxpart1  | -              | {1}
idxpart_b_not_null  | n       | idxpart1  | -              | {2}
idxpart2_pkey       | p       | idxpart2  | idxpart2_pkey  | {1,2}
idxpart_a_not_null  | n       | idxpart2  | -              | {1}
idxpart_b_not_null  | n       | idxpart2  | -              | {2}
idxpart21_pkey      | p       | idxpart21 | idxpart21_pkey | {1,2}
idxpart_a_not_null  | n       | idxpart21 | -              | {1}
idxpart_b_not_null  | n       | idxpart21 | -              | {2}
idxpart22_pkey      | p       | idxpart22 | idxpart22_pkey | {1,2}
idxpart_a_not_null  | n       | idxpart22 | -              | {1}
idxpart_b_not_null  | n       | idxpart22 | -              | {2}
idxpart3_a_not_null | n       | idxpart3  | -              | {2}
idxpart3_b_not_null | n       | idxpart3  | -              | {1}
idxpart3_pkey       | p       | idxpart3  | idxpart3_pkey  | {2,1}
(18 rows)

drop table idxpart;
-- Verify that multi-layer partitioning honors the requirement that all
-- columns in the partition key must appear in primary/unique key
create table idxpart (a int, b int, primary key (a)) partition by range (a);
create table idxpart2 partition of idxpart
for values from (0) to (1000) partition by range (b); -- fail
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "idxpart2" lacks column "b" which is part of the partition key.
drop table idxpart;
-- Ditto for the ATTACH PARTITION case
create table idxpart (a int unique, b int) partition by range (a);
create table idxpart1 (a int not null, b int, unique (a, b))
  partition by range (a, b);
alter table idxpart attach partition idxpart1 for values from (1) to (1000);
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  UNIQUE constraint on table "idxpart1" lacks column "b" which is part of the partition key.
DROP TABLE idxpart, idxpart1;
-- Multi-layer partitioning works correctly in this case:
create table idxpart (a int, b int, primary key (a, b)) partition by range (a);
create table idxpart2 partition of idxpart for values from (0) to (1000) partition by range (b);
create table idxpart21 partition of idxpart2 for values from (0) to (1000);
select conname, contype, conrelid::regclass, conindid::regclass, conkey
  from pg_constraint where conrelid::regclass::text like 'idxpart%'
  order by conrelid::regclass::text, conname;
      conname       | contype | conrelid  |    conindid    | conkey
--------------------+---------+-----------+----------------+--------
idxpart_a_not_null | n       | idxpart   | -              | {1}
idxpart_b_not_null | n       | idxpart   | -              | {2}
idxpart_pkey       | p       | idxpart   | idxpart_pkey   | {1,2}
idxpart2_pkey      | p       | idxpart2  | idxpart2_pkey  | {1,2}
idxpart_a_not_null | n       | idxpart2  | -              | {1}
idxpart_b_not_null | n       | idxpart2  | -              | {2}
idxpart21_pkey     | p       | idxpart21 | idxpart21_pkey | {1,2}
idxpart_a_not_null | n       | idxpart21 | -              | {1}
idxpart_b_not_null | n       | idxpart21 | -              | {2}
(9 rows)

drop table idxpart;
-- If a partitioned table has a unique/PK constraint, then it's not possible
-- to drop the corresponding constraint in the children; nor it's possible
-- to drop the indexes individually.  Dropping the constraint in the parent
-- gets rid of the lot.
create table idxpart (i int) partition by hash (i);
create table idxpart0 partition of idxpart (i) for values with (modulus 2, remainder 0);
create table idxpart1 partition of idxpart (i) for values with (modulus 2, remainder 1);
alter table idxpart0 add primary key(i);
alter table idxpart add primary key(i);
select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,
  conname, conislocal, coninhcount, connoinherit, convalidated
  from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)
  left join pg_constraint con on (idx.indexrelid = con.conindid)
  where indrelid::regclass::text like 'idxpart%'
  order by indexrelid::regclass::text collate "C";
indrelid |  indexrelid   |  inhparent   | indisvalid |    conname    | conislocal | coninhcount | connoinherit | convalidated
----------+---------------+--------------+------------+---------------+------------+-------------+--------------+--------------
idxpart0 | idxpart0_pkey | idxpart_pkey | t          | idxpart0_pkey | f          |           1 | t            | t
idxpart1 | idxpart1_pkey | idxpart_pkey | t          | idxpart1_pkey | f          |           1 | f            | t
idxpart  | idxpart_pkey  |              | t          | idxpart_pkey  | t          |           0 | t            | t
(3 rows)

drop index idxpart0_pkey;        -- fail
ERROR:  cannot drop index idxpart0_pkey because index idxpart_pkey requires it
HINT:  You can drop index idxpart_pkey instead.
drop index idxpart1_pkey;        -- fail
ERROR:  cannot drop index idxpart1_pkey because index idxpart_pkey requires it
HINT:  You can drop index idxpart_pkey instead.
alter table idxpart0 drop constraint idxpart0_pkey;  -- fail
ERROR:  cannot drop inherited constraint "idxpart0_pkey" of relation "idxpart0"
alter table idxpart1 drop constraint idxpart1_pkey;  -- fail
ERROR:  cannot drop inherited constraint "idxpart1_pkey" of relation "idxpart1"
alter table idxpart drop constraint idxpart_pkey;  -- ok
select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,
  conname, conislocal, coninhcount, connoinherit, convalidated
  from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)
  left join pg_constraint con on (idx.indexrelid = con.conindid)
  where indrelid::regclass::text like 'idxpart%'
  order by indexrelid::regclass::text collate "C";
indrelid | indexrelid | inhparent | indisvalid | conname | conislocal | coninhcount | connoinherit | convalidated
----------+------------+-----------+------------+---------+------------+-------------+--------------+--------------
(0 rows)

drop table idxpart;
-- If the partition to be attached already has a primary key, fail if
-- it doesn't match the parent's PK.
CREATE TABLE idxpart (c1 INT PRIMARY KEY, c2 INT, c3 VARCHAR(10)) PARTITION BY RANGE(c1);
CREATE TABLE idxpart1 (LIKE idxpart);
ALTER TABLE idxpart1 ADD PRIMARY KEY (c1, c2);
ALTER TABLE idxpart ATTACH PARTITION idxpart1 FOR VALUES FROM (100) TO (200);
ERROR:  multiple primary keys for table "idxpart1" are not allowed
DROP TABLE idxpart, idxpart1;
-- Ditto if there is some distance between the PKs (subpartitioning)
create table idxpart (a int, b int, primary key (a)) partition by range (a);
create table idxpart1 (a int not null, b int) partition by range (a);
create table idxpart11 (a int not null, b int primary key);
alter table idxpart1 attach partition idxpart11 for values from (0) to (1000);
alter table idxpart attach partition idxpart1 for values from (0) to (10000);
ERROR:  multiple primary keys for table "idxpart11" are not allowed
drop table idxpart, idxpart1, idxpart11;
-- If a partitioned table has a constraint whose index is not valid,
-- attaching a missing partition makes it valid.
create table idxpart (a int) partition by range (a);
create table idxpart0 (like idxpart);
alter table idxpart0 add primary key (a);
alter table idxpart attach partition idxpart0 for values from (0) to (1000);
alter table only idxpart add primary key (a);
select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,
  conname, conislocal, coninhcount, connoinherit, convalidated
  from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)
  left join pg_constraint con on (idx.indexrelid = con.conindid)
  where indrelid::regclass::text like 'idxpart%'
  order by indexrelid::regclass::text collate "C";
indrelid |  indexrelid   | inhparent | indisvalid |    conname    | conislocal | coninhcount | connoinherit | convalidated
----------+---------------+-----------+------------+---------------+------------+-------------+--------------+--------------
idxpart0 | idxpart0_pkey |           | t          | idxpart0_pkey | t          |           0 | t            | t
idxpart  | idxpart_pkey  |           | f          | idxpart_pkey  | t          |           0 | t            | t
(2 rows)

alter index idxpart_pkey attach partition idxpart0_pkey;
select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,
  conname, conislocal, coninhcount, connoinherit, convalidated
  from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)
  left join pg_constraint con on (idx.indexrelid = con.conindid)
  where indrelid::regclass::text like 'idxpart%'
  order by indexrelid::regclass::text collate "C";
indrelid |  indexrelid   |  inhparent   | indisvalid |    conname    | conislocal | coninhcount | connoinherit | convalidated
----------+---------------+--------------+------------+---------------+------------+-------------+--------------+--------------
idxpart0 | idxpart0_pkey | idxpart_pkey | t          | idxpart0_pkey | f          |           1 | t            | t
idxpart  | idxpart_pkey  |              | t          | idxpart_pkey  | t          |           0 | t            | t
(2 rows)

drop table idxpart;
-- Related to the above scenario: ADD PRIMARY KEY on the parent mustn't
-- automatically propagate NOT NULL to child columns.
create table idxpart (a int) partition by range (a);
create table idxpart0 (like idxpart);
alter table idxpart0 add unique (a);
alter table idxpart attach partition idxpart0 default;
alter table only idxpart add primary key (a);  -- fail, no not-null constraint
ERROR:  column "a" of table "idxpart0" is not marked NOT NULL
alter table idxpart0 alter column a set not null;
alter table only idxpart add primary key (a);  -- now it works
alter index idxpart_pkey attach partition idxpart0_a_key;
drop table idxpart;
-- if a partition has a unique index without a constraint, does not attach
-- automatically; creates a new index instead.
create table idxpart (a int, b int) partition by range (a);
create table idxpart1 (a int not null, b int);
create unique index on idxpart1 (a);
alter table idxpart add primary key (a);
alter table idxpart attach partition idxpart1 for values from (1) to (1000);
select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,
  conname, conislocal, coninhcount, connoinherit, convalidated
  from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)
  left join pg_constraint con on (idx.indexrelid = con.conindid)
  where indrelid::regclass::text like 'idxpart%'
  order by indexrelid::regclass::text collate "C";
indrelid |   indexrelid   |  inhparent   | indisvalid |    conname    | conislocal | coninhcount | connoinherit | convalidated
----------+----------------+--------------+------------+---------------+------------+-------------+--------------+--------------
idxpart1 | idxpart1_a_idx |              | t          |               |            |             |              |
idxpart1 | idxpart1_pkey  | idxpart_pkey | t          | idxpart1_pkey | f          |           1 | f            | t
idxpart  | idxpart_pkey   |              | t          | idxpart_pkey  | t          |           0 | t            | t
(3 rows)

drop table idxpart;
-- Can't attach an index without a corresponding constraint
create table idxpart (a int, b int) partition by range (a);
create table idxpart1 (a int not null, b int);
create unique index on idxpart1 (a);
alter table idxpart attach partition idxpart1 for values from (1) to (1000);
alter table only idxpart add primary key (a);
alter index idxpart_pkey attach partition idxpart1_a_idx; -- fail
ERROR:  cannot attach index "idxpart1_a_idx" as a partition of index "idxpart_pkey"
DETAIL:  The index "idxpart_pkey" belongs to a constraint in table "idxpart" but no constraint exists for index "idxpart1_a_idx".
drop table idxpart;
-- Test that unique constraints are working
create table idxpart (a int, b text, primary key (a, b)) partition by range (a);
create table idxpart1 partition of idxpart for values from (0) to (100000);
create table idxpart2 (c int, like idxpart);
insert into idxpart2 (c, a, b) values (42, 572814, 'inserted first');
alter table idxpart2 drop column c;
create unique index on idxpart (a);
alter table idxpart attach partition idxpart2 for values from (100000) to (1000000);
insert into idxpart values (0, 'zero'), (42, 'life'), (2^16, 'sixteen');
insert into idxpart select 2^g, format('two to power of %s', g) from generate_series(15, 17) g;
ERROR:  duplicate key value violates unique constraint "idxpart1_a_idx"
DETAIL:  Key (a)=(65536) already exists.
insert into idxpart values (16, 'sixteen');
insert into idxpart (b, a) values ('one', 142857), ('two', 285714);
insert into idxpart select a * 2, b || b from idxpart where a between 2^16 and 2^19;
ERROR:  duplicate key value violates unique constraint "idxpart2_a_idx"
DETAIL:  Key (a)=(285714) already exists.
insert into idxpart values (572814, 'five');
ERROR:  duplicate key value violates unique constraint "idxpart2_a_idx"
DETAIL:  Key (a)=(572814) already exists.
insert into idxpart values (857142, 'six');
select tableoid::regclass, * from idxpart order by a;
tableoid |   a    |       b
----------+--------+----------------
idxpart1 |      0 | zero
idxpart1 |     16 | sixteen
idxpart1 |     42 | life
idxpart1 |  65536 | sixteen
idxpart2 | 142857 | one
idxpart2 | 285714 | two
idxpart2 | 572814 | inserted first
idxpart2 | 857142 | six
(8 rows)

drop table idxpart;
-- Test some other non-btree index types
create table idxpart (a int, b text, c int[]) partition by range (a);
create table idxpart1 partition of idxpart for values from (0) to (100000);
set enable_seqscan to off;
create index idxpart_brin on idxpart using brin(b);
explain (costs off) select * from idxpart where b = 'abcd';
                QUERY PLAN
-------------------------------------------
Bitmap Heap Scan on idxpart1 idxpart
   Recheck Cond: (b = 'abcd'::text)
   ->  Bitmap Index Scan on idxpart1_b_idx
         Index Cond: (b = 'abcd'::text)
(4 rows)

drop index idxpart_brin;
create index idxpart_spgist on idxpart using spgist(b);
explain (costs off) select * from idxpart where b = 'abcd';
                QUERY PLAN
-------------------------------------------
Bitmap Heap Scan on idxpart1 idxpart
   Recheck Cond: (b = 'abcd'::text)
   ->  Bitmap Index Scan on idxpart1_b_idx
         Index Cond: (b = 'abcd'::text)
(4 rows)

drop index idxpart_spgist;
create index idxpart_gin on idxpart using gin(c);
explain (costs off) select * from idxpart where c @> array[42];
                  QUERY PLAN
----------------------------------------------
Bitmap Heap Scan on idxpart1 idxpart
   Recheck Cond: (c @> '{42}'::integer[])
   ->  Bitmap Index Scan on idxpart1_c_idx
         Index Cond: (c @> '{42}'::integer[])
(4 rows)

drop index idxpart_gin;
reset enable_seqscan;
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);
create table idxpart_another (a int, b int, primary key (a, b)) partition by range (a);
create table idxpart_another_1 partition of idxpart_another for values from (0) to (100);
create table idxpart3 (c int, b int, a int) partition by range (a);
alter table idxpart3 drop column b, drop column c;
create table idxpart31 partition of idxpart3 for values from (1000) to (1200);
create table idxpart32 partition of idxpart3 for values from (1200) to (1400);
alter table idxpart attach partition idxpart3 for values from (1000) to (2000);
-- More objects intentionally left behind, to verify some pg_dump/pg_upgrade
-- behavior; see https://postgr.es/m/20190321204928.GA17535@alvherre.pgsql
create schema regress_indexing;
set search_path to regress_indexing;
create table pk (a int primary key) partition by range (a);
create table pk1 partition of pk for values from (0) to (1000);
create table pk2 (b int, a int);
alter table pk2 drop column b;
alter table pk2 alter a set not null;
alter table pk attach partition pk2 for values from (1000) to (2000);
create table pk3 partition of pk for values from (2000) to (3000);
create table pk4 (like pk);
alter table pk attach partition pk4 for values from (3000) to (4000);
create table pk5 (like pk) partition by range (a);
create table pk51 partition of pk5 for values from (4000) to (4500);
create table pk52 partition of pk5 for values from (4500) to (5000);
alter table pk attach partition pk5 for values from (4000) to (5000);
reset search_path;
-- Test that covering partitioned indexes work in various cases
create table covidxpart (a int, b int) partition by list (a);
create unique index on covidxpart (a) include (b);
create table covidxpart1 partition of covidxpart for values in (1);
create table covidxpart2 partition of covidxpart for values in (2);
insert into covidxpart values (1, 1);
insert into covidxpart values (1, 1);
ERROR:  duplicate key value violates unique constraint "covidxpart1_a_b_idx"
DETAIL:  Key (a)=(1) already exists.
create table covidxpart3 (b int, c int, a int);
alter table covidxpart3 drop c;
alter table covidxpart attach partition covidxpart3 for values in (3);
insert into covidxpart values (3, 1);
insert into covidxpart values (3, 1);
ERROR:  duplicate key value violates unique constraint "covidxpart3_a_b_idx"
DETAIL:  Key (a)=(3) already exists.
create table covidxpart4 (b int, a int);
create unique index on covidxpart4 (a) include (b);
create unique index on covidxpart4 (a);
alter table covidxpart attach partition covidxpart4 for values in (4);
insert into covidxpart values (4, 1);
insert into covidxpart values (4, 1);
ERROR:  duplicate key value violates unique constraint "covidxpart4_a_b_idx"
DETAIL:  Key (a)=(4) already exists.
create unique index on covidxpart (b) include (a); -- should fail
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  UNIQUE constraint on table "covidxpart" lacks column "a" which is part of the partition key.
-- check that detaching a partition also detaches the primary key constraint
create table parted_pk_detach_test (a int primary key) partition by list (a);
create table parted_pk_detach_test1 partition of parted_pk_detach_test for values in (1);
alter table parted_pk_detach_test1 drop constraint parted_pk_detach_test1_pkey; -- should fail
ERROR:  cannot drop inherited constraint "parted_pk_detach_test1_pkey" of relation "parted_pk_detach_test1"
alter table parted_pk_detach_test detach partition parted_pk_detach_test1;
alter table parted_pk_detach_test1 drop constraint parted_pk_detach_test1_pkey;
drop table parted_pk_detach_test, parted_pk_detach_test1;
create table parted_uniq_detach_test (a int unique) partition by list (a);
create table parted_uniq_detach_test1 partition of parted_uniq_detach_test for values in (1);
alter table parted_uniq_detach_test1 drop constraint parted_uniq_detach_test1_a_key; -- should fail
ERROR:  cannot drop inherited constraint "parted_uniq_detach_test1_a_key" of relation "parted_uniq_detach_test1"
alter table parted_uniq_detach_test detach partition parted_uniq_detach_test1;
alter table parted_uniq_detach_test1 drop constraint parted_uniq_detach_test1_a_key;
drop table parted_uniq_detach_test, parted_uniq_detach_test1;
-- check that dropping a column takes with it any partitioned indexes
-- depending on it.
create table parted_index_col_drop(a int, b int, c int)
  partition by list (a);
create table parted_index_col_drop1 partition of parted_index_col_drop
  for values in (1) partition by list (a);
-- leave this partition without children.
create table parted_index_col_drop2 partition of parted_index_col_drop
  for values in (2) partition by list (a);
create table parted_index_col_drop11 partition of parted_index_col_drop1
  for values in (1);
create index on parted_index_col_drop (b);
create index on parted_index_col_drop (c);
create index on parted_index_col_drop (b, c);
alter table parted_index_col_drop drop column c;
\d parted_index_col_drop
Partitioned table "public.parted_index_col_drop"
Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a      | integer |           |          |
b      | integer |           |          |
Partition key: LIST (a)
Indexes:
    "parted_index_col_drop_b_idx" btree (b)
Number of partitions: 2 (Use \d+ to list them.)

\d parted_index_col_drop1
Partitioned table "public.parted_index_col_drop1"
Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a      | integer |           |          |
b      | integer |           |          |
Partition of: parted_index_col_drop FOR VALUES IN (1)
Partition key: LIST (a)
Indexes:
    "parted_index_col_drop1_b_idx" btree (b)
Number of partitions: 1 (Use \d+ to list them.)

\d parted_index_col_drop2
Partitioned table "public.parted_index_col_drop2"
Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a      | integer |           |          |
b      | integer |           |          |
Partition of: parted_index_col_drop FOR VALUES IN (2)
Partition key: LIST (a)
Indexes:
    "parted_index_col_drop2_b_idx" btree (b)
Number of partitions: 0

\d parted_index_col_drop11
      Table "public.parted_index_col_drop11"
Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a      | integer |           |          |
b      | integer |           |          |
Partition of: parted_index_col_drop1 FOR VALUES IN (1)
Indexes:
    "parted_index_col_drop11_b_idx" btree (b)

drop table parted_index_col_drop;
-- Check that invalid indexes are not selected when attaching a partition.
create table parted_inval_tab (a int) partition by range (a);
create index parted_inval_idx on parted_inval_tab (a);
create table parted_inval_tab_1 (a int) partition by range (a);
create table parted_inval_tab_1_1 partition of parted_inval_tab_1
  for values from (0) to (10);
create table parted_inval_tab_1_2 partition of parted_inval_tab_1
  for values from (10) to (20);
-- this creates an invalid index.
create index parted_inval_ixd_1 on only parted_inval_tab_1 (a);
-- this creates new indexes for all the partitions of parted_inval_tab_1,
-- discarding the invalid index created previously as what is chosen.
alter table parted_inval_tab attach partition parted_inval_tab_1
  for values from (1) to (100);
select indexrelid::regclass, indisvalid,
       indrelid::regclass, inhparent::regclass
  from pg_index idx left join
       pg_inherits inh on (idx.indexrelid = inh.inhrelid)
  where indexrelid::regclass::text like 'parted_inval%'
  order by indexrelid::regclass::text collate "C";
         indexrelid         | indisvalid |       indrelid       |        inhparent
----------------------------+------------+----------------------+--------------------------
parted_inval_idx           | t          | parted_inval_tab     |
parted_inval_ixd_1         | f          | parted_inval_tab_1   |
parted_inval_tab_1_1_a_idx | t          | parted_inval_tab_1_1 | parted_inval_tab_1_a_idx
parted_inval_tab_1_2_a_idx | t          | parted_inval_tab_1_2 | parted_inval_tab_1_a_idx
parted_inval_tab_1_a_idx   | t          | parted_inval_tab_1   | parted_inval_idx
(5 rows)

drop table parted_inval_tab;
-- Check setup of indisvalid across a complex partition tree on index
-- creation.  If one index in a partition index is invalid, so should its
-- partitioned index.
create table parted_isvalid_tab (a int, b int) partition by range (a);
create table parted_isvalid_tab_1 partition of parted_isvalid_tab
  for values from (1) to (10) partition by range (a);
create table parted_isvalid_tab_2 partition of parted_isvalid_tab
  for values from (10) to (20) partition by range (a);
create table parted_isvalid_tab_11 partition of parted_isvalid_tab_1
  for values from (1) to (5);
create table parted_isvalid_tab_12 partition of parted_isvalid_tab_1
  for values from (5) to (10);
-- create an invalid index on one of the partitions.
insert into parted_isvalid_tab_11 values (1, 0);
create index concurrently parted_isvalid_idx_11 on parted_isvalid_tab_11 ((a/b));
ERROR:  division by zero
-- The previous invalid index is selected, invalidating all the indexes up to
-- the top-most parent.
create index parted_isvalid_idx on parted_isvalid_tab ((a/b));
select indexrelid::regclass, indisvalid,
       indrelid::regclass, inhparent::regclass
  from pg_index idx left join
       pg_inherits inh on (idx.indexrelid = inh.inhrelid)
  where indexrelid::regclass::text like 'parted_isvalid%'
  order by indexrelid::regclass::text collate "C";
           indexrelid           | indisvalid |       indrelid        |           inhparent
--------------------------------+------------+-----------------------+-------------------------------
parted_isvalid_idx             | f          | parted_isvalid_tab    |
parted_isvalid_idx_11          | f          | parted_isvalid_tab_11 | parted_isvalid_tab_1_expr_idx
parted_isvalid_tab_12_expr_idx | t          | parted_isvalid_tab_12 | parted_isvalid_tab_1_expr_idx
parted_isvalid_tab_1_expr_idx  | f          | parted_isvalid_tab_1  | parted_isvalid_idx
parted_isvalid_tab_2_expr_idx  | t          | parted_isvalid_tab_2  | parted_isvalid_idx
(5 rows)

drop table parted_isvalid_tab;
-- Check state of replica indexes when attaching a partition.
begin;
create table parted_replica_tab (id int not null) partition by range (id);
create table parted_replica_tab_1 partition of parted_replica_tab
  for values from (1) to (10) partition by range (id);
create table parted_replica_tab_11 partition of parted_replica_tab_1
  for values from (1) to (5);
create unique index parted_replica_idx
  on only parted_replica_tab using btree (id);
create unique index parted_replica_idx_1
  on only parted_replica_tab_1 using btree (id);
-- This triggers an update of pg_index.indisreplident for parted_replica_idx.
alter table only parted_replica_tab_1 replica identity
  using index parted_replica_idx_1;
create unique index parted_replica_idx_11 on parted_replica_tab_11 USING btree (id);
select indexrelid::regclass, indisvalid, indisreplident,
       indrelid::regclass, inhparent::regclass
  from pg_index idx left join
       pg_inherits inh on (idx.indexrelid = inh.inhrelid)
  where indexrelid::regclass::text like 'parted_replica%'
  order by indexrelid::regclass::text collate "C";
      indexrelid       | indisvalid | indisreplident |       indrelid        | inhparent
-----------------------+------------+----------------+-----------------------+-----------
parted_replica_idx    | f          | f              | parted_replica_tab    |
parted_replica_idx_1  | f          | t              | parted_replica_tab_1  |
parted_replica_idx_11 | t          | f              | parted_replica_tab_11 |
(3 rows)

-- parted_replica_idx is not valid yet here, because parted_replica_idx_1
-- is not valid.
alter index parted_replica_idx ATTACH PARTITION parted_replica_idx_1;
select indexrelid::regclass, indisvalid, indisreplident,
       indrelid::regclass, inhparent::regclass
  from pg_index idx left join
       pg_inherits inh on (idx.indexrelid = inh.inhrelid)
  where indexrelid::regclass::text like 'parted_replica%'
  order by indexrelid::regclass::text collate "C";
      indexrelid       | indisvalid | indisreplident |       indrelid        |     inhparent
-----------------------+------------+----------------+-----------------------+--------------------
parted_replica_idx    | f          | f              | parted_replica_tab    |
parted_replica_idx_1  | f          | t              | parted_replica_tab_1  | parted_replica_idx
parted_replica_idx_11 | t          | f              | parted_replica_tab_11 |
(3 rows)

-- parted_replica_idx becomes valid here.
alter index parted_replica_idx_1 ATTACH PARTITION parted_replica_idx_11;
alter table only parted_replica_tab_1 replica identity
  using index parted_replica_idx_1;
commit;
select indexrelid::regclass, indisvalid, indisreplident,
       indrelid::regclass, inhparent::regclass
  from pg_index idx left join
       pg_inherits inh on (idx.indexrelid = inh.inhrelid)
  where indexrelid::regclass::text like 'parted_replica%'
  order by indexrelid::regclass::text collate "C";
      indexrelid       | indisvalid | indisreplident |       indrelid        |      inhparent
-----------------------+------------+----------------+-----------------------+----------------------
parted_replica_idx    | t          | f              | parted_replica_tab    |
parted_replica_idx_1  | t          | t              | parted_replica_tab_1  | parted_replica_idx
parted_replica_idx_11 | t          | f              | parted_replica_tab_11 | parted_replica_idx_1
(3 rows)

drop table parted_replica_tab;
-- test that indexing commands work with TOASTed values in pg_index
create table test_pg_index_toast_table (a int);
create or replace function test_pg_index_toast_func (a int, b int[])
  returns bool as $$ select true $$ language sql immutable;
select array_agg(n) b from generate_series(1, 10000) n \gset
create index concurrently test_pg_index_toast_index
  on test_pg_index_toast_table (test_pg_index_toast_func(a, :'b'));
reindex index concurrently test_pg_index_toast_index;
drop index concurrently test_pg_index_toast_index;
create index test_pg_index_toast_index
  on test_pg_index_toast_table (test_pg_index_toast_func(a, :'b'));
reindex index test_pg_index_toast_index;
drop index test_pg_index_toast_index;
drop function test_pg_index_toast_func;
drop table test_pg_index_toast_table;

Quelle indexing.out Sprache: unbekannt

[Dauer der Verarbeitung: 0.48 Sekunden, vorverarbeitet 2026-08-08]