Obtener la última fecha de modificación de una tabla de base de datos PostgreSQL

La guía paso a paso o código que hallarás en este artículo es la resolución más sencilla y efectiva que hallamos a esta duda o dilema.

Solución:

No existe un registro de autoría confiable de la última hora modificada de una tabla. Usar el relfilenode es incorrecto por muchas razones:

• Las escrituras se registran inicialmente en el registro del cabezal de escritura (WAL), luego perezosamente al montón (los archivos de la tabla). Una vez que el registro está en WAL, Pg no se apresura a escribirlo en el montón, y es posible que ni siquiera se escriba hasta el siguiente punto de control del sistema;

• Las tablas más grandes tienen varias bifurcaciones, tendría que comprobar todas las bifurcaciones y elegir la marca de tiempo más reciente;

• Un simple SELECT puede generar actividad de escritura en la tabla subyacente debido a la configuración del bit de sugerencia;

• la autovacunación y otros mantenimientos que no cambian los datos visibles del usuario aún modifican los archivos de relación;

• algunas operaciones, como vaccum full, sustituirá al relfilenode. Es posible que no esté donde espera si está tratando de mirarlo simultáneamente sin tomar un candado apropiado.

Algunas opciones

Si no necesita confiabilidad, potencialmente puede usar la información en pg_stat_database y pg_stat_all_tables. Estos pueden darle la hora del último restablecimiento de las estadísticas y las estadísticas de actividad. ya que el último restablecimiento de estadísticas. No le dice cuándo fue la actividad más reciente, solo que fue desde el último restablecimiento de las estadísticas, y no hay información sobre lo que sucedió antes de que se restablecieran las estadísticas. Entonces es limitado, pero ya está ahí.

Una opción para hacerlo de manera confiable es usar un disparador para actualizar una tabla que contiene los últimos tiempos modificados para cada tabla. Tenga en cuenta que al hacerlo serializará todas las escrituras en la tabla, destruyendo la concurrencia. También agregará un poco de gastos generales a cada transacción. No lo recomiendo.

Una alternativa un poco menos espantosa es utilizar LISTEN y NOTIFY. Haga que un proceso demonio externo se conecte a PostgreSQL y LISTEN para eventos. Usar ON INSERT OR UPDATE OR DELETE disparadores para enviar NOTIFYs cuando una tabla cambia, con la tabla oid como la carga útil de notificación. Estos se envían cuando se confirma la transacción. Su demonio puede acumular notificaciones de cambios y escribirlas perezosamente en una tabla en la base de datos. Si el sistema falla, pierde su registro de las modificaciones más recientes, pero está bien, simplemente trata todas las tablas como recién modificadas si está iniciando después de una falla.

Para evitar el peor de los problemas de simultaneidad, puede registrar las marcas de tiempo de cambio usando un before insert or update or delete or truncate on tablename for each statement execute disparador, generalizado para tomar la relación oid como parámetro. Esto insertaría un (relation_oid, timestamp) emparejar en una tabla de registro de cambios. Luego, tiene un proceso auxiliar en una conexión separada, o su aplicación lo llama periódicamente, agrega esa tabla para obtener la información más reciente, la combina en una tabla de resumen de los cambios más recientes y trunca la tabla de registro. La única ventaja de esto sobre el enfoque de escuchar / notificar es que no pierde información en caso de bloqueo, pero también es menos eficiente.

Otro enfoque podría ser escribir una función de extensión C que use (p. Ej.) ProcessUtility_hook, ExecutorRun_hook, etc. para capturar cambios en la tabla y actualizar estadísticas de forma perezosa. No he mirado para ver qué tan práctico sería esto; eche un vistazo a las diversas opciones de _hook en las fuentes.

La mejor manera sería parchear el código de estadísticas para registrar esta información y enviar un parche a PostgreSQL para su inclusión en el núcleo. No se limite a empezar escribiendo código; plantee su idea en -hackers una vez que lo haya pensado lo suficiente como para tener una forma bien definida de hacerlo (es decir, comience por leer el código, no publique simplemente preguntando “cómo puedo …”). Sería bueno agregar horas de última actualización a pg_stat_..., pero tendría que convencer a la comunidad de que valió la pena la sobrecarga o proporcionar una forma de hacer un seguimiento opcional, y tendrías que escribir el código para mantener las estadísticas y enviar un parche, porque solo alguien que quiera esta función se molestará con eso.

Como lo haría

Si tuviera que hacer esto y no tuviera tiempo para escribir un parche para hacerlo correctamente, probablemente usaría el enfoque de escuchar / notificar descrito anteriormente.

Actualización para las marcas de tiempo de confirmación de PostgreSQL 9.5

Actualizar: PostgreSQL 9.5 tiene cometer marcas de tiempo. Si los tiene habilitados en postgresql.conf (y también lo hizo en el pasado), puede verificar la marca de tiempo de confirmación de la fila con la mayor xmin para aproximado la última hora modificada. Es solo una aproximación porque si se han eliminado las filas más recientes, no se contarán.

Además, los registros de marca de tiempo de confirmación solo se mantienen durante un tiempo limitado. Entonces, si desea saber cuándo se modifica una tabla que no se ha modificado mucho, la respuesta será “no sé, hace un tiempo”.

PostgreSQL 9.5 nos permite rastrear la última confirmación modificada.

1. Compruebe que la confirmación de seguimiento está activada o desactivada mediante la siguiente consulta

   show track_commit_timestamp;
   

2. Si devuelve “ON”, vaya al paso 3; de lo contrario, modifique postgresql.conf

   cd /etc/postgresql/9.5/main/
   vi postgresql.conf
   

   Cambio

   track_commit_timestamp = off
   

   para

   track_commit_timestamp = on
   

3. Reinicie el servidor PostgreSQL

4. Repita el paso 1.

5. Utilice la siguiente consulta para realizar un seguimiento de la última confirmación

   SELECT pg_xact_commit_timestamp(xmin), * FROM  YOUR_TABLE_NAME;
   
   SELECT pg_xact_commit_timestamp(xmin), * FROM YOUR_TABLE_NAME where COLUMN_NAME=VALUE;
   

Sí, se puede esperar que esto se comporte: los datos sobre los cambios se almacenan en el registro de transacciones de inmediato. Los archivos de datos se pueden actualizar con checkpoint_timeout delay (el valor predeterminado es 5 minutos). Postgres no se mantiene de forma permanente en cualquier momento que solicite.

Si posees algún titubeo o forma de progresar nuestro artículo te evocamos dejar una crónica y con mucho gusto lo observaremos.

---

---

Utiliza Nuestro Buscador