New answers tagged locking
0
When the writes are single sector, and OS guarantees that single sector writes are atomic.
At the time being, it is just Windows with innodb-page-size=4K, and 4K sector size disk. Nothing definitive was ever said about Linux single sector writes being atomic, although I presume it could be, in some circumstances, and depend on the file system.
4
Another answer from another postgresql contributor.
PostgreSQL will not even try to use any indexes during execution of "alter table set not null". It is just not implemented.
Proper implementation of index scan is difficult part. We cannot just do something like this query
select exists(select from foos where bar1 is null)
from alter table command for ...
0
The few times I thought column level locking was needed, I came to the realization later on that it was a bad schema design. The columns needing to be locked should have been placed in a separate table.
3
I looked at the source code (function ATRewriteTable in src/backend/commands/tablecmds.c), and PostgreSQL always uses a sequential scan of the table to verify NOT NULL constraints.
So creating indexes won't speed up the execution.
1
I agree the docs are not the easiest to understand when you don't already know what they are saying. But once you know that the answer is lock table foobar in exclusive mode, I think it is pretty clear in hindsight.
EXCLUSIVE
Conflicts with the ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE
EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS ...
2
Don't do that. Explicitly locking tables is almost always the wrong thing to do. If you describe the use case in more detail, I am confident that a better solution can be found.
Locking tables frequently will keep autovacuum from doing its important work on the table, and eventually an anti-wraparound autovacuum job will come along and block you, bringing ...
Top 50 recent answers are included
