Simple tester for MVCC in PostgreSQL

[Please CC any replies, thanks]

Hi,

I saw the discussion about an tester for MVCC. Since I'd never done
anything with asyncronous queries before, I figured I'd try to write
something useful with it. The result is at:

http://svana.org/kleptog/pgsql/mvcctest.tar.gz

It's a tester that takes a testing script, runs the queries one at a
time through as many connections as you ask. It uses asyncronous
queries so it can handle queries that don't return immediatly (using &
and wait model ala UNIX). It doesn't use threads. It can assign
variables and handle expected errors and test that queries unblock at
the right time. Whether this is enough for serious testing I have no
idea.

I've included 5 selftests and 3 real tests (which about covers my
knowledge of transaction isolation levels). I hope it provides a useful
basis for a real tool.

It uses Perl and the Pg module from CPAN (included in many
distributions, it's basically a wrapper for libpq). For more details
see the README. A simple test harness type program is included. Just
typing "make" will set it going.

As an example I include a sample test script below (included as
tests/in/test3.test). It opens two connections and tries several
concurrent updates. In the first block, they don't conflict so
there is no delay. In the second block the second update has to wait
for the rollback, in the third it causes a serialization failure.

I will not be able to answer my email for the next few hours due to
sleep. Please be patient if it doesn't work straight away.

Have a nice day,

# This test tests simple serialisation failure
1I drop table t
1 SET default_transaction_isolation = serializable
2 SET default_transaction_isolation = serializable

1 create table t (grp text, value int4)
1 insert into t values ('a',10)
2 insert into t values ('a',20)
1 insert into t values ('b',30)
2 insert into t values ('b',40)
# These updates don't conflict, all should be fine
1 begin
2 begin
1 update t set value = value + 10 where grp = 'a'
2 update t set value = value + 10 where grp = 'b'
1 commit
2 commit
# These updates do conflict, but the second can proceed once first transaction aborts
1 begin
2 begin
1 update t set value = value + 10 where grp = 'a'
2& update t set value = value + 10 where grp = 'a'
1 abort
2 wait
2 commit
# These updates conflict, the second transaction aborts
1 begin
2 begin
1 update t set value = value + 10 where grp = 'a'
2&e update t set value = value + 10 where grp = 'a'
1 commit
2 wait
2 abort
1 drop table t

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.