pgwire: DEALLOCATE "Name" keeps the quotes in the lookup — a statement prepared by a Parse message as _PLAN0x7 cannot be deallocated (26000); PostgreSQL releases it

[singhpratech]

What version of Materialize are you using?

v26.38.1 (e7e46f0), materialize/materialized:latest, Docker on Linux x86_64.

What is the issue?

DEALLOCATE "<name>" does not unquote the identifier: the lookup uses the name with its double quotes. A prepared statement created at the protocol level (a Parse message, which stores the name exactly as sent) with any uppercase letter in its name therefore cannot be released by SQL — DEALLOCATE "_PLAN0x7" fails with 26000 prepared statement "\"_PLAN0x7\"" does not exist, and the unquoted form folds to lowercase and misses too. A lowercase name works either way, and PostgreSQL 16 releases the mixed-case one with the quoted form.

(SQL PREPARE "X" … followed by DEALLOCATE "X" appears to work only because the same quoted string is stored on both sides.)

Why it matters: psqlodbc prepares every statement through Parse with a name like _PLAN0x63c81a19c220 and frees it with DEALLOCATE "_PLAN0x…". On Materialize that always fails; inside a transaction it aborts the transaction, and with the current psqlodbc (see postgresql-interfaces/psqlodbc#208 / #209) a statement freed before the commit loses the transaction's rows silently. The fix on the psqlodbc side is in review (postgresql-interfaces/psqlodbc#209); this is the half that is Materialize's.

Steps to reproduce

import psycopg
for label, dsn in (("Materialize", "host=127.0.0.1 port=6875 dbname=materialize user=materialize"),
                   ("PostgreSQL 16", "host=127.0.0.1 port=5432 dbname=… user=… password=…")):
    for name in (b"_PLAN0x7", b"_plan0x8"):
        with psycopg.connect(dsn, autocommit=True) as c:
            c.pgconn.prepare(name, b"SELECT 4")                      # protocol-level Parse with this exact name
            out = []
            for form in (f'DEALLOCATE "{name.decode()}"', f'DEALLOCATE {name.decode()}'):
                try: c.execute(form); out.append(f"{form} -> ok")
                except Exception as e: out.append(f"{form} -> {str(e).splitlines()[0]}")
                c.pgconn.prepare(name, b"SELECT 4")                  # re-prepare for the second form
            print(f"{label:14s} Parse-named {name.decode():9s} | " + " | ".join(out))

Materialize    Parse-named _PLAN0x7  | DEALLOCATE "_PLAN0x7" -> prepared statement "\"_PLAN0x7\"" does not exist | DEALLOCATE _PLAN0x7 -> prepared statement "_plan0x7" does not exist
Materialize    Parse-named _plan0x8  | DEALLOCATE "_plan0x8" -> ok | DEALLOCATE _plan0x8 -> ok
PostgreSQL 16  Parse-named _PLAN0x7  | DEALLOCATE "_PLAN0x7" -> ok | DEALLOCATE _PLAN0x7 -> prepared statement "_plan0x7" does not exist
PostgreSQL 16  Parse-named _plan0x8  | DEALLOCATE "_plan0x8" -> ok | DEALLOCATE _plan0x8 -> ok

Expected behavior

DEALLOCATE "_PLAN0x7" releases the statement named _PLAN0x7, as on PostgreSQL: the identifier is unquoted (case preserved) before the lookup, and PREPARE "X" stores X without the quotes.

[antiguru]

Thanks for reporting this bug. Potential fix in #38606. Tracked in SQL-667.

[singhpratech]

Thank you so much, @antiguru — that was fast, and the PR goes further than the report: fixing CLOSE and the cursor planners alongside makes sense, since portals go through the same Parse/Bind path.

Once #38606 is in an image we can pull, I'll re-run the psqlodbc matrix from postgresql-interfaces/psqlodbc#208 against it (prepared statement freed before and after COMMIT, Protocol=7.4-0, wire captures) and post the before/after here so the pair is closed from both ends.