javascript - Can a third query execute in the middle of two queries inside the same db.any() call?

Question

If I have the following code, executed in the middle of a request:

db.any('SET search_path TO user001; SELECT * FROM messages')

Could a second request execute a query in between the SET and the SELECT of the first query? For example:

• First request arrives
• SET search_path TO user001 (1st)
• Second request arrives
• SET search_path TO user002 (2nd)
• SELECT * FROM messages (1st)
• SELECT * FROM messages (2nd)

Also what's a good way of defining the search_path at run time? I thought of using db.task() but that would limit the number of requests per second to around 100, right?

question from:https://stackoverflow.com/questions/66064187/can-a-third-query-execute-in-the-middle-of-two-queries-inside-the-same-db-any

Answer 1

Could a second request execute a query in between the SET and the SELECT of the first query?

No, such a query is processed as one, what's known as atomic operation.

Also what's a good way of defining the search_path at run time?

Within pg-promise, initialization option schema does that. It sets the schema(s) automatically for every fresh connection.

However, if you need to keep changing the current schema for every single request, i.e. if you actually have a separate database schema for every user, then it won't work, and you will need to use that approach you tried, i.e. prepend schema-changing query to every query.

However again, there is no point in doing it for each query while handling one request, you need to do it only once, when you start handling the request. And this is why automatic tasks/transactions are best suited for that. In fact, you can use event extend to add your own custom task/transaction that would execute schema change in the beginning.

I thought of using db.task() but that would limit the number of requests per second to around 100, right?

No, where did you find such information? :) Tasks do not add any such limitation.