Re: rollback doubt and connection to remoteDB

On Thu, Aug 25, 2011 at 9:35 AM, uanacho <uanacho(at)gmail(dot)com> wrote:
> hi, some days ago I have a problem I want to solve.
>
> The situation is the following:
>
> I have a connection to a remote DB (with it's different from mine, of
> course).
> The querys I do are between sql-transactions

Wait, are you running your statements between a begin; commit; pair,
or between two transactions, ala begin; yada; commit; yourstatements
here; begin; more yada; commit; ?

> 1 - Whate happens If during executing a query my pc loose the network
> connection? what happens with the rollback?
>
> Due to this time my pc that begun the query doesn't have the possibility to
> invoke the rollback to the remote DB and cannot "clean" that DB

Any aborted transaction will be rolled back. This will go one of two
ways. Either the client will issue a disconnect to the db, and the db
will immediately roll back all changes. Or the client will simply
lose the network socket without the reset, and the connection will
time out when the tcp keepalive timeout occurs, which defaults to a
shade over 2 hours on most unix installations. At this point the
database will also roll back all your changes. Until then whatever
locks are held by the transaction etc will be in place, and vacuum
will not be able to vacuum the affected objects.

The tcp keepalive can be adjusted downwards to shorten this long wait,
and reduce its impact on the database.