PostgreSQL añade a cada tabla 6 columnas «ocultas» que se llaman Columnas del Sistema y se utilizan para garantizar la consistencia en el acceso concurrente a los datos.
En este artículo vamos a conocerlas en detalle.
Vamos a empezar creando una tabla sencilla:
create table «tablaA» (id numeric, texto text);
Consultamos ahora las tablas pg_class, pg_attribute y pg_type del catálogo del sistema para listar las columnas de tablaA y sus tipos de datos:
select a.attnum «Posición», a.attname «Columna», t.typname «Tipo dato»
from pg_class c, pg_attribute a, pg_type t
where c.relfilenode = a.attrelid
and a.atttypid = t.oid
and c.relname = ‘tablaA’
order by 1 asc;
Posición | Columna | Tipo dato
-6 | tableoid | oid
-5 | cmax | cid
-4 | xmax | xid
-3 | cmin | cid
-2 | xmin | xid
-1 | ctid | tid
1 | id | numeric
2 | texto | text
(8 rows)
Vemos que en posiciones anteriores a las dos columnas conocidas de tablaA encontramos 6 columnas con unos tipos de datos especiales (*id).
Podemos ver el contenido de estas columnas, para cada fila, si las nombramos explícitamente. Vamos a insertar un par de filas, y las vemos:
insert into «tablaA» values (1, ‘Ada Lovelace’), (2, ‘Herman Hollerith’);
select ctid, xmin, cmin, xmax, cmax, tableoid, * from «tablaA»;
ctid | xmin | cmin | xmax | cmax | tableoid | id | texto
(0,1) | 487 | 0 | 0 | 0 | 16384 | 1 | Ada Lovelace
(0,2) | 487 | 0 | 0 | 0 | 16384 | 2 | Herman Hollerith
(2 rows)
Bien… ¿y qué son cada uno de esos campos?
Pues vamos a descubrirlo…
CTID: Ubicación física de la versión de la fila dentro de la tabla
Este campo almacena una coordenada (x, y) que indica que esa versión de fila se encuentra en la página «x» y es la «y-ésima» tupla dentro de esa página.
Un página es lo mismo que un bloque. Por defecto, los bloques de datos en PostgreSQL son de 8 KB por lo que podremos hablar de páginas o bloques indistintamente.
Hablamos de «versión de fila» en lugar de simplemente «fila» porque PostgreSQL no sustituye una fila modificada con UPDATE si no que inserta una nueva versión de fila y marca como eliminada la versión de fila actual, de la misma forma que marca como eliminadas las filas borradas con DELETE.
Esto forma parte del Control de Concurrencia Multiversión (MVCC), técnica de la que os hablaré en otro artículo más adelante.
XMIN: Transacción que insertó la fila
Este campo almacena el ID de la transacción que insertó la fila tras una operación INSERT o la nueva versión de fila tras una operación UPDATE.
Las transacciones que modifican el contenido de la base de datos se identifican mediante un ID (tipo de dato xid).
Podemos consultar el valor del identificador de la transacción actual ejecutando select txid_current();
CMIN: Comando que insertó la fila
Identificador del comando, dentro de la transacción, que insertó la fila tras una operación INSERT, o la nueva versión de fila tras una operación UPDATE.
Una transacción puede ejecutar uno o varios comandos SQL de modificación de datos. Para identificar el comando exacto en el caso de que una misma transacción incluya varios comandos tenemos esta columna. El primero comando es el comando 0, el segundo será el comando 1, y así sucesivamente.
XMAX: Transacción que eliminó la fila
Similar a XMIN, XMAX almacena el ID de la transacción que eliminó la fila tras una operación DELETE o la actual versión de fila tras un UPDATE.
Si la fila no ha sido eliminada el valor de este campo será 0.
Si la fila es visible y este campo contiene un valor distinto de 0 es porque la operación de eliminación fue revertida, o porque aún no se ha comprometido.
CMAX: Comando que eliminó la fila
Similar a CMIN, CMAX almacena el identificador del comando, dentro de la transacción, que eliminó la fila tras una operación DELETE o la versión de fila actual tras un UPDATE.
TABLEOID: Identificador de la tabla a la que pertenece la versión de la fila
Esta columna es especialmente útil en tablas particionadas o en herencia ya que facilita conocer con exactitud a qué tabla individual pertenece la fila.
¿Interesante verdad?
Hablaremos más sobre esto en próximos artículos 🙂