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