34. Seguimiento del historial de ediciones usando triggers¶
Un requisito común para bases de datos en producción es la capacidad de rastrear el historial: ¿cómo han cambiado los datos entre dos fechas, quién hizo los cambios y dónde ocurrieron? Algunos sistemas GIS rastrean los cambios incluyendo gestión de cambios en la interfaz del cliente, pero eso agrega mucha complejidad a las herramientas de edición.
Usando la base de datos y el sistema de triggers, es posible añadir seguimiento de historial a cualquier tabla, mientras se mantiene un acceso simple de «edición directa» a la tabla principal.
El seguimiento de historial funciona manteniendo una tabla de historial que registra, para cada edición:
Si un registro fue creado, cuándo se añadió y por quién.
Si un registro fue eliminado, cuándo se eliminó y por quién.
Si un registro fue actualizado, añadiendo un registro de eliminación (para el estado antiguo) y un registro de creación (para el nuevo estado).
34.1. Usando TSTZRANGE¶
La tabla de historial utiliza una característica específica de PostgreSQL: el tipo «timestamp range» para almacenar el rango de tiempo en el cual un registro de historial fue el registro «activo». Se espera que todos los rangos de tiempo en la tabla de historial para una característica en particular no se superpongan, pero sí sean adyacentes.
El rango para un nuevo registro comenzará en now() y tendrá un extremo abierto, de modo que el rango cubra todo el tiempo desde el actual hacia el futuro.
SELECT tstzrange(current_timestamp, NULL);
tstzrange
------------------------------------
["2021-06-01 14:49:40.910074-07",)
De manera similar, el rango de tiempo para un registro eliminado se actualizará para incluir el tiempo actual como el extremo final del rango de tiempo.
Buscar en rangos de tiempo es mucho más simple que buscar en un par de marcas de tiempo, debido a la forma en que un rango abierto de tiempo abarca todo el tiempo desde el inicio hasta el infinito. El operador «contains» @> para rangos es el que usaremos.
-- Does the range of "ten minutes ago to the future" include now?
-- It should! :)
--
SELECT tstzrange(current_timestamp - '10m'::interval, NULL) @> current_timestamp;
Los rangos pueden indexarse de manera muy eficiente usando un índice GIST, al igual que los datos espaciales, como mostraremos a continuación. Esto hace que las consultas de historial sean muy eficientes.
34.2. Construyendo la tabla de historial¶
Usando esta información es posible reconstruir el estado de la tabla de edición en cualquier punto en el tiempo. En este ejemplo, añadiremos seguimiento de historial a nuestra tabla nyc_streets.
Primero, añade una nueva tabla nyc_streets_history. Esta es la tabla que usaremos para almacenar toda la información histórica de edición. Además de todos los campos de nyc_streets, añadimos cinco campos más.
hid la clave primaria para la tabla de historial
created_by el usuario de base de datos que hizo que el registro fuera creado
deleted_by el usuario de base de datos que hizo que el registro fuera marcado como eliminado
valid_range el rango de tiempo dentro del cual el registro estuvo «activo»
Observa que en realidad no eliminamos ningún registro en la tabla de historial, solo marcamos el momento en que dejaron de ser parte del estado actual de la tabla de edición.
DROP TABLE IF EXISTS nyc_streets_history; CREATE TABLE nyc_streets_history ( hid SERIAL PRIMARY KEY, gid INTEGER, id FLOAT8, name VARCHAR(200), oneway VARCHAR(10), type VARCHAR(50), geom GEOMETRY(MultiLinestring,26918), valid_range TSTZRANGE, created_by VARCHAR(32), deleted_by VARCHAR(32) ); CREATE INDEX nyc_streets_history_geom_x ON nyc_streets_history USING GIST (geom); CREATE INDEX nyc_streets_history_tstz_x ON nyc_streets_history USING GIST (valid_range);
A continuación, importamos el estado actual de la tabla activa, nyc_streets, en la tabla de historial, para tener un punto de partida desde el cual rastrear la historia. Observa que llenamos el tiempo de creación y el usuario de creación, pero dejamos el final del rango de tiempo y la información de «deleted_by» en NULL.
INSERT INTO nyc_streets_history (gid, id, name, oneway, type, geom, valid_range, created_by) SELECT gid, id, name, oneway, type, geom, tstzrange(now(), NULL), current_user FROM nyc_streets;
Ahora necesitamos tres triggers en la tabla activa, para las acciones INSERT, DELETE y UPDATE. Primero creamos las funciones de trigger, luego las vinculamos a la tabla como triggers.
Para un insert, solo añadimos un nuevo registro en la tabla de historial con el tiempo/usuario de creación.
CREATE OR REPLACE FUNCTION nyc_streets_insert() RETURNS trigger AS $$ BEGIN INSERT INTO nyc_streets_history (gid, id, name, oneway, type, geom, valid_range, created_by) VALUES (NEW.gid, NEW.id, NEW.name, NEW.oneway, NEW.type, NEW.geom, tstzrange(current_timestamp, NULL), current_user); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER nyc_streets_insert_trigger AFTER INSERT ON nyc_streets FOR EACH ROW EXECUTE PROCEDURE nyc_streets_insert();
Para una eliminación, solo marcamos el registro de historial actualmente activo (el que tiene NULL en el tiempo de eliminación) como eliminado.
CREATE OR REPLACE FUNCTION nyc_streets_delete() RETURNS trigger AS $$ BEGIN UPDATE nyc_streets_history SET valid_range = tstzrange(lower(valid_range), current_timestamp), deleted_by = current_user WHERE valid_range @> current_timestamp AND gid = OLD.gid; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER nyc_streets_delete_trigger AFTER DELETE ON nyc_streets FOR EACH ROW EXECUTE PROCEDURE nyc_streets_delete();
Para una actualización, primero marcamos el registro de historial activo como eliminado, luego insertamos un nuevo registro para el estado actualizado.
CREATE OR REPLACE FUNCTION nyc_streets_update() RETURNS trigger AS $$ BEGIN UPDATE nyc_streets_history SET valid_range = tstzrange(lower(valid_range), current_timestamp), deleted_by = current_user WHERE valid_range @> current_timestamp AND gid = OLD.gid; INSERT INTO nyc_streets_history (gid, id, name, oneway, type, geom, valid_range, created_by) VALUES (NEW.gid, NEW.id, NEW.name, NEW.oneway, NEW.type, NEW.geom, tstzrange(current_timestamp, NULL), current_user); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER nyc_streets_update_trigger AFTER UPDATE ON nyc_streets FOR EACH ROW EXECUTE PROCEDURE nyc_streets_update();
34.3. Editando la tabla¶
Ahora que la tabla de historial está habilitada, podemos hacer ediciones en la tabla principal y ver cómo las entradas de log aparecen en la tabla de historial.
Observa el poder de este enfoque basado en base de datos para el historial: sin importar qué herramienta se use para hacer las ediciones, ya sea la línea de comandos SQL, una herramienta web basada en JDBC o una herramienta de escritorio como QGIS, el historial se rastrea de manera consistente.
34.3.1. Ediciones SQL¶
Cambiemos las dos calles llamadas «Cumberland Walk» al más elegante «Cumberland Wynde»:
Actualizar las dos calles hará que las calles originales sean marcadas como eliminadas en la tabla de historial, con un tiempo de eliminación de now, y dos nuevas calles con el nuevo nombre añadidas, con un tiempo de creación de now. Puedes inspeccionar los registros históricos:
34.4. Consultando la tabla de historial¶
Ahora que tenemos una tabla de historial, ¿para qué sirve? ¡Es útil para viajar en el tiempo! Para viajar a un tiempo particular T, necesitas construir una consulta que incluya:
Todos los registros creados antes de T, y que no hayan sido eliminados; y también
Todos los registros creados antes de T, pero eliminados después de T.
Podemos usar esta lógica para crear una consulta, o una vista, del estado de los datos en el pasado. Como presumiblemente todas tus ediciones de prueba ocurrieron en los últimos minutos, creemos una vista de la tabla de historial que muestre el estado de la tabla hace 10 minutos, antes de que empezaras a editar (es decir, los datos originales).
-- Records with a valid range that includes 10 minutes ago
-- are the ones valid at that moment.
CREATE OR REPLACE VIEW nyc_streets_ten_min_ago AS
SELECT * FROM nyc_streets_history
WHERE valid_range @> (now() - '10min'::interval)
We can also create views that show just what a particular user has added, for example:
CREATE OR REPLACE VIEW nyc_streets_postgres AS
SELECT * FROM nyc_streets_history
WHERE created_by = 'postgres';
