Re: Opinions on how to Integrate Transactions

> Hey,
>
> I'm new to using transactions. I'd like to integrate them into my PHP
> application to prevent a few issues we've been having. Such as, some
> queries in the beginning of the php script running, then an error
> occurring, preventing queries toward the bottom of the script from
> running. It's leaving me with things such as a forum topic without any
> associated forum posts. From what I've read on transactions, they
> would be the solution to my problems.

As far as I understand it, the transactions may solve your problem. Or
more precisely, symptomps of your problem. I don't say you should not
use transactions, but a proper solution would be to fix the original
problem causing the error.

Why do the statements fail? I usually see this when the user's input is
not verified properly (not all required information entered, invalid
inputs, invalid characters, etc.). If this is the case, add the
verification of inputs (and then maybe the transaction).

> So I have a couple questions about them.
>
> 1. Is there a way to list active transactions on all databases? I'd
> like a way to see if there are any "idle" transactions that are just
> hanging there. Is there a way to "kill" them from the console?

No, AFAIK it's not possible to list currently running transactions. You
can list currently running statements (see the pg_stat_activity), and
various information about the database (number of commited / rolled back
transactions etc. - see the pg_stat_database).

But I don't see why you'd be interested in a currently running
transactions, as:

(a) If the client disconnects without explicitly commiting or rolling
back a transaction, the transaction is rolled back automatically

(b) If the client is connected, and the transaction is still open, it
means a SQL statement is running (and you can see it in the
pg_stat_activity), or the client (PHP script) is performing something
else. So you can't kill it anyway as you don't know if the script will
continue or what.

> 2. I have a PHP class which communicates with the DB, which is
> included into every other page on the site. Should I just add BEGIN
> and COMMIT at the beginning and end of the class? This way, I wont
> need to edit every single page on my site to include BEGIN and COMMIT?
> Or will that type of "blind" transactions cause problems?

What do you mean by 'beginning and end of a class'? A transaction should
demarcate an atomic piece of functionality, so we it's impossible to say
where to put 'begin / commit' without a proper analysis of your code.

The simplest solution is probably to put 'begin' right after opening the
connection to the database, and 'commit' right before the disconnect.
This way you'll enclose the whole page into a single transaction, so you
won't get the 'partially created forums' and so on.

> 3. What happens when there IS an error? Do I need to run ROLLBACK
> every time? Or will it automatically reset when the connection is
> closed when the PHP script dies?

If there is an error, it's impossible to run a commit (unless you use
'rollback to' statement). More precisely - it's possible to execute
'COMMIT' but the transaction will replace it with 'ROLLBACK'. So for
example this:

CREATE TABLE Test (id integer);

BEGIN;
INSERT INTO Test VALUES (1);
INSERT INTO Test VALUES ('aaa');
COMMIT;

won't insert anything into the 'Test' table (it'll remain empty).

> Thanks for the advice.

regards
Tomas