Hi Stefan,
thanks - but I don't understand how could the BEGIN; UPDATE xxx; be
committed to database without explicit COMMIT and how could psql report
"UPDATE 153" after message "The connection was reset". This puzzles me...
Kuba
Dne 21.5.2010 17:33, Stefan Kaltenbrunner napsal(a):
> On 05/21/2010 11:19 AM, Jakub Ouhrabka wrote:
>> Hi,
>>
>> can anyone tell me how this could happen, please?
>>
>> database=# begin; update table set col = 100;
>> server closed the connection unexpectedly
>> This probably means the server terminated abnormally
>> before or while processing the request.
>> The connection to the server was lost. Attempting reset: Succeeded.
>> UPDATE 153
>> database=# ROLLBACK ;
>> WARNING: there is no transaction in progress
>> ROLLBACK
>>
>> The update was commited to database. This was psql 8.4 connectig to 8.2
>> server through pgbouncer 1.3.
>>
>> It's not reproducible for me :-(
>>
>> Any ideas?
>
> 1. you connect to pgbouncer using psql
> 2: you execute the query and something (firewall whatever) drops the
> connection between psql and pgbouncer while the one between pgbouncer
> and the backend stays alive
> 3. psql notices the lost connection and reconnects and you end up on
> another backend session (or the same one that was just RESET ALL; by
> pgbouncer after the UPDATE completed)
> 4. the ROLLBACK; does nothing because the pooled connection you are now
> connected is either a different one or got reset after the connection
> dropped.
>
>
> Stefan