Thread: active queries

active queries

From
Miklosi Attila
Date:
Hi!

Our company has a long last problem by using libpq in multi-threaded
programs. The libpq usually closes the programs without any error
message or rarely giving the 'Invalid frontend message type 87' error.
When asked you about this error message you gave the reply below. In the
reply there is only one thing which is not clear... What does an
'active' query mean? Or how can we detect active queries on a PGconn?
We have tried PQcancel() and PQreset() functions too, but they didn't
help solving the problem.
Our programs retrieve every result (PQgetResult() and PQclear()
functions) that is pending on the server before reusing a PGconn.

Thank you in advance
Attila Miklosi

P.S.: this is a copy of your reply to 'invalid frontend message type 87'
error message

>"Invalid frontend message" means the server got a message with an
>unexpected first byte, which usually means the server and libpq got
>out of sync about where the message boundaries are.  It's been quite
>a long time since we've seen an actual bug of that sort, though.
>The cases that I've heard of recently involve multiple threads in an
>application trying to use the same PGconn without any interlocking.
>You can't have multiple queries active on a single connection, but
>libpq itself doesn't contain any locking to prevent multiple threads
>from trying to use the PGconn at once.
>
>If you're getting this with a single-threaded client, please submit
>a test case to pgsql-bugs.
>
>                       regards, tom lane






Re: active queries

From
Alban Hertroys
Date:
On May 27, 2008, at 3:08 PM, Miklosi Attila wrote:

> Hi!
>
> Our company has a long last problem by using libpq in multi-threaded
> programs. The libpq usually closes the programs without any error
> message or rarely giving the 'Invalid frontend message type 87' error.
> When asked you about this error message you gave the reply below.
> In the
> reply there is only one thing which is not clear... What does an
> 'active' query mean? Or how can we detect active queries on a PGconn?

THE 'active' query (not AN) is the query that's currently being
executed on a connection. There can be multiple connections with an
active query each. You can't execute multiple queries in parallel on
the same connection. You can execute them in sequence of course.

What people are trying to tell you is that you apparently have
multiple threads that perform queries simultaneously on the same
connection, and that's not possible.

You either need to put locks on the threads to prevent queries from
happening simultaneously, or you should use a separate connection for
each thread that executes queries in parallel.

To summarise, you need to serialise your queries on the connection or
you need to parallelise your connections. Or a mix of both.

> We have tried PQcancel() and PQreset() functions too, but they didn't
> help solving the problem.
> Our programs retrieve every result (PQgetResult() and PQclear()
> functions) that is pending on the server before reusing a PGconn.
>
> Thank you in advance
> Attila Miklosi
>
> P.S.: this is a copy of your reply to 'invalid frontend message
> type 87'
> error message
>
>> "Invalid frontend message" means the server got a message with an
>> unexpected first byte, which usually means the server and libpq got
>> out of sync about where the message boundaries are.  It's been quite
>> a long time since we've seen an actual bug of that sort, though.
>> The cases that I've heard of recently involve multiple threads in an
>> application trying to use the same PGconn without any interlocking.
>> You can't have multiple queries active on a single connection, but
>> libpq itself doesn't contain any locking to prevent multiple threads
>> from trying to use the PGconn at once.
>>
>> If you're getting this with a single-threaded client, please submit
>> a test case to pgsql-bugs.
>>
>>                       regards, tom lane
>
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,483c430d927661540552177!



Re: active queries

From
Gregory Stark
Date:
"Alban Hertroys" <dalroi@solfertje.student.utwente.nl> writes:

> THE 'active' query (not AN) is the query that's currently being  executed on a
> connection. There can be multiple connections with an  active query each. You
> can't execute multiple queries in parallel on  the same connection. You can
> execute them in sequence of course.
>
> What people are trying to tell you is that you apparently have  multiple
> threads that perform queries simultaneously on the same  connection, and that's
> not possible.

FWIW it's not just queries that are a problem. You pretty much can't call
*any* libpq function on a connection while another thread is calling any other
libpq call on the same connection.

It is however safe (assuming you built postgres with the thread-safe option)
to call two libpq calls from different threads on *different* connections. And
it's always safe to call two libpq calls from different threads on the same
connection as long as you ensure that one is finished before the second one
begins. In either case the normal way to guarantee either property is to put a
mutex around either each connection or all of libpq.

You can actually have more than one query simultaneously active in the same
connection using named portals. But if you called PQgetResult() on one you
have to ensure all threads wait until it returns before issuing PQgetResult()
(or any other libpq function) on the other portal.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!