EXIN SQL Foundation: Autocommit Off, a Missing WHERE, and a Duplicated Load

Autocommit Off, a Missing WHERE, and a Duplicated Load

At a 200-person retailer, a developer is asked to correct missing shipping fees on the orders table, which holds 3.1 million rows and has no primary key - only a non-unique index on order_ref. She connects with autocommit disabled and runs UPDATE orders SET ship_fee = 0 WHERE ship_fee IS NULL;, which reports 412,000 rows affected. She then intends to review cancelled orders but pastes DELETE FROM orders WHERE status = 'cancelled'; into the same session, and the client reports 96,000 rows deleted. She closes the laptop lid and the connection drops. Meanwhile, an overnight job connected separately with autocommit on has run INSERT INTO orders SELECT * FROM orders_archive WHERE archived_on = CURRENT_DATE; twice, because the scheduler retried after a timeout; 55,000 duplicate rows are now in the table. Nobody has taken a backup since Sunday.

1. What is the state of the developer's two statements after the connection dropped, and what would you check before running anything else?

Read the worked answer

State of the work. With autocommit disabled, both statements ran inside one open transaction that was never committed. When the client's socket closes, the server aborts that session and rolls back any transaction still in progress, so the UPDATE of 412,000 rows and the DELETE of 96,000 rows should both be gone. The same is true if the server crashed: recovery replays the write-ahead log and discards uncommitted transactions.

Why 'should' is not 'is'. Some clients, connection pools and GUI tools are configured to issue COMMIT on disconnect, and some drivers commit implicitly before certain statements. A single test query is cheap insurance:

SELECT count(*) FROM orders WHERE status = 'cancelled';       -- expect ~96,000
SELECT count(*) FROM orders WHERE ship_fee IS NULL;           -- expect ~412,000
SELECT pid, state, xact_start, query
FROM pg_stat_activity
WHERE datname = 'orders_db' AND state LIKE 'idle in transaction%';

An idle in transaction session is holding locks and its work is still reversible; find the owner and decide before it commits or blocks others. Also check whether the transaction had been committed by a DDL statement or an explicit COMMIT in the script.

What not to do. Do not start new fix-up DML, do not restart the database, and do not TRUNCATE 'to start clean' - the Sunday backup is now the only other copy of the data.

2. How do you remove exactly the 55,000 rows the retry created, given that `orders` has no primary key?

Read the worked answer

First identify the exact set, then delete it. order_ref is deliberately non-unique (one order can span several rows), so a global dedupe on order_ref would destroy legitimate data. Scope the target to the archive load:

-- what did the job actually insert today?
SELECT count(*) FROM orders o
JOIN orders_archive a ON a.order_ref = o.order_ref AND a.archived_on = CURRENT_DATE;
-- expect 110,000 rows after the double run, 55,000 if clean

If the archive table has a load timestamp or run ID, join on it. If not, ctid - the physical row locator - is the pragmatic handle, because rows inserted by the same sequential load sit in the newest pages:

BEGIN;

CREATE TEMP TABLE doomed AS
SELECT ctid FROM (
  SELECT ctid,
         row_number() OVER (PARTITION BY order_ref, customer_id, order_date, amount
                            ORDER BY ctid DESC) AS rn
  FROM orders
  WHERE ctid >= (SELECT min(ctid) FROM orders WHERE order_date = CURRENT_DATE)
) d WHERE d.rn > 1;

SELECT count(*) FROM doomed;          -- must be 55,000 before continuing
DELETE FROM orders WHERE ctid IN (SELECT ctid FROM doomed);

-- verify, then COMMIT or ROLLBACK
SELECT count(*) FROM orders;
COMMIT;

row_number() numbers each duplicate group and rn > 1 keeps one copy. ctid is physical and changes on UPDATE or VACUUM FULL, so it is valid only inside one transaction - never store it.

The permanent fix is a key: add a surrogate id GENERATED ALWAYS AS IDENTITY plus a unique constraint on the natural key, so a retried load fails loudly instead of silently doubling.

3. Describe how the correction should have been run so that neither accident was possible, and name the controls you would add afterwards.

Read the worked answer

Run corrections as transactions with a rehearsal.

BEGIN;

-- 1. Rehearse the predicate as a SELECT and record the expected count
SELECT count(*) FROM orders WHERE status = 'cancelled';   -- e.g. 96,000

-- 2. Run the change and capture what it touched
UPDATE orders SET status = 'void'
WHERE status = 'cancelled'
RETURNING order_ref, status;      -- or DELETE ... RETURNING *

-- 3. Compare against the rehearsal, then decide
--    COMMIT;  or  ROLLBACK;

RETURNING turns a blind write into an auditable one: you see exactly which rows moved before you commit. The developer's real failure was not the typo, it was running a DELETE with no RETURNING, no BEGIN, and no SELECT rehearsal.

Controls to add.

A key. Surrogate identity column plus a unique constraint on the natural key; without one, no row can be identified, deduplicated or safely updated.
Least privilege. Day-to-day developers get SELECT and UPDATE on production; DELETE and TRUNCATE live behind a reviewed change procedure and a second pair of eyes.
A replica or a snapshot for exploration, so reviewing 'cancelled orders' never happens on the primary.
Guard rails at session level: statement_timeout, idle_in_transaction_session_timeout (which would have killed the abandoned open transaction), and a client configured never to commit implicitly.
Backups with tested restores, so recovery is a routine rather than an emergency.
Idempotent loads: the overnight job should insert with WHERE NOT EXISTS or a unique key, so a scheduler retry is a no-op rather than duplicated data.