5

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.

Yaron Naveh
  • 23,560
  • 32
  • 103
  • 158
  • Are you looking for somthing like [locking](http://www.postgresql.org/docs/9.1/static/explicit-locking.html) ? – Houari Jan 29 '14 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 connections – Yaron Naveh Jan 29 '14 at 19:00
  • 5
    One query per connection. You don't have to block on the client while waiting for it though: http://www.postgresql.org/docs/current/static/libpq-async.html – Richard Huxton Jan 29 '14 at 19:41
  • thanks @RichardHuxton, you can submit an answer so I will accept – Yaron Naveh Jan 29 '14 at 20:17
  • In future, please be more specific. PostgreSQL version, driver and driver version, client language, etc. – Craig Ringer Jan 30 '14 at 00:53

1 Answers1

10

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 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 ...`? – Zeruno Feb 21 '20 at 22:57
  • 1
    @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. – Craig Ringer Feb 25 '20 at 04:37