PostgreSQL Transaction in one Trip

Question

In my very unsophisticated PostgreSQL v8.4.13 database with tables A and B, column A.a, is a SERIAL PRIMARY KEY that is FOREIGN KEYed to B.a. There are other columns in both tables. a is not UNIQUE in B.

In the confines of one transaction I would like to add one row to A and a corresponding row to B.

I'm scripting with the PHP PDO for PGL.

Is it possible to do such a thing in one round trip?

Is it possible to do similarly with multiple rows to B in one round trip?

Absolutely, you have to opt-out of auto commit w/ [manual mode](http://php.net/manual/en/pdo.begintransaction.php). — Rafael, Oct 10 '17 at 03:13
When you say "one round trip", do you mean one exec call (a single query)? — Rafael, Oct 10 '17 at 03:29
I mean one message to the DB from the script with one return value of whatever. I don't know enough about the connection to know how to state it any better than this. — QED, Oct 10 '17 at 03:30
Just separate the queries with a `;` in the sql string argument. — Rafael, Oct 10 '17 at 03:40

Nick Barnes · Accepted Answer · 2017-10-12T16:14:42.140

1

You can use an INSERT ... RETURNING statement to retrieve the newly-generated SERIAL value for A.a, and you can use a CTE to pack both INSERTs into the same statement:

WITH Inserted AS (
  INSERT INTO A (data)
  VALUES ('data')
  RETURNING A.a
)
INSERT INTO B (a, data)
SELECT Inserted.a, 'more data'
FROM Inserted;

If you want to insert multiple records into B, you can fill out the remaining fields in a VALUES expression, and join in the new A.a value for the INSERT:

WITH Inserted (a) AS (
  INSERT INTO A (data)
  VALUES ('data')
  RETURNING A.a
),
BValues (data) AS (
  VALUES
    ('data1'),
    ('data2'),
    ('data3')
)
INSERT INTO B (a, data)
SELECT Inserted.a, BValues.data
FROM Inserted CROSS JOIN BValues;

edited Oct 12 '17 at 16:14

answered Oct 11 '17 at 01:24

Nick Barnes

19,816
3
51
63

Thanks! Now I'm curious to know if this is executed as a transaction without my explicitly doing so. – QED Oct 11 '17 at 21:45
1

@QED: If you haven't explicitly started a transaction, Postgres will begin and commit one for each statement (and a CTE counts as a single statement, despite the multiple sub-expressions). Many client libraries will override this behaviour by starting their own transactions behind the scenes, though from what I can tell, PDO's default behaviour seems to be in line with Postgres'. – Nick Barnes Oct 11 '17 at 23:28
Is it possible to do insert multiple rows in `B` with one round trip? – QED Oct 12 '17 at 15:09

score -1 · Answer 2 · edited Oct 22 '17 at 13:19

-1

As long as the updates are to the same DB, you can have multiple executions per transaction:

$dbh->beginTransaction();
$dbh->exec("UPDATE A SET last = 'hi'");
$dbh->exec("UPDATE B SET name = 'Rafael'");
/* MULTIPLE EXECS HERE */
$dbh->commit();

edited Oct 22 '17 at 13:19

QED

9,803
7
50
87

answered Oct 10 '17 at 03:15

Rafael

7,605
13
31
46

Why am I dropping table `A`? – QED Oct 10 '17 at 03:29
That is an example of the statements you would run, to have both in *one* transaction. Did I misunderstand your question? – Rafael Oct 10 '17 at 03:30
My answer did not steer you wrong, [look here](https://stackoverflow.com/questions/23105138/tips-on-sending-multiple-query-with-postgresql). [using multiple transactions in one function call is not recommended.](http://php.net/manual/en/function.pg-query.php), I am not sure why you downvoted me. – Rafael Oct 10 '17 at 03:33
I didn't downvote you, I think someone else did that because he knew the data in `A` is very important to me. – QED Oct 10 '17 at 03:35
The `DROP` statement was an example of multiple statements in one transaction. Surely, you wouldn't copy and paste this code and run it blindly... – Rafael Oct 10 '17 at 03:38
https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd – Rafael Oct 10 '17 at 03:39

PostgreSQL Transaction in one Trip

2 Answers2