Обсуждение: pg_transaction_status() unreliable?!
I'm having some ugly fun here. It doesn't seem as if pg_transaction_status() is reliable in the least. For example, I'm doing the equivalent of the following: pg_send_query_params($conn, 'BEGIN', array()); pg_get_result($conn); ... some other queries, each using pg_send_query_params() and pg_get_result() ... pg_send_query_params($conn, 'COMMIT', array()); pg_get_result($conn); echo pg_transaction_status($conn); Now, amazingly enough, pg_transaction_status() returns PGSQL_TRANSACTION_ACTIVE after the commit. This makes no sense to me at all ... it should return PGSQL_TRANSACTION_IDLE, since the connection is no longer in a transaction. Is anyone else seeing this? I have assertions failing all over the place because my code thinks that transactions have been left uncommitted. Postgresql 8.3.5, FreeBSD 6.3, PHP 5.2.9 with Suhosin patch -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
wmoran@potentialtech.com wrote: > > I'm having some ugly fun here. It doesn't seem as if > pg_transaction_status() is reliable in the least. > > For example, I'm doing the equivalent of the following: > > pg_send_query_params($conn, 'BEGIN', array()); > pg_get_result($conn); > ... some other queries, each using pg_send_query_params() and > pg_get_result() ... > pg_send_query_params($conn, 'COMMIT', array()); > pg_get_result($conn); > echo pg_transaction_status($conn); > > Now, amazingly enough, pg_transaction_status() returns > PGSQL_TRANSACTION_ACTIVE after the commit. This makes no sense to > me at all ... it should return PGSQL_TRANSACTION_IDLE, since the > connection is no longer in a transaction. > > Is anyone else seeing this? I have assertions failing all over the > place because my code thinks that transactions have been left > uncommitted. One needs to loop on pg_get_result() until it returns False. Are you doing this? I think perhaps you are not. You can sometimes sort of get away with a single call, but it isn't a good idea to rely on it. I duplicated your result by doing the COMMIT but only doing pg_get_result once; then pg_transaction_status gave me the odd PGSQL_TRANSACTION_ACTIVE. But if I loop on pg_get_result (it returns a result handle, then false the next time), I get the expected IDLE status.
In response to ljb <ljb1813@pobox.com>: > wmoran@potentialtech.com wrote: > > > > I'm having some ugly fun here. It doesn't seem as if > > pg_transaction_status() is reliable in the least. > > > > For example, I'm doing the equivalent of the following: > > > > pg_send_query_params($conn, 'BEGIN', array()); > > pg_get_result($conn); > > ... some other queries, each using pg_send_query_params() and > > pg_get_result() ... > > pg_send_query_params($conn, 'COMMIT', array()); > > pg_get_result($conn); > > echo pg_transaction_status($conn); > > > > Now, amazingly enough, pg_transaction_status() returns > > PGSQL_TRANSACTION_ACTIVE after the commit. This makes no sense to > > me at all ... it should return PGSQL_TRANSACTION_IDLE, since the > > connection is no longer in a transaction. > > > > Is anyone else seeing this? I have assertions failing all over the > > place because my code thinks that transactions have been left > > uncommitted. > > One needs to loop on pg_get_result() until it returns False. Are you doing > this? No. There's no documented reason that I can see to do so ;) > I think perhaps you are not. You can sometimes sort of get away > with a single call, but it isn't a good idea to rely on it. Doesn't make much sense to me. I know there's only a single query, and thus I know there's only a single result. > I duplicated your result by doing the COMMIT but only doing > pg_get_result once; then pg_transaction_status gave me the odd > PGSQL_TRANSACTION_ACTIVE. But if I loop on pg_get_result (it returns a > result handle, then false the next time), I get the expected IDLE status. It appears as if this has fixed the problem. It's random enough that I can't be sure yet, though. In any event, I owe you a beer at some point. Thanks! -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
In response to Kenneth Marshall <ktm@rice.edu>: > On Mon, May 04, 2009 at 03:34:21PM -0400, Bill Moran wrote: > > In response to ljb <ljb1813@pobox.com>: > > > > > wmoran@potentialtech.com wrote: > > > > > > > > I'm having some ugly fun here. It doesn't seem as if > > > > pg_transaction_status() is reliable in the least. > > > > > > > > For example, I'm doing the equivalent of the following: > > > > > > > > pg_send_query_params($conn, 'BEGIN', array()); > > > > pg_get_result($conn); > > > > ... some other queries, each using pg_send_query_params() and > > > > pg_get_result() ... > > > > pg_send_query_params($conn, 'COMMIT', array()); > > > > pg_get_result($conn); > > > > echo pg_transaction_status($conn); > > > > > > > > Now, amazingly enough, pg_transaction_status() returns > > > > PGSQL_TRANSACTION_ACTIVE after the commit. This makes no sense to > > > > me at all ... it should return PGSQL_TRANSACTION_IDLE, since the > > > > connection is no longer in a transaction. > > > > > > > > Is anyone else seeing this? I have assertions failing all over the > > > > place because my code thinks that transactions have been left > > > > uncommitted. > > > > > > One needs to loop on pg_get_result() until it returns False. Are you doing > > > this? > > > > No. There's no documented reason that I can see to do so ;) > > > Here is the statement in that manual to which he is referring: > > PQgetResult > > Waits for the next result from a prior PQsendQuery, PQsendQueryParams, > PQsendPrepare, or PQsendQueryPrepared call, and returns it. A null > pointer is returned when the command is complete and there will be no > more results. > > PGresult *PQgetResult(PGconn *conn); > > > PQgetResult must be called repeatedly until it returns a null pointer, > indicating that the command is done. (If called when no command is > active, PQgetResult will just return a null pointer at once.) Each > non-null result from PQgetResult should be processed using the same > PGresult accessor functions previously described. Don't forget to > free each result object with PQclear when done with it. Note that > PQgetResult > > ... Not that it says anything about PQtransactionStatus being affected by the use of it. If I read this literally, PQgetResult could return a result set that is incomplete, which begs the question: how do I merge the remaining part of the result set when I get it back next time? Of course, that's not the intended usage, which leads me to wonder what's going on when I know factually that I have no more query results pending, yet I have to call it again (apparently) to update the client's internal status data structures. Oh well, I guess that's just the quirks of the client library. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
wmoran@potentialtech.com wrote: >... > > Not that it says anything about PQtransactionStatus being affected by > the use of it. If I read this literally, PQgetResult could return a > result set that is incomplete, which begs the question: how do I merge > the remaining part of the result set when I get it back next time? > > Of course, that's not the intended usage, which leads me to wonder > what's going on when I know factually that I have no more query > results pending, yet I have to call it again (apparently) to update > the client's internal status data structures. Actually pg_transaction_status doesn't talk to the server at all. The information it needs is available at the client side. What is happening is that you are calling pg_transaction_status before you and the server have finished discussing the COMMIT (meaning, you haven't called pg_get_result enough to finish the protocol). So the client side hasn't been told that the transaction is over.
In response to ljb <ljb1813@pobox.com>: > wmoran@potentialtech.com wrote: > >... > > > > Not that it says anything about PQtransactionStatus being affected by > > the use of it. If I read this literally, PQgetResult could return a > > result set that is incomplete, which begs the question: how do I merge > > the remaining part of the result set when I get it back next time? > > > > Of course, that's not the intended usage, which leads me to wonder > > what's going on when I know factually that I have no more query > > results pending, yet I have to call it again (apparently) to update > > the client's internal status data structures. > > Actually pg_transaction_status doesn't talk to the server at all. The > information it needs is available at the client side. What is happening is > that you are calling pg_transaction_status before you and the server have > finished discussing the COMMIT (meaning, you haven't called pg_get_result > enough to finish the protocol). So the client side hasn't been told that the > transaction is over. So you're saying that pg_get_result() returns the "result" of the query, even though the query isn't complete yet. Then I have to call it again to update the client-side information on the status of the connection. As I said, if it's what needs to be done, then fine. But it still doesn't seem logical to me. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/