Re: low level protocol, implicit transactions , "idle in transaction" issue

I found the commit:

https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=e4a3a0c13e4b5e1a015aca3db238b21b88e73e2f
[Fix do_query to send sync rather than flush.]

introduced a bug. Here is a reproducer. Note that pgpool must be
configured to disable load balancing (i.e. load_balance_node = off, or
backend_weight0 = 1 and backend_weight1 = 0) to run the test by a
reason explained later in this message.

-----------------------------------------------------------------
$ psql -p 11000 -a -f failure.sql test
DROP TABLE test;
DROP TABLE
-- start a pipeline
\startpipeline
-- CREATE a table
CREATE TABLE test(i int);
-- SELECT non-existent table, which raises an error,
-- and aborts the implicit transaction started by the pipeline.
SELECT * from a;
-- Recover from the error and closes the implicit transaction.
\syncpipeline
\endpipeline
CREATE TABLE
psql:failure.sql:11: ERROR: relation "a" does not exist
LINE 1: SELECT * from a;
^
-- Try to SELECT the table created in the previous pipeline.
-- This should fail because the creation of the table "test" was rollbacked.
SELECT * from test;
i
---
(0 rows)
-----------------------------------------------------------------

In this example, a table named "test" is created in a pipeline. Then
a SELECT is executed. Because the SELECT tries to retrieve rows from
non-existent table "a", it causes an error and a roll back of the
implicit transaction started by the pipeline. As a result, the table
"test" created in the pipeline does not exist at the end of the
pipeline. However, a "sync" message was issued by do_query() while
obtaining information regarding table "a". As the sync message caused
commit of the implicit transaction started by the pipeline, creation
of "test" was not roll backed by the subsequent erroneous SELECT.

IMO this is a serious data consistency issue because it breaks the
transaction semantics. If I directly connects to PostgreSQL or pgpool
by the previous commit, and run the script, it ends up with:

SELECT * from test;
psql:failure.sql:14: ERROR: relation "test" does not exist
LINE 1: SELECT * from test;
^
which is the expected behavior.

Another bug:

In the begging of this message, I wrote that pgpool must be configured
to disable load balancing (i.e. load_balance_node = off, or
backend_weight0 = 1 and backend_weight1 = 0) to run the test. I am
going to explain the reason.

When a pipeline including a write query and a read query starts,
pgpool executes the write query on primary. The subsequent read query
can be run on standby if load balance is enabled. So it is possible
that implicit transaction including a write query runs on primary, and
an implicit transaction including a read query runs on standby. Even
if the transaction running on standby aborts by an error, it does not
affect the transaction on primary. As a result, the transaction on
primary successfully commits and the table "test" is created.

So we have two problems:

(1) An implicit transaction does not roll back when it should, due to
an internal sync message.

(2) An implicit transaction does not roll back when it should, due to
load balance.

To solve (1), I am going to revert the commit [Fix do_query to send
sync rather than flush.] Of course this cancel the fix (issue with
query cache) in the commit, but I think we should solve it in
different way.

For (2), I can't think of a solution right now. I need more time to
think of a solution.

Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

> On 8/2/26 09:50, Tatsuo Ishii wrote:
>
>> Hi Achilleas,
>>
>>>> Ok but then how can we explain the system complaining about :
>>>>
>>>> "DISCARD ALL cannot run inside a transaction block" ?
>>>>
>>>> Apparently there was inside a transaction somehow,
>>> It does not necessarily mean pg_stat_activity shows it as "idle in
>>> transaction". From my experience, without issuing an explicit
>>> transaction from client, pg_stat_activity shows "idle" or "active",
>>> but never "idle in transaction". I guess PostgreSQL distinguish an
>>> explicit transaction and an implicit transaction.
>>>
>>>> and upon hitting
>>>> the home page the app (Quarkus) apart from the xaction in the logging
>>>> table didn't start any other explicitly.
>>>>
>>>> Also the problem never manifested when against plain vanilla
>>>> postgresql, or pgbouncer -> postgresql,
>>>>
>>>> And only when against pgbouncer -> pgpool ->
>>>> postgresqlmemory_cache_enabled = true
>>> Yes, in the case above, pgpool issues do_query which causes open
>>> implicit transaction. But again, I think it does not cause
>>> pg_stat_activity showing "idle in transaction".
>> Patch pushed to all supported branches.
>> https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=e4a3a0c13e4b5e1a015aca3db238b21b88e73e2f
>>
>> As I wroite in the commit messages, I hoped the patch fixes "DISCARD
>> ALL cannot run inside a transaction block" error.
>>
>> However, the original intension of the patch was to fix "idle in
>> transaction" left in pg_stat_activity. Please try the 4.7 patch if you
>> like.
>
> Thank you Tatsuo for the hard work you are putting into pgpool !
>
> I don't quite feel right about the course of events during this
> thread, meaning me resorting to our local AI to pull the iron out of
> the fire, I hope to more personal involvement next round! at least I
> wish so.
>
>>
>> https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=bc3689a2d62f2083699b86feb267e90296913c26