PHP and PostgreSQL Transactions?

Question

A long time ago I wrote a php class that handles postgresql db connections.
I've added transactions to my insert/update functions and it works just fine for me. But recently I found out about the "pg_prepare" function.
I'm a bit confused about what that function does and if it'll be better to switch to it.

Currently whenever I do an insert/update my sql looks like this:

$transactionSql = "PREPARE TRANSACTION ".md5(time()).";"
                  .$theUpdateOrDeleteSQL.";".
                  ."COMMIT;";

This will return something like:
PREPARE TRANSACTION '4601a2e4b4aa2632167d3cc62b516e6d';
INSERT INTO users (username,g_id,email,password)
            VALUES('test',  '1', 'test','1234');
COMMIT;

I've structured my database with relations and I'm using (when it's possible):

 ON DELETE CASCADE
 ON UPDATE CASCADE

But I want to be 100% sure things are clean in the database and there are no leftovers if/when there is a failure upon updating/deleting or inserting.

It would be nice if you can share your opinion/experience about pg_prepare, do I really need the "prepare transaction" and any other addition things that might help me? :)

Answer 1

No, you don't need 2 phase commit !...

For safe PHP database handling, do not use pg_query directly, rather wrap it in a special function which does the following :

• opens the database connection on your first query
• if using persistent connections, ensure the connection is in a known state
• register_shutdown_function to a function that issues a ROLLBACK
• make sure autocommit is off, or simply issue a BEGIN before the first query
• log database error and slow queries
• only uses pg_query_params() which takes care of sql injections nicely

That way, if your script crashes or whatever, a rollback is issued automatically. You can only commit by explicitly comitting.

If you use persistent connections beware : php's handling of pg_pconnect is a little ... buggy.

Answer 2

No you don't need prepare transaction (that is intended for distributed transactions across different servers - as Milen has already pointed out.

I'm not sure how the PHP interface handles that, but as long as you can make sure you are not running in auto commit mode, things should be fine.

If you can't control the auto commit mode, simply put your statements into a BEGIN ... COMMIT block