postgresql - funciones - ¿La adición de una columna nula a una tabla de postgres provoca un bloqueo?

procedimientos almacenados en postgresql

Question

Creo que leí en alguna parte que ejecutar un ALTER TABLE foo ADD COLUMN baz text en una base de datos de postgres no causará un bloqueo de lectura o escritura. Establecer un valor predeterminado provoca el bloqueo, pero permitir un valor predeterminado nulo evita un bloqueo.

Aunque no puedo encontrar esto en la documentación. ¿Alguien puede señalar un lugar que diga, definitivamente, si esto es cierto o no?

Answer 1

Agregar una nueva columna nula bloqueará la tabla por muy poco tiempo ya que no es necesario volver a escribir todos los datos en el disco. Mientras que agregar una columna con un valor predeterminado, PostgreSQL debe crear nuevas versiones de todas las filas y almacenarlas en el disco. Y durante ese horario la mesa quedará bloqueada.

Por lo tanto, cuando necesite agregar una columna con el valor predeterminado a la tabla grande, se recomienda agregar primero el valor nulo y luego actualizar todas las filas en porciones pequeñas. De esta manera, evitará una gran carga en el disco y permitirá que Autovacuum haga su trabajo para no terminar duplicando el tamaño de la tabla.

Answer 2

Los diferentes tipos de bloqueos y cuando se usan se mencionan en el documento en Bloqueos de nivel de tabla . Por ejemplo, ALTER TABLE Postgres 11 puede adquirir un bloqueo SHARE UPDATE EXCLUSIVE , SHARE ROW EXCLUSIVE o ACCESS EXCLUSIVE .

Postgres 9.1 a 9.3 afirmó que admite dos de los tres anteriores, pero en realidad obligó a Access Exclusive para todas las variantes de este comando . Esta limitación se eliminó en Postgres 9.4 pero ADD COLUMN permanece en ACCESS EXCLUSIVE por diseño.

Es fácil verificar el código fuente porque hay una función dedicada a establecer el nivel de bloqueo necesario para este comando en varios casos: AlterTableGetLockLevel en src/backend/commands/tablecmds.c .

Con respecto a cuánto tiempo se mantiene el bloqueo , una vez adquirido:

• Cuando el valor predeterminado de la columna es NULL, la adición de la columna debe ser muy rápida porque no necesita una reescritura de la tabla: solo es una actualización en el catálogo.
• Cuando la columna tiene un valor predeterminado que no es NULL, depende de la versión de PostgreSQL: con la versión 11 o más reciente, no hay una reescritura inmediata de todas las filas, por lo que debería ser tan rápido como el caso NULL. Pero con la versión 10 o más antigua, la tabla se reescribe completamente, por lo que puede ser bastante costosa dependiendo del tamaño de la tabla.

Answer 3

http://www.postgresql.org/docs/current/static/sql-altertable.html#AEN57290

"Agregar una columna con un valor predeterminado no nulo o cambiar el tipo de una columna existente requerirá que toda la tabla y los índices se vuelvan a escribir".

Así que la documentación solo especifica cuando la tabla no se vuelve a escribir. Siempre habrá un bloqueo, pero será muy corto en caso de que la tabla no se vuelva a escribir.