public inbox for [email protected]  

help / color / mirror / Atom feed

From: Istvan Soos <[email protected]>
To: [email protected]
Subject: A table lock inside a transaction depends on query protocol being used?
Date: Wed, 20 Nov 2024 19:04:44 +0100
Message-ID: <CALdQGguDi+Pkz-Vd5HMr+ER0L1+DQMsAh+OqbM13udF1RbLzcw@mail.gmail.com> (raw)

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

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: A table lock inside a transaction depends on query protocol being used?
  In-Reply-To: <CALdQGguDi+Pkz-Vd5HMr+ER0L1+DQMsAh+OqbM13udF1RbLzcw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox