isolationtester - allow a session specific connection string

[Peter Smith]

Hi hackers.

I wanted some way to test overlapping transactions from different
publisher sessions so I could better test the logical replication
"parallel apply" feature being developed in another thread [1]. AFAIK
currently there is no way to do this kind of test except manually
(e.g. using separate psql sessions).

Meanwhile, using the isolationtester spec tests [2] it is already
possible to test overlapping transactions on multiple sessions. So
isolationtester already does almost everything I wanted except that it
currently only knows about a single connection string (either default
or passed as argument) which every one of the "spec sessions" uses. In
contrast, for my pub/sub testing, I wanted multiple servers.

The test_decoding tests [3] have specs a bit similar to this - the
difference is that I want to observe subscriber-side apply workers
execute and actually do something.

~

My patch/idea makes a small change to the isolationtester spec
grammar. Now each session can optionally specify its own connection
string. When specified, this will override any connection string for
that session that would otherwise have been used. This is the only
change.

With this change now it is possible to write spec test code like
below. Here I have 2 publisher sessions and 1 subscriber session, with
my new session ‘conninfo’ specified appropriately for each session

======

# This test assumes there is already setup as follows:
#
# PG server for publisher (running on port 7651)
# - has TABLE tbl
# - has PUBLICATION pub1
#
# PG server for subscriber (running on port 7652)
# - has TABLE tbl
# - has SUBSCRIPTION sub1 subscribing to pub1
#

################
# Publisher node
################
session ps1
conninfo "host=localhost port=7651"
setup
{
    TRUNCATE TABLE tbl;
}
step ps1_ins        { INSERT INTO tbl VALUES (111); }
step ps1_sel        { SELECT * FROM tbl ORDER BY id; }
step ps1_begin      { BEGIN; }
step ps1_commit     { COMMIT; }
step ps1_rollback   { ROLLBACK; }

session ps2
conninfo "host=localhost port=7651"
step ps2_ins        { INSERT INTO tbl VALUES (222); }
step ps2_sel        { SELECT * FROM tbl ORDER BY id; }
step ps2_begin      { BEGIN; }
step ps2_commit     { COMMIT; }
step ps2_rollback   { ROLLBACK; }


#################
# Subscriber node
#################
session sub
conninfo "host=localhost port=7652"
setup
{
    TRUNCATE TABLE tbl;
}
step sub_sleep      { SELECT pg_sleep(3); }
step sub_sel        { SELECT * FROM tbl ORDER BY id; }


#######
# Tests
#######

# overlapping tx commits
permutation ps1_begin ps1_ins ps2_begin ps2_ins ps2_commit ps1_commit
sub_sleep sub_sel
permutation ps1_begin ps1_ins ps2_begin ps2_ins ps1_commit ps2_commit
sub_sleep sub_sel

======

Because there is still some external setup needed to make the 2
servers (with their own configurations and publication/subscription)
this kind of spec test can't be added to the 'isolation_schedule'
file. But even so, it seems to be working OK, so I think this
isolationtester enhancement can be an efficient way to write and run
some difficult pub/sub regression tests without having to test
everything entirely manually each time.


Thoughts?

~~

PSA

v1-0001 - This is the enhancement to add 'conninfo' to the isloationtester.
v1-0002 - An example of how 'conninfo' can be used (requires external setup)
test_init.sh - this is my external setup script pre-requisite for the
pub-sub.spec in v1-0002

------
[1] parallel apply thread -
https://www.postgresql.org/message-id/flat/CAA4eK1%2BwyN6zpaHUkCLorEWNx75MG0xhMwcFhvjqm2KURZEAGw%40mail.gmail.com
[2] isolation tests -
https://github.com/postgres/postgres/blob/master/src/test/isolation/README
[3] test_decoding spec tests -
https://github.com/postgres/postgres/tree/master/contrib/test_decoding/specs

Kind Regards,
Peter Smith
Fujitsu Australia

#!/bin/bash

port_PUB=7651
port_SUB=7652

function cleanup_nodes()
{
	echo 'Clean up'

	pg_ctl stop -D data_PUB
	pg_ctl stop -D data_SUB

	rm -r data_PUB data_SUB *log
}


function setup_nodes()
{
	echo 'Set up'

	# Create publisher/subscriber
	initdb -D data_PUB
	initdb -D data_SUB

	cat << EOF >> data_PUB/postgresql.conf
wal_level = logical
port = $port_PUB
max_replication_slots=40
log_min_messages = debug1
force_stream_mode=true
autovacuum = off
EOF

	cat << EOF >> data_SUB/postgresql.conf
port = $port_SUB
max_logical_replication_workers=100
max_replication_slots=40
max_parallel_apply_workers_per_subscription=99
log_min_messages = debug1
autovacuum = off
EOF

	pg_ctl -D data_PUB start -w -l PUB.log
	pg_ctl -D data_SUB start -w -l SUB.log

# Create publication/subscription
psql -p $port_PUB << EOF
CREATE TABLE tbl(id int);
CREATE PUBLICATION pub1 FOR TABLE tbl;
EOF
 
psql -p $port_SUB << EOF
CREATE TABLE tbl(id int);
CREATE SUBSCRIPTION sub1
	CONNECTION 'port=$port_PUB'
	PUBLICATION pub1
	WITH (copy_data = off, streaming = parallel);
EOF
}

# =====================================================================================================================

cleanup_nodes
setup_nodes

v1-0001-isolationtester-allow-session-specific-conninfo.patch
Description: Binary data

v1-0002-isolationstester-example-of-pub-sub-spec.patch
Description: Binary data