On Mon, Apr 2, 2018 at 3:11 PM, Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote: > Why do we need AccessExclusiveLock on all children of a relation that we > want to scan to search for rows not satisfying the constraint? I think > it should be enough to get ShareLock, which prevents INSERT, no? I have > a feeling I'm missing something here, but I don't know what, and all > tests pass with that change.
I don't think it was a good idea to change this without a lot more discussion, as part of another commit that really was about something else, and after feature freeze. As Kyotaro Horiguchi also mentioned, this introduces a deadlock hazard. With current master: Setup: create table foo (a int, b text) partition by range (a); create table foo1 partition of foo for values from (1) to (100); create table food (a int, b text) partition by range (a); create table food1 partition of food for values from (1) to (100); Session 1: begin; BEGIN rhaas=# select * from food1; a | b ---+--- (0 rows) rhaas=# insert into food1 values (1, 'thunk'); Session 2: rhaas=# alter table foo attach partition food default; At which point session 1 deadlocks, because the lock has to be upgraded to AccessExclusiveLock since we're changing the constraints. Now you might think about relaxing the lock level for the later acquisition, too, but I'm not sure that's safe. The issue is that scanning a relation for rows that don't match the new constraint isn't by itself sufficient: you also have to be sure that nobody can add one later. If they don't have the relation open, they'll certainly rebuild their relcache entry when they open it, so it'll be fine. But if they already have the relation open, I'm not sure we can be certain it will get rebuilt if, later in the same transaction, they try to insert data. This whole area needs more research -- there may very well be good opportunities to reduce lock levels in this area, but it needs careful study and analysis. Please revert the part of this commit that changed the lock level. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company