Re: php with postgres - Mailing list pgsql-hackers

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;"

I believe this should be BEGIN;ROLLBACK;RESET ALL; because our default
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;"

Should be "ROLLBACK;RESET ALL;".

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?

I am confused why you are doing stuff on connect and disconnect.  Seems
it should all be done on disconnect so you don't leave open transactions
in the pooled connections --- it will keep locks around too long and
reduce the usefulness of vacuum.  If we clean up everything on
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?

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

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073