PG adapter silently aborts transactions after INSERT without RETURNING on UUID PK tables

[justin-k-bruce]

Summary

The PostgreSQL adapter's execute() probes SELECT lastval() after every INSERT that lacks a RETURNING clause, to recover an auto-increment ID. When the target table has a UUID primary key (no sequence), lastval() raises:

ERROR: there is no previous sequence value associated with this session

The adapter swallows the exception with try / except: pass, but psycopg2 has already flagged the transaction as aborted. Every subsequent statement on the same connection fails with psycopg2.errors.InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block.

Critically, Database.last_error stays None for the original INSERT — so there is no visible error at all until later code fails cryptically many lines away. This took me several hours to trace.

Affected code

tina4_python/database/postgres.py — PostgreSQLAdapter.execute(), ~lines 55–100:

if not has_returning:
    sql_upper = sql.strip().upper()
    if sql_upper.startswith("INSERT"):
        try:
            cursor.execute("SELECT lastval()")   # <-- raises on UUID PK
            row = cursor.fetchone()
            if row:
                last_id = list(row.values())[0]
        except Exception:                          # <-- swallows the error
            pass                                    # <-- but tx is now aborted

Reproduction

from tina4_python.database import Database
db = Database("postgresql://user:pass@localhost:5432/mydb", autocommit=False)

# Any table with a UUID PK (no sequence) — e.g. with gen_random_uuid() or uuidv7()
db.execute("CREATE TABLE t (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), name text)")

# This INSERT succeeds, but the adapter's post-insert lastval() probe
# aborts the transaction silently.
r = db.execute("INSERT INTO t (name) VALUES (?)", ["hello"])
print("result:", r, "last_error:", db.last_error)
# -> result: True   last_error: None    ✅ looks fine

# Next statement on the same connection fails cryptically:
db.fetch_one("SELECT * FROM t")
# -> psycopg2.errors.InFailedSqlTransaction: current transaction is aborted,
#    commands ignored until end of transaction block

Impact

• Any schema using UUID / ULID / hash-style primary keys triggers this.
• Errors surface far from the root cause — the failing statement is a perfectly valid SELECT/UPDATE that happens to run after a successful INSERT.
• Database.last_error is misleadingly None after the offending INSERT.
• Works around it by adding RETURNING id to every single INSERT (the has_returning branch skips the lastval() probe). Verbose and easy to forget.

Proposed fixes (pick one)

1. Inspect the cursor for an INSERT's implicit RETURNING instead of calling lastval(). If the caller didn't ask for it, don't probe — last_id just ends up None, which is already the default.

# Drop the lastval probe block entirely. If the user wants an insert ID,
# they add RETURNING id themselves.

2. Probe only when the schema has a sequence. Harder to detect portably; option 1 is simpler.

3. Use a savepoint around the lastval() probe so a failure doesn't abort the outer transaction:

if sql_upper.startswith("INSERT"):
    cursor.execute("SAVEPOINT _t4_lastval")
    try:
        cursor.execute("SELECT lastval()")
        row = cursor.fetchone()
        if row:
            last_id = list(row.values())[0]
        cursor.execute("RELEASE SAVEPOINT _t4_lastval")
    except Exception:
        cursor.execute("ROLLBACK TO SAVEPOINT _t4_lastval")

This is the minimum-risk fix that keeps the lastval() feature working for integer-PK tables while not corrupting the transaction for UUID ones.

Environment

• tina4-python 3.11.20
• PostgreSQL 16 (alpine docker image)
• Python 3.12 / 3.14
• psycopg2-binary 2.9.12
• Schema uses UUIDv7 primary keys (polyfilled function for PG16 — same behavior applies to gen_random_uuid(), ULID, any non-sequence PK)

Happy to open a PR with fix #3 if the maintainers agree on the approach.