Mostrando entradas con la etiqueta postgresql. Mostrar todas las entradas
Mostrando entradas con la etiqueta postgresql. Mostrar todas las entradas

martes, 31 de marzo de 2015

PostgreSQL: Interrumpir un lote de instrucciones

Complementando el artículo anterior, aquí se le puede encontrar utilidad al ejemplo tan extraño que utilicé en él (si piensas poner este artículo en práctica, no dejes de ver el anterior para que no te lleves sorpresas desagradables).

¿Y para qué voy yo a necesitar interrumpir un lote de instrucciones?


A veces hace falta lanzar de forma no interactiva un conjunto de sentencias SQL (lote) asumiendo que el conjunto se ejecutará correctamente. Para ello, se asume que en la base de datos se cumplen ciertas condiciones, que en el momento de lanzar el lote de instrucciones o durante su ejecución podrían no cumplirse.

Seguramente conozcas el concepto transacción en SQL. No siempre se puede hacer uso de esta característica, especialmente cuando el tamaño del lote produce grandes cambios en los datos (según la configuración de PostgreSQL, los ficheros WAL necesarios para la transacción podrían desbordarse).

¿Y si en medio del lote te interesa realizar alguna comprobación e interrumpir el proceso si no se dan las condiciones adecuadas?.

La solución propuesta


Cuando se produce un error, se puede detener el flujo de instrucciones (ver ON_ERROR_STOP en  psql). Por tanto, si se consigue generar un error de forma controlada, se podría utilizar para interrumpir el proceso.

Se podría crear un procedimiento almacenado que tras realizar las comprobaciones necesarias, lance una excepción cuando convenga. Sin embargo, eso requiere escribir código PL/SQL y tener privilegios para instalar procedimientos almacenados en la base de datos.

Pero hay una técnica mucho más simple y muchísimo menos intrusiva:

forzar una excepción al convertir de un tipo de dato a otro.

Por ejemplo, intentar convertir un texto no numérico en un número. 

SELECT CASE WHEN (r_count = 0) THEN CAST('Nothing to process.'||r_count AS INTEGER)
            ELSE CAST(r_count AS INTEGER)
       END AS preprocessed_count
FROM (SELECT COUNT(*) AS r_count FROM preprocessed_data
     ) foo
;

Ese ejemplo permitiría interrumpir un lote si en la tabla preprocessed_data no hay nada, por ejemplo, porque acciones anteriores no han escrito en ella.

Enlaces


Enlaces de interés relacionados con este artículo:

(Actualizado 31/03/2015)
 

Publicado por el No hay comentarios:
Etiquetas: , , ,

sábado, 28 de febrero de 2015

PostgreSQL: Un optimizador demasiado inteligente

¿Has tenido alguna mala experiencia con algún optimizador?


Mi primera mala experiencia fue con algún compilador de C, uno de Borland creo recordar. Depurando con ejecución paso a paso, llegaba a un sitio en el que misteriosamente no se ejecutaba el código. Lo que pasaba era que por defecto el IDE estaba configurado para optimizar y el código generado no se podía depurar.

El planificador de PostgreSQL


En PostgreSQL el planificador (planner) es el módulo del sistema que interpreta el código SQL para su ejecución. Incluye optimizaciones en las que hace auténticas virguerías para que la ejecución sea eficiente.

Observa el siguiente código y trata de predecir cual es el resultado de la consulta:

CREATE TABLE test(id INTEGER, name TEXT, PRIMARY KEY (id) );

INSERT INTO test(id, name) VALUES
                (1 , 'one'  ),
                (2 , 'two'  ),
                (3 , 'three')
;

SELECT CASE WHEN (r_count < 0) THEN CAST('The concatenation avoids this whole CAST sentence being pre-evaluated.'||r_count AS INTEGER)
            ELSE CAST(r_count AS INTEGER)
       END AS demonstration
FROM (SELECT COUNT(*) AS r_count FROM test WHERE (id < 0)
     ) foo
;

No hay ningún id negativo, por lo que cabría esperar una ejecución correcta de la consulta con 0 resultados. Además, en general, la función COUNT nunca devolverá un valor negativo, por lo que es obvio que nunca se producirá el primer WHEN.

Pero no, la consulta genera un error en tiempo de compilación (considerando compilación el análisis del código previo a su ejecución) porque el planificador es "tan inteligente" que precalcula todo el código que considera constante respecto a la ejecución de la consulta. En este caso, evalua la sentencia:

CAST('This case never happens but the CAST throws an exception.' AS INTEGER)

antes de lanzar la consulta, generando un error porque la String no se puede convertir a Integer.

Desde mi ignorancia, no sé si está definido el orden correcto de evaluación o si eso es algo que se deja a disposición de la implementación. Lo que está claro, es que hay que saber estas cosas y ademas, que en este caso, no es lo más óptimo malgastar tiempo en precalcular cosas que nunca se deberían calcular dado que la consulta no va a devolver resultados.

(Nota: se ha comprobado dicho comportamiento en las versiones de PostgreSQL: 8.3.7, 8.4.8, 8.4.22, 9.1.14 y 9.2.8.)

Como evitar el problema


Para no tener ese problema, basta con hacer que el código potencialmente peligroso o costoso dependa de la consulta, es decir, que no sea precalculable. El ejemplo, podría quedar así:

SELECT CASE WHEN (r_count < 0) THEN CAST('The concatenation avoids this whole CAST sentence being pre-evaluated.'||r_count AS INTEGER)
            ELSE CAST(r_count AS INTEGER)
       END AS demonstration
FROM (SELECT COUNT(*) AS r_count FROM test WHERE (id < 0)
     ) foo
;

Destacar que raramente se puede escribir código que no dependa de la consulta que sea más costoso que la consulta en sí.

Enlaces


Enlaces de interés relacionados con este artículo:

(Actualizado 28/02/2015)
 

Publicado por el No hay comentarios:
Etiquetas: , , ,

domingo, 27 de abril de 2014

¿Cómo obtener la ocurrencia más corta usando expresiones regulares?

Introducción


Alguna vez habrás usado expresiones regulares (regular expression), ya sea programando, en la línea de comandos o en algún editor de texto.

¿No te ha pasado que al usar algún comodín de repetición como el * en vez de obtener la ocurrencia más corta obtienes la más larga (que contiene ocurrencias más cortas)?

Por ejemplo, al analizar la cadena XML: 

<a>1111</a><a>2222</a><a>3333</a><a>4444</a>

con la expresión regular siguiente para locazizar los elementos (tags) a: 

<a>.*</a>

obtienes toda la cadena en vez de únicamente el primer elemento <a>1111</a>.

Ups, hay ambigüedad: encajan varios trozos de la cadena y además encaja toda la cadena. ¿Qué resultado se elige? 

Greediness vs Laziness


El problema es que al diseñar un motor de expresiones regulares, hay que resolver la ambigüedad. Hay dos comportamientos:
  1. Greediness (avaricia, codicia): el comodín se expande consumiento el máximo número de caracteres posible. En la documentación se suele denominar modo greedy.
  2. Laziness (pereza): el comodín se expande consumiendo el mínimo número de caracteres posible. En la documentación se suele denominar modo lazy, non-greedy o reluctant.
Algunos motores de búsqueda permiten configurar el comportamiento, otros no.

Si te interesa mucho el tema, en estos enlaces se explica (en inglés) muy bien como funcionan los motores de expresiones regulares y sus comportamientos:

Cómo capturar la ocurrencia más corta (non-greedy)


Algunos motores de expresiones regulares han resuelto la ambigüedad utilizando operadores diferentes para los modos greedy y non-greedy.

El operador para 0 o N ocurrencias más conocido es el greedy * mientras que el non-greedy suele ser una variación suya *? en la mayoría de los casos.

A continuación hay ejemplos del operador non-greedy en varios entornos. Para ello, se utiliza la cadena y la expresión regular de la introducción.

En lenguajes interpretados se muestra la lista de ocurrencias, por ser fácil y muy ilustrativo.

En lenguajes compilados, por simplicidad, se reemplazan todas las ocurencias por la palabra Replaced, de modo que mirando el resultado se sabe si el comportamiento fue greedy (Replaced aparecerá sólo una vez) o non-greedy (Replaced aparecerá varias veces).

grep (hay que activar el modo Perl) 
echo "<a>1111</a><a>2222</a><a>3333</a><a>4444</a>" | grep -P -o "<a>.*?</a>"

Vim (este caso difiere mucho del resto) 
<a>.\{-}<\/a>
Para detectar tags cuyo contenido incluya saltos de línea:
<a>\_.\{-}<\/a>

Perl (ejecutable en línea de comandos) 
perl -e 'my @matches= "<a>1111</a><a>2222</a><a>3333</a><a>4444</a>" =~ /<a>.*?<\/a>/g; print (join("\n", @matches), "\n");'

Ruby (ejecutable en línea de comandos) 
ruby -e 'puts "<a>1111</a><a>2222</a><a>3333</a><a>4444</a>".scan( /<a>.*?<\/a>/ ).to_a'

PostgreSQL 
SELECT regexp_matches('<a>1111</a><a>2222</a><a>3333</a><a>4444</a>', '<a>.*?<\/a>', 'g');

PHP (ejecutable en línea de comandos)
php -r '$matches= array(); $search= preg_match_all("/<a>.*?<\/a>/", "<a>1111</a><a>2222</a><a>3333</a><a>4444</a>", $matches); print_r($matches);'

Java 
"<a>1111</a><a>2222</a><a>3333</a><a>4444</a>".replaceAll("<a>.*?</a>", "Replaced")

Javascript 
function doReplace() {
  var str= "<a>1111</a><a>2222</a><a>3333</a><a>4444</a>"
  str= str.replace(new RegExp("<a>.*?</a>", "g"), "Replaced");
  alert(str);
}

C++
Para menejar expresiones regulares en C++ haría falta un compilador que soporte por completo C++11 (GCC 4.9) o bibliotecas alternativa como boost o clang.

sed (este comando siempre es greedy)
http://stackoverflow.com/questions/1103149/non-greedy-regex-matching-in-sed

bash (sus funciones/operadores de expresiones regulares como =~ siempre son greedy)
Al parecer, las expresiones regulares POSIX sólo soportan modo greedy.
http://stackoverflow.com/questions/18738576/regex-in-bash-expression

Prúebalo tu mismo


Aquí hay enlaces a varias paǵinas web para probar algunas de las expresiones anteriores directamente on-line:

Enlaces


Enlaces de interés relacionados con este artículo:


(Actualizado 10/07/2014)


Publicado por el 1 comentario:
Etiquetas: , , , , , , , , , ,

viernes, 31 de enero de 2014

Registros duplicados en PostgreSQL (y otros sistemas)

Introducción


¿Has tenido que localizar registros repetidos en una base de datos?

--CREATE TABLE test(first INTEGER, second TEXT, third TEXT);
 first | second | third 
-------+--------+----------
     0 | zero   | REPEATED
     0 | zero   | REPEATED
     0 | zero   | REPEATED
-------+--------+----------

O peor aún, ¿has tenido que borrar registros diferentes con la Primary Key repetida?

CREATE TABLE test(first INTEGER, second TEXT, third TEXT, PRIMARY KEY (first, second));
 first | second | third 
-------+--------+-----------
     0 | zero   | OK
     0 | zero   | REPEATED1
     0 | zero   | REPEATED2
-------+--------+-----------

Estarás pensando que eso es imposible... pues créeme, aunque no pueda demostrártelo, pasa. Y si te pasa, vas a querer saber como arreglarlo.

Algunas de las cosas que comento aquí son específicas de PostgreSQL, pero otras son estándar o utilizables en otros sistemas de bases de datos como SQLite.

Registros o Primary Key repetidos


Si en SQL se define una tabla sin Primary Key, se pueden almacenar registros repetidos en ella.

La experiencia me ha demostrado que incluso habiendo definido una Primary Key en una tabla, los servidores PostgreSQL (al menos en su versión 8.3) tienen algún fallo por el que se corrompe el índice utilizado para representarla, llegando a aparecer varios registros con la misma Primary Key.

No puedo reproducir el fallo, pero si buscas en Internet cosas como:
  • postgresql corrupt primary key
  • postgresql duplicate primary key
te saldrán bastantes enlaces, en los que podrás leer que hay varias personas a las que también les ha ocurrido.

¿Cómo localizar esos registros?


Recuerda esta consulta SQL estándar:

SELECT todas_las_columnas_de_la_primary_key_y_otra_columna_cualquiera_mas_si_la_hay
FROM tabla
GROUP BY todas_las_columnas_de_la_primary_key_y_otra_columna_cualquiera_mas_si_la_hay
HAVING (COUNT(*) > 1)
;

En caso de que la tabla no tenga Primary Key, habrá que agrupar por todas las columnas de la tabla.

La idea es simple: la consulta sólo mostrará aquellos registros donde las columnas por las que se agrupe presenten valores repetidos. Como la Primary Key está incluida en el GROUP BY, el COUNT debería ser siempre 1.

¿Cómo borrar todas las ocurrencias repetidas menos una?


Lo más normal, cuando se tengan registros repetidos, será quedarse con una única ocurrencia. El problema está en distinguir los registros, por ejemplo para borrarlos. Para ello, en PostgreSQL se puede echar mano de las columnas de sistema:

PostgreSQL 9.2.6 Documentation - Chapter 5. Data Definition - 5.4. System Columns
  • Si la tabla tiene OID, es muy fácil distinguir los registros, ya que se trata de un identificador interno distinto para cada fila (a mi no se me ha dado el caso de que se repita, pero a saber).
  • Siempre se puede recurrir al CTID (incluso si la tabla no tiene OID). El CTID indica la posición física de una fila de la tabla. Este valor puede variar en cuanto se realicen modificaciones sobre la fila o la tabla, por lo que NO se debe considerar un valor persistente, siendo útil sólo a corto plazo.
Esas columnas están ocultas, pero se puede acceder a ellas si se citan de forma explícita.

Si lo que se ha duplicado es la Primary Key pero no todo el registro, debería ser posible borrar directamente los registros que no interesen con una sentencia DELETE poniendo en la cláusula WHERE tantas condiciones como se necesiten para identificar unívocamente las filas: 

DELETE FROM test WHERE (first = 0) AND (second = 'zero') AND (third <> 'OK');

Si lo que se ha duplicado es el registro completo, es más complicado:


--PostgreSQL.
--Para borrar todos los repetidos menos uno usando CTID.
DELETE FROM test
WHERE ctid NOT IN (SELECT min(ctid) FROM test GROUP BY first, second, third)
;


--PostgreSQL o SQLite.
--Para borrar todos los repetidos menos uno usando OID.
DELETE FROM test
WHERE oid NOT IN (SELECT min(oid) FROM test GROUP BY first, second, third)
;

Según el caso, otra posibilidad podría ser borrar todos los repetidos e insertar luego una única ocurrencia, dado que los registros están completamente repetidos (son iguales).

Problemas a tener en cuenta


Si se ha definido una tabla sin Primary Key ni restricciones UNIQUE, en teoría no puede haber Foreign Keys apuntando a ella, por lo que no puede haber registros dependientes. Sin embargo, sí podría haber TRIGGERs por ejemplo.

Sin embargo, si el problema es que se han duplicado Primary Keys, sí podría haber haber registros dependientes, por lo que al borrar hay que tener eso en cuenta. No puedo asegurar que pasará en tal caso con los registros dependientes, pero casi seguro que se borrarán al borrar la primera ocurrencia que se encuentre en la tabla principal.

Incluso al borrar, el sistema puede hacer uso de índices en vez de barrer la tabla completa. Esto puede ser un problema añadido, ya que si el índice que implementa la Primary Key está corrupto, el DELETE no funcionará como es de esperar. Por ello, es interesante provocar una sentencia que fuerce un barrido directo sobre la tabla evitando el uso de índices.

Una cláusula GROUP BY que contenga alguna columna que no sea de la PRIMARY KEY y no esté indexada debería provocar dicho barrido. En PostgreSQL se puede utilizar la sentencia EXPLAIN para comprobar si se está utilizando algún índice o no. Además, en PostgreSQL se puede desactivar temporalmente el uso de índices con:

SET enable_indexscan TO OFF;

Ejemplos


En este caso, no puedo demostrar gran cosa porque no es posible reproducir el fallo de duplicar valores en la Primary Key. De todos modos, he puesto algunos ejemplos para ilustrar algunas de las cosas citadas en el artículo:


El ejemplo en SQLite puedes probarlo pegando el código en esta web:


Conclusiones


A veces pasan cosas que no deberían pasar, y en esos casos, no está de más tener algo de información para salir del atolladero.

La forma de localizar repetidos es sencilla, pero para identificarlos y borrarlos hay que tener mucho cuidado y prestar atención a los detalles particulares de la situación.

Enlaces


Enlaces de interés relacionados con este artículo:
En esos enlaces, entre otras cosas hay más detalles sobre el tema de este artículo, otras soluciones a los mismos problemas y otras consultas útiles para PostgreSQL.

(Actualizado 06/02/2015)


Publicado por el 5 comentarios:
Etiquetas: , , ,

Source code: Registros duplicados - PostgreSQL

Código fuente para probar registros duplicados

Ver la entrada correspondiente en el siguiente enlace:


Registros duplicados en PostgreSQL (y otros sistemas)


BEGIN;
-------------------------------------------------------------------------------

CREATE TABLE test1(first INTEGER, second TEXT, third TEXT)
WITH (OIDS = TRUE) --PostgreSQL
;

INSERT INTO test1 VALUES (0, 'zero' , 'OK' );
INSERT INTO test1 VALUES (0, 'zero' , 'OK' );
INSERT INTO test1 VALUES (0, 'zero' , 'OK' );
INSERT INTO test1 VALUES (1, 'one'  , 'OK' );
INSERT INTO test1 VALUES (2, 'two'  , 'OK' );
INSERT INTO test1 VALUES (3, 'three', 'OK' );

--Para ver las columnas ocultas.
SELECT *
     , oid, ctid --PostgreSQL
FROM test1
;

--Para ver sólo los registros duplicados.
SELECT COUNT(*), first, second, third FROM test1 GROUP BY first, second, third HAVING (COUNT(*) > 1);

/*
--PostgreSQL.
--Para borrar todos los repetidos.
DELETE FROM test1 WHERE (first, second, third) IN
    (SELECT first, second, third FROM test1 GROUP BY first, second, third HAVING (COUNT(*) > 1))
;
*/

/*
--PostgreSQL.
--Para borrar todos los repetidos menos uno usando CTID.
DELETE FROM test1
WHERE ctid NOT IN (SELECT min(ctid) FROM test1 GROUP BY first, second, third)
;
*/

/*
--PostgreSQL.
--Para borrar todos los repetidos menos uno usando OID.
DELETE FROM test1
WHERE oid NOT IN (SELECT min(oid) FROM test1 GROUP BY first, second, third)
;
*/

--Para ver lo que queda finalmente en la tabla.
SELECT * FROM test1;

DROP TABLE test1;
-------------------------------------------------------------------------------

ROLLBACK;
Publicado por el No hay comentarios:
Etiquetas: , , ,
Suscribirse a: Entradas (Atom)