column o.wallet_address does not exist

Symptom

A migration that only defines a function fails, though nothing has called the function yet:

58-community-requests.sql:68: ERROR: column o.wallet_address does not exist

The statement is a CREATE OR REPLACE FUNCTION … LANGUAGE sql (shortened):

CREATE OR REPLACE FUNCTION witan_bought_unit(op uuid, unit uuid) RETURNS boolean AS $$
  SELECT EXISTS ( ... ) OR EXISTS (
    SELECT 1 FROM sales s
      JOIN knowledge_units k ON k.id = s.unit_id
      LEFT JOIN payments p ON p.id = s.payment_id
      LEFT JOIN operators o ON o.id = op
     WHERE k.group_id = (SELECT group_id FROM knowledge_units WHERE id = unit)
       AND (s.buyer_operator_id = op
            OR (o.wallet_address IS NOT NULL AND lower(COALESCE(s.payer, p.payer)) = lower(o.wallet_address)))
  );
$$ LANGUAGE sql STABLE;

With psql -v ON_ERROR_STOP=1 (and the Docker entrypoint's init scripts) the file stops at this point, so everything after it in the file and in later files is never applied.

When it happens

• The function is LANGUAGE sql (not plpgsql).
• Its body refers to a column or table that doesn't exist when the function is created. In this case wallet_address was a column of a different table (agents), and operators had payout_address. The alias o pointed at the wrong table.
• check_function_bodies is on. That's the default, and it's what psql and the Docker entrypoint use.

Cause

With check_function_bodies = on, PostgreSQL parses and analyzes the body of a SQL-language function at CREATE FUNCTION time, so every table and column it names must exist then.

PL/pgSQL is different. At creation it only checks the syntax. The queries inside are planned the first time each statement runs. Checked on PostgreSQL 17.11:

-- plpgsql: created without complaint, fails only when called
CREATE FUNCTION f_plpgsql(op int) RETURNS boolean AS $$
BEGIN RETURN EXISTS (SELECT 1 FROM operators o WHERE o.id = op AND o.wallet_address IS NOT NULL); END
$$ LANGUAGE plpgsql STABLE;              -- CREATE FUNCTION
SELECT f_plpgsql(1);                      -- ERROR: column o.wallet_address does not exist
                                          -- CONTEXT: PL/pgSQL function f_plpgsql(integer) line 2 at RETURN

With SET check_function_bodies = off (what pg_dump output sets while restoring), the SQL function is created too, and the error moves to the first call:

ERROR:  column o.wallet_address does not exist
CONTEXT:  SQL function "f_sql_off" during inlining

So the same mistake fails at migration time for LANGUAGE sql and at run time for plpgsql.

Fix

Use the right column. Here the buyer's wallet lives in operators.payout_address:

-            OR (o.wallet_address IS NOT NULL AND lower(COALESCE(s.payer, p.payer)) = lower(o.wallet_address)))
+            OR (o.payout_address IS NOT NULL AND lower(COALESCE(s.payer, p.payer)) = lower(o.payout_address)))

Then make sure every database that stopped at the error gets the rest of the file and the later files. A fresh database must be re-initialized, because the Docker entrypoint won't re-run init on a non-empty data directory.

Don't "fix" it by switching the function to plpgsql or by turning check_function_bodies off. Either one only hides the error until the first call.

Verify

Test migrations the way they actually run, in CI, on every pull request:

1. Fresh: an empty database from the same image as production. Apply every file in order with ON_ERROR_STOP=1. A SQL function that names a missing column fails here, on the pull request that adds it.
2. Upgrade: apply the last release's files, mark them applied, then run your migration tool on the new ones, each in its own transaction, the way a deploy does.
3. Compare the two results (columns and function signatures). Then call the plpgsql functions you rely on, because nothing else checks their bodies.

Locally:

psql -d scratch -v ON_ERROR_STOP=1 -f db/init/58-community-requests.sql
psql -d scratch -tAc "SELECT witan_bought_unit('00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000')"

Notes

• This is a good property of SQL functions: the mistake shows up at migration time, not on the first request. Prefer LANGUAGE sql for simple lookup functions partly for this reason.
• When two branches change the schema in parallel, each can pass alone, and they break when the second one merges. Only a check that runs on the merged result (on every push to the main branch) catches that.
• The migration in this incident also had an earlier typo: CREATE INDEX IF NOT EXISTS idx_comments_kindON comments(...). The missing space before ON made the index name swallow the keyword, and the result was a syntax error that stopped the same file. Running a fresh apply in CI would have caught both.