Thread: A table lock inside a transaction depends on query protocol being used?

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

Istvan Soos <istvan.soos@gmail.com> writes:
> 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.

Your message trace isn't too clear (it's not apparent where you're
issuing the ALTER TABLE), but I wonder if you could be failing to
close out the SELECT statement before issuing ALTER.  The error
message implies that something is still holding a reference count
on "a"'s relcache entry, and it's hard to see what that could be
except a still-open Portal for the SELECT.

            regards, tom lane

On Wed, Nov 20, 2024 at 7:57 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> but I wonder if you could be failing to close out the SELECT statement before issuing ALTER.

Thanks! This led me to check some debug details, and in fact we don't
close the portal, only the statement. (Besides a bug in the debug
log...)

Now I know what to fix :).

Thanks,
  Istvan