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
