Thread: issues with libpg interface
We have two situations that have arisen while using libpq thru CMUCL bindings in our UncommonSQL package. We have a multi-processing application which uses a pool of database connections to perform DB queries. The first one is that we are getting unexpected retun values from pgresultstatus after calling pgexec, a value of PGRES_TUPLES_OK. The thing is that we're not sending a SELECT, just UPDATES, INSERTS and DELETES. My understanding is that none of those return tuples. Here is the message our interface spits out. ;; Warning, database-execute-command: Recieved tuples ok in response to UPDATE WCO_ALLOCATION SET CANCELLED_TIME = '2001-01-12 16:50:10+00',DELETED_AGENT_ID = 0,PICKUP = 'f',RETURN_AGENT_ID = 7104,NON_EXPIRING = 'f',INSTRUCTIONS = '',BUILDING_PASSES= 'NIL',ACCOMPANYING_PEOPLE = 'NIL',PICKUP_PEOPLE = 'NIL',COMMENTS = '',CREATED_TIME = '2001-01-12 16:50:50+00',REPEAT_RULE= 'NIL',END_TIME = '2001-01-16 17:48:00+00',START_TIME = '2001-01-12 16:50:50+00',AGENT_ID = 6761,CUSTOMER_ID= 3970,LOCATION_ID = 4,ALLOCATION_TYPE = 'COMPLETED-CHECKOUT',ALLOCATION_ID = 34166 WHERE WCO_ALLOCATION.ALLOCATION_ID= 34166 So, is it possible for an UPDATE to correctly return PGRES_TUPLES_OK, or are we seeing some confusion in the backend or libpq due to load and multi-processing? Also, unrelated, I think, to the above issue, we are getting the following message intermittently: Backend message type 0x50 arrived while idle I cannot find any more information about this message in the documentation, so I don't know what type of message that is. -- Craig Brozefsky <craig@red-bean.com> In the rich man's house there is nowhere to spit but in his face -- Diogenes
Craig Brozefsky <craig@red-bean.com> writes: > We have two situations that have arisen while using libpq thru CMUCL > bindings in our UncommonSQL package. We have a multi-processing > application which uses a pool of database connections to perform DB > queries. > The first one is that we are getting unexpected retun values from > pgresultstatus after calling pgexec, a value of PGRES_TUPLES_OK. The > thing is that we're not sending a SELECT, just UPDATES, INSERTS and > DELETES. My understanding is that none of those return tuples. They don't. I think you must have some kind of multithreading bug on the client side (whether it's in libpq or your code is hard to say). A quick look at libpq's source shows that it can only generate PGRES_TUPLES_OK after seeing a 'T' (row descriptor) message from the backend, and the backend will surely not send such a thing except for SELECT or FETCH. > Also, unrelated, I think, to the above issue, we are getting the > following message intermittently: > Backend message type 0x50 arrived while idle That would be a 'P' (CursorResponse) message, which is a normal (if not very useful) part of the query cycle. Something's definitely out of sync in the frontend/backend protocol, it seems. You might find it helpful to read http://www.postgresql.org/devel-corner/docs/postgres/protocol.htm regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > > The first one is that we are getting unexpected retun values from > > pgresultstatus after calling pgexec, a value of PGRES_TUPLES_OK. The > > thing is that we're not sending a SELECT, just UPDATES, INSERTS and > > DELETES. My understanding is that none of those return tuples. > > They don't. I think you must have some kind of multithreading bug > on the client side (whether it's in libpq or your code is hard to say). > A quick look at libpq's source shows that it can only generate > PGRES_TUPLES_OK after seeing a 'T' (row descriptor) message from the > backend, and the backend will surely not send such a thing except for > SELECT or FETCH. This is what we expected, but I wasn't familiar enough with the details of the backend protocol to know how significant these symptoms were. We've put locks on each database connection, so there should be only one query running on a connection at a time. We've been running it a few hours on a loaded application without any issues, so I think it's cleared up, but I'm not prepared to give it a clean slate for another day or so. On that note, what do I need to do to get UncommonSQL listed in the postgres interfaces page on the website? UncommonSQL, available at http://alpha.onshore.com/lisp-software, is both a functional and object-oriented interface to relational databases for Common Lisp. It includes support for PostgreSQL, as well as Oracle. It presently works in CMUCL, but should be portable to other runtimes. -- Craig Brozefsky <craig@red-bean.com> In the rich man's house there is nowhere to spit but in his face -- Diogenes