pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Add a perl function in Cluster.pm to generate WAL
24+ messages / 7 participants
[nested] [flat]
Commits from this thread:
2023-12-21 c161ab74f7 Add PostgreSQL::Test::Cluster::advance_wal
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2023-06-11 01:44 Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
0 siblings, 1 reply; 24+ messages in thread
From: Bharath Rupireddy @ 2023-06-11 01:44 UTC (permalink / raw)
To: Kyotaro Horiguchi <horikyota.ntt@gmail.com>; Michael Paquier <michael@paquier.xyz>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; Andrew Dunstan <andrew@dunslane.net>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Wed, Aug 24, 2022 at 6:42 AM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:
>
> At Tue, 16 Aug 2022 18:40:49 +0200, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote in
> > On 2022-Aug-16, Andrew Dunstan wrote:
> >
> > > I don't think there's a hard and fast rule about it. Certainly the case
> > > would be more compelling if the functions were used across different TAP
> > > suites. The SSL suite has suite-specific modules. That's a pattern also
> > > worth considering. e.g something like.
> > >
> > > use FindBin qw($Bin);
> > > use lib $Bin;
> > > use MySuite;
> > >
> > > and then you put your common routines in MySuite.pm in the same
> > > directory as the TAP test files.
> >
> > Yeah, I agree with that for advance_wal. Regarding find_in_log, that
> > one seems general enough to warrant being in Cluster.pm -- consider
> > issues_sql_like, which also slurps_file($log). That could be unified a
> > little bit, I think.
>
> +1
With the generalized function for find_in_log() has been added as part
of https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=e25e5f7fc6b74c9d4ce82627e9145ef55...,
I'm proposing a generalized function for advance_wal(). Please find
the attached patch.
I tried to replace the existing tests with the new cluster function
advance_wal(). Please let me know if I'm missing any other tests.
Also, this new function can be used by an in-progress feature -
https://commitfest.postgresql.org/43/3663/.
Thoughts?
FWIW, it's discussed here -
https://www.postgresql.org/message-id/ZIKVd%2Ba43UfsIWJE%40paquier.xyz.
--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Attachments:
v1-0001-Add-a-perl-function-in-Cluster.pm-to-generate-WAL.patch (application/x-patch, 7.1K) download
From ab7a5b56e0ed84fb0cceef1892dc435fed877029 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Sat, 10 Jun 2023 08:05:20 +0000
Subject: [PATCH v1] Add a perl function in Cluster.pm to generate WAL
This commit adds a perl function in Cluster.pm to generate WAL.
Some TAP tests are now using their own way to generate WAL.
Generalizing this functionality enables multiple TAP tests to
reuse the functionality.
---
src/test/perl/PostgreSQL/Test/Cluster.pm | 20 ++++++++
src/test/recovery/t/001_stream_rep.pl | 6 +--
src/test/recovery/t/019_replslot_limit.pl | 46 ++++---------------
.../t/035_standby_logical_decoding.pl | 7 +--
4 files changed, 33 insertions(+), 46 deletions(-)
diff --git a/src/test/perl/PostgreSQL/Test/Cluster.pm b/src/test/perl/PostgreSQL/Test/Cluster.pm
index 5e161dbee6..cf2202d170 100644
--- a/src/test/perl/PostgreSQL/Test/Cluster.pm
+++ b/src/test/perl/PostgreSQL/Test/Cluster.pm
@@ -3120,6 +3120,26 @@ sub create_logical_slot_on_standby
=pod
+=item $node->advance_wal($n)
+
+Advance WAL of given node by $n segments
+
+=cut
+
+sub advance_wal
+{
+ my ($self, $n) = @_;
+
+ # Advance by $n segments (= (wal_segment_size * $n) bytes).
+ for (my $i = 0; $i < $n; $i++)
+ {
+ $self->safe_psql('postgres',
+ "CREATE TABLE tt (); DROP TABLE tt; SELECT pg_switch_wal();");
+ }
+}
+
+=pod
+
=back
=cut
diff --git a/src/test/recovery/t/001_stream_rep.pl b/src/test/recovery/t/001_stream_rep.pl
index 0c72ba0944..0e256dab8d 100644
--- a/src/test/recovery/t/001_stream_rep.pl
+++ b/src/test/recovery/t/001_stream_rep.pl
@@ -496,11 +496,7 @@ $node_primary->safe_psql('postgres',
my $segment_removed = $node_primary->safe_psql('postgres',
'SELECT pg_walfile_name(pg_current_wal_lsn())');
chomp($segment_removed);
-$node_primary->psql(
- 'postgres', "
- CREATE TABLE tab_phys_slot (a int);
- INSERT INTO tab_phys_slot VALUES (generate_series(1,10));
- SELECT pg_switch_wal();");
+$node_primary->advance_wal(1);
my $current_lsn =
$node_primary->safe_psql('postgres', "SELECT pg_current_wal_lsn();");
chomp($current_lsn);
diff --git a/src/test/recovery/t/019_replslot_limit.pl b/src/test/recovery/t/019_replslot_limit.pl
index 33e50ad933..a6e427ebf1 100644
--- a/src/test/recovery/t/019_replslot_limit.pl
+++ b/src/test/recovery/t/019_replslot_limit.pl
@@ -59,7 +59,7 @@ $result = $node_primary->safe_psql('postgres',
is($result, "reserved|t", 'check the catching-up state');
# Advance WAL by five segments (= 5MB) on primary
-advance_wal($node_primary, 1);
+$node_primary->advance_wal(1);
$node_primary->safe_psql('postgres', "CHECKPOINT;");
# The slot is always "safe" when fitting max_wal_size
@@ -69,7 +69,7 @@ $result = $node_primary->safe_psql('postgres',
is($result, "reserved|t",
'check that it is safe if WAL fits in max_wal_size');
-advance_wal($node_primary, 4);
+$node_primary->advance_wal(4);
$node_primary->safe_psql('postgres', "CHECKPOINT;");
# The slot is always "safe" when max_slot_wal_keep_size is not set
@@ -100,7 +100,7 @@ $result = $node_primary->safe_psql('postgres',
is($result, "reserved", 'check that max_slot_wal_keep_size is working');
# Advance WAL again then checkpoint, reducing remain by 2 MB.
-advance_wal($node_primary, 2);
+$node_primary->advance_wal(2);
$node_primary->safe_psql('postgres', "CHECKPOINT;");
# The slot is still working
@@ -118,7 +118,7 @@ $node_standby->stop;
$result = $node_primary->safe_psql('postgres',
"ALTER SYSTEM SET wal_keep_size to '8MB'; SELECT pg_reload_conf();");
# Advance WAL again then checkpoint, reducing remain by 6 MB.
-advance_wal($node_primary, 6);
+$node_primary->advance_wal(6);
$result = $node_primary->safe_psql('postgres',
"SELECT wal_status as remain FROM pg_replication_slots WHERE slot_name = 'rep1'"
);
@@ -134,7 +134,7 @@ $node_primary->wait_for_catchup($node_standby);
$node_standby->stop;
# Advance WAL again without checkpoint, reducing remain by 6 MB.
-advance_wal($node_primary, 6);
+$node_primary->advance_wal(6);
# Slot gets into 'reserved' state
$result = $node_primary->safe_psql('postgres',
@@ -145,7 +145,7 @@ is($result, "extended", 'check that the slot state changes to "extended"');
$node_primary->safe_psql('postgres', "CHECKPOINT;");
# Advance WAL again without checkpoint; remain goes to 0.
-advance_wal($node_primary, 1);
+$node_primary->advance_wal(1);
# Slot gets into 'unreserved' state and safe_wal_size is negative
$result = $node_primary->safe_psql('postgres',
@@ -174,7 +174,7 @@ $node_primary->safe_psql('postgres',
# Advance WAL again. The slot loses the oldest segment by the next checkpoint
my $logstart = get_log_size($node_primary);
-advance_wal($node_primary, 7);
+$node_primary->advance_wal(7);
# Now create another checkpoint and wait until the WARNING is issued
$node_primary->safe_psql('postgres',
@@ -275,19 +275,8 @@ $node_standby->init_from_backup($node_primary2, $backup_name,
has_streaming => 1);
$node_standby->append_conf('postgresql.conf', "primary_slot_name = 'rep1'");
$node_standby->start;
-my @result =
- split(
- '\n',
- $node_primary2->safe_psql(
- 'postgres',
- "CREATE TABLE tt();
- DROP TABLE tt;
- SELECT pg_switch_wal();
- CHECKPOINT;
- SELECT 'finished';",
- timeout => $PostgreSQL::Test::Utils::timeout_default));
-is($result[1], 'finished', 'check if checkpoint command is not blocked');
-
+$node_primary2->advance_wal(1);
+$node_primary2->safe_psql('postgres', 'CHECKPOINT;');
$node_primary2->stop;
$node_standby->stop;
@@ -372,7 +361,7 @@ $logstart = get_log_size($node_primary3);
# freeze walsender and walreceiver. Slot will still be active, but walreceiver
# won't get anything anymore.
kill 'STOP', $senderpid, $receiverpid;
-advance_wal($node_primary3, 2);
+$node_primary3->advance_wal(2);
my $msg_logged = 0;
my $max_attempts = $PostgreSQL::Test::Utils::timeout_default;
@@ -418,21 +407,6 @@ kill 'CONT', $receiverpid;
$node_primary3->stop;
$node_standby3->stop;
-#####################################
-# Advance WAL of $node by $n segments
-sub advance_wal
-{
- my ($node, $n) = @_;
-
- # Advance by $n segments (= (wal_segment_size * $n) bytes) on primary.
- for (my $i = 0; $i < $n; $i++)
- {
- $node->safe_psql('postgres',
- "CREATE TABLE t (); DROP TABLE t; SELECT pg_switch_wal();");
- }
- return;
-}
-
# return the size of logfile of $node in bytes
sub get_log_size
{
diff --git a/src/test/recovery/t/035_standby_logical_decoding.pl b/src/test/recovery/t/035_standby_logical_decoding.pl
index 480e6d6caa..2d19adab4e 100644
--- a/src/test/recovery/t/035_standby_logical_decoding.pl
+++ b/src/test/recovery/t/035_standby_logical_decoding.pl
@@ -524,11 +524,8 @@ my $walfile_name = $node_primary->safe_psql('postgres',
chomp($walfile_name);
# Generate some activity and switch WAL file on the primary
-$node_primary->safe_psql(
- 'postgres', "create table retain_test(a int);
- select pg_switch_wal();
- insert into retain_test values(1);
- checkpoint;");
+$node_primary->advance_wal(1);
+$node_primary->safe_psql('postgres', "checkpoint;");
# Wait for the standby to catch up
$node_primary->wait_for_replay_catchup($node_standby);
--
2.34.1
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2023-06-15 04:40 Kyotaro Horiguchi <horikyota.ntt@gmail.com>
parent: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
0 siblings, 1 reply; 24+ messages in thread
From: Kyotaro Horiguchi @ 2023-06-15 04:40 UTC (permalink / raw)
To: bharath.rupireddyforpostgres@gmail.com; +Cc: michael@paquier.xyz; alvherre@alvh.no-ip.org; andrew@dunslane.net; pgsql-hackers@lists.postgresql.org
Mmm. It seems like the email I thought I'd sent failed to go out.
At Sun, 11 Jun 2023 07:14:54 +0530, Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> wrote in
> On Wed, Aug 24, 2022 at 6:42 AM Kyotaro Horiguchi
> <horikyota.ntt@gmail.com> wrote:
> >
> > At Tue, 16 Aug 2022 18:40:49 +0200, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote in
> > > On 2022-Aug-16, Andrew Dunstan wrote:
> > > Yeah, I agree with that for advance_wal. Regarding find_in_log, that
> > > one seems general enough to warrant being in Cluster.pm -- consider
> > > issues_sql_like, which also slurps_file($log). That could be unified a
> > > little bit, I think.
> >
> > +1
>
> With the generalized function for find_in_log() has been added as part
> of https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=e25e5f7fc6b74c9d4ce82627e9145ef55...,
> I'm proposing a generalized function for advance_wal(). Please find
> the attached patch.
>
> I tried to replace the existing tests with the new cluster function
> advance_wal(). Please let me know if I'm missing any other tests.
> Also, this new function can be used by an in-progress feature -
> https://commitfest.postgresql.org/43/3663/.
>
> Thoughts?
Thanks!
+ "CREATE TABLE tt (); DROP TABLE tt; SELECT pg_switch_wal();");
At least since 11, we can utilize pg_logical_emit_message() for this
purpose. It's more lightweight and seems appropriate, not only because
it doesn't cause any side effects but also bacause we don't have to
worry about name conflicts.
- SELECT 'finished';",
- timeout => $PostgreSQL::Test::Utils::timeout_default));
-is($result[1], 'finished', 'check if checkpoint command is not blocked');
-
+$node_primary2->advance_wal(1);
+$node_primary2->safe_psql('postgres', 'CHECKPOINT;');
This test anticipates that the checkpoint could get blocked. Shouldn't
we keep the timeout?
-$node_primary->safe_psql(
- 'postgres', "create table retain_test(a int);
- select pg_switch_wal();
- insert into retain_test values(1);
- checkpoint;");
+$node_primary->advance_wal(1);
+$node_primary->safe_psql('postgres', "checkpoint;");
The original test generated some WAL after the segment switch, which
appears to be a significant characteristics of the test.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2023-06-16 02:30 Michael Paquier <michael@paquier.xyz>
parent: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
0 siblings, 2 replies; 24+ messages in thread
From: Michael Paquier @ 2023-06-16 02:30 UTC (permalink / raw)
To: Kyotaro Horiguchi <horikyota.ntt@gmail.com>; +Cc: bharath.rupireddyforpostgres@gmail.com; alvherre@alvh.no-ip.org; andrew@dunslane.net; pgsql-hackers@lists.postgresql.org
On Thu, Jun 15, 2023 at 01:40:15PM +0900, Kyotaro Horiguchi wrote:
> + "CREATE TABLE tt (); DROP TABLE tt; SELECT pg_switch_wal();");
>
> At least since 11, we can utilize pg_logical_emit_message() for this
> purpose. It's more lightweight and seems appropriate, not only because
> it doesn't cause any side effects but also bacause we don't have to
> worry about name conflicts.
Making this as cheap as possible by design is a good concept for a
common routine. +1.
> - SELECT 'finished';",
> - timeout => $PostgreSQL::Test::Utils::timeout_default));
> -is($result[1], 'finished', 'check if checkpoint command is not blocked');
> -
> +$node_primary2->advance_wal(1);
> +$node_primary2->safe_psql('postgres', 'CHECKPOINT;');
>
> This test anticipates that the checkpoint could get blocked. Shouldn't
> we keep the timeout?
Indeed, this would partially invalidate what's getting tested in light
of 1816a1c6 where we run a secondary command after the checkpoint. So
the last SELECT should remain around.
> -$node_primary->safe_psql(
> - 'postgres', "create table retain_test(a int);
> - select pg_switch_wal();
> - insert into retain_test values(1);
> - checkpoint;");
> +$node_primary->advance_wal(1);
> +$node_primary->safe_psql('postgres', "checkpoint;");
>
> The original test generated some WAL after the segment switch, which
> appears to be a significant characteristics of the test.
Still it does not matter for this specific case? The logical slot has
been already invalidated, so we don't care much about logical changes
in WAL, do we?
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2023-06-16 04:30 Kyotaro Horiguchi <horikyota.ntt@gmail.com>
parent: Michael Paquier <michael@paquier.xyz>
1 sibling, 0 replies; 24+ messages in thread
From: Kyotaro Horiguchi @ 2023-06-16 04:30 UTC (permalink / raw)
To: michael@paquier.xyz; +Cc: bharath.rupireddyforpostgres@gmail.com; alvherre@alvh.no-ip.org; andrew@dunslane.net; pgsql-hackers@lists.postgresql.org
Thanks for the comments.
At Fri, 16 Jun 2023 11:30:15 +0900, Michael Paquier <michael@paquier.xyz> wrote in
> > -$node_primary->safe_psql(
> > - 'postgres', "create table retain_test(a int);
> > - select pg_switch_wal();
> > - insert into retain_test values(1);
> > - checkpoint;");
> > +$node_primary->advance_wal(1);
> > +$node_primary->safe_psql('postgres', "checkpoint;");
> >
> > The original test generated some WAL after the segment switch, which
> > appears to be a significant characteristics of the test.
>
> Still it does not matter for this specific case? The logical slot has
> been already invalidated, so we don't care much about logical changes
> in WAL, do we?
The change itself doesn't seem to matter, but it seems intended to let
checkpoint trigger the removal of the last segment. However, I'm
unsure how the insert would influence this that way. If my
understanding is correct, then I'd support its removal.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2023-07-19 10:41 Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
parent: Michael Paquier <michael@paquier.xyz>
1 sibling, 1 reply; 24+ messages in thread
From: Bharath Rupireddy @ 2023-07-19 10:41 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>; alvherre@alvh.no-ip.org; andrew@dunslane.net; pgsql-hackers@lists.postgresql.org
On Fri, Jun 16, 2023 at 8:00 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Thu, Jun 15, 2023 at 01:40:15PM +0900, Kyotaro Horiguchi wrote:
> > + "CREATE TABLE tt (); DROP TABLE tt; SELECT pg_switch_wal();");
> >
> > At least since 11, we can utilize pg_logical_emit_message() for this
> > purpose. It's more lightweight and seems appropriate, not only because
> > it doesn't cause any side effects but also bacause we don't have to
> > worry about name conflicts.
>
> Making this as cheap as possible by design is a good concept for a
> common routine. +1.
While it seems reasonable to use pg_logical_emit_message, it won't
work for all the cases - what if someone wants to advance WAL by a few
WAL files? I think pg_switch_wal() is the way, no? For instance, the
replslot_limit.pl test increases the WAL in a very calculated way - it
increases by 5 WAL files. So, -1 to use pg_logical_emit_message.
I understand the naming conflicts for the table name used ('tt' in
this case). If the table name 'tt' looks so simple and easy for
someone to have tables with that name in their tests file, we can
generate a random table name in advance_wal, something like in the
attached v2 patch.
> > - SELECT 'finished';",
> > - timeout => $PostgreSQL::Test::Utils::timeout_default));
> > -is($result[1], 'finished', 'check if checkpoint command is not blocked');
> > -
> > +$node_primary2->advance_wal(1);
> > +$node_primary2->safe_psql('postgres', 'CHECKPOINT;');
> >
> > This test anticipates that the checkpoint could get blocked. Shouldn't
> > we keep the timeout?
>
> Indeed, this would partially invalidate what's getting tested in light
> of 1816a1c6 where we run a secondary command after the checkpoint. So
> the last SELECT should remain around.
Changed.
> > -$node_primary->safe_psql(
> > - 'postgres', "create table retain_test(a int);
> > - select pg_switch_wal();
> > - insert into retain_test values(1);
> > - checkpoint;");
> > +$node_primary->advance_wal(1);
> > +$node_primary->safe_psql('postgres', "checkpoint;");
> >
> > The original test generated some WAL after the segment switch, which
> > appears to be a significant characteristics of the test.
>
> Still it does not matter for this specific case? The logical slot has
> been already invalidated, so we don't care much about logical changes
> in WAL, do we?
Correct, the slot has already been invalidated and the test is
verifying that WAL isn't retained by the invalidated slot, so
essentially what it needs is to generate "some" wal. So, using
advance_wal there seems fine to me. CFBot doesn't complain anything -
https://github.com/BRupireddy/postgres/tree/add_a_TAP_test_function_to_generate_WAL_v2.
Attached the v2 patch. Thoughts?
--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Attachments:
v2-0001-Add-a-TAP-test-function-to-generate-WAL.patch (application/x-patch, 7.4K) download
From 959bd5c7dd00be05c98ab5a68406ac37af61a52f Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Wed, 19 Jul 2023 09:58:11 +0000
Subject: [PATCH v2] Add a TAP test function to generate WAL
This commit adds a perl function in Cluster.pm to generate WAL.
Some TAP tests are now using their own way to generate WAL.
Generalizing this functionality enables multiple TAP tests to
reuse the functionality.
---
src/test/perl/PostgreSQL/Test/Cluster.pm | 24 ++++++++++
src/test/recovery/t/001_stream_rep.pl | 6 +--
src/test/recovery/t/019_replslot_limit.pl | 48 +++++--------------
.../t/035_standby_logical_decoding.pl | 7 +--
4 files changed, 40 insertions(+), 45 deletions(-)
diff --git a/src/test/perl/PostgreSQL/Test/Cluster.pm b/src/test/perl/PostgreSQL/Test/Cluster.pm
index 5e161dbee6..583c63f3db 100644
--- a/src/test/perl/PostgreSQL/Test/Cluster.pm
+++ b/src/test/perl/PostgreSQL/Test/Cluster.pm
@@ -3120,6 +3120,30 @@ sub create_logical_slot_on_standby
=pod
+=item $node->advance_wal($n)
+
+Advance WAL of given node by $n segments
+
+=cut
+
+sub advance_wal
+{
+ my ($self, $n) = @_;
+
+ # Generate a random table name.
+ my $table_name = $self->safe_psql('postgres',
+ "SELECT substr(md5(random()::text), 1, 10)::text;");
+
+ # Advance by $n segments (= (wal_segment_size * $n) bytes).
+ for (my $i = 0; $i < $n; $i++)
+ {
+ $self->safe_psql('postgres',
+ qq{CREATE TABLE "$table_name"(); DROP TABLE "$table_name"; SELECT pg_switch_wal();});
+ }
+}
+
+=pod
+
=back
=cut
diff --git a/src/test/recovery/t/001_stream_rep.pl b/src/test/recovery/t/001_stream_rep.pl
index 0c72ba0944..0e256dab8d 100644
--- a/src/test/recovery/t/001_stream_rep.pl
+++ b/src/test/recovery/t/001_stream_rep.pl
@@ -496,11 +496,7 @@ $node_primary->safe_psql('postgres',
my $segment_removed = $node_primary->safe_psql('postgres',
'SELECT pg_walfile_name(pg_current_wal_lsn())');
chomp($segment_removed);
-$node_primary->psql(
- 'postgres', "
- CREATE TABLE tab_phys_slot (a int);
- INSERT INTO tab_phys_slot VALUES (generate_series(1,10));
- SELECT pg_switch_wal();");
+$node_primary->advance_wal(1);
my $current_lsn =
$node_primary->safe_psql('postgres', "SELECT pg_current_wal_lsn();");
chomp($current_lsn);
diff --git a/src/test/recovery/t/019_replslot_limit.pl b/src/test/recovery/t/019_replslot_limit.pl
index 33e50ad933..a312155250 100644
--- a/src/test/recovery/t/019_replslot_limit.pl
+++ b/src/test/recovery/t/019_replslot_limit.pl
@@ -59,7 +59,7 @@ $result = $node_primary->safe_psql('postgres',
is($result, "reserved|t", 'check the catching-up state');
# Advance WAL by five segments (= 5MB) on primary
-advance_wal($node_primary, 1);
+$node_primary->advance_wal(1);
$node_primary->safe_psql('postgres', "CHECKPOINT;");
# The slot is always "safe" when fitting max_wal_size
@@ -69,7 +69,7 @@ $result = $node_primary->safe_psql('postgres',
is($result, "reserved|t",
'check that it is safe if WAL fits in max_wal_size');
-advance_wal($node_primary, 4);
+$node_primary->advance_wal(4);
$node_primary->safe_psql('postgres', "CHECKPOINT;");
# The slot is always "safe" when max_slot_wal_keep_size is not set
@@ -100,7 +100,7 @@ $result = $node_primary->safe_psql('postgres',
is($result, "reserved", 'check that max_slot_wal_keep_size is working');
# Advance WAL again then checkpoint, reducing remain by 2 MB.
-advance_wal($node_primary, 2);
+$node_primary->advance_wal(2);
$node_primary->safe_psql('postgres', "CHECKPOINT;");
# The slot is still working
@@ -118,7 +118,7 @@ $node_standby->stop;
$result = $node_primary->safe_psql('postgres',
"ALTER SYSTEM SET wal_keep_size to '8MB'; SELECT pg_reload_conf();");
# Advance WAL again then checkpoint, reducing remain by 6 MB.
-advance_wal($node_primary, 6);
+$node_primary->advance_wal(6);
$result = $node_primary->safe_psql('postgres',
"SELECT wal_status as remain FROM pg_replication_slots WHERE slot_name = 'rep1'"
);
@@ -134,7 +134,7 @@ $node_primary->wait_for_catchup($node_standby);
$node_standby->stop;
# Advance WAL again without checkpoint, reducing remain by 6 MB.
-advance_wal($node_primary, 6);
+$node_primary->advance_wal(6);
# Slot gets into 'reserved' state
$result = $node_primary->safe_psql('postgres',
@@ -145,7 +145,7 @@ is($result, "extended", 'check that the slot state changes to "extended"');
$node_primary->safe_psql('postgres', "CHECKPOINT;");
# Advance WAL again without checkpoint; remain goes to 0.
-advance_wal($node_primary, 1);
+$node_primary->advance_wal(1);
# Slot gets into 'unreserved' state and safe_wal_size is negative
$result = $node_primary->safe_psql('postgres',
@@ -174,7 +174,7 @@ $node_primary->safe_psql('postgres',
# Advance WAL again. The slot loses the oldest segment by the next checkpoint
my $logstart = get_log_size($node_primary);
-advance_wal($node_primary, 7);
+$node_primary->advance_wal(7);
# Now create another checkpoint and wait until the WARNING is issued
$node_primary->safe_psql('postgres',
@@ -275,18 +275,11 @@ $node_standby->init_from_backup($node_primary2, $backup_name,
has_streaming => 1);
$node_standby->append_conf('postgresql.conf', "primary_slot_name = 'rep1'");
$node_standby->start;
-my @result =
- split(
- '\n',
- $node_primary2->safe_psql(
- 'postgres',
- "CREATE TABLE tt();
- DROP TABLE tt;
- SELECT pg_switch_wal();
- CHECKPOINT;
- SELECT 'finished';",
- timeout => $PostgreSQL::Test::Utils::timeout_default));
-is($result[1], 'finished', 'check if checkpoint command is not blocked');
+$node_primary2->advance_wal(1);
+$result = $node_primary2->safe_psql('postgres',
+ "CHECKPOINT; SELECT 'finished';",
+ timeout => $PostgreSQL::Test::Utils::timeout_default);
+is($result, 'finished', 'check if checkpoint command is not blocked');
$node_primary2->stop;
$node_standby->stop;
@@ -372,7 +365,7 @@ $logstart = get_log_size($node_primary3);
# freeze walsender and walreceiver. Slot will still be active, but walreceiver
# won't get anything anymore.
kill 'STOP', $senderpid, $receiverpid;
-advance_wal($node_primary3, 2);
+$node_primary3->advance_wal(2);
my $msg_logged = 0;
my $max_attempts = $PostgreSQL::Test::Utils::timeout_default;
@@ -418,21 +411,6 @@ kill 'CONT', $receiverpid;
$node_primary3->stop;
$node_standby3->stop;
-#####################################
-# Advance WAL of $node by $n segments
-sub advance_wal
-{
- my ($node, $n) = @_;
-
- # Advance by $n segments (= (wal_segment_size * $n) bytes) on primary.
- for (my $i = 0; $i < $n; $i++)
- {
- $node->safe_psql('postgres',
- "CREATE TABLE t (); DROP TABLE t; SELECT pg_switch_wal();");
- }
- return;
-}
-
# return the size of logfile of $node in bytes
sub get_log_size
{
diff --git a/src/test/recovery/t/035_standby_logical_decoding.pl b/src/test/recovery/t/035_standby_logical_decoding.pl
index 480e6d6caa..2d19adab4e 100644
--- a/src/test/recovery/t/035_standby_logical_decoding.pl
+++ b/src/test/recovery/t/035_standby_logical_decoding.pl
@@ -524,11 +524,8 @@ my $walfile_name = $node_primary->safe_psql('postgres',
chomp($walfile_name);
# Generate some activity and switch WAL file on the primary
-$node_primary->safe_psql(
- 'postgres', "create table retain_test(a int);
- select pg_switch_wal();
- insert into retain_test values(1);
- checkpoint;");
+$node_primary->advance_wal(1);
+$node_primary->safe_psql('postgres', "checkpoint;");
# Wait for the standby to catch up
$node_primary->wait_for_replay_catchup($node_standby);
--
2.34.1
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2023-12-18 05:39 Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
parent: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
0 siblings, 1 reply; 24+ messages in thread
From: Bharath Rupireddy @ 2023-12-18 05:39 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>; alvherre@alvh.no-ip.org; andrew@dunslane.net; pgsql-hackers@lists.postgresql.org
On Wed, Jul 19, 2023 at 4:11 PM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:
>
> Attached the v2 patch. Thoughts?
Rebase needed, attached v3 patch.
--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Attachments:
v3-0001-Add-a-TAP-test-function-to-generate-WAL.patch (application/octet-stream, 7.3K) download
From 823ca03dcbe715c43ceffdceee52269311950f95 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Mon, 18 Dec 2023 05:38:19 +0000
Subject: [PATCH v3] Add a TAP test function to generate WAL
This commit adds a perl function in Cluster.pm to generate WAL.
Some TAP tests are now using their own way to generate WAL.
Generalizing this functionality enables multiple TAP tests to
reuse the functionality.
---
src/test/perl/PostgreSQL/Test/Cluster.pm | 24 ++++++++++
src/test/recovery/t/001_stream_rep.pl | 6 +--
src/test/recovery/t/019_replslot_limit.pl | 48 +++++--------------
.../t/035_standby_logical_decoding.pl | 7 +--
4 files changed, 40 insertions(+), 45 deletions(-)
diff --git a/src/test/perl/PostgreSQL/Test/Cluster.pm b/src/test/perl/PostgreSQL/Test/Cluster.pm
index a020377761..c0fea242c0 100644
--- a/src/test/perl/PostgreSQL/Test/Cluster.pm
+++ b/src/test/perl/PostgreSQL/Test/Cluster.pm
@@ -3178,6 +3178,30 @@ sub create_logical_slot_on_standby
=pod
+=item $node->advance_wal($n)
+
+Advance WAL of given node by $n segments
+
+=cut
+
+sub advance_wal
+{
+ my ($self, $n) = @_;
+
+ # Generate a random table name.
+ my $table_name = $self->safe_psql('postgres',
+ "SELECT substr(md5(random()::text), 1, 10)::text;");
+
+ # Advance by $n segments (= (wal_segment_size * $n) bytes).
+ for (my $i = 0; $i < $n; $i++)
+ {
+ $self->safe_psql('postgres',
+ qq{CREATE TABLE "$table_name"(); DROP TABLE "$table_name"; SELECT pg_switch_wal();});
+ }
+}
+
+=pod
+
=back
=cut
diff --git a/src/test/recovery/t/001_stream_rep.pl b/src/test/recovery/t/001_stream_rep.pl
index 95f9b0d772..f0de921b4b 100644
--- a/src/test/recovery/t/001_stream_rep.pl
+++ b/src/test/recovery/t/001_stream_rep.pl
@@ -522,11 +522,7 @@ $node_primary->safe_psql('postgres',
my $segment_removed = $node_primary->safe_psql('postgres',
'SELECT pg_walfile_name(pg_current_wal_lsn())');
chomp($segment_removed);
-$node_primary->psql(
- 'postgres', "
- CREATE TABLE tab_phys_slot (a int);
- INSERT INTO tab_phys_slot VALUES (generate_series(1,10));
- SELECT pg_switch_wal();");
+$node_primary->advance_wal(1);
my $current_lsn =
$node_primary->safe_psql('postgres', "SELECT pg_current_wal_lsn();");
chomp($current_lsn);
diff --git a/src/test/recovery/t/019_replslot_limit.pl b/src/test/recovery/t/019_replslot_limit.pl
index 7d94f15778..e4b75c6545 100644
--- a/src/test/recovery/t/019_replslot_limit.pl
+++ b/src/test/recovery/t/019_replslot_limit.pl
@@ -59,7 +59,7 @@ $result = $node_primary->safe_psql('postgres',
is($result, "reserved|t", 'check the catching-up state');
# Advance WAL by five segments (= 5MB) on primary
-advance_wal($node_primary, 1);
+$node_primary->advance_wal(1);
$node_primary->safe_psql('postgres', "CHECKPOINT;");
# The slot is always "safe" when fitting max_wal_size
@@ -69,7 +69,7 @@ $result = $node_primary->safe_psql('postgres',
is($result, "reserved|t",
'check that it is safe if WAL fits in max_wal_size');
-advance_wal($node_primary, 4);
+$node_primary->advance_wal(4);
$node_primary->safe_psql('postgres', "CHECKPOINT;");
# The slot is always "safe" when max_slot_wal_keep_size is not set
@@ -100,7 +100,7 @@ $result = $node_primary->safe_psql('postgres',
is($result, "reserved", 'check that max_slot_wal_keep_size is working');
# Advance WAL again then checkpoint, reducing remain by 2 MB.
-advance_wal($node_primary, 2);
+$node_primary->advance_wal(2);
$node_primary->safe_psql('postgres', "CHECKPOINT;");
# The slot is still working
@@ -118,7 +118,7 @@ $node_standby->stop;
$result = $node_primary->safe_psql('postgres',
"ALTER SYSTEM SET wal_keep_size to '8MB'; SELECT pg_reload_conf();");
# Advance WAL again then checkpoint, reducing remain by 6 MB.
-advance_wal($node_primary, 6);
+$node_primary->advance_wal(6);
$result = $node_primary->safe_psql('postgres',
"SELECT wal_status as remain FROM pg_replication_slots WHERE slot_name = 'rep1'"
);
@@ -134,7 +134,7 @@ $node_primary->wait_for_catchup($node_standby);
$node_standby->stop;
# Advance WAL again without checkpoint, reducing remain by 6 MB.
-advance_wal($node_primary, 6);
+$node_primary->advance_wal(6);
# Slot gets into 'reserved' state
$result = $node_primary->safe_psql('postgres',
@@ -145,7 +145,7 @@ is($result, "extended", 'check that the slot state changes to "extended"');
$node_primary->safe_psql('postgres', "CHECKPOINT;");
# Advance WAL again without checkpoint; remain goes to 0.
-advance_wal($node_primary, 1);
+$node_primary->advance_wal(1);
# Slot gets into 'unreserved' state and safe_wal_size is negative
$result = $node_primary->safe_psql('postgres',
@@ -174,7 +174,7 @@ $node_primary->safe_psql('postgres',
# Advance WAL again. The slot loses the oldest segment by the next checkpoint
my $logstart = -s $node_primary->logfile;
-advance_wal($node_primary, 7);
+$node_primary->advance_wal(7);
# Now create another checkpoint and wait until the WARNING is issued
$node_primary->safe_psql('postgres',
@@ -275,18 +275,11 @@ $node_standby->init_from_backup($node_primary2, $backup_name,
has_streaming => 1);
$node_standby->append_conf('postgresql.conf', "primary_slot_name = 'rep1'");
$node_standby->start;
-my @result =
- split(
- '\n',
- $node_primary2->safe_psql(
- 'postgres',
- "CREATE TABLE tt();
- DROP TABLE tt;
- SELECT pg_switch_wal();
- CHECKPOINT;
- SELECT 'finished';",
- timeout => $PostgreSQL::Test::Utils::timeout_default));
-is($result[1], 'finished', 'check if checkpoint command is not blocked');
+$node_primary2->advance_wal(1);
+$result = $node_primary2->safe_psql('postgres',
+ "CHECKPOINT; SELECT 'finished';",
+ timeout => $PostgreSQL::Test::Utils::timeout_default);
+is($result, 'finished', 'check if checkpoint command is not blocked');
$node_primary2->stop;
$node_standby->stop;
@@ -372,7 +365,7 @@ $logstart = -s $node_primary3->logfile;
# freeze walsender and walreceiver. Slot will still be active, but walreceiver
# won't get anything anymore.
kill 'STOP', $senderpid, $receiverpid;
-advance_wal($node_primary3, 2);
+$node_primary3->advance_wal(2);
my $msg_logged = 0;
my $max_attempts = $PostgreSQL::Test::Utils::timeout_default;
@@ -418,19 +411,4 @@ kill 'CONT', $receiverpid;
$node_primary3->stop;
$node_standby3->stop;
-#####################################
-# Advance WAL of $node by $n segments
-sub advance_wal
-{
- my ($node, $n) = @_;
-
- # Advance by $n segments (= (wal_segment_size * $n) bytes) on primary.
- for (my $i = 0; $i < $n; $i++)
- {
- $node->safe_psql('postgres',
- "CREATE TABLE t (); DROP TABLE t; SELECT pg_switch_wal();");
- }
- return;
-}
-
done_testing();
diff --git a/src/test/recovery/t/035_standby_logical_decoding.pl b/src/test/recovery/t/035_standby_logical_decoding.pl
index 9c34c0d36c..5d7c278d01 100644
--- a/src/test/recovery/t/035_standby_logical_decoding.pl
+++ b/src/test/recovery/t/035_standby_logical_decoding.pl
@@ -524,11 +524,8 @@ my $walfile_name = $node_primary->safe_psql('postgres',
chomp($walfile_name);
# Generate some activity and switch WAL file on the primary
-$node_primary->safe_psql(
- 'postgres', "create table retain_test(a int);
- select pg_switch_wal();
- insert into retain_test values(1);
- checkpoint;");
+$node_primary->advance_wal(1);
+$node_primary->safe_psql('postgres', "checkpoint;");
# Wait for the standby to catch up
$node_primary->wait_for_replay_catchup($node_standby);
--
2.34.1
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2023-12-18 11:48 Euler Taveira <euler@eulerto.com>
parent: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
0 siblings, 1 reply; 24+ messages in thread
From: Euler Taveira @ 2023-12-18 11:48 UTC (permalink / raw)
To: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>; Michael Paquier <michael@paquier.xyz>; +Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>; Alvaro Herrera <alvherre@alvh.no-ip.org>; Andrew Dunstan <andrew@dunslane.net>; pgsql-hackers@lists.postgresql.org
On Mon, Dec 18, 2023, at 2:39 AM, Bharath Rupireddy wrote:
> Rebase needed, attached v3 patch.
I think you don't understand the suggestion proposed by Michael and Kyotaro. If
you do a comparison with the following SQL commands:
euler=# select pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
--------------------------
000000010000000000000040
(1 row)
euler=# select pg_logical_emit_message(true, 'prefix', 'message4');
pg_logical_emit_message
-------------------------
0/400000A8
(1 row)
euler=# select pg_switch_wal();
pg_switch_wal
---------------
0/400000F0
(1 row)
euler=# create table cc (b int);
CREATE TABLE
euler=# drop table cc;
DROP TABLE
euler=# select pg_switch_wal();
pg_switch_wal
---------------
0/41017C88
(1 row)
euler=# select pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
--------------------------
000000010000000000000041
(1 row)
You get
$ pg_waldump 000000010000000000000040
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/40000028, prev 0/3F0001C0, desc: RUNNING_XACTS nextXid 295180 latestCompletedXid 295179 oldestRunningXid 295180
rmgr: LogicalMessage len (rec/tot): 65/ 65, tx: 295180, lsn: 0/40000060, prev 0/40000028, desc: MESSAGE transactional, prefix "prefix"; payload (8 bytes): 6D 65 73 73 61 67 65 34
rmgr: Transaction len (rec/tot): 46/ 46, tx: 295180, lsn: 0/400000A8, prev 0/40000060, desc: COMMIT 2023-12-18 08:35:06.821322 -03
rmgr: XLOG len (rec/tot): 24/ 24, tx: 0, lsn: 0/400000D8, prev 0/400000A8, desc: SWITCH
$ pg_waldump 000000010000000000000041
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/41000028, prev 0/400000D8, desc: RUNNING_XACTS nextXid 295181 latestCompletedXid 295180 oldestRunningXid 295181
rmgr: Storage len (rec/tot): 42/ 42, tx: 0, lsn: 0/41000060, prev 0/41000028, desc: CREATE base/33287/88102
rmgr: Heap2 len (rec/tot): 60/ 60, tx: 295181, lsn: 0/41000090, prev 0/41000060, desc: NEW_CID rel: 1663/33287/1247, tid: 14/16, cmin: 0, cmax: 4294967295, combo: 4294967295
rmgr: Heap len (rec/tot): 54/ 3086, tx: 295181, lsn: 0/410000D0, prev 0/41000090, desc: INSERT off: 16, flags: 0x00, blkref #0: rel 1663/33287/1247 blk 14 FPW
rmgr: Btree len (rec/tot): 53/ 5133, tx: 295181, lsn: 0/41000CE0, prev 0/410000D0, desc: INSERT_LEAF off: 252, blkref #0: rel 1663/33287/2703 blk 2 FPW
.
.
.
rmgr: Btree len (rec/tot): 72/ 72, tx: 295181, lsn: 0/41016E48, prev 0/41014F00, desc: INSERT_LEAF off: 111, blkref #0: rel 1663/33287/2674 blk 7
rmgr: Heap2 len (rec/tot): 69/ 69, tx: 295181, lsn: 0/41016E90, prev 0/41016E48, desc: PRUNE snapshotConflictHorizon: 295177, nredirected: 0, ndead: 7, nunused: 0, redirected: [], dead: [20, 21, 22, 23, 24, 26, 27], unused: [], blkref #0: rel 1663/33287/1249 blk 17
rmgr: Transaction len (rec/tot): 385/ 385, tx: 295181, lsn: 0/41016ED8, prev 0/41016E90, desc: INVALIDATION ; inval msgs: catcache 80 catcache 79 catcache 80 catcache 79 catcache 55 catcache 54 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 snapshot 2608 relcache 88102
rmgr: Standby len (rec/tot): 42/ 42, tx: 295181, lsn: 0/41017060, prev 0/41016ED8, desc: LOCK xid 295181 db 33287 rel 88102
rmgr: Transaction len (rec/tot): 405/ 405, tx: 295181, lsn: 0/41017090, prev 0/41017060, desc: COMMIT 2023-12-18 08:35:22.342122 -03; inval msgs: catcache 80 catcache 79 catcache 80 catcache 79 catcache 55 catcache 54 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 snapshot 2608 relcache 88102
rmgr: Standby len (rec/tot): 42/ 42, tx: 295182, lsn: 0/41017228, prev 0/41017090, desc: LOCK xid 295182 db 33287 rel 88102
rmgr: Heap2 len (rec/tot): 61/ 61, tx: 295182, lsn: 0/41017258, prev 0/41017228, desc: PRUNE snapshotConflictHorizon: 295177, nredirected: 0, ndead: 3, nunused: 0, redirected: [], dead: [9, 12, 15], unused: [], blkref #0: rel 1663/33287/2608 blk 3
rmgr: Heap2 len (rec/tot): 60/ 60, tx: 295182, lsn: 0/41017298, prev 0/41017258, desc: NEW_CID rel: 1663/33287/1247, tid: 14/17, cmin: 4294967295, cmax: 0, combo: 4294967295
rmgr: Heap len (rec/tot): 54/ 54, tx: 295182, lsn: 0/410172D8, prev 0/41017298, desc: DELETE xmax: 295182, off: 17, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/33287/1247 blk 14
.
.
.
rmgr: Heap2 len (rec/tot): 60/ 60, tx: 295182, lsn: 0/410178D8, prev 0/410178A0, desc: NEW_CID rel: 1663/33287/2608, tid: 3/24, cmin: 4294967295, cmax: 2, combo: 4294967295
rmgr: Heap len (rec/tot): 54/ 54, tx: 295182, lsn: 0/41017918, prev 0/410178D8, desc: DELETE xmax: 295182, off: 24, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/33287/2608 blk 3
rmgr: Transaction len (rec/tot): 321/ 321, tx: 295182, lsn: 0/41017950, prev 0/41017918, desc: INVALIDATION ; inval msgs: catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 55 catcache 54 relcache 88102 snapshot 2608
rmgr: Transaction len (rec/tot): 469/ 469, tx: 295182, lsn: 0/41017A98, prev 0/41017950, desc: COMMIT 2023-12-18 08:35:25.053905 -03; rels: base/33287/88102; dropped stats: 2/33287/88102; inval msgs: catcache 80 catcache 79 catcache 80 catcache 79 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 55 catcache 54 snapshot 2608 snapshot 2608 relcache 88102 snapshot 2608
rmgr: XLOG len (rec/tot): 24/ 24, tx: 0, lsn: 0/41017C70, prev 0/41017A98, desc: SWITCH
The difference is
euler=# select '0/400000A8'::pg_lsn - '0/40000028'::pg_lsn;
?column?
----------
128
(1 row)
euler=# select '0/41017A98'::pg_lsn - '0/41000028'::pg_lsn;
?column?
----------
96880
(1 row)
It is cheaper.
--
Euler Taveira
EDB https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2023-12-19 04:21 Michael Paquier <michael@paquier.xyz>
parent: Euler Taveira <euler@eulerto.com>
0 siblings, 1 reply; 24+ messages in thread
From: Michael Paquier @ 2023-12-19 04:21 UTC (permalink / raw)
To: Euler Taveira <euler@eulerto.com>; +Cc: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>; Kyotaro Horiguchi <horikyota.ntt@gmail.com>; Alvaro Herrera <alvherre@alvh.no-ip.org>; Andrew Dunstan <andrew@dunslane.net>; pgsql-hackers@lists.postgresql.org
On Mon, Dec 18, 2023 at 08:48:09AM -0300, Euler Taveira wrote:
> It is cheaper.
Agreed that this could just use a set of pg_logical_emit_message()
when jumping across N segments. Another thing that seems quite
important to me is to force a flush of WAL with the last segment
switch, and the new "flush" option of pg_logical_emit_message() can
be very handy for this purpose.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2023-12-19 05:55 Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 24+ messages in thread
From: Bharath Rupireddy @ 2023-12-19 05:55 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Euler Taveira <euler@eulerto.com>; Kyotaro Horiguchi <horikyota.ntt@gmail.com>; Alvaro Herrera <alvherre@alvh.no-ip.org>; Andrew Dunstan <andrew@dunslane.net>; pgsql-hackers@lists.postgresql.org
On Tue, Dec 19, 2023 at 9:51 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Mon, Dec 18, 2023 at 08:48:09AM -0300, Euler Taveira wrote:
> > It is cheaper.
>
> Agreed that this could just use a set of pg_logical_emit_message()
> when jumping across N segments.
Thanks. I missed the point of using pg_logical_emit_message() over
CREATE .. DROP TABLE to generate WAL. And, I agree that it's better
and relatively cheaper in terms of amount of WAL generated.
> Another thing that seems quite
> important to me is to force a flush of WAL with the last segment
> switch, and the new "flush" option of pg_logical_emit_message() can
> be very handy for this purpose.
I used pg_logical_emit_message() in non-transactional mode without
needing an explicit WAL flush as the pg_switch_wal() does a WAL flush
at the end [1].
Attached v4 patch.
[1]
/*
* If this was an XLOG_SWITCH record, flush the record and the empty
* padding space that fills the rest of the segment, and perform
* end-of-segment actions (eg, notifying archiver).
*/
if (class == WALINSERT_SPECIAL_SWITCH)
{
TRACE_POSTGRESQL_WAL_SWITCH();
XLogFlush(EndPos);
--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Attachments:
v4-0001-Add-a-TAP-test-function-to-generate-WAL.patch (application/x-patch, 7.2K) download
From b7fa7545eb983aaf92e3d7e99bdf76ef42b8e40e Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Tue, 19 Dec 2023 05:49:20 +0000
Subject: [PATCH v4] Add a TAP test function to generate WAL
This commit adds a perl function in Cluster.pm to generate WAL.
Some TAP tests are now using their own way to generate WAL.
Generalizing this functionality enables multiple TAP tests to
reuse the functionality.
---
src/test/perl/PostgreSQL/Test/Cluster.pm | 22 +++++++++
src/test/recovery/t/001_stream_rep.pl | 6 +--
src/test/recovery/t/019_replslot_limit.pl | 48 +++++--------------
.../t/035_standby_logical_decoding.pl | 7 +--
4 files changed, 38 insertions(+), 45 deletions(-)
diff --git a/src/test/perl/PostgreSQL/Test/Cluster.pm b/src/test/perl/PostgreSQL/Test/Cluster.pm
index a020377761..ad575ed6d6 100644
--- a/src/test/perl/PostgreSQL/Test/Cluster.pm
+++ b/src/test/perl/PostgreSQL/Test/Cluster.pm
@@ -3178,6 +3178,28 @@ sub create_logical_slot_on_standby
=pod
+=item $node->advance_wal($n)
+
+Advance WAL of given node by $n segments
+
+=cut
+
+sub advance_wal
+{
+ my ($self, $n) = @_;
+
+ # Advance by $n segments (= (wal_segment_size * $n) bytes).
+ for (my $i = 0; $i < $n; $i++)
+ {
+ $self->safe_psql('postgres', qq{
+ SELECT pg_logical_emit_message(false, '', 'foo');
+ SELECT pg_switch_wal();
+ });
+ }
+}
+
+=pod
+
=back
=cut
diff --git a/src/test/recovery/t/001_stream_rep.pl b/src/test/recovery/t/001_stream_rep.pl
index 95f9b0d772..f0de921b4b 100644
--- a/src/test/recovery/t/001_stream_rep.pl
+++ b/src/test/recovery/t/001_stream_rep.pl
@@ -522,11 +522,7 @@ $node_primary->safe_psql('postgres',
my $segment_removed = $node_primary->safe_psql('postgres',
'SELECT pg_walfile_name(pg_current_wal_lsn())');
chomp($segment_removed);
-$node_primary->psql(
- 'postgres', "
- CREATE TABLE tab_phys_slot (a int);
- INSERT INTO tab_phys_slot VALUES (generate_series(1,10));
- SELECT pg_switch_wal();");
+$node_primary->advance_wal(1);
my $current_lsn =
$node_primary->safe_psql('postgres', "SELECT pg_current_wal_lsn();");
chomp($current_lsn);
diff --git a/src/test/recovery/t/019_replslot_limit.pl b/src/test/recovery/t/019_replslot_limit.pl
index 7d94f15778..e4b75c6545 100644
--- a/src/test/recovery/t/019_replslot_limit.pl
+++ b/src/test/recovery/t/019_replslot_limit.pl
@@ -59,7 +59,7 @@ $result = $node_primary->safe_psql('postgres',
is($result, "reserved|t", 'check the catching-up state');
# Advance WAL by five segments (= 5MB) on primary
-advance_wal($node_primary, 1);
+$node_primary->advance_wal(1);
$node_primary->safe_psql('postgres', "CHECKPOINT;");
# The slot is always "safe" when fitting max_wal_size
@@ -69,7 +69,7 @@ $result = $node_primary->safe_psql('postgres',
is($result, "reserved|t",
'check that it is safe if WAL fits in max_wal_size');
-advance_wal($node_primary, 4);
+$node_primary->advance_wal(4);
$node_primary->safe_psql('postgres', "CHECKPOINT;");
# The slot is always "safe" when max_slot_wal_keep_size is not set
@@ -100,7 +100,7 @@ $result = $node_primary->safe_psql('postgres',
is($result, "reserved", 'check that max_slot_wal_keep_size is working');
# Advance WAL again then checkpoint, reducing remain by 2 MB.
-advance_wal($node_primary, 2);
+$node_primary->advance_wal(2);
$node_primary->safe_psql('postgres', "CHECKPOINT;");
# The slot is still working
@@ -118,7 +118,7 @@ $node_standby->stop;
$result = $node_primary->safe_psql('postgres',
"ALTER SYSTEM SET wal_keep_size to '8MB'; SELECT pg_reload_conf();");
# Advance WAL again then checkpoint, reducing remain by 6 MB.
-advance_wal($node_primary, 6);
+$node_primary->advance_wal(6);
$result = $node_primary->safe_psql('postgres',
"SELECT wal_status as remain FROM pg_replication_slots WHERE slot_name = 'rep1'"
);
@@ -134,7 +134,7 @@ $node_primary->wait_for_catchup($node_standby);
$node_standby->stop;
# Advance WAL again without checkpoint, reducing remain by 6 MB.
-advance_wal($node_primary, 6);
+$node_primary->advance_wal(6);
# Slot gets into 'reserved' state
$result = $node_primary->safe_psql('postgres',
@@ -145,7 +145,7 @@ is($result, "extended", 'check that the slot state changes to "extended"');
$node_primary->safe_psql('postgres', "CHECKPOINT;");
# Advance WAL again without checkpoint; remain goes to 0.
-advance_wal($node_primary, 1);
+$node_primary->advance_wal(1);
# Slot gets into 'unreserved' state and safe_wal_size is negative
$result = $node_primary->safe_psql('postgres',
@@ -174,7 +174,7 @@ $node_primary->safe_psql('postgres',
# Advance WAL again. The slot loses the oldest segment by the next checkpoint
my $logstart = -s $node_primary->logfile;
-advance_wal($node_primary, 7);
+$node_primary->advance_wal(7);
# Now create another checkpoint and wait until the WARNING is issued
$node_primary->safe_psql('postgres',
@@ -275,18 +275,11 @@ $node_standby->init_from_backup($node_primary2, $backup_name,
has_streaming => 1);
$node_standby->append_conf('postgresql.conf', "primary_slot_name = 'rep1'");
$node_standby->start;
-my @result =
- split(
- '\n',
- $node_primary2->safe_psql(
- 'postgres',
- "CREATE TABLE tt();
- DROP TABLE tt;
- SELECT pg_switch_wal();
- CHECKPOINT;
- SELECT 'finished';",
- timeout => $PostgreSQL::Test::Utils::timeout_default));
-is($result[1], 'finished', 'check if checkpoint command is not blocked');
+$node_primary2->advance_wal(1);
+$result = $node_primary2->safe_psql('postgres',
+ "CHECKPOINT; SELECT 'finished';",
+ timeout => $PostgreSQL::Test::Utils::timeout_default);
+is($result, 'finished', 'check if checkpoint command is not blocked');
$node_primary2->stop;
$node_standby->stop;
@@ -372,7 +365,7 @@ $logstart = -s $node_primary3->logfile;
# freeze walsender and walreceiver. Slot will still be active, but walreceiver
# won't get anything anymore.
kill 'STOP', $senderpid, $receiverpid;
-advance_wal($node_primary3, 2);
+$node_primary3->advance_wal(2);
my $msg_logged = 0;
my $max_attempts = $PostgreSQL::Test::Utils::timeout_default;
@@ -418,19 +411,4 @@ kill 'CONT', $receiverpid;
$node_primary3->stop;
$node_standby3->stop;
-#####################################
-# Advance WAL of $node by $n segments
-sub advance_wal
-{
- my ($node, $n) = @_;
-
- # Advance by $n segments (= (wal_segment_size * $n) bytes) on primary.
- for (my $i = 0; $i < $n; $i++)
- {
- $node->safe_psql('postgres',
- "CREATE TABLE t (); DROP TABLE t; SELECT pg_switch_wal();");
- }
- return;
-}
-
done_testing();
diff --git a/src/test/recovery/t/035_standby_logical_decoding.pl b/src/test/recovery/t/035_standby_logical_decoding.pl
index 9c34c0d36c..5d7c278d01 100644
--- a/src/test/recovery/t/035_standby_logical_decoding.pl
+++ b/src/test/recovery/t/035_standby_logical_decoding.pl
@@ -524,11 +524,8 @@ my $walfile_name = $node_primary->safe_psql('postgres',
chomp($walfile_name);
# Generate some activity and switch WAL file on the primary
-$node_primary->safe_psql(
- 'postgres', "create table retain_test(a int);
- select pg_switch_wal();
- insert into retain_test values(1);
- checkpoint;");
+$node_primary->advance_wal(1);
+$node_primary->safe_psql('postgres', "checkpoint;");
# Wait for the standby to catch up
$node_primary->wait_for_replay_catchup($node_standby);
--
2.34.1
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2023-12-19 23:00 Michael Paquier <michael@paquier.xyz>
parent: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
0 siblings, 1 reply; 24+ messages in thread
From: Michael Paquier @ 2023-12-19 23:00 UTC (permalink / raw)
To: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>; +Cc: Euler Taveira <euler@eulerto.com>; Kyotaro Horiguchi <horikyota.ntt@gmail.com>; Alvaro Herrera <alvherre@alvh.no-ip.org>; Andrew Dunstan <andrew@dunslane.net>; pgsql-hackers@lists.postgresql.org
On Tue, Dec 19, 2023 at 11:25:50AM +0530, Bharath Rupireddy wrote:
> I used pg_logical_emit_message() in non-transactional mode without
> needing an explicit WAL flush as the pg_switch_wal() does a WAL flush
> at the end [1].
Indeed, that should be enough to answer my comment.
> Attached v4 patch.
LGTM, thanks. Euler, what do you think?
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2023-12-20 03:24 Euler Taveira <euler@eulerto.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 24+ messages in thread
From: Euler Taveira @ 2023-12-20 03:24 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>; +Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>; Alvaro Herrera <alvherre@alvh.no-ip.org>; Andrew Dunstan <andrew@dunslane.net>; pgsql-hackers@lists.postgresql.org
On Tue, Dec 19, 2023, at 8:00 PM, Michael Paquier wrote:
> On Tue, Dec 19, 2023 at 11:25:50AM +0530, Bharath Rupireddy wrote:
> > I used pg_logical_emit_message() in non-transactional mode without
> > needing an explicit WAL flush as the pg_switch_wal() does a WAL flush
> > at the end [1].
>
> Indeed, that should be enough to answer my comment.
>
> > Attached v4 patch.
>
> LGTM, thanks. Euler, what do you think?
>
LGTM.
--
Euler Taveira
EDB https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2023-12-21 01:21 Michael Paquier <michael@paquier.xyz>
parent: Euler Taveira <euler@eulerto.com>
0 siblings, 1 reply; 24+ messages in thread
From: Michael Paquier @ 2023-12-21 01:21 UTC (permalink / raw)
To: Euler Taveira <euler@eulerto.com>; +Cc: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>; Kyotaro Horiguchi <horikyota.ntt@gmail.com>; Alvaro Herrera <alvherre@alvh.no-ip.org>; Andrew Dunstan <andrew@dunslane.net>; pgsql-hackers@lists.postgresql.org
On Wed, Dec 20, 2023 at 12:24:04AM -0300, Euler Taveira wrote:
> LGTM.
I was eyeing at 020_messages.pl and it has a pg_switch_wal() after a
transaction rollbacked. 020_archive_status.pl creates a table, does
one segment switch, then a checkpoint (table is used afterwards).
Perhaps these could be changed with the new routine, but it does not
seem like this improves the readability of the tests, either, contrary
to the ones updated here where a fake table is created to force some
records. What do you think?
We have a few more pg_switch_wal() calls, as well, but these rely on
WAL being already generated beforehand.
I have added a comment about pg_logical_emit_message() being in
non-transactional mode and the flush implied by pg_switch_wal() as
that's important, edited a bit the whole, then applied the patch.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2024-01-03 23:39 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 2 replies; 24+ messages in thread
From: Tom Lane @ 2024-01-03 23:39 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Euler Taveira <euler@eulerto.com>; Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>; Kyotaro Horiguchi <horikyota.ntt@gmail.com>; Alvaro Herrera <alvherre@alvh.no-ip.org>; Andrew Dunstan <andrew@dunslane.net>; pgsql-hackers@lists.postgresql.org
Michael Paquier <michael@paquier.xyz> writes:
> I have added a comment about pg_logical_emit_message() being in
> non-transactional mode and the flush implied by pg_switch_wal() as
> that's important, edited a bit the whole, then applied the patch.
Buildfarm member skink has failed 3 times in
035_standby_logical_decoding.pl in the last couple of days:
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=skink&dt=2024-01-03%2020%3A07%3A15
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=skink&dt=2024-01-03%2017%3A09%3A27
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=skink&dt=2024-01-01%2020%3A10%3A18
These all look like
# poll_query_until timed out executing this query:
# select (confl_active_logicalslot = 1) from pg_stat_database_conflicts where datname = 'testdb'
# expecting this output:
# t
# last actual query output:
# f
although it's notable that two different tests are involved
(vacuum vs. vacuum full).
I am not real sure what is happening there, but I see that c161ab74f
changed some details of how that test works, so I wonder if it's
responsible for these failures. The timing isn't a perfect match,
since this commit went in two weeks ago, but I don't see any
more-recent commits that seem like plausible explanations.
regards, tom lane
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2024-01-04 00:02 Michael Paquier <michael@paquier.xyz>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 24+ messages in thread
From: Michael Paquier @ 2024-01-04 00:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Euler Taveira <euler@eulerto.com>; Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>; Kyotaro Horiguchi <horikyota.ntt@gmail.com>; Alvaro Herrera <alvherre@alvh.no-ip.org>; Andrew Dunstan <andrew@dunslane.net>; pgsql-hackers@lists.postgresql.org
On Wed, Jan 03, 2024 at 06:39:29PM -0500, Tom Lane wrote:
> I am not real sure what is happening there, but I see that c161ab74f
> changed some details of how that test works, so I wonder if it's
> responsible for these failures. The timing isn't a perfect match,
> since this commit went in two weeks ago, but I don't see any
> more-recent commits that seem like plausible explanations.
Likely the INSERT query on retain_test that has been removed from the
test is impacting the slot conflict analysis that we'd expect.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2024-01-04 13:00 Alexander Lakhin <exclusion@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 24+ messages in thread
From: Alexander Lakhin @ 2024-01-04 13:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Michael Paquier <michael@paquier.xyz>; +Cc: Euler Taveira <euler@eulerto.com>; Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>; Kyotaro Horiguchi <horikyota.ntt@gmail.com>; Alvaro Herrera <alvherre@alvh.no-ip.org>; Andrew Dunstan <andrew@dunslane.net>; pgsql-hackers@lists.postgresql.org
Hello Tom,
04.01.2024 02:39, Tom Lane wrote:
> Buildfarm member skink has failed 3 times in
> 035_standby_logical_decoding.pl in the last couple of days:
>
> https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=skink&dt=2024-01-03%2020%3A07%3A15
>
> https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=skink&dt=2024-01-03%2017%3A09%3A27
>
> https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=skink&dt=2024-01-01%2020%3A10%3A18
>
> These all look like
>
> # poll_query_until timed out executing this query:
> # select (confl_active_logicalslot = 1) from pg_stat_database_conflicts where datname = 'testdb'
> # expecting this output:
> # t
> # last actual query output:
> # f
>
> although it's notable that two different tests are involved
> (vacuum vs. vacuum full).
>
> I am not real sure what is happening there, but I see that c161ab74f
> changed some details of how that test works, so I wonder if it's
> responsible for these failures. The timing isn't a perfect match,
> since this commit went in two weeks ago, but I don't see any
> more-recent commits that seem like plausible explanations.
Reproduced here.
As I can see in the failure logs you referenced, the first problem is:
# Failed test 'inactiveslot slot invalidation is logged with vacuum on pg_authid'
# at t/035_standby_logical_decoding.pl line 224.
It reminded me of:
https://www.postgresql.org/message-id/b2a1f7d0-7629-72c0-2da7-e9c4e336b010%40gmail.com
It seems that it's not something new, and maybe that my analysis is still
valid. If so, VACUUM FREEZE/autovacuum = off should fix the issue.
Best regards,
Alexander
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2024-01-04 23:48 Michael Paquier <michael@paquier.xyz>
parent: Alexander Lakhin <exclusion@gmail.com>
0 siblings, 1 reply; 24+ messages in thread
From: Michael Paquier @ 2024-01-04 23:48 UTC (permalink / raw)
To: Alexander Lakhin <exclusion@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Euler Taveira <euler@eulerto.com>; Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>; Kyotaro Horiguchi <horikyota.ntt@gmail.com>; Alvaro Herrera <alvherre@alvh.no-ip.org>; Andrew Dunstan <andrew@dunslane.net>; pgsql-hackers@lists.postgresql.org
On Thu, Jan 04, 2024 at 04:00:01PM +0300, Alexander Lakhin wrote:
> Reproduced here.
Did you just make the run slow enough to show the failure with
valgrind?
> As I can see in the failure logs you referenced, the first problem is:
> # Failed test 'inactiveslot slot invalidation is logged with vacuum on pg_authid'
> # at t/035_standby_logical_decoding.pl line 224.
> It reminded me of:
> https://www.postgresql.org/message-id/b2a1f7d0-7629-72c0-2da7-e9c4e336b010%40gmail.com
>
> It seems that it's not something new, and maybe that my analysis is still
> valid. If so, VACUUM FREEZE/autovacuum = off should fix the issue.
Not sure about that yet.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2024-01-05 20:00 Alexander Lakhin <exclusion@gmail.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 24+ messages in thread
From: Alexander Lakhin @ 2024-01-05 20:00 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Euler Taveira <euler@eulerto.com>; Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>; Kyotaro Horiguchi <horikyota.ntt@gmail.com>; Alvaro Herrera <alvherre@alvh.no-ip.org>; Andrew Dunstan <andrew@dunslane.net>; pgsql-hackers@lists.postgresql.org
05.01.2024 02:48, Michael Paquier wrote:
> On Thu, Jan 04, 2024 at 04:00:01PM +0300, Alexander Lakhin wrote:
>> Reproduced here.
> Did you just make the run slow enough to show the failure with
> valgrind?
Yes, I just run several test instances (with no extra modifications) under
Valgrind with parallel as follows:
for i in `seq 20`; do cp -r src/test/recovery/ src/test/recovery_$i/; sed "s|src/test/recovery|src/test/recovery_$i|" -i
src/test/recovery_$i/Makefile; done
for i in `seq 20`; do echo "iteration $i"; parallel --halt now,fail=1 -j20 --linebuffer --tag PROVE_TESTS="t/035*"
NO_TEMP_INSTALL=1 make check -s -C src/test/recovery_{} PROVE_FLAGS="--timer" ::: `seq 20` || break; done
The test run fails for me on iterations 9, 8, 14 like so:
...
iteration 9
...
5
5 # Failed test 'inactiveslot slot invalidation is logged with vacuum on pg_authid'
5 # at t/035_standby_logical_decoding.pl line 224.
5
5 # Failed test 'activeslot slot invalidation is logged with vacuum on pg_authid'
5 # at t/035_standby_logical_decoding.pl line 229.
13 [07:35:53] t/035_standby_logical_decoding.pl .. ok 432930 ms ( 0.02 usr 0.00 sys + 292.08 cusr 13.05 csys =
305.15 CPU)
13 [07:35:53]
13 All tests successful.
...
I've also reproduced it without Valgrind in a VM with CPU slowed down to
5% (on iterations 2, 5, 4), where average test duration is about 800 sec:
4
4 # Failed test 'inactiveslot slot invalidation is logged with vacuum on pg_authid'
4 # at t/035_standby_logical_decoding.pl line 222.
4
4 # Failed test 'activeslot slot invalidation is logged with vacuum on pg_authid'
4 # at t/035_standby_logical_decoding.pl line 227.
6 [15:16:53] t/035_standby_logical_decoding.pl .. ok 763168 ms ( 0.68 usr 0.10 sys + 19.55 cusr 102.59 csys =
122.92 CPU)
>
>> As I can see in the failure logs you referenced, the first problem is:
>> # Failed test 'inactiveslot slot invalidation is logged with vacuum on pg_authid'
>> # at t/035_standby_logical_decoding.pl line 224.
>> It reminded me of:
>> https://www.postgresql.org/message-id/b2a1f7d0-7629-72c0-2da7-e9c4e336b010%40gmail.com
>>
>> It seems that it's not something new, and maybe that my analysis is still
>> valid. If so, VACUUM FREEZE/autovacuum = off should fix the issue.
> Not sure about that yet.
>
Your suspicion was proved right. After
git show c161ab74f src/test/recovery/t/035_standby_logical_decoding.pl | git apply -R
20 iterations with 20 tests in parallel performed successfully for me
(twice).
So it looks like c161ab74f really made the things worse.
Best regards,
Alexander
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2024-01-07 07:10 Michael Paquier <michael@paquier.xyz>
parent: Alexander Lakhin <exclusion@gmail.com>
0 siblings, 1 reply; 24+ messages in thread
From: Michael Paquier @ 2024-01-07 07:10 UTC (permalink / raw)
To: Alexander Lakhin <exclusion@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Euler Taveira <euler@eulerto.com>; Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>; Kyotaro Horiguchi <horikyota.ntt@gmail.com>; Alvaro Herrera <alvherre@alvh.no-ip.org>; Andrew Dunstan <andrew@dunslane.net>; pgsql-hackers@lists.postgresql.org, Andres Freund <andres@anarazel.de>; Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
On Fri, Jan 05, 2024 at 11:00:00PM +0300, Alexander Lakhin wrote:
> Your suspicion was proved right. After
> git show c161ab74f src/test/recovery/t/035_standby_logical_decoding.pl | git apply -R
> 20 iterations with 20 tests in parallel performed successfully for me
> (twice).
>
> So it looks like c161ab74f really made the things worse.
We have two different failures here, one when VACUUM fails for a
shared relation:
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=skink&dt=2024-01-03%2017%3A09%3A27
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=skink&dt=2024-01-01%2020%3A10%3A18
And the second failure happens for VACUUM FULL with a shared relation:
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=skink&dt=2024-01-03%2020%3A07%3A15
In the second case, the VACUUM FULL happens *BEFORE* the new
advance_wal(), making c161ab74f unrelated, no?
Anyway, if one looks at the buildfarm logs, this failure is more
ancient than c161ab74f. We have many of them before that, some
reported back in October:
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=skink&dt=2023-10-19%2000%3A44%3A58
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=skink&dt=2023-10-30%2013%3A39%3A20
I suspect on the contrary that c161ab74f may be actually helping here,
because we've switched the CREATE TABLE/INSERT queries to not use a
snapshot anymore, reducing the reasons why a slot conflict would
happen? Or maybe that's just a matter of luck because the test is
racy anyway.
Anyway, this has the smell of a legit bug to me. I am also a bit
dubious about the choice of pg_authid as shared catalog to choose for
the slot invalidation check. Isn't that potentially racy with the
scans we may do on it at connection startup? Something else should be
chosen, like pg_shdescription as it is non-critical? I am adding in
CC Bertrand and Andres, as author and committer behind befcd77d53217b.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2024-01-07 14:00 Alexander Lakhin <exclusion@gmail.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 24+ messages in thread
From: Alexander Lakhin @ 2024-01-07 14:00 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Euler Taveira <euler@eulerto.com>; Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>; Kyotaro Horiguchi <horikyota.ntt@gmail.com>; Alvaro Herrera <alvherre@alvh.no-ip.org>; Andrew Dunstan <andrew@dunslane.net>; pgsql-hackers@lists.postgresql.org, Andres Freund <andres@anarazel.de>; Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
07.01.2024 10:10, Michael Paquier wrote:
> On Fri, Jan 05, 2024 at 11:00:00PM +0300, Alexander Lakhin wrote:
>> Your suspicion was proved right. After
>> git show c161ab74f src/test/recovery/t/035_standby_logical_decoding.pl | git apply -R
>> 20 iterations with 20 tests in parallel performed successfully for me
>> (twice).
>>
>> So it looks like c161ab74f really made the things worse.
After more waiting, I saw the test failure (with c161ab74f reverted) on
iteration 17 in VM where one test run takes up to 800 seconds.
> We have two different failures here, one when VACUUM fails for a
> shared relation:
> https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=skink&dt=2024-01-03%2017%3A09%3A27
> https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=skink&dt=2024-01-01%2020%3A10%3A18
>
> And the second failure happens for VACUUM FULL with a shared relation:
> https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=skink&dt=2024-01-03%2020%3A07%3A15
>
> In the second case, the VACUUM FULL happens *BEFORE* the new
> advance_wal(), making c161ab74f unrelated, no?
>
> Anyway, if one looks at the buildfarm logs, this failure is more
> ancient than c161ab74f. We have many of them before that, some
> reported back in October:
> https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=skink&dt=2023-10-19%2000%3A44%3A58
> https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=skink&dt=2023-10-30%2013%3A39%3A20
Yes, I wrote exactly about that upthread and referenced my previous
investigation. But what I'm observing now, is that the failure probability
greatly increased with c161ab74f, so something really changed in the test
behaviour. (I need a couple of days to investigate this.)
Best regards,
Alexander
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2024-01-08 00:16 Michael Paquier <michael@paquier.xyz>
parent: Alexander Lakhin <exclusion@gmail.com>
0 siblings, 1 reply; 24+ messages in thread
From: Michael Paquier @ 2024-01-08 00:16 UTC (permalink / raw)
To: Alexander Lakhin <exclusion@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Euler Taveira <euler@eulerto.com>; Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>; Kyotaro Horiguchi <horikyota.ntt@gmail.com>; Alvaro Herrera <alvherre@alvh.no-ip.org>; Andrew Dunstan <andrew@dunslane.net>; pgsql-hackers@lists.postgresql.org, Andres Freund <andres@anarazel.de>; Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
On Sun, Jan 07, 2024 at 05:00:00PM +0300, Alexander Lakhin wrote:
> Yes, I wrote exactly about that upthread and referenced my previous
> investigation. But what I'm observing now, is that the failure probability
> greatly increased with c161ab74f, so something really changed in the test
> behaviour. (I need a couple of days to investigate this.)
As far as I've cross-checked the logs between successful and failed
runs on skink and my own machines (not reproduced it locally
unfortunately), I did not notice a correlation with autovacuum running
while VACUUM (with or without FULL) is executed on the catalogs.
Perhaps a next sensible step would be to plug-in pg_waldump or
pg_walinspect and get some sense from the WAL records if we fail to
detect an invalidation from the log contents, from a LSN retrieved
slightly at the beginning of each scenario.
I would be tempted to add more increments of $Test::Builder::Level as
well in the subroutines of the test because it is kind of hard to find
out from where a failure comes now. One needs to grep for the
slot names, whose strings are built from prefixes and suffixes defined
as arguments of these subroutines...
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2024-01-08 07:34 Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 24+ messages in thread
From: Bertrand Drouvot @ 2024-01-08 07:34 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Alexander Lakhin <exclusion@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Euler Taveira <euler@eulerto.com>; Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>; Kyotaro Horiguchi <horikyota.ntt@gmail.com>; Alvaro Herrera <alvherre@alvh.no-ip.org>; Andrew Dunstan <andrew@dunslane.net>; pgsql-hackers@lists.postgresql.org, Andres Freund <andres@anarazel.de>
Hi,
On Mon, Jan 08, 2024 at 09:16:19AM +0900, Michael Paquier wrote:
> On Sun, Jan 07, 2024 at 05:00:00PM +0300, Alexander Lakhin wrote:
> > Yes, I wrote exactly about that upthread and referenced my previous
> > investigation. But what I'm observing now, is that the failure probability
> > greatly increased with c161ab74f, so something really changed in the test
> > behaviour. (I need a couple of days to investigate this.)
>
> As far as I've cross-checked the logs between successful and failed
> runs on skink and my own machines (not reproduced it locally
> unfortunately), I did not notice a correlation with autovacuum running
> while VACUUM (with or without FULL) is executed on the catalogs.
If one is able to reproduce, would it be possible to change the test and launch
the vacuum in verbose mode? That way, we could see if this is somehow due to [1]
(means something holding global xmin).
BTW, I think we should resume working on [1] and having it fixed in all the cases.
[1]: https://www.postgresql.org/message-id/d40d015f-03a4-1cf2-6c1f-2b9aca860762%40gmail.com
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2024-01-08 17:00 Alexander Lakhin <exclusion@gmail.com>
parent: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
0 siblings, 1 reply; 24+ messages in thread
From: Alexander Lakhin @ 2024-01-08 17:00 UTC (permalink / raw)
To: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>; Michael Paquier <michael@paquier.xyz>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Euler Taveira <euler@eulerto.com>; Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>; Kyotaro Horiguchi <horikyota.ntt@gmail.com>; Alvaro Herrera <alvherre@alvh.no-ip.org>; Andrew Dunstan <andrew@dunslane.net>; pgsql-hackers@lists.postgresql.org, Andres Freund <andres@anarazel.de>
Hello Bertrand,
08.01.2024 10:34, Bertrand Drouvot wrote:
> If one is able to reproduce, would it be possible to change the test and launch
> the vacuum in verbose mode? That way, we could see if this is somehow due to [1]
> (means something holding global xmin).
Yes, I've added (VERBOSE) and also cut down the test to catch the failure faster.
The difference between a successful and a failed run:
2024-01-08 11:58:30.679 UTC [668363] 035_standby_logical_decoding.pl INFO: vacuuming "testdb.pg_catalog.pg_authid"
2024-01-08 11:58:30.679 UTC [668363] 035_standby_logical_decoding.pl INFO: finished vacuuming
"testdb.pg_catalog.pg_authid": index scans: 1
pages: 0 removed, 1 remain, 1 scanned (100.00% of total)
tuples: 1 removed, 15 remain, 0 are dead but not yet removable
removable cutoff: 767, which was 0 XIDs old when operation ended
new relfrozenxid: 767, which is 39 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 1 pages from table (100.00% of total) had 1 dead item identifiers removed
---
2024-01-08 12:00:59.903 UTC [669119] 035_standby_logical_decoding.pl LOG: statement: VACUUM (VERBOSE) pg_authid;
2024-01-08 12:00:59.904 UTC [669119] 035_standby_logical_decoding.pl INFO: vacuuming "testdb.pg_catalog.pg_authid"
2024-01-08 12:00:59.904 UTC [669119] 035_standby_logical_decoding.pl INFO: finished vacuuming
"testdb.pg_catalog.pg_authid": index scans: 0
pages: 0 removed, 1 remain, 1 scanned (100.00% of total)
tuples: 0 removed, 16 remain, 1 are dead but not yet removable
removable cutoff: 766, which was 1 XIDs old when operation ended
new relfrozenxid: 765, which is 37 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
The difference in WAL is essentially the same as I observed before [1].
rmgr: Transaction len (rec/tot): 82/ 82, tx: 766, lsn: 0/0403D418, prev 0/0403D3D8, desc: COMMIT
2024-01-08 11:58:30.679035 UTC; inval msgs: catcache 11 catcache 10
rmgr: Heap2 len (rec/tot): 57/ 57, tx: 0, lsn: 0/0403D470, prev 0/0403D418, desc: PRUNE
snapshotConflictHorizon: 766, nredirected: 0, ndead: 1, isCatalogRel: T, nunused: 0, redirected: [], dead: [16], unused:
[], blkref #0: rel 1664/0/1260 blk 0
rmgr: Btree len (rec/tot): 52/ 52, tx: 0, lsn: 0/0403D4B0, prev 0/0403D470, desc: VACUUM ndeleted:
1, nupdated: 0, deleted: [1], updated: [], blkref #0: rel 1664/0/2676 blk 1
rmgr: Btree len (rec/tot): 52/ 52, tx: 0, lsn: 0/0403D4E8, prev 0/0403D4B0, desc: VACUUM ndeleted:
1, nupdated: 0, deleted: [16], updated: [], blkref #0: rel 1664/0/2677 blk 1
rmgr: Heap2 len (rec/tot): 50/ 50, tx: 0, lsn: 0/0403D520, prev 0/0403D4E8, desc: VACUUM nunused:
1, unused: [16], blkref #0: rel 1664/0/1260 blk 0
rmgr: Heap2 len (rec/tot): 64/ 8256, tx: 0, lsn: 0/0403D558, prev 0/0403D520, desc: VISIBLE
snapshotConflictHorizon: 0, flags: 0x07, blkref #0: rel 1664/0/1260 fork vm blk 0 FPW, blkref #1: rel 1664/0/1260 blk 0
rmgr: Heap len (rec/tot): 225/ 225, tx: 0, lsn: 0/0403F5B0, prev 0/0403D558, desc: INPLACE off: 26,
blkref #0: rel 1663/16384/16410 blk 4
vs
rmgr: Transaction len (rec/tot): 82/ 82, tx: 766, lsn: 0/0403F480, prev 0/0403F440, desc: COMMIT
2024-01-08 12:00:59.901582 UTC; inval msgs: catcache 11 catcache 10
rmgr: XLOG len (rec/tot): 49/ 8241, tx: 0, lsn: 0/0403F4D8, prev 0/0403F480, desc: FPI_FOR_HINT ,
blkref #0: rel 1664/0/1260 fork fsm blk 2 FPW
rmgr: XLOG len (rec/tot): 49/ 8241, tx: 0, lsn: 0/04041528, prev 0/0403F4D8, desc: FPI_FOR_HINT ,
blkref #0: rel 1664/0/1260 fork fsm blk 1 FPW
rmgr: XLOG len (rec/tot): 49/ 8241, tx: 0, lsn: 0/04043578, prev 0/04041528, desc: FPI_FOR_HINT ,
blkref #0: rel 1664/0/1260 fork fsm blk 0 FPW
rmgr: Heap len (rec/tot): 225/ 225, tx: 0, lsn: 0/040455C8, prev 0/04043578, desc: INPLACE off: 26,
blkref #0: rel 1663/16384/16410 blk 4
(Complete logs and the modified test are attached.)
With FREEZE, 10 iterations with 20 tests in parallel succeeded for me
(while without it, I get failures on iterations 1,2).
[1] https://www.postgresql.org/message-id/b2a1f7d0-7629-72c0-2da7-e9c4e336b010%40gmail.com
Best regards,
Alexander
Attachments:
035-sld.tar.gz (application/gzip, 31.8K) download
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2024-01-09 04:59 Michael Paquier <michael@paquier.xyz>
parent: Alexander Lakhin <exclusion@gmail.com>
0 siblings, 1 reply; 24+ messages in thread
From: Michael Paquier @ 2024-01-09 04:59 UTC (permalink / raw)
To: Alexander Lakhin <exclusion@gmail.com>; +Cc: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Euler Taveira <euler@eulerto.com>; Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>; Kyotaro Horiguchi <horikyota.ntt@gmail.com>; Alvaro Herrera <alvherre@alvh.no-ip.org>; Andrew Dunstan <andrew@dunslane.net>; pgsql-hackers@lists.postgresql.org, Andres Freund <andres@anarazel.de>
On Mon, Jan 08, 2024 at 08:00:00PM +0300, Alexander Lakhin wrote:
> Yes, I've added (VERBOSE) and also cut down the test to catch the failure faster.
> The difference between a successful and a failed run:
> tuples: 1 removed, 15 remain, 0 are dead but not yet removable
> [...]
> tuples: 0 removed, 16 remain, 1 are dead but not yet removable
Yep, it's clear that the horizon is not stable.
> With FREEZE, 10 iterations with 20 tests in parallel succeeded for me
> (while without it, I get failures on iterations 1,2).
>
> [1] https://www.postgresql.org/message-id/b2a1f7d0-7629-72c0-2da7-e9c4e336b010%40gmail.com
Alexander, does the test gain in stability once you begin using the
patch posted on [2], mentioned by Bertrand?
(Also, perhaps we'd better move the discussion to the other thread
where the patch has been sent.)
[2]: https://www.postgresql.org/message-id/d40d015f-03a4-1cf2-6c1f-2b9aca860762@gmail.com
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add a perl function in Cluster.pm to generate WAL
@ 2024-01-09 05:29 Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 0 replies; 24+ messages in thread
From: Bertrand Drouvot @ 2024-01-09 05:29 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Alexander Lakhin <exclusion@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Euler Taveira <euler@eulerto.com>; Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>; Kyotaro Horiguchi <horikyota.ntt@gmail.com>; Alvaro Herrera <alvherre@alvh.no-ip.org>; Andrew Dunstan <andrew@dunslane.net>; pgsql-hackers@lists.postgresql.org, Andres Freund <andres@anarazel.de>
Hi,
On Tue, Jan 09, 2024 at 01:59:08PM +0900, Michael Paquier wrote:
> On Mon, Jan 08, 2024 at 08:00:00PM +0300, Alexander Lakhin wrote:
> > Yes, I've added (VERBOSE) and also cut down the test to catch the failure faster.
Thanks Alexander!
> > The difference between a successful and a failed run:
> > tuples: 1 removed, 15 remain, 0 are dead but not yet removable
> > [...]
> > tuples: 0 removed, 16 remain, 1 are dead but not yet removable
>
> Yep, it's clear that the horizon is not stable.
Yeap.
>
> > With FREEZE, 10 iterations with 20 tests in parallel succeeded for me
> > (while without it, I get failures on iterations 1,2).
> >
> > [1] https://www.postgresql.org/message-id/b2a1f7d0-7629-72c0-2da7-e9c4e336b010%40gmail.com
>
> Alexander, does the test gain in stability once you begin using the
> patch posted on [2], mentioned by Bertrand?
Alexander, pleae find attached v3 which is more or less a rebased version of it.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Attachments:
v3-0001-Fix-035_standby_logical_decoding.pl-race-conditio.patch (text/x-diff, 4.2K) download
From 055349da9eba780caadeb3615749b3b5453a59c4 Mon Sep 17 00:00:00 2001
From: bdrouvot <bdrouvot@gmail.com>
Date: Tue, 9 Jan 2024 05:08:35 +0000
Subject: [PATCH v3] Fix 035_standby_logical_decoding.pl race condition
We want to ensure that vacuum was able to remove dead rows (aka no other
transactions holding back global xmin) before testing for slots invalidation on
the standby.
---
.../t/035_standby_logical_decoding.pl | 56 +++++++++----------
1 file changed, 27 insertions(+), 29 deletions(-)
100.0% src/test/recovery/t/
diff --git a/src/test/recovery/t/035_standby_logical_decoding.pl b/src/test/recovery/t/035_standby_logical_decoding.pl
index 8bc39a5f03..9be1ff9b58 100644
--- a/src/test/recovery/t/035_standby_logical_decoding.pl
+++ b/src/test/recovery/t/035_standby_logical_decoding.pl
@@ -238,6 +238,22 @@ sub check_for_invalidation
) or die "Timed out waiting confl_active_logicalslot to be updated";
}
+# Launch $sql and wait for a new snapshot that has a newer horizon before
+# doing the vacuum with $vac_option on $to_vac.
+sub wait_until_vacuum_can_remove
+{
+ my ($vac_option, $sql, $to_vac) = @_;
+
+ my $xid = $node_primary->safe_psql('testdb', qq[$sql
+ select txid_current();]);
+
+ $node_primary->poll_query_until('testdb',
+ "SELECT (select txid_snapshot_xmin(txid_current_snapshot()) - $xid) > 0")
+ or die "new snapshot does not have a newer horizon";
+
+ $node_primary->safe_psql('testdb', qq[VACUUM $vac_option verbose $to_vac;
+ INSERT INTO flush_wal DEFAULT VALUES; -- see create table flush_wal;]);
+}
########################
# Initialize primary node
########################
@@ -248,6 +264,7 @@ $node_primary->append_conf(
wal_level = 'logical'
max_replication_slots = 4
max_wal_senders = 4
+autovacuum = off
});
$node_primary->dump_info;
$node_primary->start;
@@ -468,13 +485,8 @@ reactive_slots_change_hfs_and_wait_for_xmins('behaves_ok_', 'vacuum_full_',
0, 1);
# This should trigger the conflict
-$node_primary->safe_psql(
- 'testdb', qq[
- CREATE TABLE conflict_test(x integer, y text);
- DROP TABLE conflict_test;
- VACUUM full pg_class;
- INSERT INTO flush_wal DEFAULT VALUES; -- see create table flush_wal
-]);
+wait_until_vacuum_can_remove('full', 'CREATE TABLE conflict_test(x integer, y text);
+ DROP TABLE conflict_test;', 'pg_class');
$node_primary->wait_for_replay_catchup($node_standby);
@@ -550,13 +562,8 @@ reactive_slots_change_hfs_and_wait_for_xmins('vacuum_full_', 'row_removal_',
0, 1);
# This should trigger the conflict
-$node_primary->safe_psql(
- 'testdb', qq[
- CREATE TABLE conflict_test(x integer, y text);
- DROP TABLE conflict_test;
- VACUUM pg_class;
- INSERT INTO flush_wal DEFAULT VALUES; -- see create table flush_wal
-]);
+wait_until_vacuum_can_remove('', 'CREATE TABLE conflict_test(x integer, y text);
+ DROP TABLE conflict_test;', 'pg_class');
$node_primary->wait_for_replay_catchup($node_standby);
@@ -588,13 +595,8 @@ reactive_slots_change_hfs_and_wait_for_xmins('row_removal_',
'shared_row_removal_', 0, 1);
# Trigger the conflict
-$node_primary->safe_psql(
- 'testdb', qq[
- CREATE ROLE create_trash;
- DROP ROLE create_trash;
- VACUUM pg_authid;
- INSERT INTO flush_wal DEFAULT VALUES; -- see create table flush_wal
-]);
+wait_until_vacuum_can_remove('', 'CREATE ROLE create_trash;
+ DROP ROLE create_trash;', 'pg_authid');
$node_primary->wait_for_replay_catchup($node_standby);
@@ -625,14 +627,10 @@ reactive_slots_change_hfs_and_wait_for_xmins('shared_row_removal_',
'no_conflict_', 0, 1);
# This should not trigger a conflict
-$node_primary->safe_psql(
- 'testdb', qq[
- CREATE TABLE conflict_test(x integer, y text);
- INSERT INTO conflict_test(x,y) SELECT s, s::text FROM generate_series(1,4) s;
- UPDATE conflict_test set x=1, y=1;
- VACUUM conflict_test;
- INSERT INTO flush_wal DEFAULT VALUES; -- see create table flush_wal
-]);
+wait_until_vacuum_can_remove('', 'CREATE TABLE conflict_test(x integer, y text);
+ INSERT INTO conflict_test(x,y) SELECT s, s::text FROM generate_series(1,4) s;
+ UPDATE conflict_test set x=1, y=1;', 'conflict_test');
+
$node_primary->wait_for_replay_catchup($node_standby);
# message should not be issued
--
2.34.1
end of thread, other threads:[~2024-01-09 05:29 UTC | newest]
Thread overview: 24+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-06-11 01:44 Re: Add a perl function in Cluster.pm to generate WAL Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
2023-06-15 04:40 ` Kyotaro Horiguchi <horikyota.ntt@gmail.com>
2023-06-16 02:30 ` Michael Paquier <michael@paquier.xyz>
2023-06-16 04:30 ` Kyotaro Horiguchi <horikyota.ntt@gmail.com>
2023-07-19 10:41 ` Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
2023-12-18 05:39 ` Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
2023-12-18 11:48 ` Euler Taveira <euler@eulerto.com>
2023-12-19 04:21 ` Michael Paquier <michael@paquier.xyz>
2023-12-19 05:55 ` Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
2023-12-19 23:00 ` Michael Paquier <michael@paquier.xyz>
2023-12-20 03:24 ` Euler Taveira <euler@eulerto.com>
2023-12-21 01:21 ` Michael Paquier <michael@paquier.xyz>
2024-01-03 23:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-01-04 00:02 ` Michael Paquier <michael@paquier.xyz>
2024-01-04 13:00 ` Alexander Lakhin <exclusion@gmail.com>
2024-01-04 23:48 ` Michael Paquier <michael@paquier.xyz>
2024-01-05 20:00 ` Alexander Lakhin <exclusion@gmail.com>
2024-01-07 07:10 ` Michael Paquier <michael@paquier.xyz>
2024-01-07 14:00 ` Alexander Lakhin <exclusion@gmail.com>
2024-01-08 00:16 ` Michael Paquier <michael@paquier.xyz>
2024-01-08 07:34 ` Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
2024-01-08 17:00 ` Alexander Lakhin <exclusion@gmail.com>
2024-01-09 04:59 ` Michael Paquier <michael@paquier.xyz>
2024-01-09 05:29 ` Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox