Postgresql: ПОДГОТОВИТЬ ТРАНЗАКЦИЮ

Да, это возможно, но вам это действительно нужно?

Подумайте дважды, прежде чем решить, что это действительно должны быть две отдельные базы данных.

Вы можете просто оставить оба соединения открытыми и откатить первую команду, если вторая не удалась.

Если вам действительно нужны подготовленные транзакции, продолжайте читать.

Что касается вашей схемы - я бы использовал генераторы последовательностей и предложение RETURNING на стороне базы данных, просто для удобства.

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,
  ...
);

Теперь вам понадобится внешний клей — координатор распределенных транзакций (?) — чтобы все заработало правильно.

Хитрость заключается в использовании PREPARE TRANSACTION вместо COMMIT. Затем после успешного завершения обеих транзакций используйте COMMIT PREPARED.

Доказательство концепции PHP приведено ниже.

ВНИМАНИЕ! в этом коде отсутствует критическая часть — контроль ошибок. Любая ошибка в $db2 должна быть обнаружена, а ROLLBACK PREPARED должна быть выполнена в $db1. Если вы не поймаете ошибки, вы оставите $db1 с замороженными транзакциями, что очень, очень плохо.

<?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'" );
}
?>

И еще раз - подумайте, прежде чем будете его использовать. То, что предлагает Эрвин, может быть более разумным.

Да, и еще одно замечание... Чтобы использовать эту функцию PostgreSQL, вам нужно установить max_prepared_transactions в ненулевое значение.

Если вы можете получить доступ к db2 из db1, вы можете оптимизировать процесс и фактически сохранить все это внутри транзакции. Используйте dblink или SQL MED для этого.

Если вы откатываете транзакцию на локальном сервере, то, что было сделано через dblink на удаленном сервере, не будет отброшено. (Это один из способов сделать изменения постоянными, даже если транзакция откатывается.)

Но вы можете выполнить код на удаленном сервере, который откатывается в случае неудачи, и выполнять его только в том случае, если операция в локальной базе данных была первой успешной. Если удаленная операция не удалась, вы также можете выполнить откат локально.

Кроме того, используйте предложение RETURNING в INSERT, чтобы вернуть идентификатор из серийная колонка.

С PDO будет проще...

Основное преимущество PDO заключается в захвате ошибок (по строке ошибок PHP или возвращаемых сообщениях об ошибках SQL) каждого отдельного SQL-статуса в транзакции. См. pdo.begintransaction, pdo.commit, pdo.rollback и pdo.error-handling.

Пример:

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