Hi Petter, > On 10 Feb 2020, at 16:46, Petter Egesund <[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 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]> wrote: > > > > On 10 Feb 2020, at 16:30, Petter Egesund <[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]> 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 <[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]> wrote: > > > Hi Petter, > > > > > > > On 9 Feb 2020, at 17:27, Petter Egesund <[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]> > > > > 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]> > > > > > 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) > > > > > > > > > >
