Re: [INTERFACES] Proper use of Transactions...

Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il> writes:
> I don't think there is damage to the database if a
> transaction is not committed or rolled back before the connection
> terminates - but a guru opinion would have more value than mine.

There is an implicit abort of any transaction-in-progress just before
backend shutdown. The backend's shutdown processing is just about the
same whether it receives an exit command or observes EOF on the client
socket. So a client crash or ungraceful disconnect is no problem: you
will get rolled back.

If you did not issue a BEGIN command, but are simply sending standalone
SQL queries that each implicitly form a transaction, then a disconnect
in the middle of processing a query may or may not cause that query to
be aborted rather than committed. Typically the backend won't notice
your disconnect until it comes back to read another command, so the
current query would get committed (if no error) first. I think there
might be paths where the disconnect would be noticed earlier, however.

Even if the backend itself crashes, an uncommitted transaction will
remain uncommitted, since no entry saying it's committed ever gets
written to pg_log. Any tuples the dead backend did manage to write out
will look like not-yet-committed tuples until the end of time.

BTW, I am not sure whether VACUUM ever realizes it can reclaim those
uncommitted tuples. To do that it'd have to make some kind of
guesstimate that the transaction never is going to be committed.
So you could have some space wastage in the database from a backend
crash.

regards, tom lane