Postgresql: PREPARE TRANSACTION

Question

I've two DB servers db1 and db2.

db1 has a table called tbl_album
db2 has a table called tbl_user_album

CREATE TABLE tbl_album
(
id    PRIMARY KEY,
name  varchar(128)
...
);

CREATE TABLE tbl_user_album
(
id          PRIMARY KEY,
album_id    bigint
...
);

Now if a user wants to create an album what my php code needs to do is:

Create a record in db1 and save its id(primary key)
Create a record in db2 using it saved in first statement

Is it possible to keep these two statements in a transaction? I'm ok with a php solution too. I mean I'm fine if there is a solution that needs php code to retain db handles and commit or rollback on those handles.

Any help is much appreciated.

Look at: https://wiki.postgresql.org/wiki/Atomic_Commit_of_Distributed_Transactions — marciowb, Nov 08 '19 at 14:28

filiprem · Accepted Answer · 2012-01-22T15:13:57.113

Yes it is possible, but do you really need it?

Think twice before you decide this really must be two separate databases.

You could just keep both connections open and ROLLBACK the first command if the second one fails.

If you'd really need prepared transactions, continue reading.

Regarding your schema - I would use sequence generators and RETURNING clause on database side, just for convenience.

CREATE TABLE tbl_album (
  id    serial PRIMARY KEY,
  name  varchar(128) UNIQUE,
  ...
);
CREATE TABLE tbl_user_album (
  id          serial PRIMARY KEY,
  album_id    bigint NOT NULL,
  ...
);

Now you will need some external glue - distributed transaction coordinator (?) - to make this work properly.

The trick is to use PREPARE TRANSACTION instead of COMMIT. Then after both transactions succeed, use COMMIT PREPARED.

PHP proof-of-concept is below.

WARNING! this code is missing the critical part - that is error control. Any error in $db2 should be caught and ROLLBACK PREPARED should be executed on $db1 If you don't catch errors you will leave $db1 with frozen transactions which is really, really bad.

<?php
$db1 = pg_connect( "dbname=db1" );
$db2 = pg_connect( "dbname=db2" );
$transid = uniqid();

pg_query( $db1, 'BEGIN' );
$result = pg_query( $db1, "INSERT INTO tbl_album(name) VALUES('Absolutely Free') RETURNING id" );
$row = pg_fetch_row($result);
$albumid = $row[0];
pg_query( $db1, "PREPARE TRANSACTION '$transid'" );
if ( pg_query( $db2, "INSERT INTO tbl_user_album(album_id) VALUES($albumid)" ) ) {
    pg_query( $db1, "COMMIT PREPARED '$transid'" );
}
else {
    pg_query( $db1, "ROLLBACK PREPARED '$transid'" );
}
?>

And again - think before you will use it. What Erwin proposes might be more sensible.

Oh and just one more note... To use this PostgreSQL feature, you need to set max_prepared_transactions config variable to nonzero value.

Do we really need a PREPARED TRANSACTION here. Can `pg_query( $db1, 'ROLLBACK' );` be called when $db2 query fails — Mayank, Jan 22 '12 at 06:16
@Mayank: Yeah I'm asking same question myself. This is slightly overcomplicated. PREPARE TRANSACTION makes sense with many clients in concurrent processing in highly distributed environment (because it's only way to guarantee that trasaction can be either commited/rolledback, only way except from keeping it still open). — filiprem, Jan 22 '12 at 14:58
i guess you need there is missing a transaction block to the db2... see https://stackoverflow.com/a/59058207/903998 — Victor, Nov 26 '19 at 20:30

Erwin Brandstetter · Answer 2 · 2012-01-21T22:01:52.847

3

If you can access db2 from within db1, then you could optimize the process and actually keep it all inside a transaction. Use dblink or SQL MED for that.

If you roll back a transaction on the local server, what has been done via dblink on a remote server will not be rolled back. (That is one way to make changes persistent even if a transaction is rolled back.)

But you can execute code on the remote server that rolls back if not successful, and only execute it, if the operation in the local db has been successful first. If the remote operation fails you can roll back locally, too.

Also, use the RETURNING clause of INSERT to return id from a serial column.

edited Jan 21 '12 at 22:01

answered Jan 21 '12 at 17:13

Erwin Brandstetter

605,456
145
1,078
1,228

Thanks for the response. Is it possible to rollback a command that is sent by dblink – Mayank Jan 21 '12 at 18:12
@Mayank: I added a bit about dblink to my answer. – Erwin Brandstetter Jan 21 '12 at 22:02
You can do it, using: https://wiki.postgresql.org/wiki/Atomic_Commit_of_Distributed_Transactions – marciowb Nov 08 '19 at 14:32

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

It will be easier with PDO...

The main advantage of PDO is to capture errors (by PHP error line or returning SQL error messages) of each single SQL statment in the transaction. See pdo.begintransaction, pdo.commit, pdo.rollback and pdo.error-handling.

Example:

$dbh->beginTransaction();
/* Do SQL */
$sth1 = $dbh->exec("CREATE TABLE tbl_album (..)");
$sth2 = $dbh->exec("CREATE TABLE tbl_user_album(..)");
/* Commit the changes */
$dbh->commit();

Postgresql: PREPARE TRANSACTION

3 Answers3

It will be easier with PDO...

Linked