Smalltalk › Pharo › Pharo Smalltalk Users

Postgres P3 Driver in seaside/web apps - strategy for "prepare:"

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

3 messages Options

Sanjay Minni

Postgres P3 Driver in seaside/web apps - strategy for "prepare:"

Hi

using the P3 Postgres driver

what is the optimised way of using the following in a seaside/web
application
statement := client prepare: ...
statement execute:
statement close.

Sven readme on the drivers page
"prepared statements ... need to be closed, prepared statement exist ...
single session / connection ..."

Typically in a desktop app I would fire the "prepare" statements(s) once
when open the particular UI / window (say typically 2 or 3 statements in a
UI) and then "close" when I exit

How does this work in a multi-tabbed browser app
lets say I open a tab with a particular UI and fire the "prepare" statement

now what if the tab idles for too long - and then i press send/save. The
program would have just fired the "execute ..." assuming the prepare is
active. would the prepare / session have been automatically closed ?

- when should i typically fire the prepare statement
- how should i test if the session / prepare is still active or needs to be
refired
- how are orphan prepare statements disposed off ny the database / program

thanks for pointers
Sanjay

-----
cheers,
Sanjay
--
Sent from: http://forum.world.st/Pharo-Smalltalk-Users-f1310670.html

cheers,
Sanjay

Sven Van Caekenberghe-2

Re: Postgres P3 Driver in seaside/web apps - strategy for "prepare:"

Hi Sanjay,

> On 19 Apr 2021, at 18:14, Sanjay Minni <[hidden email]> wrote:
>
> Hi
>
> using the P3 Postgres driver
>
> what is the optimised way of using the following in a seaside/web
> application
> statement := client prepare: ...
> statement execute:
> statement close.
>
> Sven readme on the drivers page
> "prepared statements ... need to be closed, prepared statement exist ...
> single session / connection ..."

First make sure that you give each session in your (seaside) web application its own connection/client to the database. P3Client is meant to be used single threaded, it is your responsibility to protect this. Make sure these are initialised and disposed of properly - use logging.

Second, I would not put time in trying to gain performance with prepared statements until you can prove that it makes a real, measurable difference. PostgreSQL is very fast. But this is IMHO.

See further.

> Typically in a desktop app I would fire the "prepare" statements(s) once
> when open the particular UI / window (say typically 2 or 3 statements in a
> UI) and then "close" when I exit
>
> How does this work in a multi-tabbed browser app
> lets say I open a tab with a particular UI and fire the "prepare" statement
>
> now what if the tab idles for too long - and then i press send/save. The
> program would have just fired the "execute ..." assuming the prepare is
> active. would the prepare / session have been automatically closed ?
>
> - when should i typically fire the prepare statement

Either upfront when you connect, or each time when you need them (see further). The scope of a prepared statement is the connection/session.

Preparing 10s of statements upfront that you might not need could increase connection time.

> - how should i test if the session / prepare is still active or needs to be
> refired

There is P3Client>>#preparedStatementNamed:
You best use P3Client>>#prepare:named: then.

> - how are orphan prepare statements disposed off ny the database / program

They get thrown out when the connection closes.

HTH,

Sven

PS:

There is also P3ConnectionPool that can protect against concurrent access, prepare connections, warm up, on top of its base functionality of pooling, of course. But a challenge with connection pooling is what to do when errors occur.

> thanks for pointers
> Sanjay
>
>
>
> -----
> cheers,
> Sanjay
> --
> Sent from: http://forum.world.st/Pharo-Smalltalk-Users-f1310670.html

Esteban A. Maringolo

Re: Postgres P3 Driver in seaside/web apps - strategy for "prepare:"

Not completely related, but also not unrelated. (?)

You can pool your connections in the server side by means of pg_bouncer [1] to scale tenfold (or more) the connectivity to your PostgreSQL server.

So you can program with P3Client and then when you need to scale the connectivity, simply do it on the server side.

Of course if you have 1000s of P3Clients on a single image, then it might take a big toll on the memory use, so it also depends on how you balance connections.

Best regards,

[1] https://www.pgbouncer.org/config.html

Esteban A. Maringolo

On Mon, Apr 19, 2021 at 2:52 PM Sven Van Caekenberghe <[hidden email]> wrote:

Hi Sanjay,

> On 19 Apr 2021, at 18:14, Sanjay Minni <[hidden email]> wrote:
>
> Hi
>
> using the P3 Postgres driver
>
> what is the optimised way of using the following in a seaside/web
> application
> statement := client prepare: ...
> statement execute:
> statement close.
>
> Sven readme on the drivers page
> "prepared statements ... need to be closed, prepared statement exist ...
> single session / connection ..."

First make sure that you give each session in your (seaside) web application its own connection/client to the database. P3Client is meant to be used single threaded, it is your responsibility to protect this. Make sure these are initialised and disposed of properly - use logging.

Second, I would not put time in trying to gain performance with prepared statements until you can prove that it makes a real, measurable difference. PostgreSQL is very fast. But this is IMHO.

See further.

> Typically in a desktop app I would fire the "prepare" statements(s) once
> when open the particular UI / window (say typically 2 or 3 statements in a
> UI) and then "close" when I exit
>
> How does this work in a multi-tabbed browser app
> lets say I open a tab with a particular UI and fire the "prepare" statement
>
> now what if the tab idles for too long - and then i press send/save. The
> program would have just fired the "execute ..." assuming the prepare is
> active. would the prepare / session have been automatically closed ?
>
> - when should i typically fire the prepare statement

Either upfront when you connect, or each time when you need them (see further). The scope of a prepared statement is the connection/session.

Preparing 10s of statements upfront that you might not need could increase connection time.

> - how should i test if the session / prepare is still active or needs to be
> refired

There is P3Client>>#preparedStatementNamed:
You best use P3Client>>#prepare:named: then.

> - how are orphan prepare statements disposed off ny the database / program

They get thrown out when the connection closes.

HTH,

Sven

PS:

There is also P3ConnectionPool that can protect against concurrent access, prepare connections, warm up, on top of its base functionality of pooling, of course. But a challenge with connection pooling is what to do when errors occur.

> thanks for pointers
> Sanjay
>
>
>
> -----
> cheers,
> Sanjay
> --
> Sent from: http://forum.world.st/Pharo-Smalltalk-Users-f1310670.html