Aquí hay algunas preguntas para comenzar:
- ¿Pueden los rangos contener huecos en los valores?
- ¿Por qué necesitamos la notación de índice para el tipo json?
- ¿Puede un índice crecer menos que una tabla con actualizaciones frecuentes? ¿Y no crecer en absoluto?
- ¿Cuánto tiempo han estado inactivas las sesiones en idle_in_transaction?
- ¿Cómo construir un diagrama ER para tablas de catálogo del sistema?
Aplicaciones de cliente
psql: \ dX - ver estadísticas extendidas
commit: ad600bba
Nuevo comando \ dX muestra los objetos de estadísticas extendidas como una lista.
CREATE STATISTICS flights_from_to (dependencies, MCV)
ON departure_airport, arrival_airport
FROM flights;
\x
\dX
List of extended statistics -[ RECORD 1 ]+------------------------------------------------ Schema | bookings Name | flights_from_to Definition | departure_airport, arrival_airport FROM flights Ndistinct | Dependencies | defined MCV | defined
Para cada tipo de estadística (Dependencias, Ndistinto, MCV), solo se muestra el hecho de recopilación. Los valores en sí deben examinarse en pg_statistic_ext_data, que, por defecto, solo es accesible por superusuarios.
psql: \ dtS muestra las tablas de TOAST
confirmar: 7d80441d
Se podía ver una tabla de TOAST separada con el comando \ d antes. Sin embargo, no fue posible obtener una lista de tales tablas con el comando \ dt o \ dtS. La omisión se ha corregido, \ dtS ahora muestra las tablas TOAST ya que son tablas de servicio.
Pero hay una salvedad. Todas las tablas TOAST se encuentran en el esquema pg_toast, que es poco probable que se incluya en search_path . Por lo tanto, para obtener la lista, debe especificar la plantilla adecuada:
\dtS pg_toast.*165*
List of relations Schema | Name | Type | Owner ----------+----------------+-------------+---------- pg_toast | pg_toast_16529 | TOAST table | postgres pg_toast | pg_toast_16539 | TOAST table | postgres pg_toast | pg_toast_16580 | TOAST table | postgres
Se mejoró la finalización de la pestaña Psql para los comandos CLOSE, FETCH, MOVE y DECLARE
commit: 3f238b88 No se
requiere descripción adicional.
Documentación
Confirmación de revisión y edición de documentación
: 2a5862f0
Muchas personas notan que PostgreSQL tiene una excelente documentación. Pero está escrito por los propios desarrolladores, que generalmente no se consideran maestros de la pluma. ¿Cómo se las arregla para mantener una alta calidad? Es sencillo. Al igual que con cualquier escritura, necesita editores y correctores de pruebas. Así que Justin Prizzby ha estado haciendo un trabajo enorme e importante durante los últimos dos años: corregir la documentación. El resultado es una gran lista de 18 parches. Y Mikael Paquier, como comprometido, lo ayudó.
Y ese es solo un gran compromiso. Y la cantidad de pequeños parches que mejoran la documentación simplemente no se puede contar.
miscelánea
Parámetro Idle_session_timeout - forzar la terminación de las sesiones inactivas
commit: 9877374b El
nuevo parámetro idle_session_timeout especifica el tiempo de espera de la sesión inactiva. Si se excede el límite de tiempo establecido, la sesión finalizará. El parámetro es muy similar al idle_in_transaction_session_timeout ,que apareció en la versión 9.6 , pero solo afecta a las sesiones en las que no hay una transacción iniciada. Por lo tanto, si desea interrumpir las sesiones inactivas, independientemente de si se inicia una transacción en ellas o no, debe configurar ambos parámetros.
Se recomienda que este parámetro se utilice con extrema precaución en sistemas que utilizan extractores de conexión o conexiones postgres_fdw.
Cualquier usuario puede configurar el parámetro para su sesión. En el siguiente ejemplo, después de configurar el parámetro y esperar un segundo, vemos que aparece una entrada de terminación de sesión en el registro del servidor. Después de eso, el intento de ejecutar la solicitud falla, pero psql establece automáticamente una nueva conexión:
SET idle_session_timeout = '1000ms';
--
\! tail -n 1 logfile
2021-02-01 12:25:06.716 MSK [5262] FATAL: terminating connection due to idle-session timeout
SHOW idle_session_timeout;
FATAL: terminating connection due to idle-session timeout server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded.
SHOW idle_session_timeout;
idle_session_timeout ---------------------- 0
Descripción por depesz.
Información de GSS en el mensaje de registro del servidor
: dc11f31a
El mensaje de conexión de la nueva sesión en el registro del servidor se ha actualizado con información de GSS si se utilizó este método de autenticación.
pageinspect: funciones para índices GiST
commit: 756ab291
Para cualquier persona interesada en explorar la organización y el almacenamiento de los índices GiST, la extensión pageinspect ofrece nuevas características .
Corregir el comportamiento EXPLAIN en los comandos con la
confirmación SI NO EXISTE : e665769e
Un intento de crear una tabla existente con la opción SI NO EXISTE da como resultado una advertencia:
CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
NOTICE: relation "tickets" already exists, skipping
Sin embargo, obtener un plan para tal comando conduce a resultados inesperados. Es solo que EXPLAIN genera un plan para SELECT, que el comando logra construir antes de verificar la existencia de la tabla de tickets. ¡Y sin advertencias!
EXPLAIN CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
QUERY PLAN ----------------------------------------------------------------- Seq Scan on bookings (cost=0.00..4301.88 rows=262788 width=21)
EXPLAIN ANALYZE falla en lugar de advertir:
EXPLAIN ANALYZE CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
ERROR: relation "tickets" already exists
En la versión 14, el comportamiento se volvió predecible:
EXPLAIN CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
NOTICE: relation "tickets" already exists, skipping QUERY PLAN ------------ (0 rows)
EXPLAIN ANALYZE CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
NOTICE: relation "tickets" already exists, skipping QUERY PLAN ------------ (0 rows)
Los mismos cambios para el comando EXPLAIN [ANALYZE] CREATE MATERIALIZED VIEW IF NOT EXISTS.
Se agregaron claves primarias y únicas a las tablas del catálogo del sistema
: dfb75e47 , 62f34097
Se agregaron restricciones de integridad a las tablas del catálogo del sistema: claves primarias y únicas. Antes había índices únicos, ahora las restricciones se basan en ellos.
Así es como se ve:
\d pg_class
Table "pg_catalog.pg_class" Column | Type | Collation | Nullable | Default ---------------------+--------------+-----------+----------+--------- oid | oid | | not null | relname | name | | not null | relnamespace | oid | | not null | ... ... Indexes: "pg_class_oid_index" PRIMARY KEY, btree (oid) "pg_class_relname_nsp_index" UNIQUE CONSTRAINT, btree (relname, relnamespace) "pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)
Pero hay excepciones: pg_depend, pg_shdepend. Estas dos tablas tienen dos índices y ambos no son únicos. No fue posible encontrar una combinación única de columnas. Debo admitir que comprender cómo funciona la tabla de dependencia no es una tarea trivial. Y la imposibilidad de crear una clave primaria es una clara confirmación de esto.
Pero no se agregaron claves externas. Hay buenas razones para esto:
- Para varias tablas, las columnas de OID pueden ser 0 cuando no hay un OID real al que hacer referencia. Para crear una clave externa, deberá reemplazar 0 con NULL en todas partes, y este es un gran trabajo de reescritura que no está listo para realizar.
- Varias tablas tienen una columna con una clave foránea potencial no solo de tipo oid, sino oid []. Es imposible crear una clave externa a partir de una matriz.
La idea original del parche era construir un diagrama de las relaciones entre las tablas en el catálogo del sistema basado en información de la base de datos. Esto podría hacerse automáticamente mediante herramientas externas. Después de todo, la construcción de un diagrama sin información sobre claves externas solo se puede hacer manualmente y con cambios regulares después de cada lanzamiento, lo cual es extremadamente inconveniente.
Al darse cuenta de que la tarea del parche no estaba completa, inmediatamente después de la confirmación, Tom Lane inició una nueva discusión en la que propuso una solución de compromiso en forma de una función que devuelve una lista de claves foráneas para todas las tablas en el catálogo del sistema. El parche se adoptó después del cierre del commitfest de enero, pero es más lógico describirlo ahora.
Por tanto, las tablas del catálogo del sistema no tienen claves foráneas. Pero podemos obtener información sobre ellos llamando a la función pg_get_catalog_foreign_keys. La siguiente consulta muestra quién se refiere a pg_attribute:
SELECT fktable, fkcols, is_array, is_opt
FROM pg_get_catalog_foreign_keys()
WHERE pktable = 'pg_attribute'::regclass
AND pkcols = ARRAY['attrelid','attnum'];
fktable | fkcols | is_array | is_opt ----------------------+-----------------------+----------+-------- pg_attrdef | {adrelid,adnum} | f | f pg_constraint | {conrelid,conkey} | t | t pg_constraint | {confrelid,confkey} | t | f pg_index | {indrelid,indkey} | t | t pg_statistic_ext | {stxrelid,stxkeys} | t | f pg_statistic | {starelid,staattnum} | f | f pg_trigger | {tgrelid,tgattr} | t | f pg_partitioned_table | {partrelid,partattrs} | t | t (8 rows)
Supervisión
Parámetro Log_recovery_conflict_waits: registro de esperas largas para resolver conflictos de recuperación
cometer: 0650ff23
Cuando se habilita el nuevo parámetro log_recovery_conflict_waits , la espera para la resolución del conflicto de recuperación mediante el proceso de inicio se registrará en el registro del servidor si el tiempo de espera excede deadlock_timeout.
Simulemos la situación. En la réplica, habilite el parámetro, luego inicie la transacción y espere:
ALTER SYSTEM SET log_recovery_conflict_waits = on;
SELECT pg_reload_conf();
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM t;
Y ahora sobre el maestro:
DELETE FROM t;
VACUUM t;
Después de un segundo ( deadlock_timeout ), aparecerá un mensaje detallado que describe el conflicto en el registro de réplica. Aquí está el número del proceso en conflicto y la posición del LSN donde se atascaron, y el nombre del archivo (leer tablas) con el número de bloque:
LOG: recovery still waiting after 1023.267 ms: recovery conflict on snapshot DETAIL: Conflicting process: 29119. CONTEXT: WAL redo at 0/1001BEB0 for Heap2/CLEAN: latestRemovedXid 717; blkref #0: rel 1663/16384/17198, blk 0
Después de otros 30 segundos de espera ( max_standby_streaming_delay ), la sesión en la réplica terminará, como debería ser en tales casos.
Esta es una continuación del trabajo iniciado y descrito en el commitfest anterior.
Vista pg_stat_database: estadísticas agregadas sobre las sesiones de los usuarios
confirmadas: 960869da Los
desarrolladores del sistema de monitoreo tienen más trabajo por hacer. ¡Los usuarios de los sistemas de monitoreo esperan cambios útiles y geniales!
Han aparecido muchas columnas en pg_stat_database con información adicional sobre las sesiones de los usuarios en cada base de datos del clúster:
- session_time - tiempo total de todas las sesiones pasadas en esta base de datos;
- active_time ― , ;
- idle_in_transaction_time ― ;
- sessions ― ;
- sessions_abandoned ― - ;
- sessions_fatal ― - FATAL;
- sessions_killed ― .
Descripción por depesz.
ps: actualización del estado de los procesos cuando se ejecuta un punto de control
commit: df9274ad Puede
monitorear los procesos de inicio y checkpointer consultando pg_stat_activity. Pero hay tres situaciones en las que la vista pg_stat_activity no está disponible y el puntero de verificación funciona. Estos son el punto de control al final del proceso de recuperación de fallos, el punto de control durante la parada del servidor y el punto de reinicio durante la parada de la réplica.
En estas tres situaciones, puede monitorear el estado de los procesos de inicio y puntero de control en el sistema operativo, por ejemplo, usando la utilidad ps.
Un ejemplo típico es la recuperación ante desastres. Al final, después de renovar los cambios de WAL, el proceso de inicio realiza un punto de control y esto puede llevar algún tiempo. Sin embargo, el estado del proceso de inicio no cambia y muestra "recuperando NNN". Aunque sería útil saber que el roll-forward de cambios está completo y queda esperar a que se complete el checkpoint. El estado ahora se actualiza para reducir el nivel de alerta del DBA en caso de emergencia.
pg_stat_statements: Cuando se eliminaron las estadísticas de
confirmación: 2e0fedf0
No hay duda de que las estadísticas de pg_stat_statements se deben eliminar con regularidad. Si no, de qué sirve acumular información sobre las solicitudes ejecutadas ayer, hace una semana, un mes, un año ...
Pero, ¿cómo saber cuándo se restablecieron las estadísticas por última vez? Muy simple. Analizamos pg_stat_statements_info:
SELECT now(), pg_stat_statements_reset();
now | pg_stat_statements_reset -------------------------------+-------------------------- 2021-02-03 13:25:44.738188+03 |
SELECT * FROM pg_stat_statements_info;
dealloc | stats_reset ---------+------------------------------- 0 | 2021-02-03 13:25:44.738468+03
La vista pg_stat_statements_info se introdujo en la versión 14. Puede leer sobre la columna dealloc en el artículo anterior.
Descripción por depesz.
Aún más útil es la idea no solo de descargar estadísticas con regularidad, sino también de guardar el contenido de pg_stat_statements antes de cada volcado. Luego, en presencia de muchos cortes realizados regularmente, es posible obtener información para intervalos de tiempo en el pasado. Este enfoque es utilizado por la extensión de monitoreo pgpro_pwr . Progreso de confirmación de
COPIA
: 8a4f618e
La familia de vistas pg_stat_progress_ * se ha actualizado. Ahora puede monitorear el progreso del comando COPY.
Hagamos una copia lógica de la base de datos de demostración:
\! pg_dump -d demo -Fc -f demo.dump
Ahora, expandamos la copia en la base de datos de postgres en dos subprocesos y, mientras el proceso continúa, echemos un vistazo a la vista pg_stat_progress_copy:
\! pg_restore tickets.dump -d postgres -j 2 &
SELECT pid, datname, relid::regclass, bytes_processed, bytes_total, lines_processed
FROM pg_stat_progress_copy\gx
-[ RECORD 1 ]---+------------------------- pid | 18771 datname | postgres relid | bookings.tickets bytes_processed | 19088527 bytes_total | 0 lines_processed | 189820 -[ RECORD 2 ]---+------------------------- pid | 18772 datname | postgres relid | bookings.boarding_passes bytes_processed | 14833287 bytes_total | 0 lines_processed | 567652
La columna bytes_total se habría llenado con el tamaño del archivo cuando se ejecutó el comando COPY ... FROM 'file'. Pero en el ejemplo anterior, la descarga es de una copia de pg_dump, por lo que no se conoce el tamaño.
El estado se puede monitorear no solo para descargar (COPY ... FROM), sino también para descargar (COPY ... TO) datos.
Descripción por depesz.
Actuación
Optimización del vaciado de la
confirmación de la caché del búfer : d6ad34f3 , bea449c6
Varias operaciones requieren que todos los búferes asociados con una tabla en particular se eliminen de la caché del búfer. Estas operaciones incluyen los comandos TRUNCATE y DROP table, un comando CREATE TABLE AS SELECT interrumpido y VACUUM cuando es necesario eliminar bloques vacíos del final de una tabla.
Para eliminarlo, se analiza todo el caché del búfer, lo que puede resultar costoso con tamaños de caché grandes. Ahora, para las tablas pequeñas, se mantendrá una estructura especial en la memoria con información sobre los búferes ocupados, lo que evitará escanear todo el caché del búfer.
Las pruebas han demostrado que con shared_buffers de 100 GB o más, truncar miles de tablas es más de 100 veces más rápido.
Esta es una continuación del trabajo iniciado en la versión 13.
postgres_fdw: modo por lotes para insertar registros de
confirmación: b663a413 Obtención de
datos de tablas externas postgres_fdw usa el modo por lotes. Los registros se transfieren desde el servidor externo en lotes de 100 (el valor predeterminado del parámetro fetch_size ). Esto es significativamente más rápido que enviarlos uno a la vez. Pero inserte, cambie, elimine el trabajo línea por línea. Y por tanto muy lentamente.
La API de FDW se ha mejorado para su optimización. Es cierto que solo se mejoró en parte del modo por lotes para operaciones de inserción. Las actualizaciones y eliminaciones son para referencia futura. Por supuesto, postgres_fdw fue el primer contenedor en aprovechar la nueva API.
Vamos a ver que pasó. Configure postgres_fdw para trabajar con tablas externas en la base de datos de demostración:
CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'postgres');
CREATE USER MAPPING FOR postgres
SERVER remote_server
OPTIONS (user 'postgres');
La tabla externa se ubicará en la base de datos de postgres vecina:
postgres=# CREATE TABLE bookings(
book_ref char(6),book_date timestamptz, total_amount numeric(10,2)
);
Tomemos la velocidad de inserción en la tabla local como velocidad de referencia. Y habilite el tiempo para las mediciones:
CREATE TABLE bookings_local (LIKE bookings);
\timing
Insertar en una tabla local:
INSERT INTO bookings_local SELECT * FROM bookings;
INSERT 0 262788 Time: 165,653 ms
Y ahora insertar en una tabla externa, el modo por lotes está deshabilitado. (En realidad está habilitado, solo que por defecto el tamaño del lote es 1 línea).
CREATE FOREIGN TABLE bookings_remote_no_batch (
book_ref char(6), book_date timestamptz, total_amount numeric(10,2)
) SERVER remote_server OPTIONS (
schema_name 'public', table_name 'bookings'
);
INSERT INTO bookings_remote_no_batch SELECT * FROM bookings;
INSERT 0 262788 Time: 6729,867 ms (00:06,730)
¡Casi 40 veces más lento! Y esto es entre las bases del mismo cluster, donde no hay retrasos en la red.
Repitamos el experimento, pero establezcamos el tamaño del lote (batch_size) en 100.
CREATE FOREIGN TABLE bookings_remote_batch_100 (
book_ref char(6), book_date timestamptz, total_amount numeric(10,2)
) SERVER remote_server OPTIONS (
schema_name 'public', table_name 'bookings',
batch_size '100'
);
INSERT INTO bookings_remote_batch_100 SELECT * FROM bookings;
INSERT 0 262788 Time: 679,632 ms
Otro asunto muy distinto. Por supuesto, la pérdida de inserción local todavía es notable, ~ 4 veces, ¡pero aún no 40!
Y finalmente. El tamaño del lote para la inserción (batch_size) podría establecerse en los parámetros del servidor externo, entonces sería válido para todas las tablas en las que no esté definido explícitamente.
Eliminando filas de índice de abajo hacia arriba
confirmado: 9dc718bd , d168b666
Esta optimización intenta evitar dividir la página de índice por dos en las operaciones de ACTUALIZAR hasta la última, en situaciones donde las columnas de índice no han cambiado. Antes de agregar una nueva versión de una fila al índice, debe ver si puede eliminar filas innecesarias en esta página. Por ejemplo, si encuentra una cadena de filas de índice duplicadas innecesarias que hacen referencia a la misma fila de la tabla, puede eliminar esas filas. Peter Geigan, el autor del parche, llamó a esto "eliminación de abajo hacia arriba".
Un problema similar (para evitar el crecimiento del índice) se resuelve mediante la optimización de la actualización HOT. Si la ACTUALIZACIÓN no cambia ninguna de las columnas indexadas, es posible que no se creen nuevas versiones de las filas en los índices. ¿Y si hay varios índices en la tabla y la columna de solo uno de ellos cambia? En este caso, HOT Update no es un asistente.
Veamos qué puede hacer "eliminar de abajo hacia arriba". Para el experimento, tomemos una tabla con dos columnas indexadas por separado y la limpieza automática deshabilitada.
CREATE TABLE t(col1 int, col2 int) WITH (autovacuum_enabled=off);
CREATE INDEX t_col1 ON t(col1);
CREATE INDEX t_col2 ON t(col2);
INSERT INTO t VALUES (1, 1);
SELECT pg_relation_size('t') AS t_size,
pg_relation_size('t_col1') AS t_col1_size,
pg_relation_size('t_col2') AS t_col2_size;
t_size | t_col1_size | t_col2_size --------+-------------+------------- 8192 | 16384 | 16384
Hay una fila en la tabla antes de la actualización masiva. El tamaño de la tabla es de una página y ambos índices ocupan dos páginas (página de servicio + página de datos).
Ahora cambiamos solo una columna col2 100,000 veces y miramos el tamaño de la tabla y los índices.
SELECT 'UPDATE t SET col2 = col2+1' FROM generate_series(1,100000)\gexec
SELECT pg_relation_size('t') AS t_size,
pg_relation_size('t_col1') AS t_col1_size,
pg_relation_size('t_col2') AS t_col2_size;
t_size | t_col1_size | t_col2_size ---------+-------------+------------- 2818048 | 2121728 | 2260992
Estos resultados se obtuvieron en PostgreSQL 12. Como puede ver, la actualización HOT no funcionó y ambos índices crecieron casi por igual en tamaño.
Ahora, este mismo experimento en PostgreSQL 13:
t_size | t_col1_size | t_col2_size ---------+-------------+------------- 2818048 | 663552 | 2260992
El índice t_col1, en el que no hubo cambios, aumentó mucho menos, ~ 3,5 veces. Este es el resultado de una famosa optimización de la versión 13: la deduplicación de índices . Sin embargo, creció.
Y finalmente, veamos qué hay en PostgreSQL 14:
t_size | t_col1_size | t_col2_size ---------+-------------+------------- 2818048 | 16384 | 2260992
¡Caray! En el índice t_col1, solo queda una página con datos. ¡Eso es genial!
Por supuesto, si se activara el autovacío, podría tener tiempo para despejar algunas de las líneas muertas durante el experimento. Pero para eso es el experimento. Además, en condiciones reales, con actualizaciones frecuentes (un gran ejemplo son las tablas de cola), el autovacío definitivamente no tendrá tiempo para limpiar todo a tiempo.
Descripción de Viktor Egorov.
Ejecución en paralelo REINDEX CONCURRENTLY
commit: f9900df5
En el artículo del commitfest de noviembre, ya escribí sobre la ejecución paralela sin bloqueo de CREATE INDEX CONCURRENTLY. Ahora hay disponible una optimización similar para REINDEX CONCURRENTEMENTE.
Lenguajes de procedimiento
Los procedimientos son más rápidos para ejecutar el
compromiso: ee895a65 Los
procedimientos fueron concebidos para poder completar transacciones. Si un procedimiento de este tipo que realiza COMMIT se llama muchas veces, por ejemplo, en un bucle, en cada iteración del bucle, se volverán a analizar todas las declaraciones dentro del procedimiento.
No había ninguna razón importante para volver a analizar los comandos, que se eliminó en el parche. Ahora, llamar a procedimientos en un bucle requiere menos trabajo y recursos. Y, como resultado, se ejecuta más rápido.
PL / pgSQL:
confirmación del operador de asignación completamente rediseñado : 844fe9f1 , c9d52984 , 1788828d , 1c1cbe27
Sin más preámbulos:
DO $$
<<local>>
DECLARE
a bookings[];
x bookings;
BEGIN
/* */
local.a[1].book_ref := 'ABCDEF';
local.a[1].book_date := current_date;
local.a[1].total_amount := 0;
/* */
local.a[2:3] := (SELECT array_agg(t.*)
FROM (SELECT b.* FROM bookings b LIMIT 2) AS t
);
FOREACH x IN ARRAY a LOOP
RAISE NOTICE '%', x;
END LOOP;
END;
$$;
NOTICE: (ABCDEF,"2021-02-04 00:00:00+03",0.00) NOTICE: (00000F,"2017-07-05 03:12:00+03",265700.00) NOTICE: (000012,"2017-07-14 09:02:00+03",37900.00) DO
Ahora, dentro de un bloque PL / pgSQL, puede asignar valores a elementos de matriz de un tipo compuesto, así como a sectores de matriz.
Con este fin, el operador de asignación PL / pgSQL se ha rediseñado por completo. Y el analizador del servidor ha aprendido a analizar expresiones PL / pgSQL.
Para evaluar una expresión, ya no necesita emitir un comando como
«
SELECT expr
»
. Puede verificar esto fácilmente mirando el mensaje de error en el siguiente ejemplo:
DO $$ BEGIN RAISE NOTICE '%', 2 + 'a'; END; $$;
ERROR: invalid input syntax for type integer: "a" LINE 1: 2 + 'a' ^ QUERY: 2 + 'a' CONTEXT: PL/pgSQL function inline_code_block line 1 at RAISE
La palabra SELECT ya no está en la línea QUERY.
Replicación
Manejo de réplicas de cambios en los parámetros de configuración en la
confirmación del asistente : 15251c0a
Los cambios en la configuración del asistente que afectan el tamaño de la memoria compartida del servidor no pasan desapercibidos en las réplicas físicas. Cuando llega un registro WAL a la réplica que indica que estos parámetros han cambiado, la réplica se detendrá automáticamente y finalizará todas las sesiones actuales. La lista de parámetros se puede encontrar en la documentación .
No es muy bueno. Por lo tanto, hicimos esto: al recibir un registro de cambios en los parámetros, la réplica continúa funcionando, pero pausa la réplica y emite un mensaje al registro. El administrador puede esperar a que terminen las sesiones importantes, actualizar los parámetros en los archivos de configuración para que coincidan con los valores del maestro y reanudar la replicación. La réplica se detendrá inmediatamente después de eso, pero dado que los cambios de configuración ya se han realizado, se puede iniciar inmediatamente, minimizando el tiempo de inactividad.
Cambie restore_command sin reiniciar el servidor
commit: 942305a3
Continuación del trabajo de Sergei Kornilov, adoptado en la versión 13. Luego fue posible cambiar los parámetros sin reiniciar el servidor primary_conninfo , primary_slot_name y wal_receiver_create_temp_slot .
Ahora se les ha agregado restore_command .
Servidor
Uso mejorado de la
confirmación de estadísticas extendidas : 25a9e54d
Las estadísticas extendidas ahora se usan en más casos para evaluar la cardinalidad de las condiciones en las consultas. En particular, ahora se usarán estadísticas extendidas cuando se combinen diferentes condiciones para las que se pueden usar estadísticas extendidas individualmente a través de OR.
En el ejemplo, recopilaremos estadísticas ampliadas para los aeropuertos de salida y llegada. Y luego contaremos el número de vuelos entre Sheremetyevo y Pulkovo o en la dirección opuesta.
CREATE STATISTICS s ON departure_airport, arrival_airport FROM flights;
ANALYZE flights;
El número exacto de vuelos es 610. Compare con las estimaciones del planificador en las versiones 13 y 14.
EXPLAIN SELECT *
FROM flights
WHERE (departure_airport = 'SVO' AND arrival_airport = 'LED')
OR (departure_airport = 'LED' AND arrival_airport = 'SVO');
PostgreSQL 13:
Seq Scan on flights (cost=0.00..1054.42 rows=885 width=63)
PostgreSQL 14:
Seq Scan on flights (cost=0.00..1054.42 rows=607 width=63)
Como puede ver, la estimación en la versión 14 es casi precisa.
Infraestructura general para admitir la notación de índice para cualquier tipo de datos
confirmados: c7aba7c1 , 0ec5f7e7 , 676887a3 La
notación de índice se utiliza para trabajar con matrices. Por ejemplo, busquemos el elemento con índice 3:
SELECT (ARRAY[10,20,30,40,50])[3];
array ------- 30
Pero hay otros tipos de datos en los que sería conveniente utilizar esta sintaxis. Primero que nada, estamos hablando de json. Fue con la idea de respaldar la notación de índice para json que comenzó el largo viaje de trabajo de Dmitry Dolgov en este parche.
Y ahora, unos años después, apareció ese apoyo. El primer parche crea la infraestructura de notación de índices necesaria para tipos de datos arbitrarios. El segundo parche agrega notación de índice al tipo hstore y el tercero al tipo json b .
Ahora, en lugar de funciones y operadores especiales, puede extraer las partes necesarias del valor json. Busquemos el número de teléfono en los datos de contacto de una de las entradas:
SELECT contact_data, contact_data['phone'] AS phone
FROM tickets
WHERE ticket_no = '0005432000994'\gx
-[ RECORD 1 ]+----------------------------------------------------------------------------------------------------------------------------------------------------------- contact_data | {"email": "antonova.irina04121972@postgrespro.ru", "phone": "+70844502960"} phone | "+70844502960"
La notación de índice también se puede usar para escribir en jsonb. Agreguemos la dirección al contacto previamente encontrado de Irina Antonova:
UPDATE tickets
SET contact_data['address'] =
'{"city": "",
"street": " ",
"building": "7"
}'::jsonb
WHERE ticket_no = '0005432000994';
Tenga en cuenta que la dirección en sí es compuesta y también puede usar la notación de índice para referirse a partes de ella:
SELECT contact_data['address'] AS address,
contact_data['address']['city'] AS city,
contact_data['address']['street'] AS street,
contact_data['address']['building'] AS building,
contact_data['phone'] AS phone,
contact_data['email'] AS email
FROM tickets
WHERE ticket_no = '0005432000994'\gx
-[ RECORD 1 ]---------------------------------------------------------------- address | {"city": "", "street": " ", "building": "7"} city | "" street | " " building | "7" phone | "+70844502960" email | "antonova.irina04121972@postgrespro.ru"
¡Es muy conveniente!
(Aclaración. Todos los contactos en la base de datos de demostración son ficticios y no existe tal empleado en Postgres Pro.)
Descripción para hstore de depesz.
Comandos SQL
Confirmación de tipos de datos de rango múltiple
: 6df7a969
Cada tipo de datos de rango ahora tiene su propio tipo de datos de rango múltiple . Este tipo es esencialmente una matriz de rangos individuales. Los rangos dentro de un tipo multibanda no deben superponerse, pero puede haber espacios entre rangos.
Los rangos regulares son rangos contiguos de valores del subtipo correspondiente: rango in4range para el subtipo int, rango de marca de tiempo para el subtipo de marca de tiempo, etc. Pero, ¿qué sucede si necesita almacenar rangos con espacios en blanco en algunos lugares? Aquí es donde las multibanda vienen al rescate.
Digamos que queremos almacenar los tiempos del commitfest en la tabla para cada versión de PostgreSQL. Un solo commitfest puede considerarse como un período de un mes. Pero, ¿cómo representar los cinco compromisos de una versión?
El rango para el subtipo timestamptz se llama tstzrange y el multirango es tstzmultirange. Los tipos disponibles se describen en la documentación . Crea una tabla:
CREATE TABLE pg_commitfest (
version text,
working_period tstzmultirange
);
Para formar valores, usamos el constructor:
INSERT INTO pg_commitfest VALUES
('13', tstzmultirange(
tstzrange('2019-07-01', '2019-08-01', '[)'),
tstzrange('2019-09-01', '2019-10-01', '[)'),
tstzrange('2019-11-01', '2019-12-01', '[)'),
tstzrange('2020-01-01', '2020-02-01', '[)'),
tstzrange('2020-03-01', '2020-04-07', '[]')
)
),
('14', tstzmultirange(
tstzrange('2020-07-01', '2020-08-01', '[)'),
tstzrange('2020-09-01', '2020-10-01', '[)'),
tstzrange('2020-11-01', '2020-12-01', '[)'),
tstzrange('2021-01-01', '2021-02-01', '[)'),
tstzrange('2021-03-01', '2021-04-01', '[)')
)
);
La lista de funciones y operadores para trabajar con tipos multibanda incluye lo mismo que para las bandas normales, más los destinados a multibanda únicamente.
Por ejemplo, podemos averiguar en qué versión de PostgreSQL trabajó la comunidad de desarrolladores durante el último año nuevo:
SELECT version
FROM pg_commitfest
WHERE working_period @> '2021-01-01'::timestamptz;
version --------- 14
O fechas de inicio y finalización para trabajar en la versión 13:
SELECT lower(working_period), upper(working_period)
FROM pg_commitfest
WHERE version = '13';
lower | upper ------------------------+------------------------ 2019-07-01 00:00:00+03 | 2020-04-07 00:00:00+03
Se pueden crear nuevos tipos personalizados de rango múltiple. Esto es útil en los casos en los que no hay una banda incorporada y el tipo multibanda correspondiente. Se utiliza el mismo comando CREATE TYPE ... AS RANGE, en el que puede especificar un nombre para el tipo de rango múltiple generado automáticamente.
Por ejemplo, estamos interesados en rangos y múltiples rangos de tiempo, subtipo de tiempo. Para crear un rango, necesita una función que calcule la diferencia entre dos valores de tipo tiempo:
CREATE FUNCTION time_diff(a time, b time) RETURNS double precision
AS $$
SELECT extract(epoch FROM (a - b));
$$ LANGUAGE sql STRICT IMMUTABLE;
Creamos un tipo para el rango de tiempo y al mismo tiempo para el rango múltiple:
CREATE TYPE timerange AS RANGE (
subtype = time,
subtype_diff = time_diff,
multirange_type_name = timemultirange
);
Ahora el tiempo de trabajo se puede formar con la siguiente expresión:
SELECT timemultirange(
timerange('09:00', '13:00', '[)'),
timerange('14:00', '18:00', '[)')
) AS working_hours;
working_hours ------------------------------------------- {[09:00:00,13:00:00),[14:00:00,18:00:00)}
Descripción por depesz.
Funciones ltrim y rtrim para cadenas binarias
commit: a6cf3df4 También puede recortar
bytes al principio y al final de una cadena bytea al mismo tiempo usando la función btrim. Ahora puede recortar cada borde por separado con las nuevas funciones ltrim y rtrim para cadenas binarias.
GRANTED BY frase en los comandos GRANT y REVOKE
la confirmación: 6aaaa76b
Para compatibilidad con el estándar SQL en los comandos GRANT y REVOKE se agregó la frase opcional GRANTED BY. Por ejemplo:
GRANT ALL ON TABLE table_name TO role_specification
[GRANTED BY role_specification];
REVOKE ALL ON TABLE table_name FROM role_specification
[GRANTED BY role_specification];
El nombre del rol en GRANTED BY debe coincidir con el rol actual. Por lo tanto, no funcionará emitir / quitar derechos en nombre de otro rol. Se ha añadido la frase para cumplir con la norma.
Esta es una continuación del trabajo descrito en el artículo del commitfest de septiembre.
Administracion del sistema
initdb --no-instructions
commit: e09155bd
La utilidad initdb se utiliza para inicializar el clúster. Y al final de su trabajo, muestra una instrucción sobre cómo iniciar un clúster:
Success. You can now start the database server using: pg_ctl -D /usr/local/pgsql/data -l logfile start
Pero esto no siempre es verdad. Por ejemplo, en las distribuciones de paquetes de Debian, la utilidad pg_ctlcluster se usa para iniciar el clúster, no pg_ctl. Y sus parámetros son diferentes.
Con la nueva opción --no-instructions, initdb ya no proporcionará consejos de inicio, que las distribuciones de paquetes pueden aprovechar.
pg_dump: restaurar una sola partición como una tabla independiente
commit: 9a4c0e36 , 9eabfe30
Si se incluye una tabla particionada en la copia lógica de pg_dump, entonces no será posible restaurar una partición separada de dicha copia como una tabla independiente. Inmediatamente después del comando CREATE TABLE viene el comando ALTER TABLE… ATTACH PARTITION, que no solo es innecesario en tal situación, sino que también termina con un error. no restauramos la tabla principal.
CREATE TABLE t (id int) PARTITION BY RANGE(id);
CREATE TABLE t_p1 PARTITION OF t FOR VALUES FROM (1) TO (1001);
CREATE TABLE t_p2 PARTITION OF t FOR VALUES FROM (1001) TO (2001);
\! pg_dump -Fc -f db.dump
\! pg_restore db.dump -t t_p1 -f -
... CREATE TABLE public.t_p1 ( id integer ); ALTER TABLE ONLY public.t ATTACH PARTITION public.t_p1 FOR VALUES FROM (1) TO (1001); ...
Ahora los comandos ALTER TABLE… ATTACH PARTITION para todas las particiones se descargan por separado y después todos los comandos para crear particiones CREATE TABLE. Por lo tanto, al restaurar una sola partición especificada con la opción -t, solo se ejecutará el comando CREATE TABLE, lo que permite restaurar la partición como una tabla independiente.
Eso es todo por ahora. Estamos esperando el compromiso final del 14 de marzo .