Repository navigation
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 #208
Description
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
- With rollback-on-error level 0 (
Protocol=7.4-0) theDEALLOCATEshould not be wrapped inSAVEPOINT/RELEASEat all — the user has asked for no savepoints, and a bareDEALLOCATE(or simply aClosemessage for the prepared statement) succeeds on these servers. - 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 asSQL_ERROR/SQL_SUCCESS_WITH_INFOfromSQLEndTran, which currently reports success for a transaction whose rows are gone.
Activity
davecramer commentedon Aug 30, 2026
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 commentedon Aug 30, 2026
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 commentedon Aug 31, 2026
@singhpratech so PR #209 is an attempt to fix the problem, let me know if it works
singhpratech commentedon Aug 31, 2026
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):QuestDB is fixed: the wire now carries a bare
DEALLOCATE "_PLAN0x…"between the executes and theCOMMIT, noSAVEPOINT/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— it keeps the double quotes as part of the name. Reproduced through libpq alone: a statement prepared with a
Parsemessage named_PLAN0x7cannot be released byDEALLOCATE "_PLAN0x7"on Materialize (PostgreSQL 16 releases it), while a lowercase_plan0x8works both quoted and unquoted. I'll report that to Materialize.One observation for the follow-up you scoped out: on Materialize, after the failed
DEALLOCATEthe driver sendsROLLBACKitself, and the application'sSQLEndTran(SQL_COMMIT)then returnsSQL_SUCCESSwithout anyCOMMITgoing 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 commentedon Sep 1, 2026
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).Don't wrap internal queries in a savepoint at rollback level 0 (#208) (…
singhpratech commentedon Sep 2, 2026
Thank you so much, Dave — from report to merged fix in four days, with the change in exactly the right place: the level-0 gate is what a client that asked for no savepoints expects, and the single commit that landed is the one I tested above, so those QuestDB numbers stand for
main. The Materialize column stays at 0 until their side of the pair ships (MaterializeInc/materialize#38606 is in review); I'll post the completed table here once it does.