Search Postgresql Archives

Re: Hot Standby Conflict on pg_attribute

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi Andres,

Thank you very much!  That's exactly what I needed.

On Fri, May 10, 2019 at 12:14 PM Andres Freund <andres@xxxxxxxxxxx> wrote:
Hi,

On 2019-05-09 13:03:50 -0700, Erik Jones wrote:
> The question then is: Why would these user queries be waiting on an
> AccessShare lock on pg_attribute?  Thus far we've been unable to recreate
> any transacitons with the above query (and others) that show any
> pg_attribute locks.  There is no ORM in play here and these queries are
> being sent as single query transactions via this Node.js postgres adapter:
> https://github.com/brianc/node-postgres which is pretty bare bones.

Queries that access a table for the *first* time after DDL happened
(including truncating the relation), need an AccessShareLock on
pg_attribute (and pg_class, pg_index, ...) for a short time.

You can reproduce that fairly easily:

S1: CREATE TABLE foo();
S2: BEGIN; LOCK pg_attribute;
S1: SELECT * FROM foo;
S2: COMMIT;

S1 could execute the select, because it has a cached view of the way the
relation looks.

S2: ALTER TABLE foo ADD COLUMN bar INT;
S2: BEGIN; LOCK pg_attribute;
S1: SELECT * FROM foo;

Here S1 is blocked, because it needs to look at pg_attribute to figure
out the "shape" of the table, but it's currently locked.

Greetings,

Andres Freund


--
Erik Jones
mage2k@xxxxxxxxx

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux