MIME-Version: 1.0
From: Istvan Soos <istvan.soos@gmail.com>
Date: Wed, 20 Nov 2024 19:04:44 +0100
Message-ID: <CALdQGguDi+Pkz-Vd5HMr+ER0L1+DQMsAh+OqbM13udF1RbLzcw@mail.gmail.com>
Subject: A table lock inside a transaction depends on query protocol being used?
To: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CALdQGguDi%2BPkz-Vd5HMr%2BER0L1%2BDQMsAh%2BOqbM13udF1RbLzcw%40mail.gmail.com>
Precedence: bulk

Hi,

I'm developing the Dart client for Postgresql, which is using the
extended query protocol by default. I have received a report which
shows a difference in locking behavior inside a transaction, depending
which protocol we are using.

My main question: is this something that is implemented in a wrong way
in the Dart client? Or is it something that is intrinsic to Postgresql
server? How should the user who found this go ahead?

The following minimal reproduction case can be used locally:

Setup:
CREATE TABLE a (
  a_id INTEGER PRIMARY KEY NOT NULL,
  a_other_id INTEGER NOT NULL
);
CREATE TABLE b (other_id INTEGER PRIMARY KEY NOT NULL);

BEGIN;
SELECT * FROM a;
ALTER TABLE a ADD CONSTRAINT fk_other FOREIGN KEY (a_other_id)
REFERENCES b(other_id);

At which point we get '55006: cannot ALTER TABLE "a" because it is
being used by active queries in this session'. It makes sense,
however, if we change the SELECT to simple query protocol, the error
is not present and the transaction completes.

Internal inside the Dart client, this is the rough message flow debug
for the extended protocol:

[292da4d4][out] Aggregated [Parse SELECT * FROM a;, Instance of 'SyncMessage']
[292da4d4][in] Parse Complete Message
[292da4d4][in] ReadyForQueryMessage(state = T)
[292da4d4][out] Aggregated [Instance of 'BindMessage', Instance of
'DescribeMessage', Instance of 'ExecuteMessage', Instance of
'SyncMessage']
[292da4d4][in] Bind Complete Message
[292da4d4][in] Instance of 'RowDescriptionMessage'
[292da4d4][in] CommandCompleteMessage(0 affected rows)
[292da4d4][in] ReadyForQueryMessage(state = T)
[292da4d4][out] Aggregated [Instance of 'CloseMessage', Instance of
'SyncMessage']
[out] Aggregated [Instance of 'CloseMessage', Instance of 'SyncMessage']
[292da4d4][in] Bind Complete Message
[292da4d4][in] ReadyForQueryMessage(state = T)

And for the simple protocol:

[3f02e699][out] Query: SELECT * FROM a;
[3f02e699][in] Instance of 'RowDescriptionMessage'
[3f02e699][in] CommandCompleteMessage(0 affected rows)
[3f02e699][in] ReadyForQueryMessage(state = T)

Thank you,
  Istvan