Data-modifying CTEs

Before you start

Connect with psql shop. Every step runs inside BEGIN; … ROLLBACK;, so the data stays unchanged.

1. See what each part can see

Raise the keyboard’s price by 5% in a CTE, and read the price back in the same statement:

BEGIN;
WITH raised AS (
  UPDATE products SET price = price * 1.05 WHERE id = 1 RETURNING id, price
)
SELECT p.id, p.price AS price_seen_by_main_query, r.price AS new_price
FROM products p JOIN raised r ON r.id = p.id;

 id | price_seen_by_main_query | new_price
----+--------------------------+-----------
  1 |                  4500.00 |   4725.00

The main query reading products still sees 4,500, even though the update has happened. Every part of the statement works from the same snapshot of the data, taken before the statement started. The only way to see what the update did is its RETURNING output, here r.price. Run SELECT price FROM products WHERE id = 1; as a separate statement and you get 4,725. Then ROLLBACK;.

2. Move rows in one statement

The shop wants cancelled orders moved to archive tables. Done as separate statements, a failure halfway could leave an order in neither table, or in both. One statement either does all of it or none of it:

BEGIN;
CREATE TABLE orders_archive      (LIKE orders);
CREATE TABLE order_items_archive (LIKE order_items);

WITH gone_items AS (
  DELETE FROM order_items
  WHERE order_id IN (SELECT id FROM orders WHERE status = 'cancelled')
  RETURNING *
), gone_orders AS (
  DELETE FROM orders WHERE status = 'cancelled' RETURNING *
), saved_items AS (
  INSERT INTO order_items_archive SELECT * FROM gone_items
)
INSERT INTO orders_archive SELECT * FROM gone_orders RETURNING id, status;

 id  |  status
-----+-----------
 104 | cancelled

Check the counts before rolling back:

SELECT (SELECT count(*) FROM orders) AS orders,
       (SELECT count(*) FROM orders_archive) AS archived_orders,
       (SELECT count(*) FROM order_items_archive) AS archived_items;
ROLLBACK;

Notice that the main query never reads saved_items, yet the items were archived. A data-modifying CTE always runs to completion, whether or not anything uses its output.

3. Break it

Delete the cancelled orders but forget their items:

BEGIN;
WITH gone_orders AS (DELETE FROM orders WHERE status = 'cancelled' RETURNING *)
SELECT count(*) FROM gone_orders;
ROLLBACK;

ERROR:  update or delete on table "orders" violates foreign key constraint "order_items_order_id_fkey" on table "order_items"
DETAIL:  Key (id)=(104) is still referenced from table "order_items".

The foreign key protects the data even inside a clever statement. The whole statement fails, and nothing is deleted.

Check yourself

1. Inside one statement, a CTE updates a price and the main query selects from the same table. Which price does the main query see, and why?

   Show answer

   The old price. All parts of the statement use the same snapshot, taken before the statement ran. The new value is only visible through the CTE’s RETURNING.

   I could explain this without looking

2. Why archive with one statement rather than separate DELETE and INSERT statements?

   Show answer

   One statement succeeds or fails as a whole, so an order cannot end up deleted but not archived, or archived twice.

   I could explain this without looking

3. A data-modifying CTE’s output is never used by the main query. Does it still run?

   Show answer

   Yes. Data-modifying statements in WITH always run exactly once and to completion, whether or not their output is read.

   I could explain this without looking