In Postgres is there a limitation of having just one executing query per connection (and other queries in the connection wait for the first to complete before they start)? I think I am seeing this in one driver so I want to be sure this is a db behavior and not a specific driver limitation.
5
-
Are you looking for somthing like locking ?Houari– Houari2014-01-29 18:58:45 +00:00Commented Jan 29, 2014 at 18:58
-
No, I just want to understand how things work... and what I gain and lose by using a single and not multiple connectionsYaron Naveh– Yaron Naveh2014-01-29 19:00:09 +00:00Commented Jan 29, 2014 at 19:00
-
5One query per connection. You don't have to block on the client while waiting for it though: postgresql.org/docs/current/static/libpq-async.htmlRichard Huxton– Richard Huxton2014-01-29 19:41:23 +00:00Commented Jan 29, 2014 at 19:41
-
thanks @RichardHuxton, you can submit an answer so I will acceptYaron Naveh– Yaron Naveh2014-01-29 20:17:50 +00:00Commented Jan 29, 2014 at 20:17
-
In future, please be more specific. PostgreSQL version, driver and driver version, client language, etc.Craig Ringer– Craig Ringer2014-01-30 00:53:41 +00:00Commented Jan 30, 2014 at 0:53
Add a comment
|
1 Answer
In Postgres is there a limitation of having just one executing query per connection
Yes. PostgreSQL doesn't let you suspend and resume transactions, nor does it support background (asynchronous) queries on the server back-end.
You can still run multiple concurrent queries, you just need one connection per concurrent query. You can use threads (one thread per connection) but it's usually better to use asynchronous query interfaces in your client library.
Without knowing what you're trying to achieve, and what what programming language (and thus what client library) you're using it's hard to offer more detailed advice.
2 Comments
Zeruno
Sorry for posting on an old question - I see you reference concurrency here, but I wonder whether one can achieve a speedup in this way or if there are some locks that would prevent that? What about when using
CREATE TABLE ... AS ...?
Craig Ringer
@Zeruno Same applies. PostgreSQL does now have parallelisation of read-only queries, but you can't have multiple different statements running concurrently in one session. You need multiple sessions.