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

Istvan Soos <istvan(dot)soos(at)gmail(dot)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