For a project using Postgres, we need to set some variables using SET command.
To do so, we are using a custom query step (1 for each SET query). Followed by another custom/regular query, which utilizes that variable.
As per my understanding, one request to a SA, should open just 1 connection to the DB, and use that for the complete life of that SA.
But in this case, I frequently get errors saying that that variable value is not set, when executing the DB step after the SET custom query.
Trying to debug this, when setting pool size to 1 in the DB connection, this issue does not show up.
But setting it to just 2 start throwing the same error again.
Is my understaind with DB connection session/pool incorrect here? And what should be the best way to approach this configuration in Wappler to avoid losing the SET variable's value??
I don't know how Knex or the postgres driver handles connection pools internally. Having multiple connections within a pool allows to have multiple parallel queries. When you do a query within Server Connect it will be assigned one of the free connections from the pool, this doesn't have to be the same as the previous query. The connections are not per action but globally.
The SET command is normally only for within a Transactions, Stored Procedure or Function. The variable is available at local or session level. A session has a lifetime of a single connection. When you use variables within a session you have a problem when you reuse that connection like Server Connect does, two simultaneous running client requests could hijack the variables from the other or overwrite them. Local variables are only available with a transaction and are more safe to use.
Since you can't use transactions since you have logic separated over 2 different actions I would suggest instead of setting the variables on the database server use normal server connect variables and pass those as parameters to the queries that require them. Is there a reason why you require the postgres variables instead of just passing them as parameters in the action?
Thank you for the explanation.
I had always assumed that since the Globals were introduced, DB connection would be created on per SA request, and not for the lifespan of the application itself.
This is part of a Postgres Row-Level-Security implementation attempt, which requires these variables to be set before any query is run. So that there is no need to configure these variable on the query level separately.
The temporary solution of setting pool size as 1 is probably not recommended... As it will easily choke the application when there are multiple concurrent users.
Passing the variables to the SA would defeat the ease of configuration via Global steps.
If I understand correctly, using this via transaction is actually the only way though, since otherwise the variables would be overwritten and they could have incorrect state?
@patrick Tried converting the setup to transaction. Separate transaction block in globals, and separate in SA.
Duplicated the variable-setting queries to achieve this.
But, its still failing the very first time.
Everytime I open the page first time, the first SC request fails. Consistently. In the globals steps.
All globals step executions in subsequent SC requests go through correctly.
What would you need to identify the underlying issue? The app itself is in local, so no URL to share.
Can share ngrok when you are online - although I doubt it would help.
How did you implement the transaction, will the transaction include all the queries from the global steps and the called action steps? Check with the postgres documentation that you set the variables correctly to the local transaction scope so that concurrent connections don't have access to the variable.