Thread: 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@momjian.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@momjian.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@nasby.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@enterprisedb.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@enterprisedb.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@momjian.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@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Gregory Stark Sent: Tuesday, March 27, 2007 6:39 AM To: pgsql-hackers@postgresql.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@enterprisedb.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@2ndquadrant.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@2ndquadrant.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 normalpg_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
+++ 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@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Gregory Stark Sent: Tuesday, March 27, 2007 6:39 AM To: pgsql-hackers@postgresql.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@enterprisedb.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