InforSupports

Autor: Francisco

  • nextval() en Vertica: diferencias con PostgreSQL y cómo auditar secuencias

    Versión probada: Vertica 11.x — sintaxis y vistas de catálogo vigentes también en 12.x, 23.x, 24.x y la línea actual 26.x, sin cambios.

    Un caso común al revisar un DDL heredado en Vertica: encontrar una columna definida con DEFAULT nextval('esquema.secuencia') y necesitar confirmar que esa secuencia realmente existe, antes de asumir que el DDL es válido.

    id int NOT NULL DEFAULT nextval('public.seq_ejemplo')

    nextval() en Vertica vs. PostgreSQL

    Vertica sí soporta DEFAULT nextval('esquema.secuencia') en la definición de una columna, igual que PostgreSQL — pero con una diferencia importante que suele generar confusión a quienes vienen de ese motor: Vertica no crea secuencias automáticamente. En PostgreSQL, una columna SERIAL crea su secuencia asociada de forma implícita; en Vertica, la secuencia debe existir previamente, creada de forma explícita con CREATE SEQUENCE, antes de poder referenciarla en cualquier DEFAULT nextval(...).

    Verificar si una secuencia existe

    La vista correcta del catálogo del sistema para esto es v_catalog.sequences:

    SELECT *
FROM v_catalog.sequences
WHERE sequence_schema = 'public'
  AND sequence_name = 'seq_ejemplo';

    Para listar todas las secuencias existentes en la base de datos, con su valor actual:

    SELECT sequence_schema, sequence_name, current_value
FROM v_catalog.sequences;

    Si la consulta devuelve una fila con un current_value numérico, la secuencia existe y está activa — el DDL que la referencia es válido y funcionará correctamente al insertar nuevas filas.

    Ver dependencias entre tabla y secuencia

    Para confirmar específicamente qué tabla(s) están usando una secuencia determinada (útil antes de eliminar o modificar una secuencia, para no romper nada):

    SELECT *
FROM v_catalog.sequence_dependencies
WHERE sequence_name = 'seq_ejemplo';

    Columnas IDENTITY vs. secuencias nombradas

    Vale la pena distinguir dos mecanismos distintos que Vertica ofrece para autoincrementar valores:

    • Columnas IDENTITY — se definen directamente en el DDL de la tabla (id IDENTITY(1,1)) y Vertica crea automáticamente una secuencia interna asociada, visible también en v_catalog.sequences, pero gestionada implícitamente junto con la tabla.
    • Secuencias nombradas — creadas explícitamente con CREATE SEQUENCE, y luego referenciadas manualmente con nextval('esquema.nombre') en el DEFAULT de una o más columnas. Este es el caso descrito en este artículo.

    La diferencia práctica más importante: una secuencia nombrada puede ser compartida entre varias tablas o columnas distintas si así se diseña, mientras que una columna IDENTITY está atada exclusivamente a la tabla donde se definió.

    Comportamiento distribuido de las secuencias

    Al ser Vertica un motor distribuido (MPP), las secuencias no funcionan exactamente igual que en un motor de un solo nodo: cada nodo del clúster mantiene su propia caché local de valores de secuencia durante una sesión, y las solicita de forma independiente conforme las va necesitando. Esto garantiza que todos los valores generados sean únicos, pero como consecuencia, los valores de nextval() pueden aparecer no consecutivos si distintos nodos están insertando datos en paralelo — es un comportamiento esperado y normal en Vertica, no un error.

    Detalle adicional: simplificar expresiones de timestamp

    Un ajuste menor mencionado durante la revisión del DDL — la expresión con cast explícito:

    (statement_timestamp())::timestamp

    Puede simplificarse, ya que statement_timestamp() ya retorna un tipo timestamp nativo, haciendo el cast redundante:

    DEFAULT statement_timestamp()

    Resumen del flujo de verificación

    1. Confirmar que la secuencia existe con SELECT * FROM v_catalog.sequences WHERE sequence_name = '...'.
    2. Si no existe, crearla explícitamente con CREATE SEQUENCE antes de referenciarla en cualquier DEFAULT nextval(...).
    3. Revisar v_catalog.sequence_dependencies antes de modificar o eliminar una secuencia en uso, para identificar todas las tablas que dependen de ella.
    4. Recordar que los valores pueden no ser consecutivos en un clúster con varios nodos activos — no es un bug, es el comportamiento distribuido esperado.

  • Duplicados y calendario recursivo: dos técnicas SQL útiles

    Dos problemas frecuentes en el día a día con SQL: detectar registros que se repiten cuando no deberían, y generar un calendario completo (incluyendo los días sin actividad) para auditar si un proceso programado corrió todos los días que debía. Ambos se resuelven con técnicas de SQL avanzado que vale la pena tener siempre a mano.

    Contexto del problema

    Partiendo de una tabla que registra movimientos (por ejemplo, renovaciones de un registro que debería ocurrir una sola vez por año), se detectó que algunos registros tenían más de un movimiento del mismo tipo en el mismo año — algo que, por reglas de negocio, no debería pasar.

    1. Ver el detalle completo de los registros duplicados

    SELECT 
    r.codigo_registro,
    r.tipo_movimiento,
    r.fecha_carga,
    r.cliente,
    r.producto,
    r.monto,
    r.vigencia_inicio,
    r.vigencia_fin
FROM movimientos r
WHERE r.tipo_movimiento = 'Renovacion'
  AND r.fecha_carga >= '2025-01-01'
  AND r.codigo_registro IN (
        SELECT codigo_registro
        FROM movimientos
        WHERE tipo_movimiento = 'Renovacion'
          AND fecha_carga >= '2025-01-01'
        GROUP BY codigo_registro
        HAVING COUNT(*) > 1
  )
ORDER BY r.codigo_registro, r.fecha_carga;

    La lógica en dos pasos: la subconsulta (dentro del IN) identifica primero qué códigos de registro tienen más de un movimiento del tipo buscado (usando GROUP BY + HAVING COUNT(*) > 1), y la consulta externa trae el detalle completo de esos registros específicos, ordenados para poder comparar visualmente las fechas de cada duplicado.

    2. Agrupar por año para dimensionar el problema

    SELECT 
    r.codigo_registro,
    YEAR(r.fecha_carga) AS Anio,
    COUNT(*) AS Cantidad
FROM movimientos r
WHERE r.tipo_movimiento = 'Renovacion'
  AND r.fecha_carga >= '2025-01-01'
GROUP BY r.codigo_registro, YEAR(r.fecha_carga)
HAVING COUNT(*) > 1;

    Una vista rápida y resumida, útil antes de bucear al detalle: cuántos registros están duplicados, y en qué años específicamente ocurre — sin traer todavía toda la información de cada fila.

    3. Detectar duplicados con funciones de ventana

    SELECT *
FROM (
    SELECT 
        r.*,
        ROW_NUMBER() OVER (PARTITION BY r.codigo_registro, YEAR(r.fecha_carga) ORDER BY r.fecha_carga) AS rn
    FROM movimientos r
    WHERE r.tipo_movimiento = 'Renovacion'
      AND r.fecha_carga >= '2025-01-01'
) t
WHERE rn > 1;

    Esta es una forma alternativa (y en muchos casos más flexible) de lograr lo mismo que el primer query, pero sin subconsulta. ROW_NUMBER() numera cada fila dentro de su partición (codigo_registro + año), en orden de fecha. La primera aparición de cada combinación recibe rn = 1; cualquier repetición adicional recibe 2, 3, etc. Filtrando por rn > 1, el resultado son exactamente los duplicados sobrantes — es decir, todo lo que vendría «de más» después del primer registro legítimo. Esta forma es la base para, además de detectar, poder eliminar duplicados fácilmente (borrando directamente las filas con rn > 1).

    4. Generar un calendario completo con CTE recursivo

    Un caso distinto pero relacionado: verificar si un proceso automatizado corrió todos los días que debía, entre una fecha de inicio y hoy — incluyendo explícitamente los días donde no hay registro de ejecución (que es justo lo que se necesita detectar).

    DECLARE @FechaInicio DATE = '2025-01-01';

;WITH Calendar AS (
    SELECT CAST(@FechaInicio AS DATE) AS Fecha
    UNION ALL
    SELECT DATEADD(DAY, 1, Fecha)
    FROM Calendar
    WHERE Fecha < CAST(GETDATE() AS DATE)
)
SELECT 
    c.Fecha,
    l.Fecha_ejecucion,
    CASE 
        WHEN l.Fecha_ejecucion IS NULL THEN 'FALTA'
        ELSE 'OK'
    END AS Estado
FROM Calendar c
LEFT JOIN (
    SELECT CAST(Fecha_ejecucion AS DATE) AS Fecha_ejecucion
    FROM Proceso_Log
    WHERE proceso = 'Carga_Diaria'
) l
    ON l.Fecha_ejecucion = c.Fecha
OPTION (MAXRECURSION 366);

    Cómo funciona el CTE recursivo

    Un CTE recursivo (Common Table Expression) se define en dos partes unidas por UNION ALL: el ancla (el primer SELECT, que da el punto de partida — aquí, la fecha de inicio) y la parte recursiva (el segundo SELECT, que hace referencia a sí misma, sumando un día cada vez). SQL Server repite la parte recursiva una y otra vez, generando una fila nueva por cada iteración, hasta que la condición del WHERE deja de cumplirse (cuando Fecha alcanza el día de hoy). El resultado es una tabla virtual con una fila por cada día del rango, sin necesitar ninguna tabla física de calendario pre-creada.

    OPTION (MAXRECURSION 366) es obligatorio para rangos largos: por defecto, SQL Server limita cualquier CTE recursivo a 100 iteraciones como medida de seguridad (para evitar loops infinitos accidentales) — 366 cubre incluso un año bisiesto completo. Si el rango a cubrir fuera mayor a un año, este número debe ajustarse en consecuencia.

    El LEFT JOIN contra el log real de ejecuciones es lo que revela los huecos: como es un LEFT JOIN (no un INNER JOIN), todas las fechas del calendario aparecen en el resultado sin importar si hay o no un registro de ejecución correspondiente — cuando no lo hay, el campo Fecha_ejecucion queda en NULL, y el CASE lo marca explícitamente como 'FALTA'.

    Nota sobre deadlocks al consultar tablas con carga masiva activa

    Al ejecutar consultas de solo lectura (SELECT) contra tablas que en ese momento están recibiendo una carga masiva de datos (un INSERT o UPDATE grande), es posible toparse con un error de deadlock, donde SQL Server elige una de las dos transacciones en conflicto como «víctima» y la cancela para poder resolver el bloqueo mutuo.

    Es importante entender que, si el SELECT es elegido como víctima, no significa que haya dañado ni afectado el proceso de carga masiva — SQL Server simplemente decide cuál de las dos operaciones en conflicto cancelar, generalmente priorizando la transacción más costosa de revertir. Lo más seguro en ese caso es simplemente esperar a que el proceso masivo termine, y volver a intentar la consulta después, en vez de forzar la ejecución repetidamente mientras la carga sigue en curso.