critwire · Learn · Learn's data

Transactions d4

Prerequisites: Indexes and query plans (d3)

Make a load you can run again after it fails: transactions, savepoints, row versions (MVCC), locks, isolation levels and INSERT … ON CONFLICT. Everything runs in your own PostgreSQL, on the lab tables (the setup page).

Concept

A transaction is a group of statements the database treats as one change: either all of it happens or none of it does. Its four promises are called ACID:

How you work with them:

Row versions, for one record that session A updates while session B reads it:

An UPDATE writes a new row version; each session reads the version its snapshot can see

Every lab below runs in psql. Most start from the loaded tables in a transaction the tests roll back: type BEGIN; before one and ROLLBACK; after it. The worked example's and the first lab's blocks are typed exactly as shown, BEGIN and all, and end by removing what they made; a statement marked -- fails: fails on purpose, with that message. Leave ON_ERROR_STOP off for them (psql's default when you type), so psql goes on after the error as it does here.

Worked example

How do I re-run a failed load safely? A game wants a table of Tasmanian devil sightings (species 48) with the year of each, so year is NOT NULL. The load runs in two batches: the records since 2000, then the older ones. 41 of the devil's 2,000 records have no year, so the second batch fails. Run as a plain script, in autocommit:

Checked 2026-11-02 on PostgreSQL 16.15, PostGIS 3.4.2, GEOS 3.12.1, GDAL 3.8.4.

d4-autocommit · PostgreSQL with PostGIS 

CREATE TABLE sightings (
  occurrence_id bigint PRIMARY KEY,
  species_id integer NOT NULL REFERENCES species (id),
  year integer NOT NULL,
  month integer,
  country_code text REFERENCES countries (code)
);
INSERT INTO sightings
SELECT id, species_id, year, month, country_code
FROM occurrences
WHERE species_id = 48 AND year >= 2000;
-- fails: null value in column "year"
INSERT INTO sightings
SELECT id, species_id, year, month, country_code
FROM occurrences
WHERE species_id = 48 AND (year < 2000 OR year IS NULL);
-- the script, run again after the failure:
-- fails: duplicate key value violates unique constraint "sightings_pkey"
INSERT INTO sightings
SELECT id, species_id, year, month, country_code
FROM occurrences
WHERE species_id = 48 AND year >= 2000;
SELECT count(*) AS rows_left_behind FROM sightings;
DROP TABLE sightings;
-- result: d4-autocommit · 1 row · sha256 89b4148cdca5
rows_left_behind
1858

In d4.sql, with the blocks it runs after.

The first batch committed the moment it finished, so the failed load left half its rows behind, and running the script again fails on them (its CREATE TABLE would fail first: the table already exists). The second batch itself inserted nothing: one statement is atomic. The same load inside one transaction:

d4-one-transaction · PostgreSQL with PostGIS 

BEGIN;
CREATE TABLE sightings (
  occurrence_id bigint PRIMARY KEY,
  species_id integer NOT NULL REFERENCES species (id),
  year integer NOT NULL,
  month integer,
  country_code text REFERENCES countries (code)
);
INSERT INTO sightings
SELECT id, species_id, year, month, country_code
FROM occurrences
WHERE species_id = 48 AND year >= 2000;
-- fails: null value in column "year"
INSERT INTO sightings
SELECT id, species_id, year, month, country_code
FROM occurrences
WHERE species_id = 48 AND (year < 2000 OR year IS NULL);
-- fails: current transaction is aborted
SELECT count(*) FROM sightings;
ROLLBACK;
SELECT to_regclass('sightings') IS NULL AS nothing_left;
-- result: d4-one-transaction · 1 row · sha256 c7809e52559a
nothing_left
true

In d4.sql, with the blocks it runs after.

After the error even a SELECT is refused; ROLLBACK undoes the first batch and the CREATE TABLE with it. Nothing is left to clean up, so after fixing the cause you run the same file again. The fix here is a decision, not a trick: the game needs a year, so records without one stay out. Written as "replace in one transaction" and run twice, as a scheduled job would:

d4-replace · PostgreSQL with PostGIS 

BEGIN;
DROP TABLE IF EXISTS sightings;
CREATE TABLE sightings (
  occurrence_id bigint PRIMARY KEY,
  species_id integer NOT NULL REFERENCES species (id),
  year integer NOT NULL,
  month integer,
  country_code text REFERENCES countries (code)
);
INSERT INTO sightings
SELECT id, species_id, year, month, country_code
FROM occurrences
WHERE species_id = 48 AND year IS NOT NULL;
COMMIT;
-- the same file, run again:
BEGIN;
DROP TABLE IF EXISTS sightings;
CREATE TABLE sightings (
  occurrence_id bigint PRIMARY KEY,
  species_id integer NOT NULL REFERENCES species (id),
  year integer NOT NULL,
  month integer,
  country_code text REFERENCES countries (code)
);
INSERT INTO sightings
SELECT id, species_id, year, month, country_code
FROM occurrences
WHERE species_id = 48 AND year IS NOT NULL;
COMMIT;
SELECT count(*) AS sightings, count(DISTINCT xmin::text) AS written_by_transactions FROM sightings;
DROP TABLE sightings;
-- result: d4-replace · 1 row · sha256 de77387b65ec
sightings | written_by_transactions
1959 | 1

In d4.sql, with the blocks it runs after.

Run once or twice, the table holds the same rows, all written by one transaction (xmin, below). The last line removes the table, so the lab leaves your database as it found it.

Lab

  1. A savepoint keeps the good part of a transaction. Mark the point before the second batch, roll back to it when the batch fails, and load the older records that do have a year:

    d4-savepoint · PostgreSQL with PostGIS 

    BEGIN;
CREATE TABLE sightings (
  occurrence_id bigint PRIMARY KEY,
  species_id integer NOT NULL REFERENCES species (id),
  year integer NOT NULL,
  month integer,
  country_code text REFERENCES countries (code)
);
INSERT INTO sightings
SELECT id, species_id, year, month, country_code
FROM occurrences
WHERE species_id = 48 AND year >= 2000;
SAVEPOINT before_batch_2;
-- fails: null value in column "year"
INSERT INTO sightings
SELECT id, species_id, year, month, country_code
FROM occurrences
WHERE species_id = 48 AND (year < 2000 OR year IS NULL);
ROLLBACK TO SAVEPOINT before_batch_2;
INSERT INTO sightings
SELECT id, species_id, year, month, country_code
FROM occurrences
WHERE species_id = 48 AND year < 2000;
SELECT count(*) FILTER (WHERE year >= 2000) AS since_2000,
       count(*) FILTER (WHERE year < 2000) AS before_2000
FROM sightings;
ROLLBACK;
    -- result: d4-savepoint · 1 row · sha256 5bc17d8ef8b5
since_2000 | before_2000
1858 | 101

    In d4.sql, with the blocks it runs after.

  2. Row versions. Every row the lab file loaded carries the id of the transaction that wrote it, in xmin. Across the sixteen tables there is one:

    d4-xmin-load · PostgreSQL with PostGIS 

    SELECT count(DISTINCT xmin::text) AS transactions, count(*) AS row_versions
FROM (
  SELECT xmin FROM taxa
  UNION ALL SELECT xmin FROM species
  UNION ALL SELECT xmin FROM sources
  UNION ALL SELECT xmin FROM species_sources
  UNION ALL SELECT xmin FROM countries
  UNION ALL SELECT xmin FROM species_countries
  UNION ALL SELECT xmin FROM datasets
  UNION ALL SELECT xmin FROM occurrences
  UNION ALL SELECT xmin FROM country_shapes
  UNION ALL SELECT xmin FROM admin1_shapes
  UNION ALL SELECT xmin FROM biomes
  UNION ALL SELECT xmin FROM ecoregions
  UNION ALL SELECT xmin FROM habitat_classes
  UNION ALL SELECT xmin FROM country_habitats
  UNION ALL SELECT xmin FROM occurrence_habitats
  UNION ALL SELECT xmin FROM habitat_clips
) AS loaded;
    -- result: d4-xmin-load · 1 row · sha256 aa19f0eaf2f6
transactions | row_versions
1 | 90072

    In d4.sql, with the blocks it runs after.

    That is the loader's atomicity in the data itself: the lab arrived in one transaction, so no reader could ever see part of it. The number itself differs on every machine and every load, which is why the lab compares it rather than printing it.

  3. An UPDATE writes a new version. Give one of the devil's records from 2005 a month, then compare it with its neighbour:

    d4-xmin-update · PostgreSQL with PostGIS 

    UPDATE occurrences SET month = 7 WHERE id = 6004132305;
SELECT id, month, xmin::text = pg_current_xact_id()::xid::text AS written_by_this_transaction
FROM occurrences
WHERE id IN (6004132305, 6004132353)
ORDER BY id;
    -- result: d4-xmin-update · 2 rows · sha256 12ad209f5ad8
id | month | written_by_this_transaction
6004132305 | 7 | true
6004132353 | NULL | false

    In d4.sql, with the blocks it runs after.

    The updated row is a new version, written by your open transaction; the old version is still in the table, marked deleted, and every other session goes on reading it until you commit. ROLLBACK makes the new version the dead one instead. Either way VACUUM removes the dead version later.

  4. The locks a transaction holds, from pg_locks. Update a row of occurrences, then add a column to species:

    d4-locks · PostgreSQL with PostGIS 

    UPDATE occurrences SET month = 7 WHERE id = 6004132305;
ALTER TABLE species ADD COLUMN note text;
SELECT relation::regclass::text AS table_name, mode
FROM pg_locks
WHERE pid = pg_backend_pid()
  AND locktype = 'relation'
  AND relation IN ('occurrences'::regclass, 'species'::regclass)
ORDER BY table_name, mode;
    -- result: d4-locks · 2 rows · sha256 527e596492f7
table_name | mode
occurrences | RowExclusiveLock
species | AccessExclusiveLock

    In d4.sql, with the blocks it runs after.

    RowExclusiveLock lets everyone else read occurrences and write its other rows. AccessExclusiveLock stops every session from even reading species until this transaction ends: keep transactions that change a table's structure short.

  5. Plain readers don't wait; writers of the same row do. This needs two psql windows, A and B, each after pg-session; type the lines in the order shown. -- waits: marks the statement that waits, and -- fails: (lab 6) the one that fails on purpose. The comments give each statement's outcome; the result lists what each SELECT returned, by window and statement number. critwire's tests run it the same way, in two sessions, and check that the marked statement waits and no other does.

    d4-wait · PostgreSQL with PostGIS 

    -- window A
BEGIN;
UPDATE occurrences SET month = 7 WHERE id = 6004132305;
-- window B
BEGIN;
SELECT month FROM occurrences WHERE id = 6004132305;     -- NULL, at once: B reads the old version
-- waits: A holds the row's lock
UPDATE occurrences SET month = 8 WHERE id = 6004132305;
-- window A
ROLLBACK;                                                -- now B's UPDATE finishes: UPDATE 1
-- window B
SELECT month FROM occurrences WHERE id = 6004132305;     -- 8
ROLLBACK;
    -- result: d4-wait · 2 rows · sha256 ddbfcdb9a1de
window | statement | month
B | 4 | NULL
B | 7 | 8

    In d4.sql, with the blocks it runs after.

  6. Isolation levels, with the same two windows. B changes the row and commits (autocommit) while A's transaction is open:

    d4-isolation · PostgreSQL with PostGIS 

    -- window A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT month FROM occurrences WHERE id = 6004132305;     -- NULL
-- window B
UPDATE occurrences SET month = 1 WHERE id = 6004132305;  -- committed at once
-- window A
SELECT month FROM occurrences WHERE id = 6004132305;     -- still NULL: one snapshot for the whole transaction
-- fails: could not serialize access due to concurrent update
UPDATE occurrences SET month = 2 WHERE id = 6004132305;
ROLLBACK;
-- window B: undo the change
UPDATE occurrences SET month = NULL WHERE id = 6004132305;
    -- result: d4-isolation · 2 rows · sha256 ba58c317969a
window | statement | month
A | 2 | NULL
A | 4 | NULL

    In d4.sql, with the blocks it runs after.

    Had B read with SELECT month … FOR UPDATE in lab 5, it would have waited for A just as its UPDATE did; had A run lab 4's ALTER TABLE species …, even B's plain SELECT on species would have waited. Begin A with plain BEGIN; (read committed) instead and A's second SELECT shows B's 1, and its UPDATE succeeds. Which is right depends on the job: a report that reads several tables wants one snapshot (repeatable read, read only); a job that decides from what it read and then writes wants to hear that the data changed under it (repeatable read or serializable, with a retry).

  7. Upserts. The game's feed, version 1: the devil's records from 2000 to 2019, without months. Labs 8 to 11 and the exercise start from it:

    d4-feed · PostgreSQL with PostGIS 

    CREATE TABLE sightings (
  occurrence_id bigint PRIMARY KEY,
  species_id integer NOT NULL REFERENCES species (id),
  year integer NOT NULL,
  month integer,
  country_code text REFERENCES countries (code)
);
INSERT INTO sightings (occurrence_id, species_id, year, country_code)
SELECT id, species_id, year, country_code
FROM occurrences
WHERE species_id = 48 AND year BETWEEN 2000 AND 2019;
SELECT count(*) AS sightings, count(month) AS with_month FROM sightings;
    -- result: d4-feed · 1 row · sha256 2eded79bd687
sightings | with_month
1816 | 0

    In d4.sql, with the blocks it runs after.

  8. A plain INSERT can't be run twice:

    d4-rerun · PostgreSQL with PostGIS

    Runs after: d4-feed

    INSERT INTO sightings (occurrence_id, species_id, year, country_code)
SELECT id, species_id, year, country_code
FROM occurrences
WHERE species_id = 48 AND year BETWEEN 2000 AND 2019;
    -- result: d4-rerun · error: statement 1 of 1: duplicate key value violates unique constraint "sightings_pkey"

    In d4.sql, with the blocks it runs after.

  9. ON CONFLICT … DO NOTHING adds what's missing and skips the rest. Widen the batch to every year since 2000; RETURNING hands back the rows actually inserted, which a WITH query can count:

    d4-do-nothing · PostgreSQL with PostGIS

    Runs after: d4-feed

    WITH added AS (
  INSERT INTO sightings (occurrence_id, species_id, year, country_code)
  SELECT id, species_id, year, country_code
  FROM occurrences
  WHERE species_id = 48 AND year >= 2000
  ON CONFLICT (occurrence_id) DO NOTHING
  RETURNING year
)
SELECT count(*) AS added, min(year) AS first_year FROM added;
    -- result: d4-do-nothing · 1 row · sha256 9cb3872ad5bc
added | first_year
42 | 2020

    In d4.sql, with the blocks it runs after.

  10. Version 2 of the feed carries the month. DO UPDATE brings existing rows up to date from EXCLUDED, the row that was refused; its WHERE skips rows that wouldn't change, so they aren't rewritten (a new version each, for nothing). IS DISTINCT FROM is <> for values that may be NULL: two NULLs count as the same, a NULL and a value as different. Whether RETURNING hands back an inserted or an updated row shows in xmax, which is 0 on a freshly inserted version: a PostgreSQL detail, but a well-known one.

    d4-do-update · PostgreSQL with PostGIS

    Runs after: d4-feed

    WITH upserted AS (
  INSERT INTO sightings AS s (occurrence_id, species_id, year, month, country_code)
  SELECT id, species_id, year, month, country_code
  FROM occurrences
  WHERE species_id = 48 AND year >= 2000
  ON CONFLICT (occurrence_id) DO UPDATE
    SET month = EXCLUDED.month, country_code = EXCLUDED.country_code
    WHERE (s.month, s.country_code) IS DISTINCT FROM (EXCLUDED.month, EXCLUDED.country_code)
  RETURNING xmax = 0 AS inserted
)
SELECT count(*) FILTER (WHERE inserted) AS inserted,
       count(*) FILTER (WHERE NOT inserted) AS updated
FROM upserted;
    -- result: d4-do-update · 1 row · sha256 418ed4da479b
inserted | updated
42 | 1320

    In d4.sql, with the blocks it runs after.

    The other 496 rows of version 1 have no month in version 2 either, so they were left alone. The feed's upsert brings month and country up to date and nothing else: a record whose animal or year changed upstream keeps its old values here, and one GBIF removed stays in sightings. A full refresh would update those columns too and delete the rows missing from a complete batch.

  11. Two ways an upsert fails. ON CONFLICT names the columns of a unique constraint or index; there is none on (species_id, year):

    d4-no-arbiter · PostgreSQL with PostGIS

    Runs after: d4-feed

    INSERT INTO sightings (occurrence_id, species_id, year)
SELECT id, species_id, year
FROM occurrences
WHERE species_id = 48 AND year >= 2020
ON CONFLICT (species_id, year) DO NOTHING;
    -- result: d4-no-arbiter · error: statement 1 of 1: there is no unique or exclusion constraint matching the ON CONFLICT specification

    In d4.sql, with the blocks it runs after.

    And a batch that holds the same key twice can't decide which row wins, so DO UPDATE refuses it: remove duplicates in the batch first (DISTINCT ON (key), which keeps one row per key, or a check in staging).

    d4-twice · PostgreSQL with PostGIS

    Runs after: d4-feed

    INSERT INTO sightings AS s (occurrence_id, species_id, year, month)
SELECT id, species_id, year, month FROM occurrences WHERE species_id = 48 AND year >= 2020
UNION ALL
SELECT id, species_id, year, month FROM occurrences WHERE species_id = 48 AND year >= 2020
ON CONFLICT (occurrence_id) DO UPDATE SET month = EXCLUDED.month;
    -- result: d4-twice · error: statement 1 of 1: ON CONFLICT DO UPDATE command cannot affect row a second time

    In d4.sql, with the blocks it runs after.

Result

In autocommit, a load that fails on its second batch keeps the first (1,858 rows), and running it again fails on them; in one transaction the error aborts everything, even a SELECT, and ROLLBACK leaves nothing behind, so the fixed file can simply be run again, once or twice, for the same 1,959 rows. A savepoint keeps the first batch and lets the transaction go on. MVCC is visible in xmin: the whole lab was written by one transaction, and an UPDATE writes a new version under your own transaction's id. An UPDATE holds RowExclusiveLock, an ALTER TABLE AccessExclusiveLock; in two sessions, a plain reader didn't wait for a writer, a second writer did, and repeatable read refused to update a row changed since its snapshot. ON CONFLICT made the feed re-runnable: DO NOTHING added the 42 missing records, and DO UPDATE … WHERE … IS DISTINCT FROM inserted those 42 and updated the 1,320 that gained a month, leaving 496 alone.

Exercise

The feed grows to a second animal, the jaguar (species 51). Starting from d4-feed's table, write one statement that brings sightings up to date with every record of the devil and the jaguar that has a year: new records inserted, month and country updated where they changed, other rows left alone. Log what it did in a table sightings_runs (run, inserted, updated), run it twice, and show the log. The second run must change nothing.

Show answer

d4-ex · PostgreSQL with PostGIS

Runs after: d4-feed

CREATE TABLE sightings_runs (run integer PRIMARY KEY, inserted bigint NOT NULL, updated bigint NOT NULL);
WITH upserted AS (
  INSERT INTO sightings AS s (occurrence_id, species_id, year, month, country_code)
  SELECT id, species_id, year, month, country_code
  FROM occurrences
  WHERE species_id IN (48, 51) AND year IS NOT NULL
  ON CONFLICT (occurrence_id) DO UPDATE
    SET month = EXCLUDED.month, country_code = EXCLUDED.country_code
    WHERE (s.month, s.country_code) IS DISTINCT FROM (EXCLUDED.month, EXCLUDED.country_code)
  RETURNING xmax = 0 AS inserted
)
INSERT INTO sightings_runs
SELECT 1, count(*) FILTER (WHERE inserted), count(*) FILTER (WHERE NOT inserted) FROM upserted;
WITH upserted AS (
  INSERT INTO sightings AS s (occurrence_id, species_id, year, month, country_code)
  SELECT id, species_id, year, month, country_code
  FROM occurrences
  WHERE species_id IN (48, 51) AND year IS NOT NULL
  ON CONFLICT (occurrence_id) DO UPDATE
    SET month = EXCLUDED.month, country_code = EXCLUDED.country_code
    WHERE (s.month, s.country_code) IS DISTINCT FROM (EXCLUDED.month, EXCLUDED.country_code)
  RETURNING xmax = 0 AS inserted
)
INSERT INTO sightings_runs
SELECT 2, count(*) FILTER (WHERE inserted), count(*) FILTER (WHERE NOT inserted) FROM upserted;
SELECT run, inserted, updated, (SELECT count(*) FROM sightings) AS sightings_after
FROM sightings_runs
ORDER BY run;
-- result: d4-ex · 2 rows · sha256 cff78f0f35bc
run | inserted | updated | sightings_after
1 | 2137 | 1320 | 3953
2 | 0 | 0 | 3953

In d4.sql, with the blocks it runs after.

Check

The answer: the upsert of lab 10 over both animals, with its counts written to the log by a second INSERT that reads the WITH query; the same statement again for run 2.

Your two runs must match the answer's rows: run 1 inserts 2,137 (the devil's 143 records outside 2000–2019 and the jaguar's 1,994) and updates 1,320; run 2 inserts and updates nothing; sightings ends with 3,953 rows. Without the WHERE … IS DISTINCT FROM, run 2 "updates" every existing row that matches (a new version each) and the check fails; without ON CONFLICT, run 1 fails on the first duplicate.

Dialects

Lab schema: the PostGIS tables

critwire Learn lab database, PostGIS schema learn_curriculum

Every column, in words · The diagram alone