Hi Amit,
Thanks for your reply.
> This happens because parallel background workers uses database
> connection (BGWORKER_BACKEND_DATABASE_CONNECTION).
Yes, that's what I suspected, though I didn't expect to have to delve into the pages about the C-API to find it, and I still
can't see anything explicitly mentioning that it counts towards a user's connection limit.
> Query can proceed with lesser workers only when we launch lesser
> workers to process it. As this happens after launching the workers,
> it is treated as any other normal error and will abort the query
> execution. We might avoid this error by checking users 'connection
> limit' before launching the workers, but not sure if it is worth.
...
> No, if there is any error while starting workers, the query will abort.
Doesn't this severely limit the usability of parallel queries?
Essentially, any perfectly well-formed query can now fail, and
clients must be prepared to retry every single transaction as this error cannot be followed by anything but a rollback, otherwise:
"ERROR: current transaction is aborted, commands ignored until end of transaction block"
So the _user_ is penalised because of the way the _server_ chose to execute the query, which seems wrong to me.
It isn't enough for clients to set their own max_parallel_workers_per_gather value (to anything except zero) after
first discovering how many allowed connections they have remaining, since there is a race condition - between setting
it and executing the potentially parallel query, another connection or background worker could be needed by the same
user. Or the cluster-wide connection limit could be hit.
Am I missing something? Is there a recommended way of managing max_connections, max_worker_processes, max_parallel_workers_per_gather, and individual workers' connection limits?
(We also use pgBouncer to multiplex connections, which complicates things further.)
OTOH, if parallel queries could be made tolerant of workers failing to start then there wouldn't be as much of an issue (though doubtless this is easier said than done!)
Regards,
Steve.
This email is confidential. If you are not the intended recipient, please advise us immediately and delete this message. The registered name of Cantab- part of GAM Systematic is Cantab Capital Partners LLP. See - http://www.gam.com/en/Legal/Email+disclosures+EU for further information on confidentiality, the risks of non-secure electronic communication, and certain disclosures which we are required to make in accordance with applicable legislation and regulations. If you cannot access this link, please notify us by reply message and we will send the contents to you.