Re: php with postgres

Hello Bruce,

Wednesday, July 23, 2003, 6:18:13 AM, you wrote:

BM> Marcus B?rger wrote:
>> >> However it may be very usefull to terminate any open transaction before
>> >> reusing a persisten connection. Typically this happens when the same script
>> >> runs again. But anyway using transactions together with persistent conenctions
>> >> in a multithreaded environment isn't the best thing you could do. So our
>> >> options are
>> >> 1) tell the users to do 'auto commit mode'
>> >> 2) nested transactions
>> >> 3) locking
>> >>
>> >> >From my perspective 2) and 3) are bad ideas for the web environment. In other
>> >> words i guess we should leave it as is with transaction rollback only when the
>> >> client terminates (e.g. the webserver stops).
>>
>> 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;"

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

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?

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

--
Best regards,
Marcus mailto:marcus(dot)boerger(at)post(dot)rwth-aachen(dot)de