On 2024-07-26 Fr 1:46 PM, Masahiko Sawada wrote:
On Thu, Jul 25, 2024 at 6:52 PM Andrew Dunstan <and...@dunslane.net> wrote:
On 2024-07-25 Th 3:40 PM, Masahiko Sawada wrote:
On Thu, Jul 25, 2024 at 11:06 AM Masahiko Sawada <sawada.m...@gmail.com> wrote:
On Thu, Jul 25, 2024 at 10:56 AM Andrew Dunstan <and...@dunslane.net> wrote:
On 2024-07-23 Tu 6:59 PM, Masahiko Sawada wrote:
See
<https://bitbucket.org/adunstan/rotfang-fdw/downloads/xid-wraparound-result.tar.bz2>
The failure logs are from a run where both tests 1 and 2 failed.
Thank you for sharing the logs.
I think that the problem seems to match what Alexander Lakhin
mentioned[1]. Probably we can fix such a race condition somehow but
I'm not sure it's worth it as setting autovacuum = off and
autovacuum_max_workers = 1 (or a low number) is an extremely rare
case. I think it would be better to stabilize these tests. One idea is
to turn the autovacuum GUC parameter on while setting
autovacuum_enabled = off for each table. That way, we can ensure that
autovacuum workers are launched. And I think it seems to align real
use cases.
Regards,
[1]
https://www.postgresql.org/message-id/02373ec3-50c6-df5a-0d65-5b9b1c0c86d6%40gmail.com
OK, do you want to propose a patch?
Yes, I'll prepare and share it soon.
I've attached the patch. Could you please test if the patch fixes the
instability you observed?
Since we turn off autovacuum on all three tests and we wait for
autovacuum to complete processing databases, these tests potentially
have a similar (but lower) risk. So I modified these tests to turn it
on so we can ensure the autovacuum runs periodically.
I assume you actually meant to remove the "autovacuum = off" in
003_wraparound.pl. With that change in your patch I retried my test, but on iteration 100
out of 100 it failed on test 002_limits.pl.
I think we need to remove the "autovacuum = off' also in 002_limits.pl
as it waits for autovacuum to process both template0 and template1
databases. Just to be clear, the failure happened even without
"autovacuum = off"?
The attached patch, a slight modification of yours, removes "autovacuum
= off" for all three tests, and given that a set of 200 runs was clean
for me.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
diff --git a/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl b/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
index 37550b67a4..2692b35f34 100644
--- a/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
+++ b/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
@@ -18,7 +18,6 @@ my $node = PostgreSQL::Test::Cluster->new('main');
$node->init;
$node->append_conf(
'postgresql.conf', qq[
-autovacuum = off # run autovacuum only when to anti wraparound
autovacuum_naptime = 1s
# so it's easier to verify the order of operations
autovacuum_max_workers = 1
@@ -27,23 +26,25 @@ log_autovacuum_min_duration = 0
$node->start;
$node->safe_psql('postgres', 'CREATE EXTENSION xid_wraparound');
-# Create tables for a few different test scenarios
+# Create tables for a few different test scenarios. We disable autovacuum
+# on these tables to run it only to prevent wraparound.
$node->safe_psql(
'postgres', qq[
-CREATE TABLE large(id serial primary key, data text, filler text default repeat(random()::text, 10));
+CREATE TABLE large(id serial primary key, data text, filler text default repeat(random()::text, 10))
+ WITH (autovacuum_enabled = off);
INSERT INTO large(data) SELECT generate_series(1,30000);
-CREATE TABLE large_trunc(id serial primary key, data text, filler text default repeat(random()::text, 10));
+CREATE TABLE large_trunc(id serial primary key, data text, filler text default repeat(random()::text, 10))
+ WITH (autovacuum_enabled = off);
INSERT INTO large_trunc(data) SELECT generate_series(1,30000);
-CREATE TABLE small(id serial primary key, data text, filler text default repeat(random()::text, 10));
+CREATE TABLE small(id serial primary key, data text, filler text default repeat(random()::text, 10))
+ WITH (autovacuum_enabled = off);
INSERT INTO small(data) SELECT generate_series(1,15000);
-CREATE TABLE small_trunc(id serial primary key, data text, filler text default repeat(random()::text, 10));
+CREATE TABLE small_trunc(id serial primary key, data text, filler text default repeat(random()::text, 10))
+ WITH (autovacuum_enabled = off);
INSERT INTO small_trunc(data) SELECT generate_series(1,15000);
-
-CREATE TABLE autovacuum_disabled(id serial primary key, data text) WITH (autovacuum_enabled=false);
-INSERT INTO autovacuum_disabled(data) SELECT generate_series(1,1000);
]);
# Bump the query timeout to avoid false negatives on slow test systems.
@@ -63,7 +64,6 @@ $background_psql->query_safe(
DELETE FROM large_trunc WHERE id > 10000;
DELETE FROM small WHERE id % 2 = 0;
DELETE FROM small_trunc WHERE id > 1000;
- DELETE FROM autovacuum_disabled WHERE id % 2 = 0;
]);
# Consume 2 billion XIDs, to get us very close to wraparound
@@ -107,20 +107,18 @@ $ret = $node->safe_psql(
'postgres', qq[
SELECT relname, age(relfrozenxid) > current_setting('autovacuum_freeze_max_age')::int
FROM pg_class
-WHERE relname IN ('large', 'large_trunc', 'small', 'small_trunc', 'autovacuum_disabled')
+WHERE relname IN ('large', 'large_trunc', 'small', 'small_trunc')
ORDER BY 1
]);
-is( $ret, "autovacuum_disabled|f
-large|f
+is( $ret, "large|f
large_trunc|f
small|f
small_trunc|f", "all tables are vacuumed");
# Check if vacuum failsafe was triggered for each table.
my $log_contents = slurp_file($node->logfile, $log_offset);
-foreach my $tablename ('large', 'large_trunc', 'small', 'small_trunc',
- 'autovacuum_disabled')
+foreach my $tablename ('large', 'large_trunc', 'small', 'small_trunc')
{
like(
$log_contents,
diff --git a/src/test/modules/xid_wraparound/t/002_limits.pl b/src/test/modules/xid_wraparound/t/002_limits.pl
index c02c287167..aca3fa1514 100644
--- a/src/test/modules/xid_wraparound/t/002_limits.pl
+++ b/src/test/modules/xid_wraparound/t/002_limits.pl
@@ -27,17 +27,17 @@ my $node = PostgreSQL::Test::Cluster->new('wraparound');
$node->init;
$node->append_conf(
'postgresql.conf', qq[
-autovacuum = off # run autovacuum only to prevent wraparound
autovacuum_naptime = 1s
log_autovacuum_min_duration = 0
]);
$node->start;
$node->safe_psql('postgres', 'CREATE EXTENSION xid_wraparound');
-# Create a test table
+# Create a test table. We disable autovacuum on the table to run it only
+# to prevent wraparound.
$node->safe_psql(
'postgres', qq[
-CREATE TABLE wraparoundtest(t text);
+CREATE TABLE wraparoundtest(t text) WITH (autovacuum_enabled = off);
INSERT INTO wraparoundtest VALUES ('start');
]);
diff --git a/src/test/modules/xid_wraparound/t/003_wraparounds.pl b/src/test/modules/xid_wraparound/t/003_wraparounds.pl
index 88063b4b52..3eaa46a94d 100644
--- a/src/test/modules/xid_wraparound/t/003_wraparounds.pl
+++ b/src/test/modules/xid_wraparound/t/003_wraparounds.pl
@@ -21,7 +21,6 @@ my $node = PostgreSQL::Test::Cluster->new('wraparound');
$node->init;
$node->append_conf(
'postgresql.conf', qq[
-autovacuum = off # run autovacuum only when to anti wraparound
autovacuum_naptime = 1s
# so it's easier to verify the order of operations
autovacuum_max_workers = 1
@@ -30,10 +29,11 @@ log_autovacuum_min_duration = 0
$node->start;
$node->safe_psql('postgres', 'CREATE EXTENSION xid_wraparound');
-# Create a test table
+# Create a test table. We disable autovacuum on the table to run
+# it only to prevent wraparound.
$node->safe_psql(
'postgres', qq[
-CREATE TABLE wraparoundtest(t text);
+CREATE TABLE wraparoundtest(t text) WITH (autovacuum_enabled = off);
INSERT INTO wraparoundtest VALUES ('beginning');
]);
