Thread: isolationtester - allow a session specific connection string
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
Attachment
Peter Smith <smithpb2250@gmail.com> writes:
> 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.
Surely this cannot work, because isolationtester only runs one
monitoring session. How will it detect wait conditions for
sessions connected to some other postmaster?
regards, tom lane
On Mon, Dec 19, 2022 at 5:04 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Peter Smith <smithpb2250@gmail.com> writes: > > 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. > > Surely this cannot work, because isolationtester only runs one > monitoring session. How will it detect wait conditions for > sessions connected to some other postmaster? > You are right - probably it can't work in a generic sense. But if the "controller session" (internal session 0) is also configured to use the same conninfo as all my "publisher" sessions (the current patch can't do this but it seems only a small change) then all of the publisher-side sessions will be monitored like they ought to be -- which is all I really needed I think. ------ Kind Regards, Peter Smith Fujitsu Australia
On Mon, Dec 19, 2022 at 5:35 PM Peter Smith <smithpb2250@gmail.com> wrote:
>
> On Mon, Dec 19, 2022 at 5:04 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > Peter Smith <smithpb2250@gmail.com> writes:
> > > 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.
> >
> > Surely this cannot work, because isolationtester only runs one
> > monitoring session. How will it detect wait conditions for
> > sessions connected to some other postmaster?
> >
>
> You are right - probably it can't work in a generic sense. But if the
> "controller session" (internal session 0) is also configured to use
> the same conninfo as all my "publisher" sessions (the current patch
> can't do this but it seems only a small change) then all of the
> publisher-side sessions will be monitored like they ought to be --
> which is all I really needed I think.
>
PSA v2 of this patch. Now the conninfo can be specified at the *.spec
file global scope. This will set the connection string for the
"controller", and this will be used by every other session unless they
too specify a conninfo. For example,
======
# Set the isolationtester controller's conninfo. User sessions will also use
# this unless they specify otherwise.
conninfo "host=localhost port=7651"
################
# Publisher node
################
session ps1
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
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; }
...
======
The above spec file gives:
======
Parsed test spec with 3 sessions
control connection conninfo 'host=localhost port=7651'
ps1 conninfo 'host=localhost port=7651'
ps2 conninfo 'host=localhost port=7651'
sub conninfo 'host=localhost port=7652'
WARNING: session sub is not using same connection as the controller
...
======
In this way, IIUC the isolationtester's session locking mechanism can
work OK at least for all of my "publishing" sessions.
------
Kind Regards,
Peter Smith
Fujitsu Australia