Errors, SQLSTATE and the Status Byte

Why this stage exists

Your server already sends an ErrorResponse when a statement fails. Its SQLSTATE so far was any five characters. A program has no way to read meaning out of an English message, so it switches on the SQLSTATE instead. A web shop retries an order on 40P01 (deadlock), shows "that email is taken" on 23505 (duplicate key), and logs a bug on 42601 (syntax error). Every PostgreSQL driver exposes this code, and code written against PostgreSQL relies on it. This stage gives every droid error the SQLSTATE PostgreSQL would give the same condition.

It also completes ReadyForQuery. Its last byte has said I so far, because no test opened a transaction. It must say where the session stands:

A driver reads this byte after every answer, so it always knows whether a transaction is open, without asking. Connection pools rely on it too, as the last stages of this track show.

One table, droid code to SQLSTATE

A SQLSTATE is five characters, and the first two are its class: 23 a constraint was violated, 42 the statement itself is wrong, 22 a value is wrong, 25 the transaction is in the wrong state, 40 the transaction was rolled back and can be retried. Your droid codes already sort errors by component. The contract adds one column: for each droid code, the SQLSTATE PostgreSQL gives the same condition. Several droid codes can share one SQLSTATE. 00701 (a duplicate primary key) and 01605 (a duplicate value in a unique index) are both 23505, because to a client both mean "that value is taken".

The droid code is not lost. It travels in the D (detail) field, so psql shows both:

$psql -h 127.0.0.1 -p 5433 -U droid droid
droid=> insert into t (id, name) values (1, 'again');
ERROR:  duplicate key 1 in table t
DETAIL:  droid error 00701

Failed transactions

In the REPL, a statement that fails inside a transaction changes nothing, and the transaction goes on: section 3 of the contract, from the very first stages. PostgreSQL is stricter. After an error, the transaction is failed, and it refuses every statement until the client ends it. The reasoning: if the third of five steps failed, the fourth was probably written assuming it worked. So a client must decide, and the only decision left is to roll back. Even COMMIT rolls back, and its tag says ROLLBACK, so a program that blindly committed learns that nothing was kept.

Drivers and connection pools are built around this rule, so the server follows it, in the protocol layer only. The engine stays exactly as it was: the failed statement changed nothing, and the transaction is still open underneath. The connection only remembers that it failed, answers 25P02 to anything but COMMIT or ROLLBACK, and rolls back when either arrives. The REPL and the line protocol keep their old behaviour, and every earlier test still passes.

Several statements in one Query

A Query can hold several statements: insert ...; select ...;. They run in turn, each sending its own result, and one ReadyForQuery comes after the last. When the client has no transaction open, PostgreSQL wraps them in an implicit transaction: they all happen, or none of them does.

The first INSERT was answered INSERT 0 1 and still did not happen. That is the point: the client sent the three as one unit. When the Query itself says BEGIN, COMMIT or ROLLBACK, the client has taken charge of the transaction, and each statement runs as if it came in a Query of its own.

Output contract for this stage

These are the exact fields and bytes the tests check. The full definition, with the SQLSTATE of every code, is on the output contract page, section 96.

What you should see when done

$psql -h 127.0.0.1 -p 5433 -U droid droid
droid=> begin;
BEGIN
droid=*> insert into t (id, name) values (2, 'n2');
INSERT 0 1
droid=*> select * from missing;
ERROR:  table missing does not exist
DETAIL:  droid error 00401
droid=!> select id from t;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
DETAIL:  droid error 01903
droid=!> commit;
ROLLBACK
droid=> 

psql reads the status byte and shows it in its prompt: =*> inside a transaction, =!> inside a failed one. It reads it from your ReadyForQuery.

Common bugs in this stage

• Forgetting to clear the failed mark. After ROLLBACK, the next transaction starts clean.
• Answering COMMIT with COMMIT after a failure. The client then believes its data was kept.
• Sending ReadyForQuery after each statement of a multi-statement Query. One, after the last.
• Running the statements after an error. The first error ends the Query.
• Committing the implicit transaction after an error. It must roll back. That is the only reason it exists.
• Changing the engine to fail transactions. The REPL's earlier tests would break. The mark lives in the connection.

Language notes

The page above is the same for every language. These are the only language-specific hints.

• C: a sorted array of {code, sqlstate} pairs and a binary search, or a switch on the code. A bool failed in the connection struct.
• C++: a static const std::unordered_map<std::string_view, std::string_view>.
• Rust: a match on the code returning &'static str is checked for missing arms only if codes are an enum, which is a good reason to make them one.
• Zig: a std.StaticStringMap built at compile time from the table.