public inbox for [email protected]
help / color / mirror / Atom feed[PATCH 1/2] Fix not-null constraint representation in \d+
13+ messages / 7 participants
[nested] [flat]
* [PATCH 1/2] Fix not-null constraint representation in \d+
@ 2023-08-28 05:38 Kyotaro Horiguchi <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Kyotaro Horiguchi @ 2023-08-28 05:38 UTC (permalink / raw)
The recent commit b0e96f3119 added the description about not-null
constraints in the output of \d+ command. It hided constraints' origin
when it is marked as NO INHERIT. Show their origin irrespective of
the NO INHERIT state.
---
src/bin/psql/describe.c | 22 +++++++---
src/test/regress/expected/constraints.out | 12 ++---
src/test/regress/expected/create_table.out | 6 +--
.../regress/expected/create_table_like.out | 6 +--
src/test/regress/expected/foreign_data.out | 44 +++++++++----------
src/test/regress/expected/generated.out | 2 +-
src/test/regress/expected/inherit.out | 30 ++++++-------
7 files changed, 66 insertions(+), 56 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index bac94a338c..3bf1c0cb97 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3079,16 +3079,26 @@ describeOneTableDetails(const char *schemaname,
/* Might be an empty set - that's ok */
for (i = 0; i < tuples; i++)
{
+ bool noinherit = PQgetvalue(result, i, 2)[0] == 't';
bool islocal = PQgetvalue(result, i, 3)[0] == 't';
bool inherited = PQgetvalue(result, i, 4)[0] == 't';
- printfPQExpBuffer(&buf, " \"%s\" NOT NULL \"%s\"%s",
+ printfPQExpBuffer(&buf, " \"%s\" NOT NULL \"%s\"",
PQgetvalue(result, i, 0),
- PQgetvalue(result, i, 1),
- PQgetvalue(result, i, 2)[0] == 't' ?
- " NO INHERIT" :
- islocal && inherited ? _(" (local, inherited)") :
- inherited ? _(" (inherited)") : "");
+ PQgetvalue(result, i, 1));
+ if (inherited || noinherit)
+ {
+ appendPQExpBufferChar(&buf, ':');
+
+ if (inherited)
+ {
+ if (islocal)
+ appendPQExpBufferStr(&buf, _(" local"));
+ appendPQExpBufferStr(&buf, _(" inherited"));
+ }
+ if (noinherit)
+ appendPQExpBufferStr(&buf, _(" uninheritable"));
+ }
printTableAddFooter(&cont, buf.data);
}
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index b7de50ad6a..d4adbaa518 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -914,7 +914,7 @@ ALTER TABLE cnn_parent ADD PRIMARY KEY (b);
a | integer | | | | plain | |
b | integer | | not null | | plain | |
Not-null constraints:
- "cnn_grandchild_b_not_null" NOT NULL "b" (local, inherited)
+ "cnn_grandchild_b_not_null" NOT NULL "b": local inherited
Inherits: cnn_child
Child tables: cnn_grandchild2
@@ -925,7 +925,7 @@ Child tables: cnn_grandchild2
a | integer | | | | plain | |
b | integer | | not null | | plain | |
Not-null constraints:
- "cnn_grandchild_b_not_null" NOT NULL "b" (inherited)
+ "cnn_grandchild_b_not_null" NOT NULL "b": inherited
Inherits: cnn_grandchild,
cnn_child2
@@ -951,7 +951,7 @@ ERROR: multiple primary keys for table "cnn_parent" are not allowed
a | integer | | | | plain | |
b | integer | | not null | | plain | |
Not-null constraints:
- "cnn_grandchild_b_not_null" NOT NULL "b" (local, inherited)
+ "cnn_grandchild_b_not_null" NOT NULL "b": local inherited
Inherits: cnn_child
Child tables: cnn_grandchild2
@@ -962,7 +962,7 @@ Child tables: cnn_grandchild2
a | integer | | | | plain | |
b | integer | | not null | | plain | |
Not-null constraints:
- "cnn_grandchild_b_not_null" NOT NULL "b" (inherited)
+ "cnn_grandchild_b_not_null" NOT NULL "b": inherited
Inherits: cnn_grandchild,
cnn_child2
@@ -988,7 +988,7 @@ ALTER TABLE cnn_parent ADD PRIMARY KEY USING INDEX b_uq;
a | integer | | | | plain | |
b | integer | | not null | | plain | |
Not-null constraints:
- "cnn_grandchild_b_not_null" NOT NULL "b" (local, inherited)
+ "cnn_grandchild_b_not_null" NOT NULL "b": local inherited
Inherits: cnn_child
Child tables: cnn_grandchild2
@@ -999,7 +999,7 @@ Child tables: cnn_grandchild2
a | integer | | | | plain | |
b | integer | | not null | | plain | |
Not-null constraints:
- "cnn_grandchild_b_not_null" NOT NULL "b" (inherited)
+ "cnn_grandchild_b_not_null" NOT NULL "b": inherited
Inherits: cnn_grandchild,
cnn_child2
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 344d05233a..293e4292e9 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -855,7 +855,7 @@ drop table test_part_coll_posix;
Partition of: parted FOR VALUES IN ('b')
Partition constraint: ((a IS NOT NULL) AND (a = 'b'::text))
Not-null constraints:
- "part_b_b_not_null" NOT NULL "b" (local, inherited)
+ "part_b_b_not_null" NOT NULL "b": local inherited
-- Both partition bound and partition key in describe output
\d+ part_c
@@ -868,7 +868,7 @@ Partition of: parted FOR VALUES IN ('c')
Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text))
Partition key: RANGE (b)
Not-null constraints:
- "part_c_b_not_null" NOT NULL "b" (local, inherited)
+ "part_c_b_not_null" NOT NULL "b": local inherited
Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10)
-- a level-2 partition's constraint will include the parent's expressions
@@ -881,7 +881,7 @@ Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10)
Partition of: part_c FOR VALUES FROM (1) TO (10)
Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text) AND (b IS NOT NULL) AND (b >= 1) AND (b < 10))
Not-null constraints:
- "part_c_b_not_null" NOT NULL "b" (inherited)
+ "part_c_b_not_null" NOT NULL "b": inherited
-- Show partition count in the parent's describe output
-- Tempted to include \d+ output listing partitions with bound info but
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index 61956773ff..6604d0c7fa 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -360,7 +360,7 @@ NOTICE: merging constraint "ctlt1_a_check" with inherited definition
Check constraints:
"ctlt1_a_check" CHECK (length(a) > 2)
Not-null constraints:
- "ctlt1_inh_a_not_null" NOT NULL "a" (local, inherited)
+ "ctlt1_inh_a_not_null" NOT NULL "a": local inherited
Inherits: ctlt1
SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 'ctlt1_inh'::regclass;
@@ -383,7 +383,7 @@ Check constraints:
"ctlt3_a_check" CHECK (length(a) < 5)
"ctlt3_c_check" CHECK (length(c) < 7)
Not-null constraints:
- "ctlt13_inh_a_not_null" NOT NULL "a" (inherited)
+ "ctlt13_inh_a_not_null" NOT NULL "a": inherited
Inherits: ctlt1,
ctlt3
@@ -403,7 +403,7 @@ Check constraints:
"ctlt3_a_check" CHECK (length(a) < 5)
"ctlt3_c_check" CHECK (length(c) < 7)
Not-null constraints:
- "ctlt13_like_a_not_null" NOT NULL "a" (inherited)
+ "ctlt13_like_a_not_null" NOT NULL "a": inherited
Inherits: ctlt1
SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 'ctlt13_like'::regclass;
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 1dfe23cc1e..17cdd7e5b3 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -1421,7 +1421,7 @@ Child tables: ft2, FOREIGN
c2 | text | | | | | extended | |
c3 | date | | | | | plain | |
Not-null constraints:
- "fd_pt1_c1_not_null" NOT NULL "c1" (inherited)
+ "fd_pt1_c1_not_null" NOT NULL "c1": inherited
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
@@ -1474,7 +1474,7 @@ Child tables: ft2, FOREIGN
c2 | text | | | | | extended | |
c3 | date | | | | | plain | |
Not-null constraints:
- "ft2_c1_not_null" NOT NULL "c1" (local, inherited)
+ "ft2_c1_not_null" NOT NULL "c1": local inherited
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
@@ -1497,7 +1497,7 @@ NOTICE: merging column "c3" with inherited definition
c2 | text | | | | | extended | |
c3 | date | | | | | plain | |
Not-null constraints:
- "ft2_c1_not_null" NOT NULL "c1" (local, inherited)
+ "ft2_c1_not_null" NOT NULL "c1": local inherited
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
@@ -1512,7 +1512,7 @@ Child tables: ct3,
c2 | text | | | | extended | |
c3 | date | | | | plain | |
Not-null constraints:
- "ft2_c1_not_null" NOT NULL "c1" (inherited)
+ "ft2_c1_not_null" NOT NULL "c1": inherited
Inherits: ft2
\d+ ft3
@@ -1523,7 +1523,7 @@ Inherits: ft2
c2 | text | | | | | extended | |
c3 | date | | | | | plain | |
Not-null constraints:
- "ft3_c1_not_null" NOT NULL "c1" (local, inherited)
+ "ft3_c1_not_null" NOT NULL "c1": local inherited
Server: s0
Inherits: ft2
@@ -1563,8 +1563,8 @@ Child tables: ft2, FOREIGN
c7 | integer | | not null | | | plain | |
c8 | integer | | | | | plain | |
Not-null constraints:
- "ft2_c1_not_null" NOT NULL "c1" (local, inherited)
- "fd_pt1_c7_not_null" NOT NULL "c7" (inherited)
+ "ft2_c1_not_null" NOT NULL "c1": local inherited
+ "fd_pt1_c7_not_null" NOT NULL "c7": inherited
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
@@ -1584,8 +1584,8 @@ Child tables: ct3,
c7 | integer | | not null | | plain | |
c8 | integer | | | | plain | |
Not-null constraints:
- "ft2_c1_not_null" NOT NULL "c1" (inherited)
- "fd_pt1_c7_not_null" NOT NULL "c7" (inherited)
+ "ft2_c1_not_null" NOT NULL "c1": inherited
+ "fd_pt1_c7_not_null" NOT NULL "c7": inherited
Inherits: ft2
\d+ ft3
@@ -1601,8 +1601,8 @@ Inherits: ft2
c7 | integer | | not null | | | plain | |
c8 | integer | | | | | plain | |
Not-null constraints:
- "ft3_c1_not_null" NOT NULL "c1" (local, inherited)
- "fd_pt1_c7_not_null" NOT NULL "c7" (inherited)
+ "ft3_c1_not_null" NOT NULL "c1": local inherited
+ "fd_pt1_c7_not_null" NOT NULL "c7": inherited
Server: s0
Inherits: ft2
@@ -1649,8 +1649,8 @@ Child tables: ft2, FOREIGN
c7 | integer | | | | | plain | |
c8 | text | | | | | external | |
Not-null constraints:
- "ft2_c1_not_null" NOT NULL "c1" (local, inherited)
- "fd_pt1_c6_not_null" NOT NULL "c6" (inherited)
+ "ft2_c1_not_null" NOT NULL "c1": local inherited
+ "fd_pt1_c6_not_null" NOT NULL "c6": inherited
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
@@ -1682,7 +1682,7 @@ Child tables: ft2, FOREIGN
c2 | text | | | | | extended | |
c3 | date | | | | | plain | |
Not-null constraints:
- "ft2_c1_not_null" NOT NULL "c1" (local, inherited)
+ "ft2_c1_not_null" NOT NULL "c1": local inherited
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
@@ -1729,7 +1729,7 @@ Child tables: ft2, FOREIGN
Check constraints:
"fd_pt1chk2" CHECK (c2 <> ''::text)
Not-null constraints:
- "ft2_c1_not_null" NOT NULL "c1" (local, inherited)
+ "ft2_c1_not_null" NOT NULL "c1": local inherited
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
@@ -1780,7 +1780,7 @@ Child tables: ft2, FOREIGN
Check constraints:
"fd_pt1chk2" CHECK (c2 <> ''::text)
Not-null constraints:
- "ft2_c1_not_null" NOT NULL "c1" (local, inherited)
+ "ft2_c1_not_null" NOT NULL "c1": local inherited
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
@@ -1815,7 +1815,7 @@ Check constraints:
"fd_pt1chk2" CHECK (c2 <> ''::text)
"fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID
Not-null constraints:
- "ft2_c1_not_null" NOT NULL "c1" (local, inherited)
+ "ft2_c1_not_null" NOT NULL "c1": local inherited
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
@@ -1846,7 +1846,7 @@ Check constraints:
"fd_pt1chk2" CHECK (c2 <> ''::text)
"fd_pt1chk3" CHECK (c2 <> ''::text)
Not-null constraints:
- "ft2_c1_not_null" NOT NULL "c1" (local, inherited)
+ "ft2_c1_not_null" NOT NULL "c1": local inherited
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
@@ -1881,7 +1881,7 @@ Check constraints:
"f2_check" CHECK (f2 <> ''::text)
"fd_pt1chk2" CHECK (f2 <> ''::text)
Not-null constraints:
- "ft2_c1_not_null" NOT NULL "f1" (local, inherited)
+ "ft2_c1_not_null" NOT NULL "f1": local inherited
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
@@ -1942,7 +1942,7 @@ Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN
Partition of: fd_pt2 FOR VALUES IN (1)
Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1))
Not-null constraints:
- "fd_pt2_c1_not_null" NOT NULL "c1" (inherited)
+ "fd_pt2_c1_not_null" NOT NULL "c1": inherited
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -2024,7 +2024,7 @@ Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN
Partition of: fd_pt2 FOR VALUES IN (1)
Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1))
Not-null constraints:
- "fd_pt2_1_c1_not_null" NOT NULL "c1" (inherited)
+ "fd_pt2_1_c1_not_null" NOT NULL "c1": inherited
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -2058,7 +2058,7 @@ Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1))
Check constraints:
"p21chk" CHECK (c2 <> ''::text)
Not-null constraints:
- "fd_pt2_1_c1_not_null" NOT NULL "c1" (inherited)
+ "fd_pt2_1_c1_not_null" NOT NULL "c1": inherited
"fd_pt2_1_c3_not_null" NOT NULL "c3"
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
diff --git a/src/test/regress/expected/generated.out b/src/test/regress/expected/generated.out
index dc97ed3fe0..4a1744f129 100644
--- a/src/test/regress/expected/generated.out
+++ b/src/test/regress/expected/generated.out
@@ -316,7 +316,7 @@ NOTICE: merging column "b" with inherited definition
b | integer | | | generated always as (a * 22) stored | plain | |
x | integer | | | | plain | |
Not-null constraints:
- "gtestx_a_not_null" NOT NULL "a" (inherited)
+ "gtestx_a_not_null" NOT NULL "a": inherited
Inherits: gtest1
CREATE TABLE gtestxx_1 (a int NOT NULL, b int);
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 6daca12340..c80e398f71 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -2007,7 +2007,7 @@ Child tables: cc2
f4 | double precision | | | | plain | |
a2 | integer | | not null | | plain | |
Not-null constraints:
- "nn" NOT NULL "a2" (inherited)
+ "nn" NOT NULL "a2": inherited
Inherits: pp1,
cc1
@@ -2031,7 +2031,7 @@ Child tables: cc1,
f3 | integer | | | | plain | |
a2 | integer | | not null | | plain | |
Not-null constraints:
- "pp1_f1_not_null" NOT NULL "f1" (inherited)
+ "pp1_f1_not_null" NOT NULL "f1": inherited
"nn" NOT NULL "a2"
Inherits: pp1
Child tables: cc2
@@ -2046,8 +2046,8 @@ Child tables: cc2
f4 | double precision | | | | plain | |
a2 | integer | | not null | | plain | |
Not-null constraints:
- "pp1_f1_not_null" NOT NULL "f1" (inherited)
- "nn" NOT NULL "a2" (inherited)
+ "pp1_f1_not_null" NOT NULL "f1": inherited
+ "nn" NOT NULL "a2": inherited
Inherits: pp1,
cc1
@@ -2065,7 +2065,7 @@ alter table cc1 alter column a2 drop not null;
f3 | integer | | | | plain | |
a2 | integer | | | | plain | |
Not-null constraints:
- "pp1_f1_not_null" NOT NULL "f1" (inherited)
+ "pp1_f1_not_null" NOT NULL "f1": inherited
Inherits: pp1
Child tables: cc2
@@ -2082,7 +2082,7 @@ ERROR: cannot drop inherited constraint "pp1_f1_not_null" of relation "cc2"
f4 | double precision | | | | plain | |
a2 | integer | | | | plain | |
Not-null constraints:
- "pp1_f1_not_null" NOT NULL "f1" (inherited)
+ "pp1_f1_not_null" NOT NULL "f1": inherited
Inherits: pp1,
cc1
@@ -2112,8 +2112,8 @@ create table inh_child () inherits (inh_parent1, inh_parent2);
a | integer | | not null | | plain | |
b | integer | | not null | | plain | |
Not-null constraints:
- "nn" NOT NULL "a" (inherited)
- "inh_child_b_not_null" NOT NULL "b" (inherited)
+ "nn" NOT NULL "a": inherited
+ "inh_child_b_not_null" NOT NULL "b": inherited
Inherits: inh_parent1,
inh_parent2
@@ -2147,9 +2147,9 @@ select conrelid::regclass, conname, contype, conkey,
d | integer | | not null | | plain | |
e | integer | | | | plain | |
Not-null constraints:
- "inh_child_a_not_null" NOT NULL "a" (inherited)
- "inh_child_b_not_null" NOT NULL "b" (inherited)
- "inh_child_d_not_null" NOT NULL "d" (inherited)
+ "inh_child_a_not_null" NOT NULL "a": inherited
+ "inh_child_b_not_null" NOT NULL "b": inherited
+ "inh_child_d_not_null" NOT NULL "d": inherited
Inherits: inh_parent1,
inh_parent2
@@ -2188,7 +2188,7 @@ Inherits: inh_nn_parent
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | not null | | plain | |
Not-null constraints:
- "inh_nn_parent_a_not_null" NOT NULL "a" NO INHERIT
+ "inh_nn_parent_a_not_null" NOT NULL "a": uninheritable
Child tables: inh_nn_child,
inh_nn_child2
@@ -2223,7 +2223,7 @@ Child tables: inh_child1
--------+---------+-----------+----------+---------+---------+--------------+-------------
f1 | integer | | not null | | plain | |
Not-null constraints:
- "inh_child1_f1_not_null" NOT NULL "f1" (local, inherited)
+ "inh_child1_f1_not_null" NOT NULL "f1": local inherited
Inherits: inh_parent
Child tables: inh_child2
@@ -2233,7 +2233,7 @@ Child tables: inh_child2
--------+---------+-----------+----------+---------+---------+--------------+-------------
f1 | integer | | not null | | plain | |
Not-null constraints:
- "inh_child2_f1_not_null" NOT NULL "f1" (local, inherited)
+ "inh_child2_f1_not_null" NOT NULL "f1": local inherited
Inherits: inh_child1
select conrelid::regclass, conname, contype, coninhcount, conislocal
@@ -2277,7 +2277,7 @@ Child tables: inh_child2,
--------+---------+-----------+----------+---------+---------+--------------+-------------
f1 | integer | | not null | | plain | |
Not-null constraints:
- "inh_child2_f1_not_null" NOT NULL "f1" (local, inherited)
+ "inh_child2_f1_not_null" NOT NULL "f1": local inherited
Inherits: inh_child1
select conrelid::regclass, conname, contype, coninhcount, conislocal
--
2.39.3
----Next_Part(Mon_Aug_28_16_16_58_2023_869)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="0002-Add-tests-for-d-not-null-constraints.patch"
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Proposal to Enable/Disable Index using ALTER INDEX (with patch)
@ 2025-01-31 04:48 Sami Imseih <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Sami Imseih @ 2025-01-31 04:48 UTC (permalink / raw)
To: Benoit Lobréau <[email protected]>; +Cc: Shayon Mukherjee <[email protected]>; Michail Nikolaev <[email protected]>; David Rowley <[email protected]>; Robert Haas <[email protected]>; Peter Eisentraut <[email protected]>; PostgreSQL Hackers <[email protected]>
> I had the "BEGIN; ALTER INDEX; EXPLAIN; ROLLBACK;" scenario in mind, but
> didn't realise we acquire an AccessExclusiveLock on the index. Therefore, it's
> not possible to change the visibility within a single transaction....
> unless you don’t mind blocking all access to the relation.
>
> I read the comments at the top of "AlterTableGetLockLevel" and in the
> documentation and I understand that this behavior seems unavoidable.
> I suppose this is what was meant by the "globally visible effects" of an ALTER
> INDEX in the old discussion ? [1]
What is being discussed here is different from what I can tell. This
is referring
to the index changing status ( visible/invisible ) and those changes being
visible by another transaction.
However, the current patch may be too restrictive. Why do we need
an AccessExclusiveLock on the table to perform the change. We are
only changing the catalog and not the underlying data. This is a lot like
ALTER INDEX RENAME, which only takes a ShareUpdateExclusiveLock.
Can we do the same here?
I am also still reviewing and have a few comments on v9
1/ Missing ATSimpleRecursion call in PrepCmd for
case AT_SetIndexVisible:
case AT_SetIndexInvisible:
Without the recursion call, the visibility changes on a
parent will not apply to the partitions. We are also
missing tests for partitions.
2/ In ATExecSetIndexVisibility
Change:
elog(ERROR, "could not find tuple for index %u", indexOid);
To:
elog(ERROR, "cache lookup failed for index %u", indexOid);
I see both message formats are used all over the place,
but in tablecmds.c, the "cache lookup" variant is the one
used, so let's do that for consistency.
3/ In ATExecSetIndexVisibility
if (indexForm->indcheckxmin)
+ {
+ heap_freetuple(indexTuple);
+ table_close(pg_index, RowExclusiveLock);
+ ereport(ERROR,
There is no need to close the table or free the tuple, as
these are "cleaned up" when the transaction aborts.
4/ In ATExecSetIndexVisibility
I have a suggestion below:
What about we open both heapOid and IndexRelationId
at the start and close them at the end. Also,
inside the block for indexForm->indisvisible != visible,
do the work to invalidate the cache, invoke the post
alter hook and increment the command counter. This will
also allow us to get rid of the update boolean as well.
What do you think?
Regards,
Sami
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Proposal to Enable/Disable Index using ALTER INDEX (with patch)
@ 2025-02-01 07:35 Shayon Mukherjee <[email protected]>
parent: Sami Imseih <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Shayon Mukherjee @ 2025-02-01 07:35 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: Benoit Lobréau <[email protected]>; Michail Nikolaev <[email protected]>; David Rowley <[email protected]>; Robert Haas <[email protected]>; Peter Eisentraut <[email protected]>; PostgreSQL Hackers <[email protected]>
On Fri, Jan 31, 2025 at 10:18 AM Sami Imseih <[email protected]> wrote:
>
> What is being discussed here is different from what I can tell. This
> is referring
> to the index changing status ( visible/invisible ) and those changes being
> visible by another transaction.
>
>
+1. My vote would be to keep the behavior as is, at least for the first
roll out of this feature.
> However, the current patch may be too restrictive. Why do we need
> an AccessExclusiveLock on the table to perform the change. We are
> only changing the catalog and not the underlying data. This is a lot like
> ALTER INDEX RENAME, which only takes a ShareUpdateExclusiveLock.
> Can we do the same here?
>
>
Makes sense, I think this ended up being a remananent from one of the first
iterations. Updated it in the attached v10 patch.
> 1/ Missing ATSimpleRecursion call in PrepCmd for
> case AT_SetIndexVisible:
> case AT_SetIndexInvisible:
> Without the recursion call, the visibility changes on a
> parent will not apply to the partitions. We are also
> missing tests for partitions.
>
>
Great catch! Thank you!! Updated the patch with the support for partitions
and backed by regression specs.
2/ In ATExecSetIndexVisibility
>
> Change:
> elog(ERROR, "could not find tuple for index %u", indexOid);
>
> To:
> elog(ERROR, "cache lookup failed for index %u", indexOid);
>
Good eye! I missed this :).
>
> 3/ In ATExecSetIndexVisibility
>
> if (indexForm->indcheckxmin)
> + {
> + heap_freetuple(indexTuple);
> + table_close(pg_index, RowExclusiveLock);
> + ereport(ERROR,
>
> There is no need to close the table or free the tuple, as
> these are "cleaned up" when the transaction aborts.
>
> 4/ In ATExecSetIndexVisibility
>
> I have a suggestion below:
>
> What about we open both heapOid and IndexRelationId
> at the start and close them at the end. Also,
> inside the block for indexForm->indisvisible != visible,
> do the work to invalidate the cache, invoke the post
> alter hook and increment the command counter. This will
> also allow us to get rid of the update boolean as well.
>
> What do you think?
>
Thank you for the suggestions!! I was err'ing a bit on the "defensive" side
being new to this area, however your suggestions are much more simpler. I
have updated the patch accordingly.
I have also added support for suggestions from the earlier messages, I will
respond there accordingly.
Thank you!
Shayon
Attachments:
[application/octet-stream] v10-0001-Introduce-the-ability-to-set-index-visibility-us.patch (68.2K, ../../CANqtF-rs5N4ZepiH9YV1VVi6YNtyFDzDbJabhcZKqh-hNgLmgw@mail.gmail.com/3-v10-0001-Introduce-the-ability-to-set-index-visibility-us.patch)
download | inline diff:
From 1ac78ad9cf653a83008b7e9d6f2a9a20b155a8d1 Mon Sep 17 00:00:00 2001
From: Shayon Mukherjee <[email protected]>
Date: Sun, 12 Jan 2025 14:34:48 -0500
Subject: [PATCH v10] Introduce the ability to set index visibility using ALTER
INDEX
This patch introduces index visibility control using ALTER INDEX and CREATE INDEX
commands.
Original motivation for the problem and proposal for a patch
can be found at [1].
This patch passes all the existing specs and the newly added regression tests. The patch
is ready for review and test. It compiles, so the patch can be applied for testing as well.
Note: The patch has gone through a few iterations. Earlier versions of the patch had the ENABLE/DISABLE grammar.
The current version has the VISIBLE/INVISIBLE grammar. So, you will the local variable names using the new grammar accordingly.
Implementation details:
- New Grammar:
* ALTER INDEX ... VISIBLE/INVISIBLE
* CREATE INDEX ... INVISIBLE
- Default state is visible. Indexes are only invisible when explicitly
instructed via CREATE INDEX ... INVISIBLE or ALTER INDEX ... INVISIBLE.
- Primary Key and Unique constraint indexes are always visible. The
VISIBLE/INVISIBLE grammar is supported for these types of indexes and they can
be made invisible via ALTER INDEX ... INVISIBLE.
- ALTER INDEX ... VISIBLE/INVISIBLE performs update on the relevant row in pg_index
catalog
- pg_get_indexdef() supports the new functionality and grammar. This change is
reflected in \d output for tables and pg_dump. We show the INVISIBLE syntax accordingly.
- Added force_invisible_index GUC parameter that forces the planner to use invisible
indexes. This is useful for testing and validating index behavior without changing
their visibility state. Based on feedback from Sami S [2]
- Updated create_index.sql regression test to cover the new grammar and verify
that invisible indexes are not used in queries. The test covers:
- Basic single-column and multi-column indexes
- Partial indexes
- Expression indexes
- Join indexes
- GIN and GiST indexes
- Covering indexes
- Range indexes
- Unique indexes and constraints
- Adds a new indisvisible attribute to the IndexOptInfo structure.
- Modifies get_relation_info in plancat.c to skip invisible indexes entirely, thus reducing the number of places we need to check if an index is invisible or not. Inspired by the conversations start at [3].
- I chose to modify the logic within get_relation_info as compared to, say, reducing the cost to make the planner not consider an index during planning, mostly to keep the number of changes being introduced to a minimum and also the logic itself being self-contained and easier to understand perhaps (?).
- No changes are made to stop the index from getting maintained. This way we ensure no
data loss or corruption when index is made visible again.
- TOAST indexes are supported and visible by default as well.
- REINDEX CONCURRENTLY is supported as well and existing state of pg_index.indisvisible
is carried over accordingly.
- See the changes in create_index.sql to get an idea of the grammar and sql statements.
- See the changes in create_index.out to get an idea of the catalogue states and EXPLAIN
output to see when an index is getting used or isn't (when invisible).
- Incorporated DavidR's feedback from [4] around documentation and also you will see that by skipping invisible indexes entirely from get_relation_info in plancat.c (as mentioned above), we address the other mentioned issues as well.
- Lastly, protects against the case where indcheckxmin is true by raising ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE.
Looking forward to any and all feedback on this patch, including but not limited to code quality, tests, fundamental logic.
[1] https://www.postgresql.org/message-id/CANqtF-oXKe0M%3D0QOih6H%2BsZRjE2BWAbkW_1%2B9nMEAMLxUJg5jA%40mail.gmail.com
[2] https://www.postgresql.org/message-id/CAA5RZ0udzydObMDi65C59-oq54B9ZmjSZ1wVH3h%2Bv4XiVm6QDA%40mail.gmail.com
[3] https://www.postgresql.org/message-id/3465209.1727202064%40sss.pgh.pa.us
[4] https://www.postgresql.org/message-id/CAApHDvpUNu%3DiVcdJ74sypvgeaCF%2Btfpyb8VRhZaF7DTd1xVr7g%40mail.gmail.com
---
doc/src/sgml/catalogs.sgml | 11 +
doc/src/sgml/config.sgml | 16 +
doc/src/sgml/ref/alter_index.sgml | 38 ++
doc/src/sgml/ref/create_index.sgml | 29 +
src/backend/bootstrap/bootparse.y | 2 +
src/backend/catalog/index.c | 31 +-
src/backend/catalog/toasting.c | 2 +-
src/backend/commands/indexcmds.c | 4 +
src/backend/commands/tablecmds.c | 66 +-
src/backend/optimizer/util/plancat.c | 31 +-
src/backend/parser/gram.y | 56 +-
src/backend/parser/parse_utilcmd.c | 3 +
src/backend/utils/adt/ruleutils.c | 4 +
src/backend/utils/cache/relcache.c | 1 +
src/backend/utils/misc/guc_tables.c | 11 +
src/bin/psql/tab-complete.in.c | 3 +-
src/include/catalog/index.h | 2 +-
src/include/catalog/pg_index.h | 1 +
src/include/nodes/parsenodes.h | 3 +
src/include/optimizer/optimizer.h | 5 +
src/include/parser/kwlist.h | 2 +
src/include/utils/guc_hooks.h | 1 +
src/test/regress/expected/create_index.out | 705 +++++++++++++++++++++
src/test/regress/sql/create_index.sql | 291 +++++++++
24 files changed, 1298 insertions(+), 20 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 088fb175cc..8b0019d0e6 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -4595,6 +4595,17 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
partial index.
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>indisvisible</structfield> <type>bool</type>
+ </para>
+ <para>
+ If true, the index is currently visible to the planner and may be used for queries.
+ If false, the index is invisible and may not be used for queries,
+ but is still updated when the table is modified. Default is true.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index a782f10998..873fae9a93 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5589,6 +5589,22 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
</listitem>
</varlistentry>
+ <varlistentry id="guc-force-invisible-index" xreflabel="use_invisible_index">
+ <term><varname>use_invisible_index</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>use_invisible_index</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Forces the query planner to consider indexes that have been marked as invisible using
+ <command>ALTER INDEX ... INVISIBLE</command>. This parameter is useful for selective
+ use of invisible indexes in specific application contexts. The default
+ is <literal>off</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect2>
<sect2 id="runtime-config-query-constants">
diff --git a/doc/src/sgml/ref/alter_index.sgml b/doc/src/sgml/ref/alter_index.sgml
index 1d42d05d85..eaaa40d54c 100644
--- a/doc/src/sgml/ref/alter_index.sgml
+++ b/doc/src/sgml/ref/alter_index.sgml
@@ -31,6 +31,7 @@ ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> ALTE
SET STATISTICS <replaceable class="parameter">integer</replaceable>
ALTER INDEX ALL IN TABLESPACE <replaceable class="parameter">name</replaceable> [ OWNED BY <replaceable class="parameter">role_name</replaceable> [, ... ] ]
SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> [ NOWAIT ]
+ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> { VISIBLE | INVISIBLE }
</synopsis>
</refsynopsisdiv>
@@ -159,6 +160,29 @@ ALTER INDEX ALL IN TABLESPACE <replaceable class="parameter">name</replaceable>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>VISIBLE</literal></term>
+ <listitem>
+ <para>
+ Make the specified index visible. The index will be used for queries.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INVISIBLE</literal></term>
+ <listitem>
+ <para>
+ Make the specified index invisible. The index will not be used for queries.
+ This can be useful for testing query performance with and without specific
+ indexes. If performance degrades after making an index invisible, it can be easily
+ be made visible using <literal>VISIBLE</literal>. Before making an index invisible, it's recommended
+ to check <structname>pg_stat_user_indexes</structname>.<structfield>idx_scan</structfield>
+ to identify potentially unused indexes.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
@@ -301,6 +325,20 @@ CREATE INDEX coord_idx ON measured (x, y, (z + t));
ALTER INDEX coord_idx ALTER COLUMN 3 SET STATISTICS 1000;
</programlisting></para>
+ <para>
+ To make an index visible:
+<programlisting>
+ALTER INDEX idx_name VISIBLE;
+</programlisting>
+ </para>
+
+ <para>
+ To make an index invisible:
+<programlisting>
+ALTER INDEX idx_name INVISIBLE;
+</programlisting>
+ </para>
+
</refsect1>
<refsect1>
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index 208389e800..90d7d90a2b 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -28,6 +28,7 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
[ WHERE <replaceable class="parameter">predicate</replaceable> ]
+ [ INVISIBLE ]
</synopsis>
</refsynopsisdiv>
@@ -380,6 +381,19 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>INVISIBLE</literal></term>
+ <listitem>
+ <para>
+ Creates the index in an invisible state (default visible). An invisible index is
+ not used by the query planner for queries, but it is still maintained
+ when the underlying table data changes. This can be useful when you want to create
+ an index without immediately impacting query planning, allowing you to make it
+ visible later at a more convenient time. The index can be made visible later
+ using <command>ALTER INDEX ... VISIBLE</command>.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
<refsect2 id="sql-createindex-storage-parameters" xreflabel="Index Storage Parameters">
@@ -701,6 +715,14 @@ Indexes:
partitioned index is a metadata only operation.
</para>
+ <para>
+ When creating an index with the <literal>INVISIBLE</literal> option, the index
+ will be created but not used for query planning. This can be useful for
+ preparing an index in advance of its use, or for testing purposes. The index
+ will still be maintained as the table is modified, so it can be made visible
+ later without needing to be rebuilt. By default all new indexes are visible.
+ </para>
+
</refsect2>
</refsect1>
@@ -980,6 +1002,13 @@ SELECT * FROM points
CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);
</programlisting></para>
+ <para>
+ To create an index on the table <literal>test_table</literal> with the default
+ name, but have it initially invisible:
+<programlisting>
+CREATE INDEX ON test_table (col1) INVISIBLE;
+</programlisting>
+ </para>
</refsect1>
<refsect1>
diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.y
index 9833f52c1b..ea21511a20 100644
--- a/src/backend/bootstrap/bootparse.y
+++ b/src/backend/bootstrap/bootparse.y
@@ -303,6 +303,7 @@ Boot_DeclareIndexStmt:
stmt->concurrent = false;
stmt->if_not_exists = false;
stmt->reset_default_tblspc = false;
+ stmt->isvisible = true;
/* locks and races need not concern us in bootstrap mode */
relationId = RangeVarGetRelid(stmt->relation, NoLock,
@@ -356,6 +357,7 @@ Boot_DeclareUniqueIndexStmt:
stmt->concurrent = false;
stmt->if_not_exists = false;
stmt->reset_default_tblspc = false;
+ stmt->isvisible = true;
/* locks and races need not concern us in bootstrap mode */
relationId = RangeVarGetRelid(stmt->relation, NoLock,
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 7377912b41..ef51e6de0b 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -118,7 +118,8 @@ static void UpdateIndexRelation(Oid indexoid, Oid heapoid,
bool isexclusion,
bool immediate,
bool isvalid,
- bool isready);
+ bool isready,
+ bool isvisible);
static void index_update_stats(Relation rel,
bool hasindex,
double reltuples);
@@ -570,7 +571,8 @@ UpdateIndexRelation(Oid indexoid,
bool isexclusion,
bool immediate,
bool isvalid,
- bool isready)
+ bool isready,
+ bool isvisible)
{
int2vector *indkey;
oidvector *indcollation;
@@ -648,6 +650,7 @@ UpdateIndexRelation(Oid indexoid,
values[Anum_pg_index_indisready - 1] = BoolGetDatum(isready);
values[Anum_pg_index_indislive - 1] = BoolGetDatum(true);
values[Anum_pg_index_indisreplident - 1] = BoolGetDatum(false);
+ values[Anum_pg_index_indisvisible - 1] = BoolGetDatum(isvisible);
values[Anum_pg_index_indkey - 1] = PointerGetDatum(indkey);
values[Anum_pg_index_indcollation - 1] = PointerGetDatum(indcollation);
values[Anum_pg_index_indclass - 1] = PointerGetDatum(indclass);
@@ -713,6 +716,8 @@ UpdateIndexRelation(Oid indexoid,
* already exists.
* INDEX_CREATE_PARTITIONED:
* create a partitioned index (table must be partitioned)
+ * INDEX_CREATE_VISIBLE:
+ * create the index as visible to query planner
* constr_flags: flags passed to index_constraint_create
* (only if INDEX_CREATE_ADD_CONSTRAINT is set)
* allow_system_table_mods: allow table to be a system catalog
@@ -758,6 +763,7 @@ index_create(Relation heapRelation,
bool invalid = (flags & INDEX_CREATE_INVALID) != 0;
bool concurrent = (flags & INDEX_CREATE_CONCURRENT) != 0;
bool partitioned = (flags & INDEX_CREATE_PARTITIONED) != 0;
+ bool isvisible = (flags & INDEX_CREATE_VISIBLE) != 0;
char relkind;
TransactionId relfrozenxid;
MultiXactId relminmxid;
@@ -1041,13 +1047,15 @@ index_create(Relation heapRelation,
* (Or, could define a rule to maintain the predicate) --Nels, Feb '92
* ----------------
*/
+
UpdateIndexRelation(indexRelationId, heapRelationId, parentIndexRelid,
indexInfo,
collationIds, opclassIds, coloptions,
isprimary, is_exclusion,
(constr_flags & INDEX_CONSTR_CREATE_DEFERRABLE) == 0,
!concurrent && !invalid,
- !concurrent);
+ !concurrent,
+ isvisible);
/*
* Register relcache invalidation on the indexes' heap relation, to
@@ -1316,6 +1324,8 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
List *indexColNames = NIL;
List *indexExprs = NIL;
List *indexPreds = NIL;
+ Form_pg_index indexForm;
+ bits16 createFlags;
indexRelation = index_open(oldIndexId, RowExclusiveLock);
@@ -1343,6 +1353,9 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
Anum_pg_index_indoption);
indcoloptions = (int2vector *) DatumGetPointer(colOptionDatum);
+ /* Get the visibility state of the original index */
+ indexForm = (Form_pg_index) GETSTRUCT(indexTuple);
+
/* Fetch reloptions of index if any */
classTuple = SearchSysCache1(RELOID, ObjectIdGetDatum(oldIndexId));
if (!HeapTupleIsValid(classTuple))
@@ -1434,6 +1447,16 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
stattargets[i].isnull = isnull;
}
+ /*
+ * Determine the create flags for the new index.
+ * We always use SKIP_BUILD and CONCURRENT for concurrent reindexing.
+ * If the original index was visible, we also set the VISIBLE flag
+ * to maintain the same state in the new index.
+ */
+ createFlags = INDEX_CREATE_SKIP_BUILD | INDEX_CREATE_CONCURRENT;
+ if (indexForm->indisvisible)
+ createFlags |= INDEX_CREATE_VISIBLE;
+
/*
* Now create the new index.
*
@@ -1457,7 +1480,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
indcoloptions->values,
stattargets,
reloptionsDatum,
- INDEX_CREATE_SKIP_BUILD | INDEX_CREATE_CONCURRENT,
+ createFlags,
0,
true, /* allow table to be a system catalog? */
false, /* is_internal? */
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c
index 874a8fc89a..7721d16fd3 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -325,7 +325,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
BTREE_AM_OID,
rel->rd_rel->reltablespace,
collationIds, opclassIds, NULL, coloptions, NULL, (Datum) 0,
- INDEX_CREATE_IS_PRIMARY, 0, true, true, NULL);
+ INDEX_CREATE_IS_PRIMARY | INDEX_CREATE_VISIBLE, 0, true, true, NULL);
table_close(toast_rel, NoLock);
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 59c836fc24..9e86ea413b 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -1198,6 +1198,10 @@ DefineIndex(Oid tableId,
flags |= INDEX_CREATE_PARTITIONED;
if (stmt->primary)
flags |= INDEX_CREATE_IS_PRIMARY;
+ if (stmt->isvisible)
+ flags |= INDEX_CREATE_VISIBLE;
+ else
+ flags &= ~INDEX_CREATE_VISIBLE;
/*
* If the table is partitioned, and recursion was declined but partitions
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index d617c4bc63..b975e83a13 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -700,7 +700,7 @@ static List *GetParentedForeignKeyRefs(Relation partition);
static void ATDetachCheckNoForeignKeyRefs(Relation partition);
static char GetAttributeCompression(Oid atttypid, const char *compression);
static char GetAttributeStorage(Oid atttypid, const char *storagemode);
-
+static void ATExecSetIndexVisibility(Relation rel, bool visible);
/* ----------------------------------------------------------------
* DefineRelation
@@ -4789,6 +4789,11 @@ AlterTableGetLockLevel(List *cmds)
cmd_lockmode = ShareUpdateExclusiveLock;
break;
+ case AT_SetIndexVisible:
+ case AT_SetIndexInvisible:
+ cmd_lockmode = ShareUpdateExclusiveLock;
+ break;
+
default: /* oops */
elog(ERROR, "unrecognized alter table type: %d",
(int) cmd->subtype);
@@ -5222,6 +5227,13 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
/* No command-specific prep needed */
pass = AT_PASS_MISC;
break;
+ case AT_SetIndexVisible:
+ case AT_SetIndexInvisible:
+ ATSimplePermissions(cmd->subtype, rel, ATT_INDEX | ATT_PARTITIONED_INDEX);
+ ATSimpleRecursion(wqueue, rel, cmd, true, lockmode, context);
+ /* No command-specific prep needed */
+ pass = AT_PASS_MISC;
+ break;
default: /* oops */
elog(ERROR, "unrecognized alter table type: %d",
(int) cmd->subtype);
@@ -5616,6 +5628,12 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
case AT_DetachPartitionFinalize:
address = ATExecDetachPartitionFinalize(rel, ((PartitionCmd *) cmd->def)->name);
break;
+ case AT_SetIndexVisible:
+ ATExecSetIndexVisibility(rel, true);
+ break;
+ case AT_SetIndexInvisible:
+ ATExecSetIndexVisibility(rel, false);
+ break;
default: /* oops */
elog(ERROR, "unrecognized alter table type: %d",
(int) cmd->subtype);
@@ -6510,6 +6528,8 @@ alter_table_type_to_string(AlterTableType cmdtype)
return "DROP COLUMN";
case AT_AddIndex:
case AT_ReAddIndex:
+ case AT_SetIndexVisible:
+ case AT_SetIndexInvisible:
return NULL; /* not real grammar */
case AT_AddConstraint:
case AT_ReAddConstraint:
@@ -21050,3 +21070,47 @@ GetAttributeStorage(Oid atttypid, const char *storagemode)
return cstorage;
}
+
+/*
+ * ATExecSetIndexVisibility
+ * Performs a catalog update to mark an index as visible or invisible in pg_index.
+ */
+static void
+ATExecSetIndexVisibility(Relation rel, bool visible)
+{
+ Oid indexOid = RelationGetRelid(rel);
+ Oid heapOid;
+ Relation pg_index;
+ Relation heapRel;
+ HeapTuple indexTuple;
+ Form_pg_index indexForm;
+
+ heapOid = IndexGetRelation(indexOid, false);
+ heapRel = table_open(heapOid, AccessShareLock);
+ pg_index = table_open(IndexRelationId, RowExclusiveLock);
+
+ indexTuple = SearchSysCacheCopy1(INDEXRELID, ObjectIdGetDatum(indexOid));
+ if (!HeapTupleIsValid(indexTuple))
+ elog(ERROR, "cache lookup failed for index %u", indexOid);
+
+ indexForm = (Form_pg_index) GETSTRUCT(indexTuple);
+ if (indexForm->indcheckxmin)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot update index visibility while indcheckxmin is true"),
+ errhint("Wait for all transactions that might see inconsistent HOT chains to complete")));
+
+ if (indexForm->indisvisible != visible)
+ {
+ indexForm->indisvisible = visible;
+ CatalogTupleUpdate(pg_index, &indexTuple->t_self, indexTuple);
+
+ CacheInvalidateRelcache(heapRel);
+ InvokeObjectPostAlterHook(IndexRelationId, indexOid, 0);
+ CommandCounterIncrement();
+ }
+
+ heap_freetuple(indexTuple);
+ table_close(pg_index, RowExclusiveLock);
+ table_close(heapRel, AccessShareLock);
+}
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 71abb01f65..0b0682b505 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -47,7 +47,9 @@
#include "storage/bufmgr.h"
#include "tcop/tcopprot.h"
#include "utils/builtins.h"
+#include "utils/guc_hooks.h"
#include "utils/lsyscache.h"
+#include "utils/plancache.h"
#include "utils/partcache.h"
#include "utils/rel.h"
#include "utils/snapmgr.h"
@@ -55,6 +57,7 @@
/* GUC parameter */
int constraint_exclusion = CONSTRAINT_EXCLUSION_PARTITION;
+bool use_invisible_index = false;
/* Hook for plugins to get control in get_relation_info() */
get_relation_info_hook_type get_relation_info_hook = NULL;
@@ -254,13 +257,12 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
index = indexRelation->rd_index;
/*
- * Ignore invalid indexes, since they can't safely be used for
- * queries. Note that this is OK because the data structure we
- * are constructing is only used by the planner --- the executor
- * still needs to insert into "invalid" indexes, if they're marked
- * indisready.
- */
- if (!index->indisvalid)
+ * Skip invalid indexes, and invisible indexes unless use_invisible_index
+ * is set. This is OK because the data structure we are constructing is
+ * only used by the planner - the executor still needs to insert into
+ * these indexes if they're marked indisready.
+ */
+ if (!index->indisvalid || (!index->indisvisible && !use_invisible_index))
{
index_close(indexRelation, NoLock);
continue;
@@ -2610,3 +2612,18 @@ set_baserel_partition_constraint(Relation relation, RelOptInfo *rel)
rel->partition_qual = partconstr;
}
}
+
+/*
+ * assign_use_invisible_index
+ * GUC assign_hook for "use_invisible_index" GUC variable.
+ * Resets the plan cache when the value changes.
+ */
+void
+assign_use_invisible_index(bool newval, void *extra)
+{
+ if (use_invisible_index != newval)
+ {
+ use_invisible_index = newval;
+ ResetPlanCache();
+ }
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index d7f9c00c40..475ee26036 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -324,7 +324,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <ival> add_drop opt_asc_desc opt_nulls_order
%type <node> alter_table_cmd alter_type_cmd opt_collate_clause
- replica_identity partition_cmd index_partition_cmd
+ replica_identity partition_cmd index_partition_cmd index_alter_cmd
%type <list> alter_table_cmds alter_type_cmds
%type <list> alter_identity_column_option_list
%type <defelt> alter_identity_column_option
@@ -491,6 +491,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <boolean> opt_unique opt_verbose opt_full
%type <boolean> opt_freeze opt_analyze opt_default
%type <defelt> opt_binary copy_delimiter
+%type <boolean> opt_index_visibility
%type <boolean> copy_from opt_program
@@ -733,7 +734,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE
INCLUDING INCREMENT INDENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P
INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER
- INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
+ INTERSECT INTERVAL INTO INVISIBLE_P INVOKER IS ISNULL ISOLATION
JOIN JSON JSON_ARRAY JSON_ARRAYAGG JSON_EXISTS JSON_OBJECT JSON_OBJECTAGG
JSON_QUERY JSON_SCALAR JSON_SERIALIZE JSON_TABLE JSON_VALUE
@@ -784,7 +785,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
UNLISTEN UNLOGGED UNTIL UPDATE USER USING
VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
- VERBOSE VERSION_P VIEW VIEWS VOLATILE
+ VERBOSE VERSION_P VIEW VIEWS VISIBLE_P VOLATILE
WHEN WHERE WHITESPACE_P WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE
@@ -2157,6 +2158,24 @@ AlterTableStmt:
n->nowait = $13;
$$ = (Node *) n;
}
+ | ALTER INDEX qualified_name index_alter_cmd
+ {
+ AlterTableStmt *n = makeNode(AlterTableStmt);
+ n->relation = $3;
+ n->cmds = list_make1($4);
+ n->objtype = OBJECT_INDEX;
+ n->missing_ok = false;
+ $$ = (Node *) n;
+ }
+ | ALTER INDEX IF_P EXISTS qualified_name index_alter_cmd
+ {
+ AlterTableStmt *n = makeNode(AlterTableStmt);
+ n->relation = $5;
+ n->cmds = list_make1($6);
+ n->objtype = OBJECT_INDEX;
+ n->missing_ok = true;
+ $$ = (Node *) n;
+ }
| ALTER INDEX qualified_name alter_table_cmds
{
AlterTableStmt *n = makeNode(AlterTableStmt);
@@ -2382,6 +2401,21 @@ index_partition_cmd:
}
;
+index_alter_cmd:
+ /* ALTER INDEX <name> VISIBLE|INVISIBLE */
+ VISIBLE_P
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_SetIndexVisible;
+ $$ = (Node *) n;
+ }
+ | INVISIBLE_P
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_SetIndexInvisible;
+ $$ = (Node *) n;
+ }
+ ;
alter_table_cmd:
/* ALTER TABLE <name> ADD <coldef> */
ADD_P columnDef
@@ -8160,7 +8194,7 @@ defacl_privilege_target:
IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_single_name
ON relation_expr access_method_clause '(' index_params ')'
- opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause
+ opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause opt_index_visibility
{
IndexStmt *n = makeNode(IndexStmt);
@@ -8175,6 +8209,7 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_single_name
n->options = $14;
n->tableSpace = $15;
n->whereClause = $16;
+ n->isvisible = $17;
n->excludeOpNames = NIL;
n->idxcomment = NULL;
n->indexOid = InvalidOid;
@@ -8192,7 +8227,7 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_single_name
}
| CREATE opt_unique INDEX opt_concurrently IF_P NOT EXISTS name
ON relation_expr access_method_clause '(' index_params ')'
- opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause
+ opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause opt_index_visibility
{
IndexStmt *n = makeNode(IndexStmt);
@@ -8207,6 +8242,7 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_single_name
n->options = $17;
n->tableSpace = $18;
n->whereClause = $19;
+ n->isvisible = $20;
n->excludeOpNames = NIL;
n->idxcomment = NULL;
n->indexOid = InvalidOid;
@@ -8229,6 +8265,12 @@ opt_unique:
| /*EMPTY*/ { $$ = false; }
;
+opt_index_visibility:
+ VISIBLE_P { $$ = true; }
+ | INVISIBLE_P { $$ = false; }
+ | /*EMPTY*/ { $$ = true; }
+ ;
+
access_method_clause:
USING name { $$ = $2; }
| /*EMPTY*/ { $$ = DEFAULT_INDEX_TYPE; }
@@ -17806,6 +17848,7 @@ unreserved_keyword:
| INSENSITIVE
| INSERT
| INSTEAD
+ | INVISIBLE_P
| INVOKER
| ISOLATION
| KEEP
@@ -17990,6 +18033,7 @@ unreserved_keyword:
| VERSION_P
| VIEW
| VIEWS
+ | VISIBLE_P
| VOLATILE
| WHITESPACE_P
| WITHIN
@@ -18399,6 +18443,7 @@ bare_label_keyword:
| INT_P
| INTEGER
| INTERVAL
+ | INVISIBLE_P
| INVOKER
| IS
| ISOLATION
@@ -18645,6 +18690,7 @@ bare_label_keyword:
| VERSION_P
| VIEW
| VIEWS
+ | VISIBLE_P
| VOLATILE
| WHEN
| WHITESPACE_P
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index ca028d2a66..9cd8a65b52 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1743,6 +1743,7 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx,
index->concurrent = false;
index->if_not_exists = false;
index->reset_default_tblspc = false;
+ index->isvisible = idxrec->indisvisible;
/*
* We don't try to preserve the name of the source index; instead, just
@@ -2364,6 +2365,8 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
index->concurrent = false;
index->if_not_exists = false;
index->reset_default_tblspc = constraint->reset_default_tblspc;
+ /* Ensure indexes for constraints are created as visible by default */
+ index->isvisible = true;
/*
* If it's ALTER TABLE ADD CONSTRAINT USING INDEX, look up the index and
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 54dad97555..a46780de87 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -1562,6 +1562,10 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
else
appendStringInfo(&buf, " WHERE %s", str);
}
+
+ /* Add INVISIBLE clause if the index is invisible */
+ if (!idxrec->indisvisible)
+ appendStringInfoString(&buf, " INVISIBLE");
}
/* Clean up */
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 43219a9629..8c084622eb 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -2311,6 +2311,7 @@ RelationReloadIndexInfo(Relation relation)
relation->rd_index->indisready = index->indisready;
relation->rd_index->indislive = index->indislive;
relation->rd_index->indisreplident = index->indisreplident;
+ relation->rd_index->indisvisible = index->indisvisible;
/* Copy xmin too, as that is needed to make sense of indcheckxmin */
HeapTupleHeaderSetXmin(relation->rd_indextuple->t_data,
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 0bdfc83907..9699ceebed 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -2096,6 +2096,17 @@ struct config_bool ConfigureNamesBool[] =
NULL, NULL, NULL
},
+ {
+ {"use_invisible_index", PGC_USERSET, QUERY_TUNING_OTHER,
+ gettext_noop("Forces the planner to consider invisible indexes."),
+ NULL,
+ GUC_NOT_IN_SAMPLE | GUC_EXPLAIN
+ },
+ &use_invisible_index,
+ false,
+ NULL, assign_use_invisible_index, NULL
+ },
+
/* End-of-list marker */
{
{NULL, 0, 0, NULL, NULL}, NULL, false, NULL, NULL, NULL
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 81cbf10aa2..43ea8e55fd 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2393,7 +2393,8 @@ match_previous_words(int pattern_id,
else if (Matches("ALTER", "INDEX", MatchAny))
COMPLETE_WITH("ALTER COLUMN", "OWNER TO", "RENAME TO", "SET",
"RESET", "ATTACH PARTITION",
- "DEPENDS ON EXTENSION", "NO DEPENDS ON EXTENSION");
+ "DEPENDS ON EXTENSION", "NO DEPENDS ON EXTENSION",
+ "INVISIBLE", "VISIBLE");
else if (Matches("ALTER", "INDEX", MatchAny, "ATTACH"))
COMPLETE_WITH("PARTITION");
else if (Matches("ALTER", "INDEX", MatchAny, "ATTACH", "PARTITION"))
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index 4daa8bef5e..ff0f9916de 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -65,6 +65,7 @@ extern void index_check_primary_key(Relation heapRel,
#define INDEX_CREATE_IF_NOT_EXISTS (1 << 4)
#define INDEX_CREATE_PARTITIONED (1 << 5)
#define INDEX_CREATE_INVALID (1 << 6)
+#define INDEX_CREATE_VISIBLE (1 << 7)
extern Oid index_create(Relation heapRelation,
const char *indexRelationName,
@@ -175,7 +176,6 @@ extern void RestoreReindexState(const void *reindexstate);
extern void IndexSetParentIndex(Relation partitionIdx, Oid parentOid);
-
/*
* itemptr_encode - Encode ItemPointer as int64/int8
*
diff --git a/src/include/catalog/pg_index.h b/src/include/catalog/pg_index.h
index 4392b9d221..cf1bea9c81 100644
--- a/src/include/catalog/pg_index.h
+++ b/src/include/catalog/pg_index.h
@@ -44,6 +44,7 @@ CATALOG(pg_index,2610,IndexRelationId) BKI_SCHEMA_MACRO
bool indisready; /* is this index ready for inserts? */
bool indislive; /* is this index alive at all? */
bool indisreplident; /* is this index the identity for replication? */
+ bool indisvisible BKI_DEFAULT(t); /* is this index visible for use by queries? */
/* variable-length fields start here, but we allow direct access to indkey */
int2vector indkey BKI_FORCE_NOT_NULL; /* column numbers of indexed cols,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ffe155ee20..e9553d0f24 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2467,6 +2467,8 @@ typedef enum AlterTableType
AT_SetIdentity, /* SET identity column options */
AT_DropIdentity, /* DROP IDENTITY */
AT_ReAddStatistics, /* internal to commands/tablecmds.c */
+ AT_SetIndexVisible, /* Set INDEX visible */
+ AT_SetIndexInvisible, /* Set INDEX invisible */
} AlterTableType;
typedef struct ReplicaIdentityStmt
@@ -3449,6 +3451,7 @@ typedef struct IndexStmt
bool if_not_exists; /* just do nothing if index already exists? */
bool reset_default_tblspc; /* reset default_tablespace prior to
* executing */
+ bool isvisible; /* true if VISIBLE (default), false if INVISIBLE */
} IndexStmt;
/* ----------------------
diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h
index bcf8ed645c..99e435076a 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -205,4 +205,9 @@ extern List *pull_var_clause(Node *node, int flags);
extern Node *flatten_join_alias_vars(PlannerInfo *root, Query *query, Node *node);
extern Node *flatten_group_exprs(PlannerInfo *root, Query *query, Node *node);
+/*
+ * GUC to tell planner to consider invisible indexes.
+ */
+extern bool use_invisible_index;
+
#endif /* OPTIMIZER_H */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index cf2917ad07..18b97aefb0 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -229,6 +229,7 @@ PG_KEYWORD("integer", INTEGER, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("intersect", INTERSECT, RESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("interval", INTERVAL, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("into", INTO, RESERVED_KEYWORD, AS_LABEL)
+PG_KEYWORD("invisible", INVISIBLE_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("invoker", INVOKER, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("is", IS, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("isnull", ISNULL, TYPE_FUNC_NAME_KEYWORD, AS_LABEL)
@@ -491,6 +492,7 @@ PG_KEYWORD("verbose", VERBOSE, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("version", VERSION_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("view", VIEW, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("views", VIEWS, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("visible", VISIBLE_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("volatile", VOLATILE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("when", WHEN, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("where", WHERE, RESERVED_KEYWORD, AS_LABEL)
diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h
index 87999218d6..909f9b57ed 100644
--- a/src/include/utils/guc_hooks.h
+++ b/src/include/utils/guc_hooks.h
@@ -174,5 +174,6 @@ extern void assign_wal_sync_method(int new_wal_sync_method, void *extra);
extern bool check_synchronized_standby_slots(char **newval, void **extra,
GucSource source);
extern void assign_synchronized_standby_slots(const char *newval, void *extra);
+extern void assign_use_invisible_index(bool newval, void *extra);
#endif /* GUC_HOOKS_H */
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 8011c141bf..66201b888b 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -3331,6 +3331,711 @@ ERROR: REINDEX SCHEMA cannot run inside a transaction block
END;
-- concurrently
REINDEX SCHEMA CONCURRENTLY schema_to_reindex;
+-- Test index visibility functionality
+CREATE TABLE index_test(
+ id INT PRIMARY KEY,
+ data TEXT,
+ num INT,
+ vector INT[],
+ range INT4RANGE
+);
+INSERT INTO index_test
+SELECT
+ g,
+ 'data ' || g,
+ g % 100,
+ ARRAY[g, g+1, g+2],
+ int4range(g, g+10)
+FROM generate_series(1, 1000) g;
+-- Function for testing
+CREATE FUNCTION get_data_length(text) RETURNS INT AS $$
+ SELECT length($1);
+$$ LANGUAGE SQL IMMUTABLE;
+-- Helper function to show index status
+CREATE OR REPLACE FUNCTION show_index_status(index_name text)
+RETURNS TABLE (
+ indexrelid regclass,
+ indisvalid boolean,
+ indisready boolean,
+ indislive boolean,
+ indisvisible boolean
+) AS $$
+BEGIN
+ RETURN QUERY EXECUTE format('
+ SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisvisible
+ FROM pg_index
+ WHERE indexrelid = %L::regclass', index_name);
+END;
+$$ LANGUAGE plpgsql;
+-- Create and test each index type
+-- 1. Basic single-column index
+CREATE INDEX idx_single ON index_test(data);
+SELECT show_index_status('idx_single');
+ show_index_status
+----------------------
+ (idx_single,t,t,t,t)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE data = 'data 500';
+ QUERY PLAN
+-----------------------------------------------
+ Bitmap Heap Scan on index_test
+ Recheck Cond: (data = 'data 500'::text)
+ -> Bitmap Index Scan on idx_single
+ Index Cond: (data = 'data 500'::text)
+(4 rows)
+
+ALTER INDEX idx_single INVISIBLE;
+SELECT show_index_status('idx_single');
+ show_index_status
+----------------------
+ (idx_single,t,t,t,f)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE data = 'data 500';
+ QUERY PLAN
+-------------------------------------
+ Seq Scan on index_test
+ Filter: (data = 'data 500'::text)
+(2 rows)
+
+ALTER INDEX idx_single VISIBLE;
+SELECT show_index_status('idx_single');
+ show_index_status
+----------------------
+ (idx_single,t,t,t,t)
+(1 row)
+
+-- 2. Multi-column index
+CREATE INDEX idx_multi ON index_test(num, data);
+SELECT show_index_status('idx_multi');
+ show_index_status
+---------------------
+ (idx_multi,t,t,t,t)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 50 AND data > 'data 500';
+ QUERY PLAN
+----------------------------------------------------------------
+ Bitmap Heap Scan on index_test
+ Recheck Cond: ((num = 50) AND (data > 'data 500'::text))
+ -> Bitmap Index Scan on idx_multi
+ Index Cond: ((num = 50) AND (data > 'data 500'::text))
+(4 rows)
+
+ALTER INDEX idx_multi INVISIBLE;
+SELECT show_index_status('idx_multi');
+ show_index_status
+---------------------
+ (idx_multi,t,t,t,f)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 50 AND data > 'data 500';
+ QUERY PLAN
+------------------------------------------------------
+ Seq Scan on index_test
+ Filter: ((data > 'data 500'::text) AND (num = 50))
+(2 rows)
+
+ALTER INDEX idx_multi VISIBLE;
+SELECT show_index_status('idx_multi');
+ show_index_status
+---------------------
+ (idx_multi,t,t,t,t)
+(1 row)
+
+-- 3. Partial index
+CREATE INDEX idx_partial ON index_test(num) WHERE num < 50;
+SELECT show_index_status('idx_partial');
+ show_index_status
+-----------------------
+ (idx_partial,t,t,t,t)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 25;
+ QUERY PLAN
+----------------------------------------
+ Bitmap Heap Scan on index_test
+ Recheck Cond: (num = 25)
+ -> Bitmap Index Scan on idx_partial
+ Index Cond: (num = 25)
+(4 rows)
+
+ALTER INDEX idx_partial INVISIBLE;
+SELECT show_index_status('idx_partial');
+ show_index_status
+-----------------------
+ (idx_partial,t,t,t,f)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 25;
+ QUERY PLAN
+--------------------------------------
+ Bitmap Heap Scan on index_test
+ Recheck Cond: (num = 25)
+ -> Bitmap Index Scan on idx_multi
+ Index Cond: (num = 25)
+(4 rows)
+
+ALTER INDEX idx_partial VISIBLE;
+SELECT show_index_status('idx_partial');
+ show_index_status
+-----------------------
+ (idx_partial,t,t,t,t)
+(1 row)
+
+-- 4. Expression index
+CREATE INDEX idx_expression ON index_test((lower(data)));
+SELECT show_index_status('idx_expression');
+ show_index_status
+--------------------------
+ (idx_expression,t,t,t,t)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE lower(data) = 'data 500';
+ QUERY PLAN
+------------------------------------------------------
+ Bitmap Heap Scan on index_test
+ Recheck Cond: (lower(data) = 'data 500'::text)
+ -> Bitmap Index Scan on idx_expression
+ Index Cond: (lower(data) = 'data 500'::text)
+(4 rows)
+
+ALTER INDEX idx_expression INVISIBLE;
+SELECT show_index_status('idx_expression');
+ show_index_status
+--------------------------
+ (idx_expression,t,t,t,f)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE lower(data) = 'data 500';
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on index_test
+ Filter: (lower(data) = 'data 500'::text)
+(2 rows)
+
+ALTER INDEX idx_expression VISIBLE;
+SELECT show_index_status('idx_expression');
+ show_index_status
+--------------------------
+ (idx_expression,t,t,t,t)
+(1 row)
+
+-- 5. GIN index
+CREATE INDEX idx_gin ON index_test USING gin(vector);
+SELECT show_index_status('idx_gin');
+ show_index_status
+-------------------
+ (idx_gin,t,t,t,t)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE vector @> ARRAY[500];
+ QUERY PLAN
+----------------------------------------------------
+ Bitmap Heap Scan on index_test
+ Recheck Cond: (vector @> '{500}'::integer[])
+ -> Bitmap Index Scan on idx_gin
+ Index Cond: (vector @> '{500}'::integer[])
+(4 rows)
+
+ALTER INDEX idx_gin INVISIBLE;
+SELECT show_index_status('idx_gin');
+ show_index_status
+-------------------
+ (idx_gin,t,t,t,f)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE vector @> ARRAY[500];
+ QUERY PLAN
+------------------------------------------
+ Seq Scan on index_test
+ Filter: (vector @> '{500}'::integer[])
+(2 rows)
+
+ALTER INDEX idx_gin VISIBLE;
+SELECT show_index_status('idx_gin');
+ show_index_status
+-------------------
+ (idx_gin,t,t,t,t)
+(1 row)
+
+-- 6. GiST index
+CREATE INDEX idx_gist ON index_test USING gist(range);
+SELECT show_index_status('idx_gist');
+ show_index_status
+--------------------
+ (idx_gist,t,t,t,t)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE range && int4range(100, 110);
+ QUERY PLAN
+-------------------------------------------------------
+ Bitmap Heap Scan on index_test
+ Recheck Cond: (range && '[100,110)'::int4range)
+ -> Bitmap Index Scan on idx_gist
+ Index Cond: (range && '[100,110)'::int4range)
+(4 rows)
+
+ALTER INDEX idx_gist INVISIBLE;
+SELECT show_index_status('idx_gist');
+ show_index_status
+--------------------
+ (idx_gist,t,t,t,f)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE range && int4range(100, 110);
+ QUERY PLAN
+---------------------------------------------
+ Seq Scan on index_test
+ Filter: (range && '[100,110)'::int4range)
+(2 rows)
+
+ALTER INDEX idx_gist VISIBLE;
+SELECT show_index_status('idx_gist');
+ show_index_status
+--------------------
+ (idx_gist,t,t,t,t)
+(1 row)
+
+-- 7. Covering index
+CREATE INDEX idx_covering ON index_test(num) INCLUDE (data);
+SELECT show_index_status('idx_covering');
+ show_index_status
+------------------------
+ (idx_covering,t,t,t,t)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT num, data FROM index_test WHERE num = 50;
+ QUERY PLAN
+-----------------------------------------
+ Bitmap Heap Scan on index_test
+ Recheck Cond: (num = 50)
+ -> Bitmap Index Scan on idx_covering
+ Index Cond: (num = 50)
+(4 rows)
+
+ALTER INDEX idx_covering INVISIBLE;
+SELECT show_index_status('idx_covering');
+ show_index_status
+------------------------
+ (idx_covering,t,t,t,f)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT num, data FROM index_test WHERE num = 50;
+ QUERY PLAN
+--------------------------------------
+ Bitmap Heap Scan on index_test
+ Recheck Cond: (num = 50)
+ -> Bitmap Index Scan on idx_multi
+ Index Cond: (num = 50)
+(4 rows)
+
+ALTER INDEX idx_covering VISIBLE;
+SELECT show_index_status('idx_covering');
+ show_index_status
+------------------------
+ (idx_covering,t,t,t,t)
+(1 row)
+
+-- 8. Unique index
+CREATE UNIQUE INDEX idx_unique ON index_test(id, data);
+SELECT show_index_status('idx_unique');
+ show_index_status
+----------------------
+ (idx_unique,t,t,t,t)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE id = 500 AND data = 'data 500';
+ QUERY PLAN
+----------------------------------------------------------
+ Index Scan using idx_unique on index_test
+ Index Cond: ((id = 500) AND (data = 'data 500'::text))
+(2 rows)
+
+ALTER INDEX idx_unique INVISIBLE;
+SELECT show_index_status('idx_unique');
+ show_index_status
+----------------------
+ (idx_unique,t,t,t,f)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE id = 500 AND data = 'data 500';
+ QUERY PLAN
+------------------------------------------------
+ Index Scan using index_test_pkey on index_test
+ Index Cond: (id = 500)
+ Filter: (data = 'data 500'::text)
+(3 rows)
+
+ALTER INDEX idx_unique VISIBLE;
+SELECT show_index_status('idx_unique');
+ show_index_status
+----------------------
+ (idx_unique,t,t,t,t)
+(1 row)
+
+-- 9. Function-based index
+CREATE INDEX idx_func ON index_test(get_data_length(data));
+SELECT show_index_status('idx_func');
+ show_index_status
+--------------------
+ (idx_func,t,t,t,t)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE get_data_length(data) = 10;
+ QUERY PLAN
+-----------------------------------------
+ Bitmap Heap Scan on index_test
+ Recheck Cond: (length(data) = 10)
+ -> Bitmap Index Scan on idx_func
+ Index Cond: (length(data) = 10)
+(4 rows)
+
+ALTER INDEX idx_func INVISIBLE;
+SELECT show_index_status('idx_func');
+ show_index_status
+--------------------
+ (idx_func,t,t,t,f)
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE get_data_length(data) = 10;
+ QUERY PLAN
+-------------------------------
+ Seq Scan on index_test
+ Filter: (length(data) = 10)
+(2 rows)
+
+ALTER INDEX idx_func VISIBLE;
+SELECT show_index_status('idx_func');
+ show_index_status
+--------------------
+ (idx_func,t,t,t,t)
+(1 row)
+
+-- 10. Join index
+CREATE TABLE join_test (id INT PRIMARY KEY, ref_id INT);
+INSERT INTO join_test SELECT g, g % 100 FROM generate_series(1, 1000) g;
+CREATE INDEX idx_join ON join_test(ref_id);
+SELECT show_index_status('idx_join');
+ show_index_status
+--------------------
+ (idx_join,t,t,t,t)
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT jt.id, it.data
+FROM join_test jt
+JOIN index_test it ON jt.ref_id = it.num
+WHERE jt.id BETWEEN 100 AND 200;
+ QUERY PLAN
+---------------------------------------------------------------
+ Hash Join
+ Hash Cond: (it.num = jt.ref_id)
+ -> Seq Scan on index_test it
+ -> Hash
+ -> Bitmap Heap Scan on join_test jt
+ Recheck Cond: ((id >= 100) AND (id <= 200))
+ -> Bitmap Index Scan on join_test_pkey
+ Index Cond: ((id >= 100) AND (id <= 200))
+(8 rows)
+
+-- Set all indexes to invisible to force seq scan
+ALTER INDEX idx_join INVISIBLE;
+ALTER INDEX join_test_pkey INVISIBLE;
+SELECT show_index_status('idx_join');
+ show_index_status
+--------------------
+ (idx_join,t,t,t,f)
+(1 row)
+
+SELECT show_index_status('join_test_pkey');
+ show_index_status
+--------------------------
+ (join_test_pkey,t,t,t,f)
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT jt.id, it.data
+FROM join_test jt
+JOIN index_test it ON jt.ref_id = it.num
+WHERE jt.id BETWEEN 100 AND 200;
+ QUERY PLAN
+-----------------------------------------------------
+ Hash Join
+ Hash Cond: (it.num = jt.ref_id)
+ -> Seq Scan on index_test it
+ -> Hash
+ -> Seq Scan on join_test jt
+ Filter: ((id >= 100) AND (id <= 200))
+(6 rows)
+
+ALTER INDEX idx_join VISIBLE;
+ALTER INDEX join_test_pkey VISIBLE;
+SELECT show_index_status('idx_join');
+ show_index_status
+--------------------
+ (idx_join,t,t,t,t)
+(1 row)
+
+SELECT show_index_status('join_test_pkey');
+ show_index_status
+--------------------------
+ (join_test_pkey,t,t,t,t)
+(1 row)
+
+-- Test REINDEX CONCURRENTLY
+REINDEX INDEX CONCURRENTLY idx_join;
+SELECT show_index_status('idx_join');
+ show_index_status
+--------------------
+ (idx_join,t,t,t,t)
+(1 row)
+
+ALTER INDEX idx_join INVISIBLE;
+SELECT show_index_status('idx_join');
+ show_index_status
+--------------------
+ (idx_join,t,t,t,f)
+(1 row)
+
+REINDEX INDEX CONCURRENTLY idx_join;
+SELECT show_index_status('idx_join');
+ show_index_status
+--------------------
+ (idx_join,t,t,t,f)
+(1 row)
+
+SELECT pg_get_indexdef('idx_join'::regclass);
+ pg_get_indexdef
+-------------------------------------------------------------------------------------
+ CREATE INDEX idx_join ON schema_to_reindex.join_test USING btree (ref_id) INVISIBLE
+(1 row)
+
+ALTER INDEX idx_join VISIBLE;
+SELECT pg_get_indexdef('idx_join'::regclass);
+ pg_get_indexdef
+---------------------------------------------------------------------------
+ CREATE INDEX idx_join ON schema_to_reindex.join_test USING btree (ref_id)
+(1 row)
+
+-- Test index visibility with indcheckxmin being true
+CREATE TABLE indcheckxmin_test(id int PRIMARY KEY);
+INSERT INTO indcheckxmin_test VALUES (1), (2), (3);
+CREATE INDEX CONCURRENTLY idx_checkxmin ON indcheckxmin_test(id);
+BEGIN;
+-- Simulate indcheckxmin being true
+UPDATE pg_index SET indcheckxmin = true WHERE indexrelid = 'idx_checkxmin'::regclass;
+ALTER INDEX idx_checkxmin INVISIBLE; -- expect fail
+ERROR: cannot update index visibility while indcheckxmin is true
+HINT: Wait for all transactions that might see inconsistent HOT chains to complete
+ROLLBACK;
+-- Test prepared statements with index visibility
+CREATE TABLE prep_idx_test (id int, data text);
+CREATE INDEX prep_idx1 ON prep_idx_test(data);
+INSERT INTO prep_idx_test SELECT g, 'data ' || g FROM generate_series(1,100) g;
+-- Test with visible index
+PREPARE idx_plan AS SELECT * FROM prep_idx_test WHERE data = $1;
+EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1');
+ QUERY PLAN
+---------------------------------------------
+ Bitmap Heap Scan on prep_idx_test
+ Recheck Cond: (data = 'data 1'::text)
+ -> Bitmap Index Scan on prep_idx1
+ Index Cond: (data = 'data 1'::text)
+(4 rows)
+
+EXECUTE idx_plan('data 1');
+ id | data
+----+--------
+ 1 | data 1
+(1 row)
+
+-- Test with invisible index
+ALTER INDEX prep_idx1 INVISIBLE;
+EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1');
+ QUERY PLAN
+-----------------------------------
+ Seq Scan on prep_idx_test
+ Filter: (data = 'data 1'::text)
+(2 rows)
+
+EXECUTE idx_plan('data 1');
+ id | data
+----+--------
+ 1 | data 1
+(1 row)
+
+-- Test with use_invisible_index
+SET use_invisible_index TO on;
+EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1');
+ QUERY PLAN
+---------------------------------------------
+ Bitmap Heap Scan on prep_idx_test
+ Recheck Cond: (data = 'data 1'::text)
+ -> Bitmap Index Scan on prep_idx1
+ Index Cond: (data = 'data 1'::text)
+(4 rows)
+
+EXECUTE idx_plan('data 1');
+ id | data
+----+--------
+ 1 | data 1
+(1 row)
+
+SET use_invisible_index TO off;
+-- Assert plan cache is reset and index is not used
+EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1');
+ QUERY PLAN
+-----------------------------------
+ Seq Scan on prep_idx_test
+ Filter: (data = 'data 1'::text)
+(2 rows)
+
+-- Make index visible and test again
+ALTER INDEX prep_idx1 VISIBLE;
+EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1');
+ QUERY PLAN
+---------------------------------------------
+ Bitmap Heap Scan on prep_idx_test
+ Recheck Cond: (data = 'data 1'::text)
+ -> Bitmap Index Scan on prep_idx1
+ Index Cond: (data = 'data 1'::text)
+(4 rows)
+
+EXECUTE idx_plan('data 1');
+ id | data
+----+--------
+ 1 | data 1
+(1 row)
+
+-- Test index visibility with partitioned tables
+CREATE TABLE part_tbl(id int, data text) PARTITION BY RANGE(id);
+CREATE TABLE part1 PARTITION OF part_tbl FOR VALUES FROM (1) TO (100);
+CREATE TABLE part2 PARTITION OF part_tbl FOR VALUES FROM (100) TO (200);
+INSERT INTO part_tbl
+SELECT g, 'data ' || g
+FROM generate_series(1, 199) g;
+CREATE INDEX idx_part_tbl ON part_tbl(data);
+SELECT c.relname, i.indisvisible
+FROM pg_index i
+JOIN pg_class c ON i.indexrelid = c.oid
+WHERE c.relname LIKE 'idx_part_tbl%'
+ORDER BY c.relname;
+ relname | indisvisible
+--------------+--------------
+ idx_part_tbl | t
+(1 row)
+
+-- Force use of indexes to avoid flaky test results
+SET enable_seqscan = off;
+EXPLAIN (COSTS OFF)
+SELECT * FROM part_tbl WHERE data = 'data 50';
+ QUERY PLAN
+-----------------------------------------------------------
+ Append
+ -> Index Scan using part1_data_idx on part1 part_tbl_1
+ Index Cond: (data = 'data 50'::text)
+ -> Index Scan using part2_data_idx on part2 part_tbl_2
+ Index Cond: (data = 'data 50'::text)
+(5 rows)
+
+ALTER INDEX idx_part_tbl INVISIBLE;
+SELECT c.relname, i.indisvisible
+FROM pg_index i
+JOIN pg_class c ON i.indexrelid = c.oid
+WHERE c.relname LIKE 'idx_part_tbl%'
+ORDER BY c.relname;
+ relname | indisvisible
+--------------+--------------
+ idx_part_tbl | f
+(1 row)
+
+-- Check query plan after setting invisible (should use seq scan)
+EXPLAIN (COSTS OFF)
+SELECT * FROM part_tbl WHERE data = 'data 50';
+ QUERY PLAN
+------------------------------------------
+ Append
+ -> Seq Scan on part1 part_tbl_1
+ Disabled: true
+ Filter: (data = 'data 50'::text)
+ -> Seq Scan on part2 part_tbl_2
+ Disabled: true
+ Filter: (data = 'data 50'::text)
+(7 rows)
+
+SET use_invisible_index TO on;
+EXPLAIN (COSTS OFF)
+SELECT * FROM part_tbl WHERE data = 'data 50';
+ QUERY PLAN
+-----------------------------------------------------------
+ Append
+ -> Index Scan using part1_data_idx on part1 part_tbl_1
+ Index Cond: (data = 'data 50'::text)
+ -> Index Scan using part2_data_idx on part2 part_tbl_2
+ Index Cond: (data = 'data 50'::text)
+(5 rows)
+
+SET use_invisible_index TO off;
+EXPLAIN (COSTS OFF)
+SELECT * FROM part_tbl WHERE data = 'data 50';
+ QUERY PLAN
+------------------------------------------
+ Append
+ -> Seq Scan on part1 part_tbl_1
+ Disabled: true
+ Filter: (data = 'data 50'::text)
+ -> Seq Scan on part2 part_tbl_2
+ Disabled: true
+ Filter: (data = 'data 50'::text)
+(7 rows)
+
+ALTER INDEX idx_part_tbl VISIBLE;
+SELECT c.relname, i.indisvisible
+FROM pg_index i
+JOIN pg_class c ON i.indexrelid = c.oid
+WHERE c.relname LIKE 'idx_part_tbl%'
+ORDER BY c.relname;
+ relname | indisvisible
+--------------+--------------
+ idx_part_tbl | t
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM part_tbl WHERE data = 'data 50';
+ QUERY PLAN
+-----------------------------------------------------------
+ Append
+ -> Index Scan using part1_data_idx on part1 part_tbl_1
+ Index Cond: (data = 'data 50'::text)
+ -> Index Scan using part2_data_idx on part2 part_tbl_2
+ Index Cond: (data = 'data 50'::text)
+(5 rows)
+
+SET enable_seqscan = on;
+-- Test REINDEX maintains visibility setting
+ALTER INDEX idx_part_tbl INVISIBLE;
+REINDEX INDEX idx_part_tbl;
+SELECT c.relname, i.indisvisible
+FROM pg_index i
+JOIN pg_class c ON i.indexrelid = c.oid
+WHERE c.relname LIKE 'idx_part_tbl%'
+ORDER BY c.relname;
+ relname | indisvisible
+--------------+--------------
+ idx_part_tbl | f
+(1 row)
+
+-- Clean up
+DROP TABLE index_test;
+DROP TABLE join_test;
+DROP FUNCTION get_data_length;
+DROP FUNCTION show_index_status;
+DROP TABLE indcheckxmin_test CASCADE;
+DEALLOCATE idx_plan;
+DROP TABLE prep_idx_test;
+DROP TABLE part_tbl CASCADE;
-- Failure for unauthorized user
CREATE ROLE regress_reindexuser NOLOGIN;
SET SESSION ROLE regress_reindexuser;
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index 068c66b95a..2e6293eda0 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -1415,6 +1415,297 @@ END;
-- concurrently
REINDEX SCHEMA CONCURRENTLY schema_to_reindex;
+-- Test index visibility functionality
+
+CREATE TABLE index_test(
+ id INT PRIMARY KEY,
+ data TEXT,
+ num INT,
+ vector INT[],
+ range INT4RANGE
+);
+
+INSERT INTO index_test
+SELECT
+ g,
+ 'data ' || g,
+ g % 100,
+ ARRAY[g, g+1, g+2],
+ int4range(g, g+10)
+FROM generate_series(1, 1000) g;
+
+-- Function for testing
+CREATE FUNCTION get_data_length(text) RETURNS INT AS $$
+ SELECT length($1);
+$$ LANGUAGE SQL IMMUTABLE;
+
+-- Helper function to show index status
+CREATE OR REPLACE FUNCTION show_index_status(index_name text)
+RETURNS TABLE (
+ indexrelid regclass,
+ indisvalid boolean,
+ indisready boolean,
+ indislive boolean,
+ indisvisible boolean
+) AS $$
+BEGIN
+ RETURN QUERY EXECUTE format('
+ SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisvisible
+ FROM pg_index
+ WHERE indexrelid = %L::regclass', index_name);
+END;
+$$ LANGUAGE plpgsql;
+
+-- Create and test each index type
+-- 1. Basic single-column index
+CREATE INDEX idx_single ON index_test(data);
+SELECT show_index_status('idx_single');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE data = 'data 500';
+ALTER INDEX idx_single INVISIBLE;
+SELECT show_index_status('idx_single');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE data = 'data 500';
+ALTER INDEX idx_single VISIBLE;
+SELECT show_index_status('idx_single');
+
+-- 2. Multi-column index
+CREATE INDEX idx_multi ON index_test(num, data);
+SELECT show_index_status('idx_multi');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 50 AND data > 'data 500';
+ALTER INDEX idx_multi INVISIBLE;
+SELECT show_index_status('idx_multi');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 50 AND data > 'data 500';
+ALTER INDEX idx_multi VISIBLE;
+SELECT show_index_status('idx_multi');
+
+-- 3. Partial index
+CREATE INDEX idx_partial ON index_test(num) WHERE num < 50;
+SELECT show_index_status('idx_partial');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 25;
+ALTER INDEX idx_partial INVISIBLE;
+SELECT show_index_status('idx_partial');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 25;
+ALTER INDEX idx_partial VISIBLE;
+SELECT show_index_status('idx_partial');
+
+-- 4. Expression index
+CREATE INDEX idx_expression ON index_test((lower(data)));
+SELECT show_index_status('idx_expression');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE lower(data) = 'data 500';
+ALTER INDEX idx_expression INVISIBLE;
+SELECT show_index_status('idx_expression');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE lower(data) = 'data 500';
+ALTER INDEX idx_expression VISIBLE;
+SELECT show_index_status('idx_expression');
+
+-- 5. GIN index
+CREATE INDEX idx_gin ON index_test USING gin(vector);
+SELECT show_index_status('idx_gin');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE vector @> ARRAY[500];
+ALTER INDEX idx_gin INVISIBLE;
+SELECT show_index_status('idx_gin');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE vector @> ARRAY[500];
+ALTER INDEX idx_gin VISIBLE;
+SELECT show_index_status('idx_gin');
+
+-- 6. GiST index
+CREATE INDEX idx_gist ON index_test USING gist(range);
+SELECT show_index_status('idx_gist');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE range && int4range(100, 110);
+ALTER INDEX idx_gist INVISIBLE;
+SELECT show_index_status('idx_gist');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE range && int4range(100, 110);
+ALTER INDEX idx_gist VISIBLE;
+SELECT show_index_status('idx_gist');
+
+-- 7. Covering index
+CREATE INDEX idx_covering ON index_test(num) INCLUDE (data);
+SELECT show_index_status('idx_covering');
+EXPLAIN (COSTS OFF) SELECT num, data FROM index_test WHERE num = 50;
+ALTER INDEX idx_covering INVISIBLE;
+SELECT show_index_status('idx_covering');
+EXPLAIN (COSTS OFF) SELECT num, data FROM index_test WHERE num = 50;
+ALTER INDEX idx_covering VISIBLE;
+SELECT show_index_status('idx_covering');
+
+-- 8. Unique index
+CREATE UNIQUE INDEX idx_unique ON index_test(id, data);
+SELECT show_index_status('idx_unique');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE id = 500 AND data = 'data 500';
+ALTER INDEX idx_unique INVISIBLE;
+SELECT show_index_status('idx_unique');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE id = 500 AND data = 'data 500';
+ALTER INDEX idx_unique VISIBLE;
+SELECT show_index_status('idx_unique');
+
+-- 9. Function-based index
+CREATE INDEX idx_func ON index_test(get_data_length(data));
+SELECT show_index_status('idx_func');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE get_data_length(data) = 10;
+ALTER INDEX idx_func INVISIBLE;
+SELECT show_index_status('idx_func');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE get_data_length(data) = 10;
+ALTER INDEX idx_func VISIBLE;
+SELECT show_index_status('idx_func');
+
+-- 10. Join index
+CREATE TABLE join_test (id INT PRIMARY KEY, ref_id INT);
+INSERT INTO join_test SELECT g, g % 100 FROM generate_series(1, 1000) g;
+CREATE INDEX idx_join ON join_test(ref_id);
+SELECT show_index_status('idx_join');
+
+EXPLAIN (COSTS OFF)
+SELECT jt.id, it.data
+FROM join_test jt
+JOIN index_test it ON jt.ref_id = it.num
+WHERE jt.id BETWEEN 100 AND 200;
+
+-- Set all indexes to invisible to force seq scan
+ALTER INDEX idx_join INVISIBLE;
+ALTER INDEX join_test_pkey INVISIBLE;
+SELECT show_index_status('idx_join');
+SELECT show_index_status('join_test_pkey');
+
+EXPLAIN (COSTS OFF)
+SELECT jt.id, it.data
+FROM join_test jt
+JOIN index_test it ON jt.ref_id = it.num
+WHERE jt.id BETWEEN 100 AND 200;
+
+ALTER INDEX idx_join VISIBLE;
+ALTER INDEX join_test_pkey VISIBLE;
+SELECT show_index_status('idx_join');
+SELECT show_index_status('join_test_pkey');
+
+-- Test REINDEX CONCURRENTLY
+REINDEX INDEX CONCURRENTLY idx_join;
+SELECT show_index_status('idx_join');
+
+ALTER INDEX idx_join INVISIBLE;
+SELECT show_index_status('idx_join');
+REINDEX INDEX CONCURRENTLY idx_join;
+SELECT show_index_status('idx_join');
+
+SELECT pg_get_indexdef('idx_join'::regclass);
+ALTER INDEX idx_join VISIBLE;
+SELECT pg_get_indexdef('idx_join'::regclass);
+
+-- Test index visibility with indcheckxmin being true
+
+CREATE TABLE indcheckxmin_test(id int PRIMARY KEY);
+INSERT INTO indcheckxmin_test VALUES (1), (2), (3);
+CREATE INDEX CONCURRENTLY idx_checkxmin ON indcheckxmin_test(id);
+BEGIN;
+-- Simulate indcheckxmin being true
+UPDATE pg_index SET indcheckxmin = true WHERE indexrelid = 'idx_checkxmin'::regclass;
+ALTER INDEX idx_checkxmin INVISIBLE; -- expect fail
+ROLLBACK;
+
+-- Test prepared statements with index visibility
+CREATE TABLE prep_idx_test (id int, data text);
+CREATE INDEX prep_idx1 ON prep_idx_test(data);
+INSERT INTO prep_idx_test SELECT g, 'data ' || g FROM generate_series(1,100) g;
+
+-- Test with visible index
+PREPARE idx_plan AS SELECT * FROM prep_idx_test WHERE data = $1;
+EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1');
+EXECUTE idx_plan('data 1');
+
+-- Test with invisible index
+ALTER INDEX prep_idx1 INVISIBLE;
+EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1');
+EXECUTE idx_plan('data 1');
+
+-- Test with use_invisible_index
+SET use_invisible_index TO on;
+EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1');
+EXECUTE idx_plan('data 1');
+SET use_invisible_index TO off;
+-- Assert plan cache is reset and index is not used
+EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1');
+
+-- Make index visible and test again
+ALTER INDEX prep_idx1 VISIBLE;
+EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1');
+EXECUTE idx_plan('data 1');
+
+-- Test index visibility with partitioned tables
+CREATE TABLE part_tbl(id int, data text) PARTITION BY RANGE(id);
+CREATE TABLE part1 PARTITION OF part_tbl FOR VALUES FROM (1) TO (100);
+CREATE TABLE part2 PARTITION OF part_tbl FOR VALUES FROM (100) TO (200);
+
+INSERT INTO part_tbl
+SELECT g, 'data ' || g
+FROM generate_series(1, 199) g;
+
+CREATE INDEX idx_part_tbl ON part_tbl(data);
+
+SELECT c.relname, i.indisvisible
+FROM pg_index i
+JOIN pg_class c ON i.indexrelid = c.oid
+WHERE c.relname LIKE 'idx_part_tbl%'
+ORDER BY c.relname;
+
+-- Force use of indexes to avoid flaky test results
+SET enable_seqscan = off;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM part_tbl WHERE data = 'data 50';
+
+ALTER INDEX idx_part_tbl INVISIBLE;
+
+SELECT c.relname, i.indisvisible
+FROM pg_index i
+JOIN pg_class c ON i.indexrelid = c.oid
+WHERE c.relname LIKE 'idx_part_tbl%'
+ORDER BY c.relname;
+
+-- Check query plan after setting invisible (should use seq scan)
+EXPLAIN (COSTS OFF)
+SELECT * FROM part_tbl WHERE data = 'data 50';
+
+SET use_invisible_index TO on;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM part_tbl WHERE data = 'data 50';
+
+SET use_invisible_index TO off;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM part_tbl WHERE data = 'data 50';
+
+ALTER INDEX idx_part_tbl VISIBLE;
+
+SELECT c.relname, i.indisvisible
+FROM pg_index i
+JOIN pg_class c ON i.indexrelid = c.oid
+WHERE c.relname LIKE 'idx_part_tbl%'
+ORDER BY c.relname;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM part_tbl WHERE data = 'data 50';
+
+SET enable_seqscan = on;
+
+-- Test REINDEX maintains visibility setting
+ALTER INDEX idx_part_tbl INVISIBLE;
+REINDEX INDEX idx_part_tbl;
+
+SELECT c.relname, i.indisvisible
+FROM pg_index i
+JOIN pg_class c ON i.indexrelid = c.oid
+WHERE c.relname LIKE 'idx_part_tbl%'
+ORDER BY c.relname;
+
+-- Clean up
+DROP TABLE index_test;
+DROP TABLE join_test;
+DROP FUNCTION get_data_length;
+DROP FUNCTION show_index_status;
+DROP TABLE indcheckxmin_test CASCADE;
+DEALLOCATE idx_plan;
+DROP TABLE prep_idx_test;
+DROP TABLE part_tbl CASCADE;
+
-- Failure for unauthorized user
CREATE ROLE regress_reindexuser NOLOGIN;
SET SESSION ROLE regress_reindexuser;
--
2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Commitfest app release on Feb 17 with many improvements
@ 2025-03-06 23:48 Jelte Fennema-Nio <[email protected]>
1 sibling, 2 replies; 13+ messages in thread
From: Jelte Fennema-Nio @ 2025-03-06 23:48 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: vignesh C <[email protected]>; PostgreSQL Hackers <[email protected]>; Jacob Brazeal <[email protected]>
On Thu, 6 Mar 2025 at 18:39, Jelte Fennema-Nio <[email protected]> wrote:
>
> On Thu, 6 Mar 2025 at 18:10, Tom Lane <[email protected]> wrote:
> > Please see if you can make it use patch(1). IME git is too
> > stiff-necked about slightly stale patches no matter which
> > subcommand you use.
>
> It was using patch(1) in the past for that reason, but with --3way I
> was able to get "git apply" to apply patches that patch(1) was unable
> to apply. Also with the git command you get much more useful conflict
> output.
Okay, I went for the approach of just trying everything until one
works. Starting with "git am", then patch(1), and as a final attempt
"git apply". Patch 5272 applies correctly now. I've removed any
backoff caused by repeated failures for all existing patches, so all
should be retried in the next ~48 hours.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Commitfest app release on Feb 17 with many improvements
@ 2025-03-07 03:08 Tom Lane <[email protected]>
parent: Jelte Fennema-Nio <[email protected]>
1 sibling, 0 replies; 13+ messages in thread
From: Tom Lane @ 2025-03-07 03:08 UTC (permalink / raw)
To: Jelte Fennema-Nio <[email protected]>; +Cc: vignesh C <[email protected]>; PostgreSQL Hackers <[email protected]>; Jacob Brazeal <[email protected]>
Jelte Fennema-Nio <[email protected]> writes:
> Okay, I went for the approach of just trying everything until one
> works. Starting with "git am", then patch(1), and as a final attempt
> "git apply".
+1, thanks!
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Commitfest app release on Feb 17 with many improvements
@ 2025-03-07 10:26 Andreas Karlsson <[email protected]>
parent: Jelte Fennema-Nio <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: Andreas Karlsson @ 2025-03-07 10:26 UTC (permalink / raw)
To: [email protected]
On 3/7/25 12:48 AM, Jelte Fennema-Nio wrote:
> Okay, I went for the approach of just trying everything until one
> works. Starting with "git am", then patch(1), and as a final attempt
> "git apply". Patch 5272 applies correctly now. I've removed any
> backoff caused by repeated failures for all existing patches, so all
> should be retried in the next ~48 hours.
Out of curiosity: do you track which method works? I would expect
everything to be applied with either git am or patch which can be
applied with git apply making git apply technically unnecessary.
Andreas
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Commitfest app release on Feb 17 with many improvements
@ 2025-03-07 10:49 Jelte Fennema-Nio <[email protected]>
parent: Andreas Karlsson <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Jelte Fennema-Nio @ 2025-03-07 10:49 UTC (permalink / raw)
To: Andreas Karlsson <[email protected]>; +Cc: [email protected]
On Fri, 7 Mar 2025 at 11:26, Andreas Karlsson <[email protected]> wrote:
> Out of curiosity: do you track which method works? I would expect
> everything to be applied with either git am or patch which can be
> applied with git apply making git apply technically unnecessary.
I think we need all of them...
- git apply is unable to apply the patch in 5272 on top of main
- git am, works with that 5272, but cannot apply any patch not created
by git format-patch (for instance 5522)
- I've been unable to get patch(1) not to return a failure if the
patch is already applied. So applying e.g. 5522 twice. git apply
handles that fine.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Commitfest app release on Feb 17 with many improvements
@ 2025-03-07 16:42 Tom Lane <[email protected]>
parent: Jelte Fennema-Nio <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2025-03-07 16:42 UTC (permalink / raw)
To: Jelte Fennema-Nio <[email protected]>; +Cc: Andreas Karlsson <[email protected]>; [email protected]
Jelte Fennema-Nio <[email protected]> writes:
> On Fri, 7 Mar 2025 at 11:26, Andreas Karlsson <[email protected]> wrote:
>> Out of curiosity: do you track which method works? I would expect
>> everything to be applied with either git am or patch which can be
>> applied with git apply making git apply technically unnecessary.
> I think we need all of them...
> - git apply is unable to apply the patch in 5272 on top of main
> - git am, works with that 5272, but cannot apply any patch not created
> by git format-patch (for instance 5522)
> - I've been unable to get patch(1) not to return a failure if the
> patch is already applied. So applying e.g. 5522 twice. git apply
> handles that fine.
Hm, don't you *want* a failure if the patch is already applied?
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Commitfest app release on Feb 17 with many improvements
@ 2025-03-07 17:52 Jelte Fennema-Nio <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Jelte Fennema-Nio @ 2025-03-07 17:52 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Andreas Karlsson <[email protected]>; [email protected]
On Fri, 7 Mar 2025 at 17:42, Tom Lane <[email protected]> wrote:
> Hm, don't you *want* a failure if the patch is already applied?
It's pretty common that in a larger patchset the first 1-2
small/trivial patches get committed before the rest. Having to then
send an additional email, resubmitting the rest of the patchset
because CFBot fails when re-applying those is kind of silly. This
avoids that.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Commitfest app release on Feb 17 with many improvements
@ 2025-03-07 17:57 Tom Lane <[email protected]>
parent: Jelte Fennema-Nio <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Tom Lane @ 2025-03-07 17:57 UTC (permalink / raw)
To: Jelte Fennema-Nio <[email protected]>; +Cc: Andreas Karlsson <[email protected]>; [email protected]
Jelte Fennema-Nio <[email protected]> writes:
> On Fri, 7 Mar 2025 at 17:42, Tom Lane <[email protected]> wrote:
>> Hm, don't you *want* a failure if the patch is already applied?
> It's pretty common that in a larger patchset the first 1-2
> small/trivial patches get committed before the rest. Having to then
> send an additional email, resubmitting the rest of the patchset
> because CFBot fails when re-applying those is kind of silly. This
> avoids that.
Got it. That is indeed an improvement, though of course it only
helps if those patches were applied verbatim.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Commitfest app release on Feb 17 with many improvements
@ 2025-03-09 02:20 vignesh C <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: vignesh C @ 2025-03-09 02:20 UTC (permalink / raw)
To: Jelte Fennema-Nio <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Jacob Brazeal <[email protected]>
On Fri, 31 Jan 2025 at 19:54, Jelte Fennema-Nio <[email protected]> wrote:
>
> At the FOSDEM dev meeting we discussed potential improvements to the
> commitfest app and how to handle deploying future changes with minimal
> disruption to existing workflows. We're going to try a new approach:
> announcing a new commitfest release ~2 weeks in advance, including
> release notes. That way people can try out the changes on the staging
> environment and raise concerns/objections. So hereby such an
> announcement:
>
> The next commitfest app release will take place on February 17 and
> will contain the following user facing changes:
>
> 1. Showing CFBot status on patch and commitfest pages
Couple of suggestions: a) No need to show CI status as "Needs rebase,"
"Not processed," etc., for committed patches. b) Can we add a filter
for "Needs rebase!"? This would help the CommitFest manager easily
list patches that need updating.
Regards,
Vignesh
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Commitfest app release on Feb 17 with many improvements
@ 2025-03-09 13:35 Jelte Fennema-Nio <[email protected]>
parent: vignesh C <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Jelte Fennema-Nio @ 2025-03-09 13:35 UTC (permalink / raw)
To: vignesh C <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Jacob Brazeal <[email protected]>
On Sun, 9 Mar 2025 at 03:21, vignesh C <[email protected]> wrote:
> Couple of suggestions: a) No need to show CI status as "Needs rebase,"
> "Not processed," etc., for committed patches.
Do you mean specifically for committed ones? Or just for any patch
with a "closed" status.
> b) Can we add a filter
> for "Needs rebase!"? This would help the CommitFest manager easily
> list patches that need updating.
That should be pretty easy to implement. But is that really what we
want? In the next release, sorting by "failing since" is implemented.
It sounds like that could be enough instead. i.e. do we really only
want to call out patches that need a rebase? Or also ones that have
been failing in CI for a long time?
I'm even wondering if this whole flow still makes sense. Do we really
want to send an email to the mailing list about this? And if so, why
is someone doing that manually? If people subscribe to updates for
patches that they authored, then they get these "needs rebase"
automatically. Should we maybe simply default that option to true? And
for instance send a notification automatically to all people with a
"needs rebase" CI status whenever we start a new commitfest.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Commitfest app release on Feb 17 with many improvements
@ 2025-03-11 03:47 vignesh C <[email protected]>
parent: Jelte Fennema-Nio <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: vignesh C @ 2025-03-11 03:47 UTC (permalink / raw)
To: Jelte Fennema-Nio <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Jacob Brazeal <[email protected]>
On Sun, 9 Mar 2025 at 19:05, Jelte Fennema-Nio <[email protected]> wrote:
>
> On Sun, 9 Mar 2025 at 03:21, vignesh C <[email protected]> wrote:
> > Couple of suggestions: a) No need to show CI status as "Needs rebase,"
> > "Not processed," etc., for committed patches.
>
> Do you mean specifically for committed ones? Or just for any patch
> with a "closed" status.
It is for any closed status patches.
> > b) Can we add a filter
> > for "Needs rebase!"? This would help the CommitFest manager easily
> > list patches that need updating.
>
> That should be pretty easy to implement. But is that really what we
> want? In the next release, sorting by "failing since" is implemented.
> It sounds like that could be enough instead. i.e. do we really only
> want to call out patches that need a rebase? Or also ones that have
> been failing in CI for a long time?
>
> I'm even wondering if this whole flow still makes sense. Do we really
> want to send an email to the mailing list about this? And if so, why
> is someone doing that manually? If people subscribe to updates for
> patches that they authored, then they get these "needs rebase"
> automatically. Should we maybe simply default that option to true? And
> for instance send a notification automatically to all people with a
> "needs rebase" CI status whenever we start a new commitfest.
It will be good if you can send a notification automatically to the
patch requiring a rebase when the CFBot first identifies this(not only
during the start of commitfest) and probably send this notification
once in a day for 3 or so days and then change the status to "waiting
on author" if a new rebased version is not posted in this time.
Regards,
Vignesh
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2025-03-11 03:47 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-28 05:38 [PATCH 1/2] Fix not-null constraint representation in \d+ Kyotaro Horiguchi <[email protected]>
2025-01-31 04:48 Re: Proposal to Enable/Disable Index using ALTER INDEX (with patch) Sami Imseih <[email protected]>
2025-02-01 07:35 ` Re: Proposal to Enable/Disable Index using ALTER INDEX (with patch) Shayon Mukherjee <[email protected]>
2025-03-06 23:48 ` Re: Commitfest app release on Feb 17 with many improvements Jelte Fennema-Nio <[email protected]>
2025-03-07 03:08 ` Re: Commitfest app release on Feb 17 with many improvements Tom Lane <[email protected]>
2025-03-07 10:26 ` Re: Commitfest app release on Feb 17 with many improvements Andreas Karlsson <[email protected]>
2025-03-07 10:49 ` Re: Commitfest app release on Feb 17 with many improvements Jelte Fennema-Nio <[email protected]>
2025-03-07 16:42 ` Re: Commitfest app release on Feb 17 with many improvements Tom Lane <[email protected]>
2025-03-07 17:52 ` Re: Commitfest app release on Feb 17 with many improvements Jelte Fennema-Nio <[email protected]>
2025-03-07 17:57 ` Re: Commitfest app release on Feb 17 with many improvements Tom Lane <[email protected]>
2025-03-09 02:20 ` Re: Commitfest app release on Feb 17 with many improvements vignesh C <[email protected]>
2025-03-09 13:35 ` Re: Commitfest app release on Feb 17 with many improvements Jelte Fennema-Nio <[email protected]>
2025-03-11 03:47 ` Re: Commitfest app release on Feb 17 with many improvements vignesh C <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox