With Protocol=7.4-0 (rollback on error: none), SQLFreeHandle of a prepared statement inside a transaction still sends "SAVEPOINT _per_query_svp_;DEALLOCATE …;RELEASE _per_query_svp_"; when the server rejects it the error is swallowed and SQLEndTran(SQL_COMMIT) returns SQL_SUCCESS although the server rolled the transaction back

[singhpratech]

Context

Several PostgreSQL-wire servers have no SAVEPOINT (QuestDB, Materialize are two I run against). For them one sets Protocol=7.4-0 — rollback-on-error level 0 — and psqlodbc indeed stops sending its per-execute SAVEPOINT _EXEC_SVP_… (with the default level the first execute inside a transaction fails with HY000 internal SAVEPOINT failed, which is at least reported — that is what #31 shows). But one savepoint remains, and its failure is silent and costs the whole transaction.

What happens

Autocommit off, Protocol=7.4-0. SQLPrepare an INSERT, SQLExecute it twice (both SQL_SUCCESS, the server answers INSERT 0 1 twice), then SQLFreeHandle(SQL_HANDLE_STMT) before SQLEndTran(SQL_COMMIT). On the free psqlodbc sends one simple query

SAVEPOINT _per_query_svp_;DEALLOCATE "_PLAN0x…";RELEASE _per_query_svp_

The server answers ErrorResponse (here: table does not exist [table=SAVEPOINT]) and, being inside a transaction, aborts it. psqlodbc does not surface that error anywhere — SQLFreeHandle returns SQL_SUCCESS, SQLEndTran(SQL_COMMIT) returns SQL_SUCCESS (the server does answer COMMIT, of a transaction it has already rolled back) — and count(*) is 0 on the same connection and on a fresh one. Free the statement after the commit and both rows are there; literal INSERTs, autocommit inserts and UseServerSidePrepare=0 are unaffected. Through libpq directly (psycopg 3: named or unnamed statements, a plain DEALLOCATE inside the transaction) every combination keeps its rows, so it is this cleanup path meeting a server that has no savepoints.

Environment: psqlodbc 16.00.0000 (psqlodbcw.so, Ubuntu 24.04 package), unixODBC 2.3.12, Linux x86_64; QuestDB 10.0.0 (PostgreSQL wire, server_version 11.3).

Reproduction (gcc -O1 -Wall free_before_commit.c -lodbc; FB_CONN = Driver=…/psqlodbcw.so;Server=…;Port=8812;Database=qdb;Uid=admin;Pwd=quest;Protocol=7.4-0;; argument b frees before the commit, a after; common.h is a 30-line connect/diagnostic helper):

/* p10b: autocommit OFF, prepared INSERT with params; free the statement handle AFTER (case a) or BEFORE (case b) SQLEndTran(COMMIT) */
#include "common.h"
static void ex(const char*sql){SQLHSTMT h;SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&h);SQLRETURN rc=SQLExecDirect(h,(SQLCHAR*)sql,SQL_NTS);printf("  [%s] rc=%d\n",sql,(int)rc);if(!SQL_SUCCEEDED(rc))diag("   ",SQL_HANDLE_STMT,h);SQLFreeHandle(SQL_HANDLE_STMT,h);}
static void count(const char*label){SQLHSTMT c;SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&c);SQLRETURN rc=SQLExecDirect(c,(SQLCHAR*)"SELECT count(*) FROM adbc_p10",SQL_NTS);
  if(SQL_SUCCEEDED(rc)&&SQLFetch(c)==SQL_SUCCESS){char b[64];SQLLEN ind;SQLGetData(c,1,SQL_C_CHAR,b,sizeof b,&ind);printf("  %s count(*)=%s\n",label,b);}else printf("  %s count rc=%d\n",label,(int)rc);SQLFreeHandle(SQL_HANDLE_STMT,c);}
int main(int argc,char**argv){int before=argc>1&&argv[1][0]=='b';probe_connect(1);
  ex("DROP TABLE IF EXISTS adbc_p10");ex("CREATE TABLE adbc_p10 (i INT, s STRING)");
  SQLSetConnectAttr(hdbc,SQL_ATTR_AUTOCOMMIT,(SQLPOINTER)SQL_AUTOCOMMIT_OFF,0);
  SQLHSTMT h;SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&h);
  RC("prepare",SQL_HANDLE_STMT,h,SQLPrepare(h,(SQLCHAR*)"INSERT INTO adbc_p10 (i,s) VALUES (?,?)",SQL_NTS));
  SQLINTEGER v=0;char s[32]="row";SQLLEN l=SQL_NTS;
  SQLBindParameter(h,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&v,0,NULL);
  SQLBindParameter(h,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,32,0,s,sizeof s,&l);
  for(int k=0;k<2;k++){v=k;RC("execute",SQL_HANDLE_STMT,h,SQLExecute(h));}
  if(before){printf("  free stmt BEFORE commit\n");SQLFreeHandle(SQL_HANDLE_STMT,h);}
  RC("EndTran commit",SQL_HANDLE_DBC,hdbc,SQLEndTran(SQL_HANDLE_DBC,hdbc,SQL_COMMIT));
  if(!before){printf("  free stmt AFTER commit\n");SQLFreeHandle(SQL_HANDLE_STMT,h);}
  SQLSetConnectAttr(hdbc,SQL_ATTR_AUTOCOMMIT,(SQLPOINTER)SQL_AUTOCOMMIT_ON,0);
  count("after");probe_disconnect();probe_connect(1);count("fresh conn");ex("DROP TABLE IF EXISTS adbc_p10");probe_disconnect();return 0;}

$ ./p10b b      # free BEFORE commit
prepare -> rc=0
execute -> rc=0
execute -> rc=0
  free stmt BEFORE commit
EndTran commit -> rc=0
  after count(*)=0
  fresh conn count(*)=0

$ ./p10b a      # free AFTER commit
  after count(*)=2
  fresh conn count(*)=2

The wire (strace, -e trace=sendto,recvfrom), from BEGIN to the count:

sendto(3, "Q\0\0\0\nBEGIN\0", 11) = 11
recvfrom(3, "C\0\0\0\nBEGIN\0Z\0\0\0\5T") = 17
sendto(3, "P\0\0\0L_PLAN0x5c911f00e240\0INSERT INTO adbc_p10 (i,s) VALUES ($1,$2)\0\0\2\0\0\0\0\0\0\0\0S\0\0\0\4", 82) = 82
recvfrom(3, "1\0\0\0\4Z\0\0\0\5T") = 11
sendto(3, "D\0\0\0\31S_PLAN0x5c911f00e240\0S\0\0\0\4", 31) = 31
recvfrom(3, "t\0\0\0\16\0\2\0\0\0\27\0\0\4\23n\0\0\0\4Z\0\0\0\5T") = 26
sendto(3, "B\0\0\0001\0_PLAN0x5c911f00e240\0\0\2\0\0\0\0\0\2\0\0\0\0010\0\0\0\3row\0\1\0\0D\0\0\0\6P\0E\0\0\0\t\0\0\0\0\0S\0\0\0\4", 72) = 72
recvfrom(3, "2\0\0\0\4n\0\0\0\4C\0\0\0\17INSERT 0 1\0Z\0\0\0\5T") = 32
sendto(3, "B\0\0\0001\0_PLAN0x5c911f00e240\0\0\2\0\0\0\0\0\2\0\0\0\0011\0\0\0\3row\0\1\0\0D\0\0\0\6P\0E\0\0\0\t\0\0\0\0\0S\0\0\0\4", 72) = 72
recvfrom(3, "2\0\0\0\4n\0\0\0\4C\0\0\0\17INSERT 0 1\0Z\0\0\0\5T") = 32
sendto(3, "Q\0\0\0WSAVEPOINT _per_query_svp_;DEALLOCATE \"_PLAN0x5c911f00e240\";RELEASE _per_query_svp_\0", 88) = 88
recvfrom(3, "E\0\0\0>C00000\0Mtable does not exist [table=SAVEPOINT]\0SERROR\0P1\0\0Z\0\0\0\5E") = 69
sendto(3, "Q\0\0\0\vCOMMIT\0", 12) = 12
recvfrom(3, "C\0\0\0\vCOMMIT\0Z\0\0\0\5I") = 18
sendto(3, "Q\0\0\0\"SELECT count(*) FROM adbc_p10\0", 35) = 35
recvfrom(3, "T\0\0\0 \0\1count()\0\0\0\0\0\0\1\0\0\0\24\0\10\377\377\377\377\0\0D\0\0\0\v\0\1\0\0\0\0010C\0\0\0\rSELECT 1\0Z\0\0\0\5I") = 65

Expected

1. With rollback-on-error level 0 (Protocol=7.4-0) the DEALLOCATE should not be wrapped in SAVEPOINT/RELEASE at all — the user has asked for no savepoints, and a bare DEALLOCATE (or simply a Close message for the prepared statement) succeeds on these servers.
2. Independently of the level: if the cleanup query fails inside a transaction, that has to reach the application — as an error from SQLFreeHandle, or at the latest as SQL_ERROR/SQL_SUCCESS_WITH_INFO from SQLEndTran, which currently reports success for a transaction whose rows are gone.

[davecramer]

Thanks for the report. I do find it interesting that folks expect this to just work with non-postgres servers. That said I think we can fix it

[singhpratech]

Thanks, Dave. Fair point — the expectation isn't really that psqlodbc supports those servers, more that with rollback-on-error set to 0 the driver has said "no savepoints", so the one left in the cleanup path is the surprise, and its failure going unreported is what turns it from a compatibility gap into a silent loss of rows. If it helps, I'm happy to test a patch . I have QuestDB and Materialize running locally and the program above is self-contained, so I can confirm the free-before-commit case on both and re-run the same thing against PostgreSQL 16 to make sure nothing changes there.

[davecramer]

@singhpratech so PR #209 is an attempt to fix the problem, let me know if it works

[singhpratech]

Tested the branch (922e08e, built on Ubuntu 24.04 against libpq 16 / unixODBC 2.3.12) with the program from #208, Protocol=7.4-0, freeing the prepared statement before the commit (b) and after it (a):

server	stock 16.00 (b / a)	PR #209 (b / a)
QuestDB 10.0.0	0 / 2	2 / 2
PostgreSQL 16.15	2 / 2	2 / 2
Materialize v26.38.1	0 / 2	0 / 2

QuestDB is fixed: the wire now carries a bare DEALLOCATE "_PLAN0x…" between the executes and the COMMIT, no SAVEPOINT/RELEASE, and both rows survive. PostgreSQL is unchanged.

Materialize still loses the rows, but for a different reason that is Materialize's, not this PR's: it answers the bare DEALLOCATE "_PLAN0x…" with

26000  prepared statement "\"_PLAN0x63c81a19c220\"" does not exist

— it keeps the double quotes as part of the name. Reproduced through libpq alone: a statement prepared with a Parse message named _PLAN0x7 cannot be released by DEALLOCATE "_PLAN0x7" on Materialize (PostgreSQL 16 releases it), while a lowercase _plan0x8 works both quoted and unquoted. I'll report that to Materialize.

One observation for the follow-up you scoped out: on Materialize, after the failed DEALLOCATE the driver sends ROLLBACK itself, and the application's SQLEndTran(SQL_COMMIT) then returns SQL_SUCCESS without any COMMIT going to the server (the next message on the wire is the application's next query). Same net effect as before for the application — rows gone, no diagnostic — so surfacing that internal-query error is indeed the remaining piece.

And thank you so much for the fast turnaround on this, Dave — the level-0 gate is exactly the right place for it.

[singhpratech]

Filed the Materialize side: MaterializeInc/materialize#38605 (their bug-report discussion — DEALLOCATE "Name" keeps the quotes in the lookup, side-by-side with PostgreSQL 16 included).