Tengo una tabla en mi PostgreSQL (en realidad, sus tablas múltiples, pero por simplicidad, supongamos que es solo una) y varios clientes que periódicamente necesitan consultar la tabla para encontrar elementos modificados. Estos son elementos actualizados o insertados (los elementos eliminados se manejan primero marcándolos para su eliminación y luego eliminándolos después de un período de gracia).
Ahora, la solución obvia sería simplemente mantener una columna de marca de tiempo "modificada" para cada fila, recordarla para cada cliente y luego simplemente buscar las modificadas.
SELECT * FROM the_table WHERE modified > saved_modified_timestamp;
La columna modificada se mantendría actualizada usando disparadores como:
CREATE FUNCTION update_timestamp() RETURNS trigger LANGUAGE 'plpgsql' AS $$ BEGIN NEW.modified = NOW(); RETURN NEW; END; $$; CREATE TRIGGER update_timestamp_update BEFORE UPDATE ON the_table FOR EACH ROW EXECUTE PROCEDURE update_timestamp(); CREATE TRIGGER update_timestamp_insert BEFORE INSERT ON the_table FOR EACH ROW EXECUTE PROCEDURE update_timestamp();
El problema obvio aquí es que NOW()
es el momento en que comenzó la transacción. Por lo tanto, puede suceder que una transacción aún no se confirme al obtener las filas actualizadas y, cuando se confirme, la marca de tiempo sea menor que la marca de tiempo de modificación guardada, por lo que la actualización nunca se registra.
Creo que encontré una solución que funcionaría y quería ver si puede encontrar algún defecto con este enfoque.
La idea básica es usar xmin (o más bien txid_current()
) en lugar de la marca de tiempo y luego, al buscar los cambios, envolverlos en una transacción explícita con REPEATABLE READ
y leer txid_snapshot()
(o más bien los tres valores que contiene txid_snapshot_xmin()
, txid_snapshot_xmax()
, txid_snapshot_xip()
) de la transacción.
Si leo la documentación de postgres correctamente, entonces todos los cambios realizados en las transacciones que son < txid_snapshot_xmax()
y no en txid_snapshot_xip()
deben devolverse en esa transacción de recuperación. Esta información debería ser todo lo que se requiere para obtener todas las filas de actualización al volver a buscar. La selección se vería así, con xmin_version
reemplazando la columna modified
:
SELECT * FROM the_table WHERE xmin_version >= last_fetch_txid_snapshot_xmax OR xmin_version IN last_fetch_txid_snapshot_xip;
Los factores desencadenantes serían simplemente así:
CREATE FUNCTION update_xmin_version() RETURNS trigger LANGUAGE 'plpgsql' AS $$ BEGIN NEW.xmin_version = txid_current(); RETURN NEW; END; $$; CREATE TRIGGER update_timestamp_update BEFORE UPDATE ON the_table FOR EACH ROW EXECUTE PROCEDURE update_xmin_version(); CREATE TRIGGER update_timestamp_update_insert BEFORE INSERT ON the_table FOR EACH ROW EXECUTE PROCEDURE update_xmin_version();
¿Funcionaría esto? ¿O me estoy perdiendo algo?
Gracias por la aclaración sobre el retorno de 64 bits de txid_current()
y cómo pasa la época. Lamento haber confundido ese contador de época con la época del tiempo.
No puedo ver ninguna falla en su enfoque, pero verificaría a través de la experimentación que tener varias sesiones de clientes al mismo tiempo en transacciones de lectura repetibles que toman la instantánea txid_snapshot_xip()
no causa ningún problema.
No usaría este método en la práctica porque asumo que el código del cliente deberá resolver el manejo de lecturas duplicadas del mismo cambio (insertar/actualizar/eliminar), así como reconciliaciones periódicas entre el contenido de la base de datos y el conjunto de trabajo del cliente para manejar deriva debido a fallas de comunicación o caídas del cliente. Una vez que se escribe ese código, usar now()
en la tabla de seguimiento del cliente, clock_timestamp()
en los activadores y una superposición de intervalo de gracia cuando el cliente extrae conjuntos de cambios funcionaría para los casos de uso que he encontrado.
Si los requisitos requirieran una integridad en tiempo real más sólida que esa, entonces recomendaría una estrategia de confirmación distribuida.
Ok, lo he probado en profundidad ahora y no he encontrado ningún defecto hasta ahora. Tenía alrededor de 30 clientes escribiendo y leyendo en la base de datos al mismo tiempo y todos ellos recibieron actualizaciones constantes. Así que supongo que este enfoque funciona.