[PostgreSQL-Hackers] Concurrent connections in psql

On Tue, 2006-12-12 at 18:54 -0500, Gregory Stark wrote:

A brief explanation including an example regression test (the SAVEPOINT
locking bug discovered recently) and the patch here:

http://community.enterprisedb.com/concurrent/index.html

I mentioned this a while back, now that 8.2 is out perhaps others will be more
interested in new code.

Currently Postgres regression tests only test functionality within a single
session. There are no regression tests that test the transaction semantics or
locking behaviour across multiple transactions.

I modified psql to allow you to open multiple connections and switch between
them with a sort of csh job control style interface. It actually works out
pretty well. It's fairly easy to write regression tests for basic 2-client or
3-client cases.

The actual user interface may need some discussion though. I didn't want to
play the name game so I just prefixed all my commands with "c" and figured we
can always rename them later.

And experience with actually writing the tests shows that the explicit \cwait
command which was needed to eliminate (in practice if not in theory) race
conditions in regression tests turns out to be more flexibility than
necessary. Since you end up having to insert one in precisely predictable
locations -- namely after every asynchronous command and after every
connection switch -- perhaps it would be simpler to just have a "\pset cwait"
command that automatically introduces timeouts in precisely those places.

A brief explanation including an example regression test (the SAVEPOINT
locking bug discovered recently) and the patch here:

http://community.enterprisedb.com/concurrent/index.html

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Sat, Jan 20, 2007 at 05:11:25PM -0500, Bruce Momjian wrote:

What are people's opinions on this patch? (It is at the URL below.)

I like the capability, and am impressed it allowed testing that found
some concurrency bugs in our server, but it is a large patch to psql.

---------------------------------------------------------------------------

Gregory Stark wrote:
--
Bruce Momjian [email protected]
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

I mentioned this a while back, now that 8.2 is out perhaps others will be more
interested in new code.

Currently Postgres regression tests only test functionality within a single
session. There are no regression tests that test the transaction semantics or
locking behaviour across multiple transactions.

I modified psql to allow you to open multiple connections and switch between
them with a sort of csh job control style interface. It actually works out
pretty well. It's fairly easy to write regression tests for basic 2-client or
3-client cases.

The actual user interface may need some discussion though. I didn't want to
play the name game so I just prefixed all my commands with "c" and figured we
can always rename them later.

And experience with actually writing the tests shows that the explicit \cwait
command which was needed to eliminate (in practice if not in theory) race
conditions in regression tests turns out to be more flexibility than
necessary. Since you end up having to insert one in precisely predictable
locations -- namely after every asynchronous command and after every
connection switch -- perhaps it would be simpler to just have a "\pset cwait"
command that automatically introduces timeouts in precisely those places.

A brief explanation including an example regression test (the SAVEPOINT
locking bug discovered recently) and the patch here:

http://community.enterprisedb.com/concurrent/index.html

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Would people be interested in this feature? There was some positive reaction
from users but I'm not sure how excited developers are about complicating the
logic in psql (which is already pretty tangled).

This code bitrotted severely when Tom added the cursor support to psql. I
don't mind redoing it if people want it though. I already did a first pass at
doing so but it wasn't clear to me how best to integrate it with that cursor
support change. I elected to treat each chunk of results from the cursor as a
separate result set which makes it possible to switch connections between
chunks. That's nice but probably not really acceptable judging by how much
effort Tom went through in the cursor code to avoid having the chunks appear
as separate result sets. Probably I'll have to do more work in that area.

Are people interested in having this? The reason I think it's particularly
interesting is writing regression tests -- especially to test HOT cases.


"Gregory Stark" <[email protected]> writes:
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Bruce Momjian wrote:
Yes, yes. I would like to have used it when testing the MyProc->xmin
improvements. The only thing that has held it back from being applied
was that there was no documentation/examples of how it would be used.

On Tue, 2007-03-27 at 18:16 +0100, Heikki Linnakangas wrote:
Bruce Momjian wrote:
Hear hear! I had trouble writing regression tests for the MVCC-safe
cluster patch. Had I had an up-to-date version at hand, I would've used it.

I haven't looked at the psql source code, but would it be possible to
clean it up to make it less tangled and ugly, while you're at it?

Simon Riggs wrote:
I would love, love, love to be able to use this syntax within pg_dump as
well, so we can create multiple indexes in parallel at restore time.
Anyone fancy adding that as well? We should be able to speed up overall
index builds by x2 using concurrent builds.

On Tue, 2007-03-27 at 17:11 -0400, Andrew Dunstan wrote:
Simon Riggs wrote:
You will need to teach pg_restore any trick you use here - it doesn't
use psql.

Simon Riggs wrote:
The proposed change is to psql and making it work with pg_restore would
take a lot more thought, so probably not an 8.3 item. Should we make it
to neither because we can't make it to both?

"Simon Riggs" <[email protected]> writes:
I would love, love, love to be able to use this syntax within pg_dump as
well, so we can create multiple indexes in parallel at restore time.

Tom Lane wrote:
"Simon Riggs" <[email protected]> writes:
I can hardly conceive of greater folly than putting an *experimental*
psql facility into pg_dump scripts, thereby forcing us to support it
until the end of time.

I mentioned this a while back, now that 8.2 is out perhaps others will be
more
interested in new code.

Currently Postgres regression tests only test functionality within a
single
session. There are no regression tests that test the transaction semantics
or
locking behaviour across multiple transactions.

I modified psql to allow you to open multiple connections and switch
between
them with a sort of csh job control style interface. It actually works out
pretty well. It's fairly easy to write regression tests for basic 2-client
or
3-client cases.

The actual user interface may need some discussion though. I didn't want
to
play the name game so I just prefixed all my commands with "c" and figured
we
can always rename them later.

And experience with actually writing the tests shows that the explicit
\cwait
command which was needed to eliminate (in practice if not in theory) race
conditions in regression tests turns out to be more flexibility than
necessary. Since you end up having to insert one in precisely predictable
locations -- namely after every asynchronous command and after every
connection switch -- perhaps it would be simpler to just have a "\pset
cwait"
command that automatically introduces timeouts in precisely those places.

A brief explanation including an example regression test (the SAVEPOINT
locking bug discovered recently) and the patch here:

http://community.enterprisedb.com/concurrent/index.html

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

I mentioned this a while back, now that 8.2 is out perhaps others will be
more
interested in new code.

Currently Postgres regression tests only test functionality within a
single
session. There are no regression tests that test the transaction semantics
or
locking behaviour across multiple transactions.

I modified psql to allow you to open multiple connections and switch
between
them with a sort of csh job control style interface. It actually works out
pretty well. It's fairly easy to write regression tests for basic 2-client
or
3-client cases.

The actual user interface may need some discussion though. I didn't want
to
play the name game so I just prefixed all my commands with "c" and figured
we
can always rename them later.

And experience with actually writing the tests shows that the explicit
\cwait
command which was needed to eliminate (in practice if not in theory) race
conditions in regression tests turns out to be more flexibility than
necessary. Since you end up having to insert one in precisely predictable
locations -- namely after every asynchronous command and after every
connection switch -- perhaps it would be simpler to just have a "\pset
cwait"
command that automatically introduces timeouts in precisely those places.

A brief explanation including an example regression test (the SAVEPOINT
locking bug discovered recently) and the patch here:

http://community.enterprisedb.com/concurrent/index.html

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings