PostgreSQL sorts its problems by severity. An ERROR aborts the current statement and rolls back the transaction, but the session lives on; you issue a ROLLBACK and keep working. A FATAL ends the session, dropping that one connection while the server runs on. A PANIC takes the whole server down. In normal operation the line between “your statement failed” and “your connection is gone” sits between ERROR and FATAL, and nearly everything that goes wrong in day-to-day SQL, a typo, a constraint violation, a division by zero, is a mere ERROR.
exit_on_error erases that line. Turn it on and every ERROR is promoted to session-terminating: any failure, however routine, kills your backend and closes the connection. It is a boolean, default off, context user, so any role can set it for a session, a role, or a database.
It reads like a “fail fast” switch, and that is the trap. This is fail-fast at the level of the operating-system process, not the level of your script or your transaction, and those are almost never the same thing. What people usually want when they reach for something like this is for a batch of statements to stop at the first failure instead of blundering onward. That is a client concern, and the client already handles it: psql has \set ON_ERROR_STOP on, and every application framework has a way to catch an error and stop. Those tools halt the work while leaving the connection intact. exit_on_error throws the connection away, which is strictly more destructive and buys you nothing the client tools do not already give you.
In two settings it is worse than useless. Interactively it is infuriating, because one fat-fingered query disconnects you. Behind a connection pooler it is a hazard, because an ERROR that should have rolled back cleanly instead destroys a pooled backend and forces a reconnect, turning ordinary application errors into churn on the pool.
There is a narrow case where killing the session on any error is defensible: a single-purpose automated connection, a migration or a load job on a throwaway backend, where you genuinely want the whole thing to fall over the instant anything is off so the orchestrator above it notices. Even there, checking the error at the client is the more common and more surgical answer. For everything else, leave exit_on_error off, and if you find it on, suspect that someone confused the process for the script.