3 ms·
> the multi-table pipeline write pattern WITH new_order AS ( INSERT INTO order (po_number, bill_to, ship_to) VALUES ('ABCD
by ttfkam 2y ago
> the multi-table pipeline write pattern
WITH new_order AS (
INSERT INTO order (po_number, bill_to, ship_to)
VALUES ('ABCD1234', 42, 64)
RETURNING order_id
)
INSERT INTO order_item (order_id, product_id, quantity)
SELECT new_order.order_id, vals.product_id, vals.quantity
FROM (VALUES (10, 1), (11, 5), (12, 3)) AS vals(product_id, quantity)
CROSS JOIN new_order
;
Not super pretty, but it illustrates the point. A single statement that creates an order, gets its autogenerated id (bigint, uuid, whatever), and applies that id to the order items that follow. No network round trip necessary to get the order id before you add the items, which translates into a shorter duration for the transaction to remain open.
- evanelias 2y agoThanks, that makes sense. In this specific situation, the most common MySQL/MariaDB pattern would be to use LAST_INSERT_ID() in the second INSERT, assuming the order IDs are auto-increments. Or with UUIDs, simply generating the ID prior to the first INSERT, either on the application side or in a database-side session variable. To avoid extra network calls, this could be wrapped in a stored proc, although a fair complaint is that MySQL doesn't support a ton of different programming langauges for procs/funcs like Postgres.