February 2020 - Pharo-users

Re: [Pharo-users] P3 and concurrenty

by ducasse

Amazing experience! This is the power of Pharo at work. Thanks a lot Sven. S > On 10 Feb 2020, at 21:24, Petter Egesund <petter.egesund(a)gmail.com> wrote: > > Perfect, this is exactly what I needed. Thank so much!! > > Petter > > On Mon, Feb 10, 2020 at 8:44 PM Sven Van Caekenberghe <sven(a)stfx.eu <mailto:sven@stfx.eu>> wrote: > Hi Petter, > > > On 10 Feb 2020, at 16:46, Petter Egesund <petter.egesund(a)gmail.com <mailto:petter.egesund@gmail.com>> wrote: > > > > Yes, I see - that sounds sensible. > > > > Could it be an idea to subclass the pool-class and to prepare statements in this method, if there is a kind of init-method in the class? What do you think? > > That would have indeed been one approach but for this situation I opted for a configurator block. > > P3ConnectionPool>>#configurator: oneArgumentBlock > "Set oneArgumentBlock to be my configurator. > This is code that will be executed once on each newly created connection (P3Client). > The default configuration asserts #isWorking on the argument" > > The default is > > configurator := [ :p3Client | self assert: p3Client isWorking ] > > which basically does an extra validation and ensures the opening of the connection. > > Now, a little piece of functionality was still missing, I added it with the following commit: > > https://github.com/svenvc/P3/commit/e3f161d9f9ae34b3b020ad924b3c0f116f68c0b0 <https://github.com/svenvc/P3/commit/e3f161d9f9ae34b3b020ad924b3c0f116f68c0b0> > > Your usage scenario should now be like this: you create a P3ConnectionPool with the proper URL, then you set your configurator to add your prepared statements (you could delegate this to a helper class if you have a lot of them). > > pool configurator: [ :p3Client | > p3Client prepare: 'SELECT ...' named: 'select1'. > p3Client prepare: 'INSET ...' named: 'insert1' ] > > Later, when using a connection from the pool, you refer to your prepared statements by name. > > pool withConnection: [ :p3Client | > (p3Client preparedStatementNamed: 'select1') > execute: { args } ] > > Sven > > PS: have a look at P3PreparedStatementTest>>#testNamedPreparedStatement for an operation example (minus the connection pooling). > > > On Mon, Feb 10, 2020 at 4:36 PM Sven Van Caekenberghe <sven(a)stfx.eu <mailto:sven@stfx.eu>> wrote: > > > > > > > On 10 Feb 2020, at 16:30, Petter Egesund <petter.egesund(a)gmail.com <mailto:petter.egesund@gmail.com>> wrote: > > > > > > Hi and thanks for an answer above my expectations to our problem. > > > > > > This is really awesome, I was just about to start coding a pool myself, but getting it directly from the author of the base library is off course way better :) > > > > > > I will try out and get back as soon as we have tested. > > > > > > One quick question - are the prepared statements which are connected to the pool thread safe? > > > > Prepared statements are scoped to a single connection in PSQL, AFAIK. > > > > What you will probably want to do is use a #configurator: block to set up your prepared statements once, like you do now. Then each thread/process grabs its own connection from the pool, (which will be already configured properly) and uses it, for itself, and finally returns it to the pool. > > > > Interacting with the pool should be fully thread safe. Once you are using a connection, it not longer is, but is also does not have to, since one connection equals one client thread. > > > > > Petter > > > > > > On Mon, Feb 10, 2020 at 12:13 PM Sven Van Caekenberghe <sven(a)stfx.eu <mailto:sven@stfx.eu>> wrote: > > > Hi Petter, > > > > > > https://github.com/svenvc/P3/commit/a6b409d0d92cb92bf9b44452908bb9033523b863 <https://github.com/svenvc/P3/commit/a6b409d0d92cb92bf9b44452908bb9033523b863> adds a connection pool. > > > > > > Here is the class comment: > > > > > > ====== > > > > > > I am P3ConnectionPool. > > > > > > I offer a pool of shared PSQL connections (P3Client instances) as a resource. > > > > > > After configuring me with at least a url, you use me by calling #withConnection: > > > > > > pool := P3ConnectionPool url: 'psql://sven@localhost'. > > > pool withConnection: [ :p3Client | > > > p3Client query: 'SELECT table_name FROM information_schema.tables' ]. > > > pool close. > > > > > > When a connection is in use, it is not part of the pool. > > > When a connection is returned/released to the pool, it becomes available for reuse. > > > > > > The pool's size is the number of open connection ready to be reused. > > > The pool's capacity is the maximum number of connection that will be pooled. > > > Excess connections will be closed when they are returned/released to the pool. > > > > > > New connections are created as needed. > > > You can set a #configurator to further initialize new connections. > > > You can use #warmUp or #warmUp: to precreate a number of connections. > > > > > > When an error occurs, the connection should not be reused and be closed by the caller. > > > > > > ====== > > > > > > There are some unit tests as well. > > > > > > Let me know if this works for you. > > > > > > Sven > > > > > > > On 9 Feb 2020, at 19:04, Petter Egesund <petter.egesund(a)gmail.com <mailto:petter.egesund@gmail.com>> wrote: > > > > > > > > Yes, thanks for good feedback. > > > > > > > > I will try the pooled way, I think - not primarily because of speed, but due to that our library is built around prepared connections. > > > > > > > > Petter > > > > > > > > On Sun, Feb 9, 2020 at 6:01 PM Sven Van Caekenberghe <sven(a)stfx.eu <mailto:sven@stfx.eu>> wrote: > > > > Hi Petter, > > > > > > > > > On 9 Feb 2020, at 17:27, Petter Egesund <petter.egesund(a)gmail.com <mailto:petter.egesund@gmail.com>> wrote: > > > > > > > > > > Hi Sven and thanks for answering! > > > > > > > > > > I use Teapot with one common sql-connecton, not one for each user session. At startup I create several sql statements and these does not seem to be usable from different Teapot request at the same time. > > > > > > > > It is useable, but not concurrently. > > > > > > > > > I could create one connection pr. session and then close the database connection when the user leaves, but then I also would need to create all the prepared sql-statements for each session, which does not sound right to me? > > > > > > > > Yes and no, see further. > > > > > > > > > It seems I have will have to look into the other solution, and see if I can use some mutex stuff to avoid several tasks acessessing the same resources at the same time. > > > > > > > > Yes, follow the pointer that I gave you, it is not hard. > > > > > > > > > Creating a pool sounds like the right solution to me now - any meaning about this? > > > > > > > > Yes, you could create a connection pool. But that is harder than it sounds: what is the minimum size, the maximum size, what do you do when you go over it, how do you make sure that a resource (connection) is clean when returning it to the pool (given authentication, possible errors), ... > > > > > > > > I don't know what you are doing, but I think you focus too much on performance issues. I would first try to get the code correct and worry about performance later on. > > > > > > > > Running > > > > > > > > P3ClientTest new runBenchmark1Bench. > > > > > > > > on my machine gives me a BenchmarkResult(67 iterations in 5 seconds 57 milliseconds. 13.249 per second). This is a query that returns 10.000 records with 5 columns. It is reusing the same client/connection for all iterations. > > > > > > > > If I modify this slightly to use a new client/connection each time, like this > > > > > > > > [ > > > > (P3Client url: 'psql://sven@localhost') > > > > query: 'SELECT * FROM benchmark1'; > > > > close > > > > ] benchFor: 5 seconds > > > > > > > > I get a BenchmarkResult(65 iterations in 5 seconds 34 milliseconds. 12.912 per second) which almost as fast. Of course, for smaller queries, the connect/disconnect overhead will be more significant. > > > > > > > > And note that this is not using prepared statements. > > > > > > > > So I would start by opening/closing a connection each time you need it. > > > > > > > > HTH, > > > > > > > > Sven > > > > > > > > > Petter > > > > > > > > > > > > > > > On Sun, Feb 9, 2020 at 4:52 PM Sven Van Caekenberghe <sven(a)stfx.eu <mailto:sven@stfx.eu>> wrote: > > > > > Hi Petter, > > > > > > > > > > [ CC-ing the Pharo Users list ] > > > > > > > > > > P3Client is not built/designed to be used by multiple processes concurrently. Each database connection is represented by an instance of P3Client and holds some state both at the client as well as at the server side. > > > > > > > > > > Typically, in a multi user server application, each connection should have its own P3Client / psql connection. For example, in Seaside, a custom WASession subclass gives each session/user its own p3 connection/client. > > > > > > > > > > Is that what you are doing ? > > > > > > > > > > If not, you could wrap your db accessing code so that mutual exclusion is provided. For example, you can have a look at AbstractCache #beThreadSafe and #critical: > > > > > > > > > > That will then serialise requests and possibly block one onto the other. > > > > > > > > > > HTH, > > > > > > > > > > Sven > > > > > > > > > > PS: another thing to take care of if closing your sql connections when the session is no longer needed. > > > > > > > > > > PS: Zinc HTTP does also provide a session mechanism (ZnServerSession[Manager]) but these work with cookies and typically won't help with a REST access pattern. > > > > > > > > > > > On 9 Feb 2020, at 14:21, Petter Egesund <petter.egesund(a)gmail.com <mailto:petter.egesund@gmail.com>> wrote: > > > > > > > > > > > > Hi Sven > > > > > > > > > > > > We are using Pharo as our backend in a project and we have run into a problem with P3. > > > > > > > > > > > > The problem seems to be connected to compiled sql statements and concurrency. > > > > > > > > > > > > We keep getting this error: Bindcomplete message expected > > > > > > > > > > > > Problem seems to be easy to reproduce: > > > > > > > > > > > > 1) Compile any sql statement > > > > > > 2) Use this statement in a query twice (!) in a teapot endpoint > > > > > > > > > > > > The run some concurrent queries, like "curl http://localhost:8080/endpoint <http://localhost:8080/endpoint> & curl http://localhost:8080/endpoint. <http://localhost:8080/endpoint.>." (add several curls after here). > > > > > > > > > > > > One could also use ex. siege (https://manpages.ubuntu.com/manpages/trusty/man1/siege.1.html <https://manpages.ubuntu.com/manpages/trusty/man1/siege.1.html>) for easy reproducing. > > > > > > > > > > > > If we chain the curls after each other, like "curl http://localhost:8080/endpoint <http://localhost:8080/endpoint> && https://manpages.ubuntu.com/manpages/trusty/man1/siege.1.html <https://manpages.ubuntu.com/manpages/trusty/man1/siege.1.html> && https://manpages.ubuntu.com/manpages/trusty/man1/siege.1.html. <https://manpages.ubuntu.com/manpages/trusty/man1/siege.1.html.>." it seems to work fine, so doing the request sequentially seem to work fine. > > > > > > > > > > > > My conclusion is that this must be connected to how teapot handles concurrency in companion with the compiled statements? > > > > > > > > > > > > Any clues on this one? We are on Pharo 8.0 with latest version of P3, PG 9.x) > > > > > > > > > > > > Best regards > > > > > > > > > > > > Petter Egesund (I wrote the heysql-package based on P3) > > > > > > > > > > > > > > >

Feb. 10, 2020

Re: [Pharo-users] P3 and concurrenty

by Petter Egesund

Perfect, this is exactly what I needed. Thank so much!! Petter On Mon, Feb 10, 2020 at 8:44 PM Sven Van Caekenberghe <sven(a)stfx.eu> wrote: > Hi Petter, > > > On 10 Feb 2020, at 16:46, Petter Egesund <petter.egesund(a)gmail.com> > wrote: > > > > Yes, I see - that sounds sensible. > > > > Could it be an idea to subclass the pool-class and to prepare statements > in this method, if there is a kind of init-method in the class? What do you > think? > > That would have indeed been one approach but for this situation I opted > for a configurator block. > > P3ConnectionPool>>#configurator: oneArgumentBlock > "Set oneArgumentBlock to be my configurator. > This is code that will be executed once on each newly created > connection (P3Client). > The default configuration asserts #isWorking on the argument" > > The default is > > configurator := [ :p3Client | self assert: p3Client isWorking ] > > which basically does an extra validation and ensures the opening of the > connection. > > Now, a little piece of functionality was still missing, I added it with > the following commit: > > > https://github.com/svenvc/P3/commit/e3f161d9f9ae34b3b020ad924b3c0f116f68c0b0 > > Your usage scenario should now be like this: you create a P3ConnectionPool > with the proper URL, then you set your configurator to add your prepared > statements (you could delegate this to a helper class if you have a lot of > them). > > pool configurator: [ :p3Client | > p3Client prepare: 'SELECT ...' named: 'select1'. > p3Client prepare: 'INSET ...' named: 'insert1' ] > > Later, when using a connection from the pool, you refer to your prepared > statements by name. > > pool withConnection: [ :p3Client | > (p3Client preparedStatementNamed: 'select1') > execute: { args } ] > > Sven > > PS: have a look at P3PreparedStatementTest>>#testNamedPreparedStatement > for an operation example (minus the connection pooling). > > > On Mon, Feb 10, 2020 at 4:36 PM Sven Van Caekenberghe <sven(a)stfx.eu> > wrote: > > > > > > > On 10 Feb 2020, at 16:30, Petter Egesund <petter.egesund(a)gmail.com> > wrote: > > > > > > Hi and thanks for an answer above my expectations to our problem. > > > > > > This is really awesome, I was just about to start coding a pool > myself, but getting it directly from the author of the base library is off > course way better :) > > > > > > I will try out and get back as soon as we have tested. > > > > > > One quick question - are the prepared statements which are connected > to the pool thread safe? > > > > Prepared statements are scoped to a single connection in PSQL, AFAIK. > > > > What you will probably want to do is use a #configurator: block to set > up your prepared statements once, like you do now. Then each thread/process > grabs its own connection from the pool, (which will be already configured > properly) and uses it, for itself, and finally returns it to the pool. > > > > Interacting with the pool should be fully thread safe. Once you are > using a connection, it not longer is, but is also does not have to, since > one connection equals one client thread. > > > > > Petter > > > > > > On Mon, Feb 10, 2020 at 12:13 PM Sven Van Caekenberghe <sven(a)stfx.eu> > wrote: > > > Hi Petter, > > > > > > > https://github.com/svenvc/P3/commit/a6b409d0d92cb92bf9b44452908bb9033523b863 > adds a connection pool. > > > > > > Here is the class comment: > > > > > > ====== > > > > > > I am P3ConnectionPool. > > > > > > I offer a pool of shared PSQL connections (P3Client instances) as a > resource. > > > > > > After configuring me with at least a url, you use me by calling > #withConnection: > > > > > > pool := P3ConnectionPool url: 'psql://sven@localhost'. > > > pool withConnection: [ :p3Client | > > > p3Client query: 'SELECT table_name FROM > information_schema.tables' ]. > > > pool close. > > > > > > When a connection is in use, it is not part of the pool. > > > When a connection is returned/released to the pool, it becomes > available for reuse. > > > > > > The pool's size is the number of open connection ready to be reused. > > > The pool's capacity is the maximum number of connection that will be > pooled. > > > Excess connections will be closed when they are returned/released to > the pool. > > > > > > New connections are created as needed. > > > You can set a #configurator to further initialize new connections. > > > You can use #warmUp or #warmUp: to precreate a number of connections. > > > > > > When an error occurs, the connection should not be reused and be > closed by the caller. > > > > > > ====== > > > > > > There are some unit tests as well. > > > > > > Let me know if this works for you. > > > > > > Sven > > > > > > > On 9 Feb 2020, at 19:04, Petter Egesund <petter.egesund(a)gmail.com> > wrote: > > > > > > > > Yes, thanks for good feedback. > > > > > > > > I will try the pooled way, I think - not primarily because of speed, > but due to that our library is built around prepared connections. > > > > > > > > Petter > > > > > > > > On Sun, Feb 9, 2020 at 6:01 PM Sven Van Caekenberghe <sven(a)stfx.eu> > wrote: > > > > Hi Petter, > > > > > > > > > On 9 Feb 2020, at 17:27, Petter Egesund <petter.egesund(a)gmail.com> > wrote: > > > > > > > > > > Hi Sven and thanks for answering! > > > > > > > > > > I use Teapot with one common sql-connecton, not one for each user > session. At startup I create several sql statements and these does not seem > to be usable from different Teapot request at the same time. > > > > > > > > It is useable, but not concurrently. > > > > > > > > > I could create one connection pr. session and then close the > database connection when the user leaves, but then I also would need to > create all the prepared sql-statements for each session, which does not > sound right to me? > > > > > > > > Yes and no, see further. > > > > > > > > > It seems I have will have to look into the other solution, and see > if I can use some mutex stuff to avoid several tasks acessessing the same > resources at the same time. > > > > > > > > Yes, follow the pointer that I gave you, it is not hard. > > > > > > > > > Creating a pool sounds like the right solution to me now - any > meaning about this? > > > > > > > > Yes, you could create a connection pool. But that is harder than it > sounds: what is the minimum size, the maximum size, what do you do when you > go over it, how do you make sure that a resource (connection) is clean when > returning it to the pool (given authentication, possible errors), ... > > > > > > > > I don't know what you are doing, but I think you focus too much on > performance issues. I would first try to get the code correct and worry > about performance later on. > > > > > > > > Running > > > > > > > > P3ClientTest new runBenchmark1Bench. > > > > > > > > on my machine gives me a BenchmarkResult(67 iterations in 5 seconds > 57 milliseconds. 13.249 per second). This is a query that returns 10.000 > records with 5 columns. It is reusing the same client/connection for all > iterations. > > > > > > > > If I modify this slightly to use a new client/connection each time, > like this > > > > > > > > [ > > > > (P3Client url: 'psql://sven@localhost') > > > > query: 'SELECT * FROM benchmark1'; > > > > close > > > > ] benchFor: 5 seconds > > > > > > > > I get a BenchmarkResult(65 iterations in 5 seconds 34 milliseconds. > 12.912 per second) which almost as fast. Of course, for smaller queries, > the connect/disconnect overhead will be more significant. > > > > > > > > And note that this is not using prepared statements. > > > > > > > > So I would start by opening/closing a connection each time you need > it. > > > > > > > > HTH, > > > > > > > > Sven > > > > > > > > > Petter > > > > > > > > > > > > > > > On Sun, Feb 9, 2020 at 4:52 PM Sven Van Caekenberghe <sven(a)stfx.eu> > wrote: > > > > > Hi Petter, > > > > > > > > > > [ CC-ing the Pharo Users list ] > > > > > > > > > > P3Client is not built/designed to be used by multiple processes > concurrently. Each database connection is represented by an instance of > P3Client and holds some state both at the client as well as at the server > side. > > > > > > > > > > Typically, in a multi user server application, each connection > should have its own P3Client / psql connection. For example, in Seaside, a > custom WASession subclass gives each session/user its own p3 > connection/client. > > > > > > > > > > Is that what you are doing ? > > > > > > > > > > If not, you could wrap your db accessing code so that mutual > exclusion is provided. For example, you can have a look at AbstractCache > #beThreadSafe and #critical: > > > > > > > > > > That will then serialise requests and possibly block one onto the > other. > > > > > > > > > > HTH, > > > > > > > > > > Sven > > > > > > > > > > PS: another thing to take care of if closing your sql connections > when the session is no longer needed. > > > > > > > > > > PS: Zinc HTTP does also provide a session mechanism > (ZnServerSession[Manager]) but these work with cookies and typically won't > help with a REST access pattern. > > > > > > > > > > > On 9 Feb 2020, at 14:21, Petter Egesund < > petter.egesund(a)gmail.com> wrote: > > > > > > > > > > > > Hi Sven > > > > > > > > > > > > We are using Pharo as our backend in a project and we have run > into a problem with P3. > > > > > > > > > > > > The problem seems to be connected to compiled sql statements and > concurrency. > > > > > > > > > > > > We keep getting this error: Bindcomplete message expected > > > > > > > > > > > > Problem seems to be easy to reproduce: > > > > > > > > > > > > 1) Compile any sql statement > > > > > > 2) Use this statement in a query twice (!) in a teapot endpoint > > > > > > > > > > > > The run some concurrent queries, like "curl > http://localhost:8080/endpoint & curl http://localhost:8080/endpoint.." > (add several curls after here). > > > > > > > > > > > > One could also use ex. siege ( > https://manpages.ubuntu.com/manpages/trusty/man1/siege.1.html) for easy > reproducing. > > > > > > > > > > > > If we chain the curls after each other, like "curl > http://localhost:8080/endpoint && > https://manpages.ubuntu.com/manpages/trusty/man1/siege.1.html && > https://manpages.ubuntu.com/manpages/trusty/man1/siege.1.html.." it seems > to work fine, so doing the request sequentially seem to work fine. > > > > > > > > > > > > My conclusion is that this must be connected to how teapot > handles concurrency in companion with the compiled statements? > > > > > > > > > > > > Any clues on this one? We are on Pharo 8.0 with latest version > of P3, PG 9.x) > > > > > > > > > > > > Best regards > > > > > > > > > > > > Petter Egesund (I wrote the heysql-package based on P3) > > > > > > > > > > > > > > > >

Feb. 10, 2020

Re: [Pharo-users] [Pharo-dev] [Ann] Concurrent Programming in Pharo is available

by ducasse

You are welcome. This is really one of the few resources on the topic and at this level in the ecosystem. In the future I plan to learn and explain monitors because they are better than semaphores or mutexes but I have the learn about them before :). > On 10 Feb 2020, at 20:46, Todd Blanchard <tblanchard(a)mac.com> wrote: > > > From: Todd Blanchard <tblanchard(a)mac.com> > Subject: Re: [Pharo-dev] [Ann] Concurrent Programming in Pharo is available > Date: 10 February 2020 at 20:46:46 CET > To: Pharo Development List <pharo-dev(a)lists.pharo.org> > Cc: Any question about pharo is welcome <pharo-users(a)lists.pharo.org> > > > I have wanted a clean explanation of semaphores and process scheduling for a long time. > > Thanks for this. > > >> On Feb 9, 2020, at 3:58 AM, StÃ©phane Ducasse <stephane.ducasse(a)inria.fr <mailto:stephane.ducasse@inria.fr>> wrote: >> >> On http://books.pharo.org/booklet-ConcurrentProgramming/ <http://books.pharo.org/booklet-ConcurrentProgramming/> >> >> >> S. >> >> -------------------------------------------- >> StÃ©phane Ducasse >> http://stephane.ducasse.free.fr <http://stephane.ducasse.free.fr/> / http://www.pharo.org <http://www.pharo.org/> >> 03 59 35 87 52 >> Assistant: Julie Jonas >> FAX 03 59 57 78 50 >> TEL 03 59 35 86 16 >> S. Ducasse - Inria >> 40, avenue Halley, >> Parc Scientifique de la Haute Borne, BÃ¢t.A, Park Plaza >> Villeneuve d'Ascq 59650 >> France >> > > >

Feb. 10, 2020

Re: [Pharo-dev] [Ann] Concurrent Programming in Pharo is available

by Todd Blanchard

I have wanted a clean explanation of semaphores and process scheduling for a long time. Thanks for this. > On Feb 9, 2020, at 3:58 AM, StÃ©phane Ducasse <stephane.ducasse(a)inria.fr> wrote: > > On http://books.pharo.org/booklet-ConcurrentProgramming/ <http://books.pharo.org/booklet-ConcurrentProgramming/> > > > S. > > -------------------------------------------- > StÃ©phane Ducasse > http://stephane.ducasse.free.fr <http://stephane.ducasse.free.fr/> / http://www.pharo.org <http://www.pharo.org/> > 03 59 35 87 52 > Assistant: Julie Jonas > FAX 03 59 57 78 50 > TEL 03 59 35 86 16 > S. Ducasse - Inria > 40, avenue Halley, > Parc Scientifique de la Haute Borne, BÃ¢t.A, Park Plaza > Villeneuve d'Ascq 59650 > France >

Feb. 10, 2020

Re: [Pharo-users] P3 and concurrenty

by Sven Van Caekenberghe

Hi Petter, > On 10 Feb 2020, at 16:46, Petter Egesund <petter.egesund(a)gmail.com> wrote: > > Yes, I see - that sounds sensible. > > Could it be an idea to subclass the pool-class and to prepare statements in this method, if there is a kind of init-method in the class? What do you think? That would have indeed been one approach but for this situation I opted for a configurator block. P3ConnectionPool>>#configurator: oneArgumentBlock "Set oneArgumentBlock to be my configurator. This is code that will be executed once on each newly created connection (P3Client). The default configuration asserts #isWorking on the argument" The default is configurator := [ :p3Client | self assert: p3Client isWorking ] which basically does an extra validation and ensures the opening of the connection. Now, a little piece of functionality was still missing, I added it with the following commit: https://github.com/svenvc/P3/commit/e3f161d9f9ae34b3b020ad924b3c0f116f68c0b0 Your usage scenario should now be like this: you create a P3ConnectionPool with the proper URL, then you set your configurator to add your prepared statements (you could delegate this to a helper class if you have a lot of them). pool configurator: [ :p3Client | p3Client prepare: 'SELECT ...' named: 'select1'. p3Client prepare: 'INSET ...' named: 'insert1' ] Later, when using a connection from the pool, you refer to your prepared statements by name. pool withConnection: [ :p3Client | (p3Client preparedStatementNamed: 'select1') execute: { args } ] Sven PS: have a look at P3PreparedStatementTest>>#testNamedPreparedStatement for an operation example (minus the connection pooling). > On Mon, Feb 10, 2020 at 4:36 PM Sven Van Caekenberghe <sven(a)stfx.eu> wrote: > > > > On 10 Feb 2020, at 16:30, Petter Egesund <petter.egesund(a)gmail.com> wrote: > > > > Hi and thanks for an answer above my expectations to our problem. > > > > This is really awesome, I was just about to start coding a pool myself, but getting it directly from the author of the base library is off course way better :) > > > > I will try out and get back as soon as we have tested. > > > > One quick question - are the prepared statements which are connected to the pool thread safe? > > Prepared statements are scoped to a single connection in PSQL, AFAIK. > > What you will probably want to do is use a #configurator: block to set up your prepared statements once, like you do now. Then each thread/process grabs its own connection from the pool, (which will be already configured properly) and uses it, for itself, and finally returns it to the pool. > > Interacting with the pool should be fully thread safe. Once you are using a connection, it not longer is, but is also does not have to, since one connection equals one client thread. > > > Petter > > > > On Mon, Feb 10, 2020 at 12:13 PM Sven Van Caekenberghe <sven(a)stfx.eu> wrote: > > Hi Petter, > > > > https://github.com/svenvc/P3/commit/a6b409d0d92cb92bf9b44452908bb9033523b863 adds a connection pool. > > > > Here is the class comment: > > > > ====== > > > > I am P3ConnectionPool. > > > > I offer a pool of shared PSQL connections (P3Client instances) as a resource. > > > > After configuring me with at least a url, you use me by calling #withConnection: > > > > pool := P3ConnectionPool url: 'psql://sven@localhost'. > > pool withConnection: [ :p3Client | > > p3Client query: 'SELECT table_name FROM information_schema.tables' ]. > > pool close. > > > > When a connection is in use, it is not part of the pool. > > When a connection is returned/released to the pool, it becomes available for reuse. > > > > The pool's size is the number of open connection ready to be reused. > > The pool's capacity is the maximum number of connection that will be pooled. > > Excess connections will be closed when they are returned/released to the pool. > > > > New connections are created as needed. > > You can set a #configurator to further initialize new connections. > > You can use #warmUp or #warmUp: to precreate a number of connections. > > > > When an error occurs, the connection should not be reused and be closed by the caller. > > > > ====== > > > > There are some unit tests as well. > > > > Let me know if this works for you. > > > > Sven > > > > > On 9 Feb 2020, at 19:04, Petter Egesund <petter.egesund(a)gmail.com> wrote: > > > > > > Yes, thanks for good feedback. > > > > > > I will try the pooled way, I think - not primarily because of speed, but due to that our library is built around prepared connections. > > > > > > Petter > > > > > > On Sun, Feb 9, 2020 at 6:01 PM Sven Van Caekenberghe <sven(a)stfx.eu> wrote: > > > Hi Petter, > > > > > > > On 9 Feb 2020, at 17:27, Petter Egesund <petter.egesund(a)gmail.com> wrote: > > > > > > > > Hi Sven and thanks for answering! > > > > > > > > I use Teapot with one common sql-connecton, not one for each user session. At startup I create several sql statements and these does not seem to be usable from different Teapot request at the same time. > > > > > > It is useable, but not concurrently. > > > > > > > I could create one connection pr. session and then close the database connection when the user leaves, but then I also would need to create all the prepared sql-statements for each session, which does not sound right to me? > > > > > > Yes and no, see further. > > > > > > > It seems I have will have to look into the other solution, and see if I can use some mutex stuff to avoid several tasks acessessing the same resources at the same time. > > > > > > Yes, follow the pointer that I gave you, it is not hard. > > > > > > > Creating a pool sounds like the right solution to me now - any meaning about this? > > > > > > Yes, you could create a connection pool. But that is harder than it sounds: what is the minimum size, the maximum size, what do you do when you go over it, how do you make sure that a resource (connection) is clean when returning it to the pool (given authentication, possible errors), ... > > > > > > I don't know what you are doing, but I think you focus too much on performance issues. I would first try to get the code correct and worry about performance later on. > > > > > > Running > > > > > > P3ClientTest new runBenchmark1Bench. > > > > > > on my machine gives me a BenchmarkResult(67 iterations in 5 seconds 57 milliseconds. 13.249 per second). This is a query that returns 10.000 records with 5 columns. It is reusing the same client/connection for all iterations. > > > > > > If I modify this slightly to use a new client/connection each time, like this > > > > > > [ > > > (P3Client url: 'psql://sven@localhost') > > > query: 'SELECT * FROM benchmark1'; > > > close > > > ] benchFor: 5 seconds > > > > > > I get a BenchmarkResult(65 iterations in 5 seconds 34 milliseconds. 12.912 per second) which almost as fast. Of course, for smaller queries, the connect/disconnect overhead will be more significant. > > > > > > And note that this is not using prepared statements. > > > > > > So I would start by opening/closing a connection each time you need it. > > > > > > HTH, > > > > > > Sven > > > > > > > Petter > > > > > > > > > > > > On Sun, Feb 9, 2020 at 4:52 PM Sven Van Caekenberghe <sven(a)stfx.eu> wrote: > > > > Hi Petter, > > > > > > > > [ CC-ing the Pharo Users list ] > > > > > > > > P3Client is not built/designed to be used by multiple processes concurrently. Each database connection is represented by an instance of P3Client and holds some state both at the client as well as at the server side. > > > > > > > > Typically, in a multi user server application, each connection should have its own P3Client / psql connection. For example, in Seaside, a custom WASession subclass gives each session/user its own p3 connection/client. > > > > > > > > Is that what you are doing ? > > > > > > > > If not, you could wrap your db accessing code so that mutual exclusion is provided. For example, you can have a look at AbstractCache #beThreadSafe and #critical: > > > > > > > > That will then serialise requests and possibly block one onto the other. > > > > > > > > HTH, > > > > > > > > Sven > > > > > > > > PS: another thing to take care of if closing your sql connections when the session is no longer needed. > > > > > > > > PS: Zinc HTTP does also provide a session mechanism (ZnServerSession[Manager]) but these work with cookies and typically won't help with a REST access pattern. > > > > > > > > > On 9 Feb 2020, at 14:21, Petter Egesund <petter.egesund(a)gmail.com> wrote: > > > > > > > > > > Hi Sven > > > > > > > > > > We are using Pharo as our backend in a project and we have run into a problem with P3. > > > > > > > > > > The problem seems to be connected to compiled sql statements and concurrency. > > > > > > > > > > We keep getting this error: Bindcomplete message expected > > > > > > > > > > Problem seems to be easy to reproduce: > > > > > > > > > > 1) Compile any sql statement > > > > > 2) Use this statement in a query twice (!) in a teapot endpoint > > > > > > > > > > The run some concurrent queries, like "curl http://localhost:8080/endpoint & curl http://localhost:8080/endpoint.." (add several curls after here). > > > > > > > > > > One could also use ex. siege (https://manpages.ubuntu.com/manpages/trusty/man1/siege.1.html) for easy reproducing. > > > > > > > > > > If we chain the curls after each other, like "curl http://localhost:8080/endpoint && https://manpages.ubuntu.com/manpages/trusty/man1/siege.1.html && https://manpages.ubuntu.com/manpages/trusty/man1/siege.1.html.." it seems to work fine, so doing the request sequentially seem to work fine. > > > > > > > > > > My conclusion is that this must be connected to how teapot handles concurrency in companion with the compiled statements? > > > > > > > > > > Any clues on this one? We are on Pharo 8.0 with latest version of P3, PG 9.x) > > > > > > > > > > Best regards > > > > > > > > > > Petter Egesund (I wrote the heysql-package based on P3) > > > > > > > > > >

Feb. 10, 2020

Re: [Pharo-users] Dictionary removeKey: very low performance

by Kasper Ãsterbye

Hi RIchard, On 9 February 2020 at 17.54.30, Richard O'Keefe (raoknz(a)gmail.com) wrote: My library uses separate chaining (https://en.wikipedia.org/wiki/Hash_table#Separate_chaining) which makes deletion simple and fast and allows 'null' keys. Does your implementation use a fixed number of buckets, or do they grow (and shrink)? I had been wondering about doing an open addressing with items marked as deleted to see how it would perform. Aka "Another technique for removal is simply to mark the slot as deleted. However this eventually requires rebuilding the table simply to remove deleted records.â from https://en.wikipedia.org/wiki/Open_addressing <https://en.wikipedia.org/wiki/Open_addressing>. Best, Kasper

Feb. 10, 2020

Re: [Pharo-users] About "it's not pharo but smalltalk"

by Sean P. DeNigris

TedVanGaalen wrote > I am only suggesting that Pharo should be downward compatible > (that is, within Pharo's scope only). I agree that this is a worthy ideal, but IMHO is not realistic with the current resources (time and staff). All that additional code would have to be maintained. In the worst case, entire subsystems would have to remain in the image - we changed to Calypso browser but Nautilus would still have to lurk around in case someone hooked in. For limited cases, there could be a compatibility layer, but again, we are struggling to implement our top priority dreams, and don't have the luxury of extra resources available for worthy but lower-priority things like this. Of course anyone - or group - for which this is a higher priority, could do just that. It would be like Grease, but for Ph2Ph I guess... ----- Cheers, Sean -- Sent from: http://forum.world.st/Pharo-Smalltalk-Users-f1310670.html

Feb. 10, 2020

Re: [Pharo-users] About "it's not pharo but smalltalk"

by Sean P. DeNigris

Trygve Reenskaug wrote > I have recently completed a conceptual model with tools for a new way of > programming for novices. Hi trygve, what is the project and how can I install it? I try to follow all your work... Trygve Reenskaug wrote > the port will probably be outdated > and useless by the time it's finished. I wouldn't say that's true in my experience updating projects from Pharo 1.x -> 8.0.1 as each version came out. While it can be difficult to do a big jump, porting from one to the next has usually been fairly straightforward. So if you did port it (and maybe I can help), it shouldn't be too outdated/hard-to-maintain. That said, a Morphic replacement has been on the burner for quite a while and I assume there's a heavy graphical component, so that would have to be redone. However, this is a problem *everyone* will face together so I am sure there will be plenty of support... ----- Cheers, Sean -- Sent from: http://forum.world.st/Pharo-Smalltalk-Users-f1310670.html

Feb. 10, 2020

Re: [Pharo-users] The expanding Pharo ecosystem

by Serge Stinckwich

And don't forget to star your favorite Pharo projects! On Mon, Feb 10, 2020 at 5:58 PM Sean P. DeNigris <sean(a)clipperadams.com> wrote: > Sven Van Caekenberghe-2 wrote > > Topic tags automatically organise some projects: > > > > https://github.com/topics/pharo > > > > https://github.com/topics/pharo-smalltalk > > Yes, please everyone remember to use these tags for your Pharo projects. > Like this we get a poor-man's catalog for free ;) > > > > ----- > Cheers, > Sean > -- > Sent from: http://forum.world.st/Pharo-Smalltalk-Users-f1310670.html > > -- Serge Stinckwic âhâ Int. Research Unit on Modelling/Simulation of Complex Systems (UMMISCO) âSorbonne University (SU) French National Research Institute for Sustainable Development (IRD)â U âniversity of YaoundÃ© Iâ, Cameroon "Programs must be written for people to read, and only incidentally for machines to execute." https://twitter.com/SergeStinckwich â

Feb. 10, 2020

Re: [Pharo-users] The expanding Pharo ecosystem

by Sean P. DeNigris

Sven Van Caekenberghe-2 wrote > Topic tags automatically organise some projects: > > https://github.com/topics/pharo > > https://github.com/topics/pharo-smalltalk Yes, please everyone remember to use these tags for your Pharo projects. Like this we get a poor-man's catalog for free ;) ----- Cheers, Sean -- Sent from: http://forum.world.st/Pharo-Smalltalk-Users-f1310670.html

Feb. 10, 2020