public inbox for [email protected]help / color / mirror / Atom feed
A table lock inside a transaction depends on query protocol being used? 3+ messages / 2 participants [nested] [flat]
* A table lock inside a transaction depends on query protocol being used? @ 2024-11-20 18:04 Istvan Soos <[email protected]> 2024-11-20 18:57 ` Re: A table lock inside a transaction depends on query protocol being used? Tom Lane <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Istvan Soos @ 2024-11-20 18:04 UTC (permalink / raw) To: [email protected] 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: A table lock inside a transaction depends on query protocol being used? 2024-11-20 18:04 A table lock inside a transaction depends on query protocol being used? Istvan Soos <[email protected]> @ 2024-11-20 18:57 ` Tom Lane <[email protected]> 2024-11-20 19:39 ` Re: A table lock inside a transaction depends on query protocol being used? Istvan Soos <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2024-11-20 18:57 UTC (permalink / raw) To: Istvan Soos <[email protected]>; +Cc: [email protected] Istvan Soos <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: A table lock inside a transaction depends on query protocol being used? 2024-11-20 18:04 A table lock inside a transaction depends on query protocol being used? Istvan Soos <[email protected]> 2024-11-20 18:57 ` Re: A table lock inside a transaction depends on query protocol being used? Tom Lane <[email protected]> @ 2024-11-20 19:39 ` Istvan Soos <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Istvan Soos @ 2024-11-20 19:39 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: [email protected] On Wed, Nov 20, 2024 at 7:57 PM Tom Lane <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-11-20 19:39 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-11-20 18:04 A table lock inside a transaction depends on query protocol being used? Istvan Soos <[email protected]> 2024-11-20 18:57 ` Tom Lane <[email protected]> 2024-11-20 19:39 ` Istvan Soos <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox