Re: Testing of MVCC

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Greg Stark <gsstark(at)mit(dot)edu> writes:
> > So why bother with driving multiple invocations of psql under
> > Expect. Just use DBD::Pg to open as many connections as you want and
> > issue whatever queries you want.
>
> The bit that I think is missing in DBI is "issue a command and don't
> wait for the result just yet". Without that, you cannot for instance
> stack up several waiters for the same lock, as you might wish to do to
> verify that they get released in the correct order once the original
> lock holder goes away. Or stack up some conflicting waiters and check
> to see if deadlock is detected when it should be ... or contrariwise,
> not signalled when it should not be. There's lots of stuff you can
> do that isn't exactly probing for race conditions, yet would be awfully
> nice to check for in a routine test suite.
>
> I might be wrong though, not being exactly a DBI guru ... can this
> sort of thing be done?

Hm.

The API is designed like that. You issue a query in one call and retrieve the
results in a separate call (or series of calls). I don't know the DBD::Pg
implementation enough to be sure it's a 1:1 mapping to Postgres wire protocol
messages but I would expect you could get a pretty control at that level.

I doubt it's using asynchronous I/O though. Which would mean, for example,
that you can't arrange to send a message while another connection is in the
middle of receiving a large message.

I think part of this boils down to a deficiency in the Postgres wire protocol
though. It doesn't allow for interleaving calls in the middle of downloading a
large results block. That means DBD::Pg would be in bad shape if it returned
control to the user while in the process of downloading query results. If the
user issued any calls to the driver in that state it would have to return some
sort of error.

By comparison DBD::Oracle can stream results to the user while still
continuing to download more results. It tries to adjust the number of records
read whenever the buffer empties to keep the network pipeline full. This
allows the user to process records while the database is still working on
executing the query and the network is still working on shipping the results.
(Obviously this works better with some plans than others.) And the driver can
cancel or issue other queries between any of these block reads.

--
greg