Success is a claim, not a fact

A lesson from Green 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 is a claim about psql, not about your statements. Postgres documents four values and each one names a condition of the client process: 0 if it finished 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 went bad and the session was not interactive, and 3 if an error occurred in a script and the variable ON_ERROR_STOP was set.

  Read that list for what is missing. A statement the server rejected is not on it, and 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 holds a rejected UPDATE, a statement that ran after it, and a process that told the shell everything was fine. The account that should have moved to pro stays on standard forever, since nothing will ever re-run that statement: the run it belonged to is recorded as a success. That arrives as a finance question about one invoice a month later, filed against the billing report, and the report is correct. The error was printed. Nothing read it.

• 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.

  How many rows moved and whether anything went wrong travel on two different messages, and only one of them is wired to anything you watch. Postgres answers 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 is a different message entirely, ErrorResponse, and no field in it carries a count: the documented fields are a severity, a SQLSTATE code and a message, with detail, hint and position optional. Zero rows and twenty thousand rows are the same message with a different digit in it. Zero rows and an error are not the same message at all, and nothing in the protocol demotes the first one toward the second.

  Two details in that tag are worth the ten seconds. The count is rows that LANDED, so a load whose conflict clause suppressed every write reports zero while having read every row it was given.

  And 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, which is why a filter written to catch empty loads by matching INSERT 0 catches every insert that ever succeeded.

  That filter ships, the dashboard stays quiet for a year, and the first thing anyone questions when the number is finally wrong is the dashboard. Even the completeness of the tag is a choice with a history: the docs still record that COPY's row count "appears only in PostgreSQL 8.2 and later", so the success signal itself has a version.

• A wipe and a load print the same word.

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

  Two nights, one script, the same two statements, the same exit code. One night replaced the customer table with a full export and one night emptied it. Name where the difference between those two nights shows up, before the figure prints both of them.

  It shows up in one digit inside a completion tag, and nowhere else. The statement text is identical across the two runs, the error stream is empty in both, and the process answers the shell with 0 in both, so the entire discrimination between a healthy replace and a wipe is INSERT 0 20000 against INSERT 0 0.

  Matching nothing is not a failure condition anywhere in SQL, which is the same reason UPDATE 0 and DELETE 0 pass in silence: the protocol's alternative to a completion is an error, and no error occurred. Nothing in either transcript is a bug. Every statement did exactly what it was asked, in order, and the fastest sync the team ever shipped was the one that deleted the customers.

  So a green run is compatible with three outcomes it cannot separate: the work happened, the work matched nothing, and the work happened to everything. A re-run adds a fourth, since a load that is not keyed to overwrite its own partition succeeds twice and doubles the number, and both runs are green. The dashboard doubles overnight, someone asks the analytics team what changed in the tracking, and the answer is that nothing changed at all.

• 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. Decide what that buys before the next paragraph spends it.

  It buys a bound on the damage and a distinguishable code. The run stops at the first statement the server rejects, the statements after it never execute, and the shell gets 3, which the docs define precisely 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 difference is not whether the error was detected but whether anyone acted on it. That is worth setting on every script you own.

  It buys nothing at all for this course. Exit 3 requires a raised error, and none of the failures this lesson is about raise one: an empty export raises nothing, a conflict clause that suppresses every write raises nothing, a predicate that matches no rows raises nothing, a load that runs twice raises nothing. Turn on fail-fast, run last night's sync again, and the emptied table still exits 0.

  The runbook now says the pipeline is protected, the on-call engineer trusts the green, and the number a customer eventually questions gets filed against the dashboard, not the setting that had nothing to catch.

• 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 the night the export arrives empty, before the transcript answers.

  It passes. Zero equals zero, the block completes, and psql exits 0 over an emptied customer table, because a check anchored to the same source that failed agrees with the failure.

  Anchor it to something the failure could not move instead, a floor the table has cleared every night for a year, and it raises, which is the first wire connected and not the second, because the transcript underneath it is that same raise running without ON_ERROR_STOP, printing the identical exception text to stderr and answering the shell with 0.

  Two wires, and both have to be connected: the check has to compare against something independent of the fault, and its error has to reach the exit code the runner reads. Wire one and skip the other and you have built an assertion that writes to a log nobody greps, which is where the postmortem finds it, three screens above the incident.

  That is the whole lesson in one construct. 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 that says the data is right is one you wrote, anchored to something the fault cannot reach, and connected to the code the runner actually reads.