On 2021-Jun-30, Alexander Pyhalov wrote:
> I've seen the following effect on PostgreSQL 14 stable branch.
> Index, created on partitioned table, disappears from pg_dump or psql \d
> output.
> This seems to begin after analyze. Partitoned relation relhasindex pg_class
> field suddenly becomes false.
Yeah, that seems correct.
I didn't verify your test case, but after looking at the code I thought
there was a bit too much churn and the new conditions looked quite messy
and unexplained. It seems simpler to be explicit at the start about
what we're doing, and keep nindexes=0 for partitioned tables; with that,
the code works unchanged because the "for" loops do nothing without
having to check for anything. My proposal is attached.
I did run the tests and they do pass, but I didn't look very closely at
what the tests are actually doing.
I noticed that part of that comment seems to be a leftover from ... I
don't know when: "We do not analyze index columns if there was an
explicit column list in the ANALYZE command, however." I suppose this
is about some code that was removed, but I didn't dig into it.
--
Álvaro Herrera Valdivia, Chile
"How strange it is to find the words "Perl" and "saner" in such close
proximity, with no apparent sense of irony. I doubt that Larry himself
could have managed it." (ncm, http://lwn.net/Articles/174769/)
>From 05ca1eed3e2e1632477586ee5bc4d7b24ad045aa Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyha...@postgrespro.ru>
Date: Wed, 30 Jun 2021 17:22:37 +0300
Subject: [PATCH v3] Set relhasindex for partitioned tables correctly.
The issue appeared after 0e69f705cc1a3df273b38c9883fb5765991e04fe:
in this commit we unconditionally set nindexes to 0 for partitioned
relations.
---
src/backend/commands/analyze.c | 29 ++++++++++++++++++----------
src/test/regress/expected/vacuum.out | 17 ++++++++++++++++
src/test/regress/sql/vacuum.sql | 12 ++++++++++++
3 files changed, 48 insertions(+), 10 deletions(-)
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index 426c1e6710..3980baa41c 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -419,21 +419,30 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
/*
* Open all indexes of the relation, and see if there are any analyzable
- * columns in the indexes. We do not analyze index columns if there was
- * an explicit column list in the ANALYZE command, however. If we are
- * doing a recursive scan, we don't want to touch the parent's indexes at
- * all.
+ * columns in the indexes. If we are doing a recursive scan, we don't
+ * want to touch the parent's indexes at all. If we're processing a
+ * partitioned table, we need to know if there are any indexes, but we
+ * don't want to process them.
*/
- if (!inh)
+ if (!inh || onerel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ {
vac_open_indexes(onerel, AccessShareLock, &nindexes, &Irel);
+ hasindex = nindexes > 0;
+ if (onerel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ vac_close_indexes(nindexes, Irel, NoLock);
+ nindexes = 0;
+ Irel = NULL;
+ }
+ }
else
{
Irel = NULL;
nindexes = 0;
+ hasindex = false;
}
- hasindex = (nindexes > 0);
indexdata = NULL;
- if (hasindex)
+ if (nindexes > 0)
{
indexdata = (AnlIndexData *) palloc0(nindexes * sizeof(AnlIndexData));
for (ind = 0; ind < nindexes; ind++)
@@ -572,7 +581,7 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
MemoryContextResetAndDeleteChildren(col_context);
}
- if (hasindex)
+ if (nindexes > 0)
compute_index_stats(onerel, totalrows,
indexdata, nindexes,
rows, numrows,
@@ -660,10 +669,10 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
/*
* Partitioned tables don't have storage, so we don't set any fields
* in their pg_class entries except for reltuples, which is necessary
- * for auto-analyze to work properly.
+ * for auto-analyze to work properly, and relhasindex.
*/
vac_update_relstats(onerel, -1, totalrows,
- 0, false, InvalidTransactionId,
+ 0, hasindex, InvalidTransactionId,
InvalidMultiXactId,
in_outer_xact);
}
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index e5771462d5..e3d462b66f 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -199,6 +199,23 @@ VACUUM ANALYZE vacparted(a,b,a);
ERROR: column "a" of relation "vacparted" appears more than once
ANALYZE vacparted(a,b,b);
ERROR: column "b" of relation "vacparted" appears more than once
+-- partitioned table with index
+CREATE TABLE vacparted_i (a int primary key, b varchar(100)) PARTITION BY HASH (a);
+CREATE TABLE vacparted_i1 PARTITION OF vacparted_i FOR VALUES WITH ( MODULUS 2, REMAINDER 0);
+CREATE TABLE vacparted_i2 PARTITION OF vacparted_i FOR VALUES WITH ( MODULUS 2, REMAINDER 1);
+INSERT INTO vacparted_i SELECT i, 'test_'|| i from generate_series(1,10) i;
+VACUUM (ANALYZE) vacparted_i;
+VACUUM (FULL) vacparted_i;
+VACUUM (FREEZE) vacparted_i;
+SELECT relname,relhasindex from pg_class where relname LIKE 'vacparted_i%' and relkind in ('p','r') ORDER BY relname;
+ relname | relhasindex
+--------------+-------------
+ vacparted_i | t
+ vacparted_i1 | t
+ vacparted_i2 | t
+(3 rows)
+
+DROP TABLE vacparted_i;
-- multiple tables specified
VACUUM vaccluster, vactst;
VACUUM vacparted, does_not_exist;
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index f220fc28a7..b670612b54 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -166,10 +166,22 @@ VACUUM (ANALYZE) vacparted;
VACUUM (FULL) vacparted;
VACUUM (FREEZE) vacparted;
+
-- check behavior with duplicate column mentions
VACUUM ANALYZE vacparted(a,b,a);
ANALYZE vacparted(a,b,b);
+-- partitioned table with index
+CREATE TABLE vacparted_i (a int primary key, b varchar(100)) PARTITION BY HASH (a);
+CREATE TABLE vacparted_i1 PARTITION OF vacparted_i FOR VALUES WITH ( MODULUS 2, REMAINDER 0);
+CREATE TABLE vacparted_i2 PARTITION OF vacparted_i FOR VALUES WITH ( MODULUS 2, REMAINDER 1);
+INSERT INTO vacparted_i SELECT i, 'test_'|| i from generate_series(1,10) i;
+VACUUM (ANALYZE) vacparted_i;
+VACUUM (FULL) vacparted_i;
+VACUUM (FREEZE) vacparted_i;
+SELECT relname,relhasindex from pg_class where relname LIKE 'vacparted_i%' and relkind in ('p','r') ORDER BY relname;
+DROP TABLE vacparted_i;
+
-- multiple tables specified
VACUUM vaccluster, vactst;
VACUUM vacparted, does_not_exist;
--
2.30.2
