postgresql 向 postgres 表添加空列会导致锁定吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19525083/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does adding a null column to a postgres table cause a lock?
提问by jpadvo
I think I read somewhere that running an ALTER TABLE foo ADD COLUMN baz texton a postgres database will not cause a read or write lock. Setting a default value causes locking, but allowing a null default prevents a lock.
我想我在某处读到ALTER TABLE foo ADD COLUMN baz text在 postgres 数据库上运行不会导致读或写锁定。设置默认值会导致锁定，但允许空默认值会阻止锁定。
I can't find this in the documentation, though. Can anyone point to a place that says, definitively, if this is true or not?
不过，我在文档中找不到这个。任何人都可以指出一个地方，明确地说，这是否属实？
回答by Daniel Vérité
The different sorts of locks and when they're used are mentioned in the doc in
Table-level Locks. For instance, Postgres 11's ALTER TABLEmay acquire a SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, or ACCESS EXCLUSIVElock.
Table-level Locks的文档中提到了不同种类的锁以及何时使用它们
。例如，Postgres的11的ALTER TABLE可获取SHARE UPDATE EXCLUSIVE，SHARE ROW EXCLUSIVE或ACCESS EXCLUSIVE锁。
Postgres 9.1 through 9.3 claimed to support two of the above three but actually forced Access Exclusivefor all variants of this command. This limitation was lifted in Postgres 9.4but ADD COLUMNremains at ACCESS EXCLUSIVEby design.
Postgres 9.1 到 9.3 声称支持上述三个中的两个，但实际上强制Access Exclusive支持此命令的所有变体。这个限制在 Postgres 9.4 中被取消，但ADD COLUMN仍然ACCESS EXCLUSIVE是设计使然。
It's easy to check in the source code because there's a function dedicated to establishing the lock level needed for this command in various cases: AlterTableGetLockLevelin src/backend/commands/tablecmds.c.
签入源代码很容易，因为有一个函数专门用于在各种情况下建立此命令所需的锁定级别：AlterTableGetLockLevelin src/backend/commands/tablecmds.c。
Concerning how much time the lock is held, once acquired:
关于持有锁的时间，一旦获得：
- When the column's default value is NULL, the column's addition should be very quick because it doesn't need a table rewrite: it's only an update in the catalog.
- When the column has a non-NULL default value, it depends on PostgreSQL version: with version 11 or newer, there is no immediate rewriting of all the rows, so it should be as fast as the NULL case. But with version 10 or older, the table is entirely rewritten, so it may be quite expensive depending on the table's size.
- 当列的默认值为 NULL 时，列的添加应该非常快，因为它不需要重写表：它只是目录中的更新。
- 当列具有非 NULL 默认值时，它取决于 PostgreSQL 版本：在 11 或更高版本中，不会立即重写所有行，因此它应该与 NULL 情况一样快。但是对于版本 10 或更早的版本，该表被完全重写，因此根据表的大小，它可能会非常昂贵。
回答by alexius
Adding new null column will lock the table for very very short time since no need to rewrite all data on disk. While adding column with default value requires PostgreSQL to make new versions of all rows and store them on the disk. And during that time table will be locked.
添加新的空列会将表锁定非常短的时间，因为无需重写磁盘上的所有数据。添加具有默认值的列需要 PostgreSQL 制作所有行的新版本并将它们存储在磁盘上。并且在那个时间表将被锁定。
So when you need to add column with default value to big table it's recommended to add null value first and then update all rows in small portions. This way you'll avoid high load on disk and allow autovacuum to do it's job so you'll not end up doubling table size.
因此，当您需要将具有默认值的列添加到大表时，建议先添加空值，然后小部分更新所有行。通过这种方式，您将避免磁盘上的高负载并允许 autovacuum 完成它的工作，因此您最终不会将表大小增加一倍。
回答by Leo
http://www.postgresql.org/docs/current/static/sql-altertable.html#AEN57290
http://www.postgresql.org/docs/current/static/sql-altertable.html#AEN57290
"Adding a column with a non-null default or changing the type of an existing column will require the entire table and indexes to be rewritten."
“添加具有非空默认值的列或更改现有列的类型将需要重写整个表和索引。”
So the documentation only specifies when the table is not rewritten. There will always be a lock, but it will be very short in case the table is not to be rewritten.
因此文档仅指定何时不重写表。总会有一个锁，但它会很短，以防表不被重写。
