I ran following query sequences using extended query protocol + simple
query protocol. It seems they give inconsistent behavior.
(I ran the query sequences using pgproto. pgproto is a tool coming
with Pgpool-II which sends and receives low level F/B protocol
message).
There's a table called "t1" which has a int column. Initially there's
no row in the table. PostgreSQL version is 17.5.
The first case:
$ pgproto -d test -p 11003 -f /tmp/aaa
FE=> Parse(stmt="", query="INSERT INTO t1 VALUES(1)")
FE=> Bind(stmt="", portal="")
FE=> Execute(portal="")
FE=> Query (query="ROLLBACK")
FE=> Terminate
In this case "INSERT INTO t1 VALUES(1)" was executed using unnamed
statement and unnamed portal. Then simple query "ROLLBACK" was
executed (note that no "Sync" message between Execute and
Query). After this, no new row was added to t1.
test=# select * from t1;
i
---
(0 rows)
In the PostgreSQL log I see a complain because ROLLBACK was executed
outside of an explicit transaction.
2853787 2025-07-12 16:13:54.296 JST LOG: execute <unnamed>: INSERT INTO t1 VALUES(1)
2853787 2025-07-12 16:13:54.296 JST LOG: statement: ROLLBACK
2853787 2025-07-12 16:13:54.296 JST WARNING: there is no transaction in progress
Now the interesting part starts.
If I change the "ROLLBACK" query to "COMMIT", a different result is
observed.
The second case:
$ pgproto -d test -p 11003 -f /tmp/aaa
FE=> Parse(stmt="", query="INSERT INTO t1 VALUES(1)")
FE=> Bind(stmt="", portal="")
FE=> Execute(portal="")
FE=> Query (query="COMMIT")
FE=> Terminate
This time the INSERT succeeded and I see one row added to t1:
test=# select * from t1;
i
---
1
(1 row)
In the PostgreSQL log I see a complain too because COMMIT was executed
outside of an explicit transaction.
2853981 2025-07-12 16:17:36.849 JST LOG: execute <unnamed>: INSERT INTO t1 VALUES(1)
2853981 2025-07-12 16:17:36.849 JST LOG: statement: COMMIT
2853981 2025-07-12 16:17:36.849 JST WARNING: there is no transaction in progress
We know that if we issue a query using simple protocol query after
extended query protocol queries without finishing it by a Sync
message, the behavior of PostgreSQL is unclear.
However I was surprised by the result: the ROLLBACK case failed to
complete the transaction, while the other succeeded to complete it. I
did not expect the extended query protocol (INSERT part) gives
different result depending on the subsequent simple query (ROLLBACK or
COMMIT). I have not tested other queries than ROLLBACK and COMMIT, but
I am afraid other queries give even weirder results.
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
