Re: php with postgres - Mailing list pgsql-hackers

Hello Bruce,

Thursday, July 24, 2003, 11:05:21 PM, you wrote:

BM> Marcus B?rger wrote:
>> >> BM> I don't see why you wouldn't just do BEGIN;COMMIT;RESET ALL; when you
>> >> BM> pass the connection to a new client.
>> >> 
>> >> 
>> >> Bruce you said RESET ALL is available since 7.2. I am currently checking for
>> >> the lib version but it would be more correct to check something on the server.
>> >> So the question what do i check?
>> 
>> BM> We usually use SELECT version().
>> 
>> 
>> ATM i have a patch doing the following:
>> Connect:
>>  If PQprotocolVersion() is available and >= 3 PQparameterStatus() is available
>>  then i check the server version. Else i check the lib version (*).
>>  If the version to check is >= 7.2 ido one of the following:
>>  - If one of PQprotocolVersion() and PQtransactionStatus() is unavailable or
>>    protocol version < 3:
>>      "BEGIN;COMMIT;RESET ALL;"

BM> I believe this should be BEGIN;ROLLBACK;RESET ALL; because our default
BM> for a client that disconnects is to abort the transaction.

>>  - If protocol version >= 3 and transaction status == PQTRANS_IDLE:
>>      "RESET ALL;"
>>  - If protocol version >= 3 and transaction status != PQTRANS_IDLE:
>>      "COMMIT;RESET ALL;"

BM> Should be "ROLLBACK;RESET ALL;".

Because of above mentioned default?
The problem i have is that now we do the COMMIT - so this behavior change can
only go into php 5 with a big notice.

BM> Nice version test code, sounds good.

>> 
>> Disconnect:
>>  - When PQprotocolVersion() And PQtransactionStatus() are available then
>>    i check whether status is PQTRANS_IDLE. If so i do:
>>      "ROLLBACK;"
>>  - If the functions are not available in the client libs i do:
>>      "BEGIN;ROLLBACK;"
>> 
>> Does this sound the correct behavior?

BM> I am confused why you are doing stuff on connect and disconnect.  Seems
BM> it should all be done on disconnect so you don't leave open transactions
BM> in the pooled connections --- it will keep locks around too long and
BM> reduce the usefulness of vacuum.  If we clean up everything on
BM> disconnect, aren't we sure that the connection status will be fine?

>> And would "select split_part(version(), ' ', 2);" be too much of a slowdown to
>> do the version detection in the startup sequence completely correct?

BM> Seems fine.  Since you are doing pooled connections, you shouldn't be
BM> doing this too often anyway.

I believe the point was "RESET ALL;". But maybe i can move all but that into
shutdown. I mean for me that sounds good. Only someone must enlighten me if
there could be a problem with that reset.


-- 
Best regards,Marcus                            mailto:marcus.boerger@post.rwth-aachen.de