A sync that exits 0 can leave the table empty

A lesson from Successful Pipelines, Wrong Numbers. Play it above, or read it through below.

• psql exits 0 after an error. It finished the file.

  The common read: A statement in my script errored, so the process exits nonzero and the runner that gates on exit codes catches it.

  The exit code reports on the psql process, not on your statements. Postgres documents four values and each one names a condition of the client process: 0 if it finishes normally, 1 if a fatal error of its own occurs such as out of memory or file not found, 2 if the connection to the server fails and the session is not interactive, and 3 if an error occurs in a script and the variable ON_ERROR_STOP is set.

  Read that list for what is missing. A statement the server rejected appears on it only when ON_ERROR_STOP is set, and the setting is off by default. Finishing normally means reaching the end of the input, because the documented default is that command processing continues after an error.

  So the transcript below shows a rejected UPDATE, a statement that ran after it, and a process that returned 0 to the shell. The account that should have moved to pro stays on standard, and nothing re-runs that statement, because the job's run is recorded as a success. A month later, finance asks about one invoice and files the question against the billing report, although the report is correct. psql printed the error, but the job runner reads only the exit code.

• The count is in the tag. The tag is not the status.

  The common read: The number in the command tag is how many rows the statement handled, so a load that reports rows read its input and did its work.

  Postgres reports how many rows changed and whether anything went wrong in two different messages, and of the two, only the error message can ever change the exit code. Postgres responds to a completed command with CommandComplete, documented as "An SQL command completed normally", carrying a tag string: INSERT oid rows, UPDATE rows, DELETE rows, MERGE rows, COPY rows.

  An error produces a different message, ErrorResponse, and none of its fields is a count: the fields that are always present are a severity, a SQLSTATE code and a message, and the optional ones include detail, hint and position. Zero rows and twenty thousand rows produce the same message with a different number in it. Zero rows and an error produce different messages, and the protocol never reports zero rows as an error.

  The count is rows that were WRITTEN, so a load whose conflict clause suppressed every write reports zero after reading every row it was given. The leading field on an INSERT is not a count: it used to be the object ID of the inserted row when there was exactly one and the table had OIDs, and since OID system columns were removed it is always 0, so a filter written to catch empty loads by matching INSERT 0 matches every insert that ever succeeded.

  For example, a team deploys that filter, and it alerts after every nightly load. The team mutes it within a week, so on the night a load really is empty, the alert reaches nobody.

  The tag's contents have also changed between versions: the docs still record that COPY's row count "appears only in PostgreSQL 8.2 and later".

• A wipe and a load print the same word.

  The common read: A step that removed every row would have raised an error, so a green sync means the table came out the way it was meant to.

  The same script, with the same two statements, ran on two nights and returned the same exit code. On the first night it replaced the customer table with a full export, and on the second night it emptied the table. Before you read both runs in the figure, decide where the output shows the difference between them.

  In the sync's own output, the difference appears in one field of one completion tag. (The count(*) query in the figure is added here to show the result. The sync does not run it.) The statement text is identical across the two runs, the error stream is empty in both, and the process returns 0 in both. The only difference between a healthy replace and a wipe is INSERT 0 20000 against INSERT 0 0.

  A plain INSERT ... SELECT, UPDATE or DELETE that matches nothing is not a failure, which is why INSERT 0 0, UPDATE 0 and DELETE 0 complete with no error or warning: the protocol's alternative to a completion is an error, and no error occurred. Zero rows becomes an error only when the code asks for one, for example with INTO STRICT in PL/pgSQL. Nothing in either transcript is a bug. Every statement did what the script told it to do, in order.

  So a run that exits 0 can mean any of three outcomes, and the exit code does not distinguish them: the work happened, the work matched nothing, or the work deleted every row. A re-run adds a fourth, since a load that is not keyed to overwrite its own partition succeeds twice and doubles the row count, and both runs exit 0. For example, a dashboard total doubles overnight, someone asks the analytics team what changed in the tracking, and nothing in the tracking changed.

• Stopping early is a setting. Being wrong is not an error.

  The common read: Setting ON_ERROR_STOP makes the script fail when something goes wrong, so a run that exits 0 under it is a run whose data is right.

  Set ON_ERROR_STOP and the same script that exited 0 exits 3 and stops at the rejected statement. Before reading on, decide what that setting protects against.

  The setting stops the run early and gives a distinct exit code. The run stops at the first statement the server rejects, the statements after it never execute, and the shell receives 3, which the docs define to distinguish a script error from psql's own fatal conditions, reported as 1.

  Under the default, the same script printed the same error and kept going, so the setting does not change whether the error is detected; it changes whether psql stops and reports the error in the exit code. Set ON_ERROR_STOP on every script you own.

  The setting does not help with the other failures in this lesson. Exit 3 requires a raised error, and only the rejected UPDATE raised one: an empty export raises nothing, a conflict clause that suppresses every write raises nothing, a predicate that matches no rows raises nothing, and a load that runs twice raises nothing.

  Turn on ON_ERROR_STOP, run last night's sync again, and the sync that emptied the table still exits 0. If the runbook now says the pipeline is protected, the on-call engineer trusts a run that exits 0, and when a customer questions a number, the ticket is filed against the dashboard, not against the setting, which had no error to act on.

• A check that never fires is not a check.

  The common read: I compare the load against the source it loaded from and raise when they disagree, so the run fails when the load is wrong.

  The parity check below compares the loaded table against the export it loaded from and raises when the two counts disagree. Work out what it does on a night when the export file is empty, before you read the transcript.

  The check passes. Zero equals zero, the block completes, and psql exits 0 on an emptied customer table, because the check compares the table against the same source that failed, and both counts are zero.

  Compare instead against something the failure cannot change, such as a minimum row count (a floor) the table has exceeded every night for a year, and the check raises an error. That fixes the comparison but not the exit code: the last lines of the transcript show the same raise running without ON_ERROR_STOP, printing the same exception text to stderr and returning 0 to the shell.

  Both conditions have to hold: the check has to compare against something independent of the fault, and its error has to change the exit code the runner reads. Without the first condition, the check passes on a wiped table. Without the second, it writes an error to a log that nobody reads until the postmortem.

  Exit 0 attests that the process finished. A command tag attests that one statement completed and how many rows moved. Neither is a claim about whether the number is right, and no default anywhere makes it one. The only signal about whether the data is right is a check you write, compared against something the fault cannot change, and connected to the exit code the runner reads.