Actualmente estoy trabajando con una aplicación bitemporal, que almacena entradas de datos usando 4 marcas de tiempo:
Valid_from, Valid_to Registration_from, Registration_to Los dos primeros indican cuándo la entrada dada es valid_from y valid_to , y los otros dos son cuando la entrada ha sido registered_from y se asume como verdadera hasta registration_to .
En esta configuración de tiempo, necesito asegurarme de que cada fila tenga una columna única dentro del mismo valid_from y valid_to conocido dentro del mismo registration_from y registration_to en varias entradas.
Entonces necesito verificar cada fila antes de la inserción (pseudocódigo):
If registration period is overlapping If Valid period is overlapping Check if properties are the same Throw error if they areHe intentado con una exclusión como esta:
ADD Constraint exclusion_reg_{entity.InternalName}_registration_{string.Join('_', listOfAttributes)}_key Exclude using gist({string.Join(',', listOfAttributes.Select(x => x + " with =").ToList())} , registration WITH &&);Pero no estoy seguro de si lo estoy usando correctamente. Actualmente siempre recibo un error, ya que la verificación se realiza en orden inverso y entre las incorrectas. ¿Hay alguna manera de anidar esta verificación de exclusión, de modo que solo verifique la superposición de validación si el registro se superpone y arroja un error si esto es cierto?
¿Cómo hago eso en PostreSQL?
Simplemente enumere ambos rangos en la restricción de exclusión.
ALTER TABLE tbl ADD CONSTRAINT foo EXCLUDE USING gist (attribute_1 WITH =, attribute_2 WITH = -- more? , tsrange(valid_from, valid_to) WITH && , tsrange(registration_from, registration_to) WITH &&);Debería ser seguro asumir que los conceptos básicos esenciales están claros después de que respondí su pregunta relacionada (más simple) con más explicaciones hace un par de semanas. Otros pueden querer leer esto primero:
Para hacer cumplir su restricción, el orden de las expresiones ni siquiera importa. Considere la definición básica en el manual de cómo operan las restricciones de exclusión:
Las restricciones de exclusión garantizan que si se comparan dos filas en las columnas o expresiones especificadas mediante los operadores especificados, al menos una de estas comparaciones de operadores devolverá false o null .
Esto aplica efectivamente su restricción: solo si todas las expresiones se evalúan como true, in other words, ambos rangos se superponen y todos los atributos coinciden exactamente, la restricción genera una excepción.
Sin embargo, dado que la restricción se implementa con el uso de un índice GiST de varias columnas correspondiente, el orden de las expresiones es importante para el rendimiento después de todo. El manual:
Se puede usar un índice GiST de varias columnas con condiciones de consulta que involucren cualquier subconjunto de las columnas del índice. Las condiciones en las columnas adicionales restringen las entradas devueltas por el índice, pero la condición en la primera columna es la más importante para determinar qué parte del índice debe escanearse. Un índice GiST será relativamente ineficaz si su primera columna tiene solo unos pocos valores distintos, incluso si hay muchos valores distintos en columnas adicionales.
Así que reorganiza las expresiones para colocar primero las que tienen los valores más distintos en la columna.