Amazing experience! This is the power of Pharo at work. Thanks a lot Sven. S
> On 10 Feb 2020, at 21:24, Petter Egesund <[email protected]> wrote: > > Perfect, this is exactly what I needed. Thank so much!! > > Petter > > On Mon, Feb 10, 2020 at 8:44 PM Sven Van Caekenberghe <[email protected] > <mailto:[email protected]>> wrote: > Hi Petter, > > > On 10 Feb 2020, at 16:46, Petter Egesund <[email protected] > > <mailto:[email protected]>> 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 <[email protected] > > <mailto:[email protected]>> wrote: > > > > > > > On 10 Feb 2020, at 16:30, Petter Egesund <[email protected] > > > <mailto:[email protected]>> 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 <[email protected] > > > <mailto:[email protected]>> 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 <[email protected] > > > > <mailto:[email protected]>> 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 <[email protected] > > > > <mailto:[email protected]>> wrote: > > > > Hi Petter, > > > > > > > > > On 9 Feb 2020, at 17:27, Petter Egesund <[email protected] > > > > > <mailto:[email protected]>> 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 <[email protected] > > > > > <mailto:[email protected]>> 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 <[email protected] > > > > > > <mailto:[email protected]>> 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) > > > > > > > > > > > > > > >
