Hi.
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.
The issue happens after
commit 0e69f705cc1a3df273b38c9883fb5765991e04fe (HEAD, refs/bisect/bad)
Author: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Fri Apr 9 11:29:08 2021 -0400
Set pg_class.reltuples for partitioned tables
When commit 0827e8af70f4 added auto-analyze support for partitioned
tables, it included code to obtain reltuples for the partitioned
table
as a number of catalog accesses to read pg_class.reltuples for each
partition. That's not only very inefficient, but also problematic
because autovacuum doesn't hold any locks on any of those tables --
and
doesn't want to. Replace that code with a read of
pg_class.reltuples
for the partitioned table, and make sure ANALYZE and TRUNCATE
properly
maintain that value.
I found no code that would be affected by the change of relpages
from
zero to non-zero for partitioned tables, and no other code that
should
be maintaining it, but if there is, hopefully it'll be an easy fix.
Per buildfarm.
Author: Álvaro Herrera <alvhe...@alvh.no-ip.org>
Reviewed-by: Zhihong Yu <z...@yugabyte.com>
It seems that in this commit we unconditionally overwrite this data with
0.
I've tried to fix it by getting this information when inh is true and
ignoring nindexes when inh is not true.
--
Best regards,
Alexander Pyhalov,
Postgres Professional
From 0e66025a32c6e848b2b77355631b06b4b8d4dd08 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] 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 | 53 ++++++++++++++++------------
src/test/regress/expected/vacuum.out | 18 ++++++++++
src/test/regress/sql/vacuum.sql | 12 +++++++
3 files changed, 60 insertions(+), 23 deletions(-)
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index 426c1e67109..9c8913a1619 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -424,8 +424,10 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
* doing a recursive scan, we don't want to touch the parent's indexes at
* all.
*/
- if (!inh)
+ if (!inh || onerel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ {
vac_open_indexes(onerel, AccessShareLock, &nindexes, &Irel);
+ }
else
{
Irel = NULL;
@@ -433,7 +435,7 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
}
hasindex = (nindexes > 0);
indexdata = NULL;
- if (hasindex)
+ if (hasindex && !inh)
{
indexdata = (AnlIndexData *) palloc0(nindexes * sizeof(AnlIndexData));
for (ind = 0; ind < nindexes; ind++)
@@ -487,14 +489,17 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
if (targrows < vacattrstats[i]->minrows)
targrows = vacattrstats[i]->minrows;
}
- for (ind = 0; ind < nindexes; ind++)
+ if (!inh)
{
- AnlIndexData *thisdata = &indexdata[ind];
-
- for (i = 0; i < thisdata->attr_cnt; i++)
+ for (ind = 0; ind < nindexes; ind++)
{
- if (targrows < thisdata->vacattrstats[i]->minrows)
- targrows = thisdata->vacattrstats[i]->minrows;
+ AnlIndexData *thisdata = &indexdata[ind];
+
+ for (i = 0; i < thisdata->attr_cnt; i++)
+ {
+ if (targrows < thisdata->vacattrstats[i]->minrows)
+ targrows = thisdata->vacattrstats[i]->minrows;
+ }
}
}
@@ -572,7 +577,7 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
MemoryContextResetAndDeleteChildren(col_context);
}
- if (hasindex)
+ if (hasindex & !inh)
compute_index_stats(onerel, totalrows,
indexdata, nindexes,
rows, numrows,
@@ -589,23 +594,25 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
update_attstats(RelationGetRelid(onerel), inh,
attr_cnt, vacattrstats);
- for (ind = 0; ind < nindexes; ind++)
+ if (!inh)
{
- AnlIndexData *thisdata = &indexdata[ind];
+ for (ind = 0; ind < nindexes; ind++)
+ {
+ AnlIndexData *thisdata = &indexdata[ind];
- update_attstats(RelationGetRelid(Irel[ind]), false,
- thisdata->attr_cnt, thisdata->vacattrstats);
- }
+ update_attstats(RelationGetRelid(Irel[ind]), false,
+ thisdata->attr_cnt, thisdata->vacattrstats);
+ }
- /*
- * Build extended statistics (if there are any).
- *
- * For now we only build extended statistics on individual relations,
- * not for relations representing inheritance trees.
- */
- if (!inh)
+ /*
+ * Build extended statistics (if there are any).
+ *
+ * For now we only build extended statistics on individual
+ * relations, not for relations representing inheritance trees.
+ */
BuildRelationExtStatistics(onerel, totalrows, numrows, rows,
attr_cnt, vacattrstats);
+ }
}
pgstat_progress_update_param(PROGRESS_ANALYZE_PHASE,
@@ -663,7 +670,7 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
* for auto-analyze to work properly.
*/
vac_update_relstats(onerel, -1, totalrows,
- 0, false, InvalidTransactionId,
+ 0, hasindex, InvalidTransactionId,
InvalidMultiXactId,
in_outer_xact);
}
@@ -704,7 +711,7 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
* amvacuumcleanup() when called in ANALYZE-only mode. The only exception
* among core index AMs is GIN/ginvacuumcleanup().
*/
- if (!(params->options & VACOPT_VACUUM))
+ if (!(params->options & VACOPT_VACUUM) && !inh)
{
for (ind = 0; ind < nindexes; ind++)
{
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index e5771462d57..405c6cca94b 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -199,6 +199,24 @@ 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 SELECT i, 'test_'|| i from generate_series(1,10) i;
+ERROR: value too long for type character(1)
+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 f220fc28a70..94d69192487 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 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.25.1
