db reset: pipelined migration batch is not a transaction block, so LOCK TABLE fails with 25P01 (regression in 2.115.0)

[nikravi]

Affected area

Migrations

Supabase CLI version

2.115.0 and 2.116.0 (regression introduced in 2.115.0; 2.114.0 and earlier are fine)

Operating system

macOS 26.5.1 (arm64), Docker 29.4.0

Installation method

npx / npm

Command

supabase db reset

Actual output

Applying migration 20260101000000_lock_table.sql...
{"_tag":"Error","error":{"code":"LegacyMigrationApplyError","message":"ERROR: LOCK TABLE can only be used in transaction blocks (SQLSTATE 25P01)\nAt statement: 1\n-- Take the lock before the ALTER, so no row can be committed between a repair\n-- and a validating scan. This is the only statement that matters here.\nlock table public.t in access exclusive mode"}}

db reset stops at that migration and applies nothing after it — no later migrations, no seed files. On a large migration history the failure reads like one bad migration, but it silently leaves the database many migrations and every seed short.

Expected behavior

LOCK TABLE inside a migration applies, as it did through 2.114.0. Taking an explicit lock before a validating ALTER is a normal pattern for a migration that must not race with concurrent writes, and the statement is legal in any real transaction block.

Steps to reproduce

Minimal project — one migration, nothing else:

-- supabase/migrations/20260101000000_lock_table.sql
create table public.t (id int primary key);

lock table public.t in access exclusive mode;

alter table public.t add column note text;

supabase start
supabase db reset

Bisected against that same project, same Docker, same migration:

CLI version	db reset
2.113.0	applies
2.114.0	applies
2.115.0	25P01
2.116.0	25P01

The break lines up with #6224 ("batch migration statements"), which landed in 2.115.0.

Root cause

#6224 changed migration apply to pipeline a file over the extended protocol — "one Parse/Bind/Execute per statement and one Sync per batch". The Go implementation it replaced sent the file as a single multi-statement simple query, which PostgreSQL wraps in a real transaction block, and that is what LOCK TABLE was relying on. Nothing in the migration ever issued a BEGIN; the protocol supplied one.

A pipeline is not a substitute, because PostgreSQL treats it as neither in nor out of a transaction block. Probed directly against the same database with psycopg 3.3.4:

with conn.pipeline():                                    # one Sync for the batch
    conn.execute("create table if not exists p (id int)")
    conn.execute("lock table p in access exclusive mode")
# LOCK TABLE can only be used in transaction blocks          (25P01)

with conn.pipeline():
    conn.execute("create index concurrently q_idx on q (id)")
# CREATE INDEX CONCURRENTLY cannot be executed within a pipeline  (25001)

Both fail, in opposite directions. RequireTransactionBlock (LOCK TABLE, SET LOCAL, DECLARE … WITH HOLD) says a pipeline is not a transaction block; PreventInTransactionBlock (CONCURRENTLY, VACUUM, ALTER SYSTEM, CLUSTER) says it is. So there is no statement class for which a bare pipeline is equivalent to what the Go CLI did.

This also means the classifier approach in #5671 and #6276 cannot cover this case. That list moves statements out of the batch so they run standalone; LOCK TABLE needs the opposite — to be inside a genuine transaction block. Adding it to legacyIsPipelineIncompatible would turn 25P01 into a silently useless lock, taken and released before the statement it was meant to protect.

Suggested fix

Wrap the pipelined batch in an explicit BEGIN / COMMIT rather than relying on the pipeline's implicit semantics. That restores the Go behaviour exactly for LOCK TABLE and friends, and the existing legacyIsPipelineIncompatible list keeps doing its job for the statements that must stay outside it. Verified on the same connection that the pipeline rejects:

conn.execute("begin")
conn.execute("create table if not exists r (id int)")
conn.execute("lock table r in access exclusive mode")   # OK
conn.execute("commit")

Impact

Any repository with a migration that takes an explicit lock cannot run supabase db reset on 2.115.0 or 2.116.0, and the failure is partial rather than clean — the reset leaves a half-migrated, unseeded database behind. Since #6276 shipped a fix for the mirror-image case (DROP INDEX CONCURRENTLY) three weeks ago, I suspect the pipeline's transaction semantics are worth settling once rather than statement class by statement class.