Re: Concurrent connections in psql

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

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
>

One of the original inspirations for this was the observation from
Martijn that concurrent test cases are much easier to write in a single
script, which has proved to be an essential observation to the
development of useful multi-session test cases. So kudos to Martijn.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

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:
>
> 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

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

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

Sounds like good reason to get it in early... :)

It would be nice if there were some tests for this/that used this...
wasn't there a patch for that floating around somewhere?

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:
> >
> > 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
>
> --
> Bruce Momjian bruce(at)momjian(dot)us
> 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
>

--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

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" <stark(at)enterprisedb(dot)com> writes:

> 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

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

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.

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

Gregory Stark wrote:
>
> 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" <stark(at)enterprisedb(dot)com> writes:
>
> > 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
>
> --
> Gregory Stark
> EnterpriseDB http://www.enterprisedb.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

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

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.

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?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

+++

We'd love this feature as it would really help us write better test cases !

Regards
Sailesh

--
Sailesh Krishnamurthy
Amalgamated Insight
[W] (650) 242-3503
[C] (650) 804-6585

-----Original Message-----
From: pgsql-hackers-owner(at)postgresql(dot)org
[mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Gregory Stark
Sent: Tuesday, March 27, 2007 6:39 AM
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] Concurrent connections in psql

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" <stark(at)enterprisedb(dot)com> writes:

> 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

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

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

+++

We'd love this feature as it would really help us write better test cases !

Regards
Sailesh

--
Sailesh Krishnamurthy
Amalgamated Insight
[W] (650) 242-3503
[C] (650) 804-6585

-----Original Message-----
From: pgsql-hackers-owner(at)postgresql(dot)org
[mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Gregory Stark
Sent: Tuesday, March 27, 2007 6:39 AM
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] Concurrent connections in psql

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" <stark(at)enterprisedb(dot)com> writes:

> 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

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

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

On Tue, 2007-03-27 at 18:16 +0100, Heikki Linnakangas wrote:
> 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.
>
> 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?

Greg,

It sounds like we still need to remove the \cwait command, yes??
If we are going to perform that surgery, it probably should be you,
sorry. Others may have input on the internal implementation but its your
name on the tin.

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.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

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.
>
>

You will need to teach pg_restore any trick you use here - it doesn't
use psql.

cheers

andrew

On Tue, 2007-03-27 at 17:11 -0400, Andrew Dunstan wrote:
> 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.

> You will need to teach pg_restore any trick you use here - it doesn't
> use psql.

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
EnterpriseDB http://www.enterprisedb.com

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

I'm all for the change to psql, but personally I think it's late in the
day to be specifying a change to pg_dump.

cheers

andrew

"Simon Riggs" <simon(at)2ndquadrant(dot)com> 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.

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.

regards, tom lane

Tom Lane wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> 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.
>
> 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.

We could write a [awk | perl | scripting language of your choice]-script
to transform CREATE INDEX statements in normal pg_dump output to use
the concurrent psql commands. That way we don't need to modify pg_dump.
Whether or not to build indexes should be a restore-time, not dump-time
option anyway. Whether you want it or not it depends on the target, not
the source.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com