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

Hi Sanjay, > On 19 Apr 2021, at 18:14, Sanjay Minni <sm@planage.com> 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

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 <sven@stfx.eu> wrote: > Hi Sanjay, > > > On 19 Apr 2021, at 18:14, Sanjay Minni <sm@planage.com> 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 >