Re: To what extent should tests rely on VACUUM ANALYZE? - Mailing list pgsql-hackers

I wrote:
> I experimented with the attached modified version of the patch,
> which probes just after the relevant VACUUMs and reduces the
> crankiness of ConditionalLockBufferForCleanup a bit to more nearly
> approximate what we're likely to see in the buildfarm.

Sigh, forgot to attach the patch, not that you couldn't have
guessed what's in it.

            regards, tom lane

diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index e63c86cae4..6accaec26d 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -1351,6 +1351,11 @@ vac_estimate_reltuples(Relation relation,
     old_density = old_rel_tuples / old_rel_pages;
     unscanned_pages = (double) total_pages - (double) scanned_pages;
     total_tuples = old_density * unscanned_pages + scanned_tuples;
+    ereport(LOG,
+            (errmsg("vac_estimate_reltuples(%s): od %g, sp %u tp %u, st %g orl %g tt %g",
+                    RelationGetRelationName(relation),
+                    old_density, scanned_pages, total_pages,
+                    scanned_tuples, old_rel_tuples, total_tuples)));
     return floor(total_tuples + 0.5);
 }

diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c
index f0f8d4259c..53cc74d88f 100644
--- a/src/backend/storage/buffer/bufmgr.c
+++ b/src/backend/storage/buffer/bufmgr.c
@@ -5041,6 +5041,8 @@ ConditionalLockBufferForCleanup(Buffer buffer)

     Assert(BufferIsValid(buffer));

+    if (rand() % 100 == 0) return false;
+
     if (BufferIsLocal(buffer))
     {
         refcount = LocalRefCount[-buffer - 1];
diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out
index 8370c1561c..8841581d0a 100644
--- a/src/test/regress/expected/sanity_check.out
+++ b/src/test/regress/expected/sanity_check.out
@@ -1,4 +1,24 @@
+select c.relname,c.relpages,c.reltuples,c.relallvisible,s.autovacuum_count,s.autoanalyze_count
+from pg_class c
+left join pg_stat_all_tables s on c.oid = s.relid
+where c.relname like 'tenk_' order by c.relname;
+ relname | relpages | reltuples | relallvisible | autovacuum_count | autoanalyze_count
+---------+----------+-----------+---------------+------------------+-------------------
+ tenk1   |      345 |     10000 |           345 |                0 |                 0
+ tenk2   |      345 |     10000 |           345 |                0 |                 0
+(2 rows)
+
 VACUUM;
+select c.relname,c.relpages,c.reltuples,c.relallvisible,s.autovacuum_count,s.autoanalyze_count
+from pg_class c
+left join pg_stat_all_tables s on c.oid = s.relid
+where c.relname like 'tenk_' order by c.relname;
+ relname | relpages | reltuples | relallvisible | autovacuum_count | autoanalyze_count
+---------+----------+-----------+---------------+------------------+-------------------
+ tenk1   |      345 |     10000 |           345 |                0 |                 0
+ tenk2   |      345 |     10000 |           345 |                0 |                 0
+(2 rows)
+
 --
 -- Sanity check: every system catalog that has OIDs should have
 -- a unique index on OID.  This ensures that the OIDs will be unique,
diff --git a/src/test/regress/expected/test_setup.out b/src/test/regress/expected/test_setup.out
index 3d0eeec996..84943ecbae 100644
--- a/src/test/regress/expected/test_setup.out
+++ b/src/test/regress/expected/test_setup.out
@@ -138,6 +138,16 @@ COPY tenk1 FROM :'filename';
 VACUUM ANALYZE tenk1;
 CREATE TABLE tenk2 AS SELECT * FROM tenk1;
 VACUUM ANALYZE tenk2;
+select c.relname,c.relpages,c.reltuples,c.relallvisible,s.autovacuum_count,s.autoanalyze_count
+from pg_class c
+left join pg_stat_all_tables s on c.oid = s.relid
+where c.relname like 'tenk_' order by c.relname;
+ relname | relpages | reltuples | relallvisible | autovacuum_count | autoanalyze_count
+---------+----------+-----------+---------------+------------------+-------------------
+ tenk1   |      345 |     10000 |           345 |                0 |                 0
+ tenk2   |      345 |     10000 |           345 |                0 |                 0
+(2 rows)
+
 CREATE TABLE person (
     name         text,
     age            int4,
diff --git a/src/test/regress/sql/sanity_check.sql b/src/test/regress/sql/sanity_check.sql
index 162e5324b5..28635a1287 100644
--- a/src/test/regress/sql/sanity_check.sql
+++ b/src/test/regress/sql/sanity_check.sql
@@ -1,5 +1,15 @@
+select c.relname,c.relpages,c.reltuples,c.relallvisible,s.autovacuum_count,s.autoanalyze_count
+from pg_class c
+left join pg_stat_all_tables s on c.oid = s.relid
+where c.relname like 'tenk_' order by c.relname;
+
 VACUUM;

+select c.relname,c.relpages,c.reltuples,c.relallvisible,s.autovacuum_count,s.autoanalyze_count
+from pg_class c
+left join pg_stat_all_tables s on c.oid = s.relid
+where c.relname like 'tenk_' order by c.relname;
+
 --
 -- Sanity check: every system catalog that has OIDs should have
 -- a unique index on OID.  This ensures that the OIDs will be unique,
diff --git a/src/test/regress/sql/test_setup.sql b/src/test/regress/sql/test_setup.sql
index 06b0e2121f..a96c4b36f5 100644
--- a/src/test/regress/sql/test_setup.sql
+++ b/src/test/regress/sql/test_setup.sql
@@ -167,6 +167,11 @@ VACUUM ANALYZE tenk1;
 CREATE TABLE tenk2 AS SELECT * FROM tenk1;
 VACUUM ANALYZE tenk2;

+select c.relname,c.relpages,c.reltuples,c.relallvisible,s.autovacuum_count,s.autoanalyze_count
+from pg_class c
+left join pg_stat_all_tables s on c.oid = s.relid
+where c.relname like 'tenk_' order by c.relname;
+
 CREATE TABLE person (
     name         text,
     age            int4,