diff options
| author | Jeff Davis | 2025-03-07 03:36:34 +0000 |
|---|---|---|
| committer | Jeff Davis | 2025-03-07 03:39:14 +0000 |
| commit | d611f8b1587b8f30caa7c0da99ae5d28e914d54f (patch) | |
| tree | 81e8e261c9988b878fd80e095814e8029d53d246 /src/test | |
| parent | 19e57f4f78e4354d9a21c284868373d28bb0d368 (diff) | |
CREATE INDEX: don't update table stats if autovacuum=off.
We previously fixed this for binary upgrade in 71b66171d0, but a
similar problem remained when dumping statistics without data.
Fix by not opportunistically updating table stats during CREATE INDEX
when autovacuum is disabled. For stats to be stable at all, the server
needs to be aware that it should not take every opportunity to update
stats. Per discussion, autovacuum=off is a signal that the user
expects stats to be stable; though if necessary, we could create
a more specific mode in the future.
Reported-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Discussion: https://postgr.es/m/CAExHW5vf9D+8-a5_BEX3y=2y_xY9hiCxV1=C+FnxDvfprWvkng@mail.gmail.com
Discussion: https://postgr.es/m/ca81cbf6e6ea2af838df972801ad4da52640a503.camel%40j-davis.com
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/stats_import.out | 29 | ||||
| -rw-r--r-- | src/test/regress/sql/stats_import.sql | 17 |
2 files changed, 46 insertions, 0 deletions
diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out index ebba14c6a1d..1f46d5e7854 100644 --- a/src/test/regress/expected/stats_import.out +++ b/src/test/regress/expected/stats_import.out @@ -12,7 +12,36 @@ CREATE TABLE stats_import.test( arange int4range, tags text[] ) WITH (autovacuum_enabled = false); +SELECT + pg_catalog.pg_restore_relation_stats( + 'relation', 'stats_import.test'::regclass, + 'relpages', 18::integer, + 'reltuples', 21::real, + 'relallvisible', 24::integer, + 'relallfrozen', 27::integer); + pg_restore_relation_stats +--------------------------- + t +(1 row) + +-- CREATE INDEX on a table with autovac disabled should not overwrite +-- stats CREATE INDEX test_i ON stats_import.test(id); +SELECT relname, relpages, reltuples, relallvisible, relallfrozen +FROM pg_class +WHERE oid = 'stats_import.test'::regclass +ORDER BY relname; + relname | relpages | reltuples | relallvisible | relallfrozen +---------+----------+-----------+---------------+-------------- + test | 18 | 21 | 24 | 27 +(1 row) + +SELECT pg_clear_relation_stats('stats_import.test'::regclass); + pg_clear_relation_stats +------------------------- + +(1 row) + -- -- relstats tests -- diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql index 8d04ff4f378..0ec590688c2 100644 --- a/src/test/regress/sql/stats_import.sql +++ b/src/test/regress/sql/stats_import.sql @@ -15,8 +15,25 @@ CREATE TABLE stats_import.test( tags text[] ) WITH (autovacuum_enabled = false); +SELECT + pg_catalog.pg_restore_relation_stats( + 'relation', 'stats_import.test'::regclass, + 'relpages', 18::integer, + 'reltuples', 21::real, + 'relallvisible', 24::integer, + 'relallfrozen', 27::integer); + +-- CREATE INDEX on a table with autovac disabled should not overwrite +-- stats CREATE INDEX test_i ON stats_import.test(id); +SELECT relname, relpages, reltuples, relallvisible, relallfrozen +FROM pg_class +WHERE oid = 'stats_import.test'::regclass +ORDER BY relname; + +SELECT pg_clear_relation_stats('stats_import.test'::regclass); + -- -- relstats tests -- |