On 2020/09/09 2:53, Andres Freund wrote:
Hi,
On 2020-09-08 16:44:17 +1200, Thomas Munro wrote:
On Tue, Sep 8, 2020 at 4:11 PM Andres Freund <and...@anarazel.de> wrote:
At first I was very confused as to why none of the existing tests have
found this significant issue. But after thinking about it for a minute
that's because they all use psql, and largely separate psql invocations
for each query :(. Which means that there's no cached snapshot around...
I prototyped a TAP test patch that could maybe do the sort of thing
you need, in patch 0006 over at [1]. Later versions of that patch set
dropped it, because I figured out how to use the isolation tester
instead, but I guess you can't do that for a standby test (at least
not until someone teaches the isolation tester to support multi-node
schedules, something that would be extremely useful...).
Unfortunately proper multi-node isolationtester test basically is
equivalent to building a global lock graph. I think, at least? Including
a need to be able to correlate connections with their locks between the
nodes.
But for something like the bug at hand it'd probably sufficient to just
"hack" something with dblink. In session 1) insert a row on the primary
using dblink, return the LSN, wait for the LSN to have replicated and
finally in session 2) check for row visibility.
The attached seems to do the trick.
Regards
Ian Barwick
--
Ian Barwick https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
commit b31d587d71f75115b02dd1bf6230a56722c67832
Author: Ian Barwick <i...@2ndquadrant.com>
Date: Wed Sep 9 14:37:40 2020 +0900
test for standby row visibility
diff --git a/src/test/recovery/Makefile b/src/test/recovery/Makefile
index fa8e031526..2d9a9701fc 100644
--- a/src/test/recovery/Makefile
+++ b/src/test/recovery/Makefile
@@ -9,7 +9,7 @@
#
#-------------------------------------------------------------------------
-EXTRA_INSTALL=contrib/test_decoding
+EXTRA_INSTALL=contrib/test_decoding contrib/dblink
subdir = src/test/recovery
top_builddir = ../../..
diff --git a/src/test/recovery/t/021_row_visibility.pl b/src/test/recovery/t/021_row_visibility.pl
new file mode 100644
index 0000000000..5f591d131e
--- /dev/null
+++ b/src/test/recovery/t/021_row_visibility.pl
@@ -0,0 +1,84 @@
+# Checks that a standby session can see all expected rows
+use strict;
+use warnings;
+
+use PostgresNode;
+use TestLib;
+use Test::More tests => 1;
+
+# Initialize primary node
+my $node_primary = get_new_node('primary');
+$node_primary->init(allows_streaming => 1);
+$node_primary->start;
+
+$node_primary->safe_psql('postgres',
+ 'CREATE EXTENSION dblink');
+
+
+# Add an arbitrary table
+$node_primary->safe_psql('postgres',
+ 'CREATE TABLE public.foo (id INT)');
+
+# Take backup
+my $backup_name = 'my_backup';
+$node_primary->backup($backup_name);
+
+# Create streaming standby from backup
+my $node_standby = get_new_node('standby');
+$node_standby->init_from_backup($node_primary, $backup_name,
+ has_streaming => 1);
+$node_standby->start;
+
+sleep(5);
+# Check row visibility in an existing standby session
+
+my ($res, $stdout, $stderr) = $node_standby->psql(
+ 'postgres',
+ sprintf(
+ <<'EO_SQL',
+DO $$
+ DECLARE
+ primary_lsn pg_lsn;
+ insert_xmin xid;
+ standby_rec RECORD;
+ BEGIN
+ SELECT INTO primary_lsn, insert_xmin
+ t1.primary_lsn, t1.xmin
+ FROM dblink(
+ 'host=%s port=%i dbname=postgres',
+ 'INSERT INTO public.foo VALUES (1) RETURNING pg_catalog.pg_current_wal_lsn(), xmin'
+ ) AS t1(primary_lsn pg_lsn, xmin xid);
+
+ LOOP
+ EXIT WHEN pg_catalog.pg_last_wal_replay_lsn() > primary_lsn;
+ END LOOP;
+
+ SELECT INTO standby_rec
+ id
+ FROM public.foo
+ WHERE id = 1 AND xmin = insert_xmin;
+
+ IF FOUND
+ THEN
+ RAISE NOTICE 'row found';
+ ELSE
+ RAISE NOTICE 'row not found';
+ END IF;
+
+ END;
+$$;
+EO_SQL
+ $node_primary->host,
+ $node_primary->port,
+ ),
+);
+
+
+like (
+ $stderr,
+ qr/row found/,
+ 'check that inserted row is visible on the standby',
+);
+
+$node_primary->stop;
+$node_standby->stop;
