Testing with concurrent sessions

On Jan 1, 2010, at 6:01 PM, Kevin Grittner wrote:

It's very soon going to be critical that I be able to test particular
interleavings of statements in particular concurrent transaction sets
to be able to make meaningful progress on the serializable
transaction work. It would be wonderful if some of these scripts
could be integrated into the PostgreSQL 'make check' scripts,
although that's not an absolute requirement. I'm not really
concerned about performance tests for a while, just testing the
behavior of particular interleavings of statements in multiple
sessions. If psql isn't expected to support that soon, any
suggestions? Is pgTAP suited to this?

We've discussed it a bit in the past with regard to testing replication and such. I think the consensus was, failing support for concurrent sessions in psql, to use a Perl script to control multiple psql sessions and perhaps use Test::More to do the testing. Although pgTAP might make sense, too, if the tests ought to run in the database.

Best,

David

"David E. Wheeler" <david@kineticode.com> wrote:

I think the consensus was, failing support for concurrent sessions
in psql, to use a Perl script to control multiple psql sessions
and perhaps use Test::More to do the testing.

Are there any examples of that? While I can hack my way through
regular expressions when I need them, perl as a language is
something I don't know at all; with an example I might be able to
come up to speed quickly, though.

Although pgTAP might make sense, too, if the
tests ought to run in the database.

I need to run statements against a database; I don't particularly
need any special features of psql for this. Can anyone confirm that
pgTAP can let you interleave specific statements against specific
connections in a specific sequence? (The answer to that didn't leap
out at me in a quick scan of the docs.)

-Kevin

On mån, 2010-01-04 at 17:10 -0600, Kevin Grittner wrote:

"David E. Wheeler" <david@kineticode.com> wrote:

I think the consensus was, failing support for concurrent sessions
in psql, to use a Perl script to control multiple psql sessions
and perhaps use Test::More to do the testing.

Are there any examples of that? While I can hack my way through
regular expressions when I need them, perl as a language is
something I don't know at all; with an example I might be able to
come up to speed quickly, though.

If you're not deep into Perl, perhaps ignore the Test::More comment for
the moment and just use DBI to connect to several database sessions,
execute your queries and check if the results are what you want. Once
you have gotten somewhere with that, wrapping a test harness around it
is something others will be able to help with.

Although pgTAP might make sense, too, if the
tests ought to run in the database.

I need to run statements against a database; I don't particularly
need any special features of psql for this. Can anyone confirm that
pgTAP can let you interleave specific statements against specific
connections in a specific sequence? (The answer to that didn't leap
out at me in a quick scan of the docs.)

pgTAP isn't really going to help you here, as it runs with *one*
database session, and its main functionality is to format the result of
SQL functions into TAP output, which is not very much like what you
ought to be doing.

On Jan 4, 2010, at 3:29 PM, Peter Eisentraut wrote:

If you're not deep into Perl, perhaps ignore the Test::More comment for
the moment and just use DBI to connect to several database sessions,
execute your queries and check if the results are what you want. Once
you have gotten somewhere with that, wrapping a test harness around it
is something others will be able to help with.

Last I heard, Andrew was willing to require Test::More for testing, so that a Perl script could handle multiple psql connections (perhaps forked) and output test results based on them. But he wasn't as interested in requiring DBI and DBD::Pg, neither of which are in the Perl core and are more of a PITA to install (not huge, but the barrier might as well stay low).

pgTAP isn't really going to help you here, as it runs with *one*
database session, and its main functionality is to format the result of
SQL functions into TAP output, which is not very much like what you
ought to be doing.

Right, exactly.

Best,

David

Hi,

Kevin Grittner wrote:

It's very soon going to be critical that I be able to test particular
interleavings of statements in particular concurrent transaction sets
to be able to make meaningful progress on the serializable
transaction work.

I've something in place for Postgres-R, as I also need to test
concurrent transactions there. It's based on python/twisted and is able
to start multiple Postgres instances (as required for testing
replication) and query them concurrently (as you seem to need as well).
It uses an asynchronous event loop (from twisted) and basically controls
processes, issues queries and checks results and ordering constraints
(e.g. transaction X must commit and return a result before transaction Y).

I'm still under the impression that this testing framework needs
cleanup. However, others already showed interest as well...

Regards

Markus Wanner

Markus Wanner <markus@bluegap.ch> wrote:

Kevin Grittner wrote:

It's very soon going to be critical that I be able to test
particular interleavings of statements in particular concurrent
transaction sets to be able to make meaningful progress on the
serializable transaction work.

I've something in place for Postgres-R, as I also need to test
concurrent transactions there. It's based on python/twisted and is
able to start multiple Postgres instances (as required for testing
replication) and query them concurrently (as you seem to need as
well). It uses an asynchronous event loop (from twisted) and
basically controls processes, issues queries and checks results
and ordering constraints (e.g. transaction X must commit and
return a result before transaction Y).

Where would I find this (and any related documentation)?

-Kevin

Hi,

Kevin Grittner wrote:

Where would I find this (and any related documentation)?

Sorry, if that didn't get clear. I'm trying to put together something I
can release real soon now (tm). I'll keep you informed.

Regards

Markus Wanner

"David E. Wheeler" <david@kineticode.com> wrote:

Last I heard, Andrew was willing to require Test::More for
testing, so that a Perl script could handle multiple psql
connections (perhaps forked) and output test results based on
them. But he wasn't as interested in requiring DBI and DBD::Pg,
neither of which are in the Perl core and are more of a PITA to
install (not huge, but the barrier might as well stay low).

OK, I've gotten familiar with Perl as a programming language and
tinkered with Test::More. What's not clear to me yet is what would
be considered good technique for launching several psql sessions
from that environment, interleaving commands to each of them, and
checking results from each of them as the test plan progresses. Any
code snippets or URLs to help me understand that are welcome. (It
seems clear enough with DBI, but I'm trying to avoid that per the
above.)

-Kevin

On ons, 2010-01-06 at 15:52 -0600, Kevin Grittner wrote:

"David E. Wheeler" <david@kineticode.com> wrote:

Last I heard, Andrew was willing to require Test::More for
testing, so that a Perl script could handle multiple psql
connections (perhaps forked) and output test results based on
them. But he wasn't as interested in requiring DBI and DBD::Pg,
neither of which are in the Perl core and are more of a PITA to
install (not huge, but the barrier might as well stay low).

OK, I've gotten familiar with Perl as a programming language and
tinkered with Test::More. What's not clear to me yet is what would
be considered good technique for launching several psql sessions
from that environment, interleaving commands to each of them, and
checking results from each of them as the test plan progresses. Any
code snippets or URLs to help me understand that are welcome. (It
seems clear enough with DBI, but I'm trying to avoid that per the
above.)

Then I don't see much of a point in using Perl. You might as well fire
up a few psqls from a shell script.

On Jan 6, 2010, at 1:52 PM, Kevin Grittner wrote:

Last I heard, Andrew was willing to require Test::More for
testing, so that a Perl script could handle multiple psql
connections (perhaps forked) and output test results based on
them. But he wasn't as interested in requiring DBI and DBD::Pg,
neither of which are in the Perl core and are more of a PITA to
install (not huge, but the barrier might as well stay low).

OK, I've gotten familiar with Perl as a programming language and
tinkered with Test::More. What's not clear to me yet is what would
be considered good technique for launching several psql sessions
from that environment, interleaving commands to each of them, and
checking results from each of them as the test plan progresses. Any
code snippets or URLs to help me understand that are welcome. (It
seems clear enough with DBI, but I'm trying to avoid that per the
above.)

Probably the simplest way is to use the core IPC::Open3 module:

http://search.cpan.org/perldoc?IPC::Open3

IPC::Run might be easier to use if it's available, but it's not in Perl core, so YMMV. Really it's up to andrew what modules he requires test servers to have.

Best,

David

On Jan 6, 2010, at 2:08 PM, Peter Eisentraut wrote:

Then I don't see much of a point in using Perl. You might as well fire
up a few psqls from a shell script

If you're more comfortable with shell, then yes. Although then it won't run on Windows, will it?

Best,

David

On 2010-01-07 00:08 +0200, Peter Eisentraut wrote:

On ons, 2010-01-06 at 15:52 -0600, Kevin Grittner wrote:

"David E. Wheeler"<david@kineticode.com> wrote:

Last I heard, Andrew was willing to require Test::More for
testing, so that a Perl script could handle multiple psql
connections (perhaps forked) and output test results based on
them. But he wasn't as interested in requiring DBI and DBD::Pg,
neither of which are in the Perl core and are more of a PITA to
install (not huge, but the barrier might as well stay low).

OK, I've gotten familiar with Perl as a programming language and
tinkered with Test::More. What's not clear to me yet is what would
be considered good technique for launching several psql sessions
from that environment, interleaving commands to each of them, and
checking results from each of them as the test plan progresses. Any
code snippets or URLs to help me understand that are welcome. (It
seems clear enough with DBI, but I'm trying to avoid that per the
above.)

Then I don't see much of a point in using Perl. You might as well fire
up a few psqls from a shell script.

I don't see how that would work, but I might have misunderstood what
we're reaching for here. What I think would be most useful would be to
interleave statements between transactions, not just randomly fire psql
sessions and hope for race conditions.

Regards,
Marko Tiikkaja

Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> wrote:

On 2010-01-07 00:08 +0200, Peter Eisentraut wrote:

Then I don't see much of a point in using Perl. You might as
well fire up a few psqls from a shell script.

I don't see how that would work, but I might have misunderstood
what we're reaching for here. What I think would be most useful
would be to interleave statements between transactions, not just
randomly fire psql sessions and hope for race conditions.

Yeah, I want to test specific interleavings of statements on
concurrent connections. There may *also* be some tests which throw
a lot at the server concurrently in a more random fashion, but it is
important to be able to have some very controlled tests where we
don't count on randomly creating the desired conflicts.

It would be valuable to be able to include some of these tests with
controlled and predicatable statement interleavings in the "make
check" tests.

-Kevin

Marko Tiikkaja wrote:

On 2010-01-07 00:08 +0200, Peter Eisentraut wrote:

Then I don't see much of a point in using Perl. You might as well fire
up a few psqls from a shell script.

I don't see how that would work, but I might have misunderstood what
we're reaching for here. What I think would be most useful would be
to interleave statements between transactions, not just randomly
fire psql sessions and hope for race conditions.

Open a few psql with -f pointing to a pipe, and from the shell write
into the pipe? I don't think it's straightforward, but it should be
possible.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> wrote:

Open a few psql with -f pointing to a pipe, and from the shell
write into the pipe? I don't think it's straightforward, but it
should be possible.

I'll play with it and see what I can do.

Thanks,

-Kevin

On Wed, Jan 6, 2010 at 4:52 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:

"David E. Wheeler" <david@kineticode.com> wrote:

Last I heard, Andrew was willing to require Test::More for
testing, so that a Perl script could handle multiple psql
connections (perhaps forked) and output test results based on
them. But he wasn't as interested in requiring DBI and DBD::Pg,
neither of which are in the Perl core and are more of a PITA to
install (not huge, but the barrier might as well stay low).

OK, I've gotten familiar with Perl as a programming language and
tinkered with Test::More.  What's not clear to me yet is what would
be considered good technique for launching several psql sessions
from that environment, interleaving commands to each of them, and
checking results from each of them as the test plan progresses.  Any
code snippets or URLs to help me understand that are welcome.  (It
seems clear enough with DBI, but I'm trying to avoid that per the
above.)

Doing this without DBI is going to be ten times harder than doing it
with DBI. Are we really sure that's not a viable option?

...Robert

Robert Haas wrote:

On Wed, Jan 6, 2010 at 4:52 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:

"David E. Wheeler" <david@kineticode.com> wrote:

Last I heard, Andrew was willing to require Test::More for
testing, so that a Perl script could handle multiple psql
connections (perhaps forked) and output test results based on
them. But he wasn't as interested in requiring DBI and DBD::Pg,
neither of which are in the Perl core and are more of a PITA to
install (not huge, but the barrier might as well stay low).

OK, I've gotten familiar with Perl as a programming language and
tinkered with Test::More. What's not clear to me yet is what would
be considered good technique for launching several psql sessions
from that environment, interleaving commands to each of them, and
checking results from each of them as the test plan progresses. Any
code snippets or URLs to help me understand that are welcome. (It
seems clear enough with DBI, but I'm trying to avoid that per the
above.)

Doing this without DBI is going to be ten times harder than doing it
with DBI. Are we really sure that's not a viable option?

In the buildfarm? Yes, I think so. The philosophy of the buildfarm is
that it should do what you would do yourself by hand.

And adding DBI as a requirement for running a buildfarm member would be
a significant extra barrier to entry, ISTM. (I am very fond of DBI, and
use it frequently, BTW)

I'm persuadable on most things, but this one would take a bit of doing.

A parallel psql seems to me a better way to go. We talked about that a
while ago, but I don't recall what happened to it.

cheers

andrew

On Wed, Jan 6, 2010 at 8:40 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

Doing this without DBI is going to be ten times harder than doing it
with DBI.  Are we really sure that's not a viable option?

In the buildfarm? Yes, I think so. The philosophy of the buildfarm is that
it should do what you would do yourself by hand.

It just seems crazy to me to try to test anything without proper
language bindings. Opening a psql session and parsing the results
seems extraordinarily painful. I wonder if it would make sense write
a small wrapper program that uses libpq and dumps out the results in a
format that is easy for Perl to parse.

Another idea would be to make a set of Perl libpq bindings that is
simpler than DBD::Pg and don't go through DBI. If we put those in the
main source tree (perhaps as a contrib module) they would be available
wherever we need them.

A parallel psql seems to me a better way to go. We talked about that a while
ago, but I don't recall what happened to it.

That seems like a dead-end to me. It's hard for me to imagine it's
ever going to be more than a toy.

...Robert

Robert Haas <robertmhaas@gmail.com> writes:

It just seems crazy to me to try to test anything without proper
language bindings. Opening a psql session and parsing the results
seems extraordinarily painful. I wonder if it would make sense write
a small wrapper program that uses libpq and dumps out the results in a
format that is easy for Perl to parse.

Another idea would be to make a set of Perl libpq bindings that is
simpler than DBD::Pg and don't go through DBI. If we put those in the
main source tree (perhaps as a contrib module) they would be available
wherever we need them.

We have not yet fully accepted the notion that you must have Perl to
build (and, in fact, I am right now trying to verify that you don't).
I don't think that requiring Perl to test is going to fly.

A parallel psql seems to me a better way to go. We talked about that a while
ago, but I don't recall what happened to it.

That seems like a dead-end to me. It's hard for me to imagine it's
ever going to be more than a toy.

Well, the argument there is that it might be useful for actual use,
not only testing.

regards, tom lane