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? 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) > > > > > > >
