From: Tom Lane <tgl@sss.pgh.pa.us>
To: Istvan Soos <istvan.soos@gmail.com>
cc: pgsql-general@lists.postgresql.org
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>
References: <CALdQGguDi+Pkz-Vd5HMr+ER0L1+DQMsAh+OqbM13udF1RbLzcw@mail.gmail.com>
Comments: In-reply-to Istvan Soos <istvan.soos@gmail.com>
	message dated "Wed, 20 Nov 2024 19:04:44 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <232550.1732129077.1@sss.pgh.pa.us>
Date: Wed, 20 Nov 2024 13:57:57 -0500
Message-ID: <232551.1732129077@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/232551.1732129077%40sss.pgh.pa.us>
Precedence: bulk

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