Thread: Proper use of Transactions...

Greetings,

If I write a transaction like this:

begin transaction;
.
.
.
commit transaction;

and an error occurs somewhere in the middle, do I have to issue a rollback
command or will PostgreSQL do that automatically on an error?  The problem
I think I am having is that if some command between the begin and commit
statements causes an error, my program terminates, so I do not have an
opportunity to issue a rollback.

All this is compounded because I am using PHP3 as an Apache module to
access PostgreSQL and I am receiving the following error in my Apache log:

NOTICE:  (transaction aborted): queries ignored until END

I'm not sure where to start looking for the problem.  Any insight would be
greatly appreciated.

Thank you,
Matthew

At 23:38 +0300 on 08/08/1999, Matthew Hagerty wrote:


> All this is compounded because I am using PHP3 as an Apache module to
> access PostgreSQL and I am receiving the following error in my Apache log:
>
> NOTICE:  (transaction aborted): queries ignored until END
>
> I'm not sure where to start looking for the problem.  Any insight would be
> greatly appreciated.

This is just normal. It indicates that since an error has occured, the rest
of the commands in the transaction, if they are issued, will be ignored -
untill you say either "commit" or "rollback". It gives you the effect of
not doing anything more in a transaction if an error occured in the middle
of it.

If errors which are not database errors occur in the middle (for example,
you run a validity test on the user's data and it fails), you should catch
those errors and issue a rollback yourself.

This has nothing to do with the type of interface you are using (in your
case PHP3), unless that interface does not allow you to make validity tests
or to capture errors. 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.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

Herouth Maoz <herouth@oumail.openu.ac.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

Tom Lane wrote:
> 
> 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

It does.

Vadim