Обсуждение: active queries
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
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!
"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!