Perfect, this is exactly what I needed. Thank so much!! Petter
On Mon, Feb 10, 2020 at 8:44 PM Sven Van Caekenberghe <s...@stfx.eu> wrote: > Hi Petter, > > > On 10 Feb 2020, at 16:46, Petter Egesund <petter.eges...@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 <s...@stfx.eu> > wrote: > > > > > > > On 10 Feb 2020, at 16:30, Petter Egesund <petter.eges...@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 <s...@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.eges...@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 <s...@stfx.eu> > wrote: > > > > Hi Petter, > > > > > > > > > On 9 Feb 2020, at 17:27, Petter Egesund <petter.eges...@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 <s...@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.eges...@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) > > > > > > > > > > > > > > > >
