Migration runner: psycopg2 %-substitution trips on PL/pgSQL function bodies (misleading 'list index out of range')

[justin-k-bruce]

Symptom

A migration containing a PL/pgSQL function with % characters in a RAISE EXCEPTION (or format()) call fails with:

RuntimeError: Migration failed: list index out of range

The error message gives no hint that the % chars are the problem. The user-facing failure looks like a tina4 internal bug, when it's really psycopg2's argument-substitution system tripping on the literal percent signs.

Root cause

postgres.PostgresAdapter.execute(sql, params) calls

cursor.execute(sql, params or [])

psycopg2 interprets % as parameter placeholders always when a params arg is passed — even an empty list []. So a function body like

CREATE OR REPLACE FUNCTION foo() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    IF FOUND THEN
        RAISE EXCEPTION 'thing % conflicts with %', a, b;
    END IF;
    RETURN NEW;
END $$

…is sent to psycopg2 as (sql, []). psycopg2 tries to substitute the two % chars, finds zero values in [], and raises IndexError: list index out of range from inside its own format machinery. tina4's Database.execute catches the exception, stores str(e) in last_error, and returns False. The migration runner then surfaces

RuntimeError: Migration failed: list index out of range

…with no file location or hint about the actual cause.

Repro

# repro.py
from tina4_python.database import Database

db = Database("postgresql://user:pw@localhost/db")
sql = '''
CREATE OR REPLACE FUNCTION repro_fn() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    RAISE EXCEPTION 'a % b';
    RETURN NEW;
END $$;
'''
print(db.execute(sql))   # False
print(db.last_error)      # list index out of range

Same fires when the function uses format('… %I …', x) — any % literal triggers it.

Workaround

Double-up every % inside $$…$$ bodies → %%. The user has to know this gotcha exists; the error message doesn't suggest it.

Suggested fix

When params is empty/None, don't pass a params arg to cursor.execute at all — psycopg2 only does %-substitution when given the second arg:

# postgres.py PostgresAdapter.execute
if params:
    cursor.execute(sql, params)
else:
    cursor.execute(sql)

This is fully backwards-compatible and lets PL/pgSQL function bodies keep their literal % chars without surprising the author.

If you'd prefer to keep the unconditional 2-arg call, at minimum a clearer error message would help — wrap the IndexError with something like "SQL contains literal % chars that psycopg2 read as parameter placeholders — escape them as %% inside function bodies".

Happy to send a PR if the suggested fix above looks reasonable.

Hit on tina4-python 3.12.1 + psycopg2-binary 2.9.x against PG 16. Same code shape ran on the old _t4_lastval_probe-less version too, so it's not new.

[andrevanzuydam]

Released in tina4-python 3.12.6.

Root cause confirmed

Exactly the analysis you wrote up — cursor.execute(sql, params or []) puts psycopg2 in substitution mode whenever the params arg is supplied (even []), so a PL/pgSQL function body with RAISE EXCEPTION 'thing %', x is misread as a placeholder string with no values, and you get list index out of range from way down inside psycopg2 — a long way from the real cause.

Fix

New helper PostgreSQLAdapter._safe_execute(cursor, sql, params) that routes empty/None params through cursor.execute(sql) (no second arg). When there are no params, psycopg2 skips the substitution pass entirely; literal % flows through. Applied at every cursor.execute(...) call site in the adapter.

Tests

5 unit tests in tests/test_postgres_percent_substitution.py pin the helper's branching with a fake cursor — no live PG needed, so they always run.

3 live-PG regression tests in tests/test_postgres_plpgsql_percent.py exercise a real CREATE FUNCTION + trigger flow with literal % chars in the body. Skipped automatically when no Postgres is reachable.

Full suite went 2448 → 2453 passing.

Cross-framework parity

Checked all four: PHP (pg_query vs pg_query_params) and Ruby (exec vs exec_params) already branch on params presence, so they don't have this bug. Node uses $1 placeholders — different driver, different syntax — same class of bug doesn't apply.

Upgrade

uv add tina4-python@3.12.6

…or pip install --upgrade tina4-python. Leaving the issue open for you to confirm against your real migration set, then please close. Thanks for the careful root-cause writeup — saved a real investigation cycle.

[justin-k-bruce]

Fix landed in 3.12.6 (PostgreSQLAdapter._safe_execute routes empty/None params through cursor.execute(sql) without a second argument). Verified locally — migrations containing PL/pgSQL function bodies with literal % (e.g. format('%I'), format('%L')) now apply cleanly with no "list index out of range" trip. Safe to close.