Los accidentes suelen ocurrir, pero para un DBA, que se te borren todos los campos de una tabla de 1 millón de registros cuando querías borrar solo 100 mil, es un accidente que no se puede dar el lujo de cometer.

No se si en otros DBMS sucede esto, pero en Postgres 8.1 no veo la razón por la que sucede, pero de cualquier forma, aquí les dejo el tip para que no les pase.

Si tenemos una la tabla deletemasivo con la siguiente estructura:

 Column |  Type
--------+---------
 id     | integer
 nombre | text

y con bastantes datos, y nosotros quisieramos todos los datos cuyo id este en otra tabla. Por ejemplo:

 Column |  Type
--------+---------
 id2    | integer
 nombre | text

Probablemente lo que haríamos sería:

delete from deletemasivo where id in (select id2 from otratabla);

El problema es que puede que nos equivoquemos y pongamos algo como:

delete from deletemasivo where id in (select id from otratabla);

Nótese que id no existe en otratabla, y que si se intenta ejecutar nos daría como resultado.

tipsdeaweb=# select id from otratabla;

ERROR:  column "id" does not exist

Pero si lo ejecutamos dentro del “delete from deletemasivo where id in (select id from otratabla);” si lo deja ejecutar y borra todo cuanto haya en la tabla deletemasivo.
El resultado es:

tipsdeaweb=#
    delete from deletemasivo where id in (select id from otratabla);
    DELETE 38

Y taráaan!!… Se perdieron todos los datos de esa tabla (que en este caso son 38).

Lo mejor sería hacer el delete de la siguiente manera. Pues da el resultado esperado.

tipsdeaweb=#
     delete from deletemasivo using otratabla where deletemasivo.id = otratabla.id2;
    DELETE 9

Así que hagan backup de sus Bases de Datos seguido y traten de no cometer errores como estos, por lo menos, no en Postgres 8.1.

Como siempre les dejo los scripts para no cometer un Delete masivo por error.

Saludos.

Algo muy común cuando se está trabajando en el mejoramiento del performance de los querys es saber cuales querys son los que están tomando más tiempo en ejecutarse, y ver cuales son los que más frecuentemente se ejecutan para considerar la posiblidad de almacenarlos en cache.

El problema es que cuando ya se está en ambiente de producción tener esta información es un poco complicada si no se conoce un poco de configuración del PostgreSQL.

En postgreSQL existe el archivo de configuración postgresql.conf de donde se leen las configuraciones iniciales.

En ese archivo hay muchas variables a configurar, pero específicamente una sección dedicada a las estadísticas.

#--------------------------------------------------------
# RUNTIME STATISTICS
#--------------------------------------------------------

# - Statistics Monitoring -

#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off

# - Query/Index Statistics Collector -

#stats_start_collector = on
stats_command_string = on
#stats_block_level = off
stats_row_level = on
#stats_reset_on_server_start = off

Por default la variable stats_command_string esta desáctivada, en las estadísticas no se permite ver el query que se ejecuta, sino simplemente aparece así:

 datid   |     datname   | procpid | usesysid |  usename |         current_query        |    query_start   |   backend_start  | client_addr    | client_port
---------+---------------+---------+----------+----------+------------------------------+------------------+------------------+----------------+-------------
58964727 | database1     |   31874 |    16384 | usuario1 | <command string not enabled> | 2007-11-29 14:51 | 2007-11-29 14:51 | 192.168.10.16  |  4096
58964727 | database1     |    3031 |    16384 | usuario1 | <command string not enabled> | 2007-11-29 22:02 | 2007-11-29 22:01 |                |    -1
58964727 | database1     |    3189 |  6191381 | rene     | <command string not enabled> | 2007-11-29 23:10 | 2007-11-29 23:09 | 192.168.10.109 |  4767
58964727 | database1     |   32157 |    16384 | usuario1 | <command string not enabled> | 2007-11-29 16:49 | 2007-11-29 15:16 | 192.168.10.16  |  2892
58964727 | database1     |    3190 |  6191381 | rene     | <command string not enabled> | 2007-11-29 23:11 | 2007-11-29 23:09 | 192.168.10.109 |  4775
35829649 | rene          |    3192 |  6191381 | rene     | <command string not enabled> | 2007-11-29 23:11 | 2007-11-29 23:11 |                |    -1

En cambio si activamos dicha variable stats_command_string = on, obtendremos:

  datid   |     datname   | procpid | usesysid |  usename |              current_query    |  query_start     | backend_start    |  client_addr   | client_port
----------+---------------+---------+----------+----------+-------------------------------+------------------+------------------+----------------+-------------
 58964727 | database1     |    3189 |  6191381 | rene     | <IDLE>                        | 2007-11-29 23:10 | 2007-11-29 23:09 | 192.168.10.109 |  4767
 58964727 | database1     |   32157 |    16384 | usuario1 | <IDLE>                        | 2007-11-29 16:49 | 2007-11-29 15:16 | 192.168.10.16  |  2892
 58964727 | database1     |    3190 |  6191381 | rene     | select to from tabla group to | 2007-11-29 23:11 | 2007-11-29 23:09 | 192.168.10.109 |  4775
 35829649 | rene          |    3192 |  6191381 | rene     | <IDLE>                        | 2007-11-29 23:11 | 2007-11-29 23:11 |                |    -1
    10793 | postgres      |     617 |  6191381 | rene     | <IDLE>                        | 2007-11-29 23:08 | 2007-11-29 16:42 | 192.168.10.109 |  1688
 35842383 | usuario1      |   30003 |    16384 | usuario1 | <IDLE>                        | 2007-11-29 12:20 | 2007-11-29 12:20 | 192.168.10.16  |  2555

De aquí podemos obtener cierta información para poder tomar desiciones sobre nuestros querys.

Les dejo el query que suelo utilizar yo cuando estoy revisando el tiempo que duran cada ejecución de un select por ejemplo

select procpid,current_query,
extract(minute from now()- query_start) ,
extract(second from now()- query_start)
from pg_stat_activity
where current_query ilike '%select%'
order by extract(minute from now()-query_start) desc;

Con este query puedo saber cuantos minutos y segundos llevan ejecutándose los querys.

Nota: Luego de cambiar el valor de la variable, no se olviden de reiniciar la Base de Datos, y no dejen activado dicho valor siempre, pues es una carga más a la BD que reducirá su rendimiento.

Espero que les sirva y como siempre, estamos en contacto!

Hace algun tiempo me topé con la necesidad de realizar un ordenamiento con base a la distancia entre la ubicación del visitante al sitio y los resultados de una busqueda. Esto fue desarrollado para los estados unidos, utilizando una base de datos de codigos postales (zipcodes) donde se tiene ,adicionalmente a la ciudad y estado a los que pertenece cada codigo postal, el geoposicionamiento en coordenadas de latitud y longitud.

Esto se trabajó utilizando Postgres 8.1 y se dejo como un procedimiento almacenado en PL/PGSQL (pl/pgsql store procedure).

La distancia entre dos coordenadas es la base para encontrar esa distancia, antes de utilizar la información hay que tomar en cuenta si la coordenada está dada en radianes o en grados. Vamos a dejar para algun blog matematico esa referencia, y veremos entonces la realización de este cálculo.

Al aquirir una base de datos con información de codigos postales, habran muchos campos por cada zipcode, pero tomemos como base una tabla con al menos codigo postal y su cordenada, es decir, la latitud y longitud que le corresponde en el globo terraqueo.

CREATE TABLE zipcodes(
zipcode text,
latitude double precision,
longitude double precision,
CONSTRAINT pk_zipcodes PRIMARY KEY (zipcode)
);

A este punto cargamos la información de la base de datos con que se cuente y entonces, como consejo, es buena idea entonces crear una tabla precargando la combinacion de codigo postal origen y codigo postal destino junto con la distancia existente entre ambos. Para ello utilizaremos la misma tabla para obtener los datos de ambos codigos postales y para ello utilizaremos la facilidad de los aliases para simular dos tablas, una con los codigos postales origen y otra con los codigos postales destino.

Adicionalmente vale la pena, segun la necesidad del caso, aplicar la restricción de que no se calcule la distancia si en ambos casos es el mismo codigo postal, así como establecer un radio o distancia máximo para calcular. Claro estas condiciones eran practicas para el enfoque que se necesitaba, sin embargo, cada caso puede variar y lo importante es la simulacion de ambas tablas y la formula del calculo de distancia que es de la siguente forma:

  • a es la tabla de codigos postales de destino.
  • b es la tabla de codigos postales de origen.

La formula es:

(3963*acos(sin(a.latitude/57.2958)*sin(b.latitude/57.2958)+cos(a.latitude/57.2958)*cos(b.latitude/57.2958)*cos(a.longitude/57.2958-b.longitude/57.2958)))

Ya con estas consideraciones entonces podemos preparar la consulta: 

select a.zipcode as destination_zipcode, b.zipcode as origin_zipcode, (3963*acos(sin(a.latitude/57.2958)*sin(b.latitude/57.2958)+cos(a.latitude/57.2958)*cos(b.latitude/57.2958)*cos(a.longitude/57.2958-b.longitude/57.2958))) as distance
from zipcode a, zipcode b
where NOT (a.zipcode = b.zipcode) AND (3963*acos(sin(a.latitude/57.2958)*sin(b.latitude/57.2958)+cos(a.latitude/57.2958)*cos(b.latitude/57.2958)*cos(a.longitude/57.2958-b.longitude/57.2958))) <= 50

Y por ultimo generar la tabla final con el pre-calculo de distancias lista para utilizarse:

SELECT
a.zipcode as destination_zipcode,
b.zipcode as origin_zipcode,
(3963*acos(sin(a.latitude/57.2958)*sin(b.latitude/57.2958)+cos(a.latitude/57.2958)*cos(b.latitude/57.2958)*cos(a.longitude/57.2958-b.longitude/57.2958))) as distance
INTO TABLE zipcode_distances
FROM zipcode a, zipcode b
WHERE NOT (a.zipcode = b.zipcode)
AND (3963*acos(sin(a.latitude/57.2958)*sin(b.latitude/57.2958)+cos(a.latitude/57.2958)*cos(b.latitude/57.2958)*cos(a.longitude/57.2958-b.longitude/57.2958))) <= 50

Agregamos un par de índices para evitar recorridos secuenciales en la información

CREATE INDEX idx_zipcode_origin ON zipcode_distances (zipcode_origin);
CREATE INDEX idx_zipcode_destination ON zipcode_distances (zipcode_destination);

y con ello ya podemos realizar el cálculo de las distancias, espero les sea funcional, nos vemos pronto nuevamente!

                              Yo en postgresql no soy un gran conocedor, y a la semana realizo varios store procedures aunque estos no son nada del otro mundo, ni complicados para nada. Pero una cosa que no sabia , y tampoco me habia tomado el tiempo para investigar (un gran error), fue ver como se podian recibir y utilizar los arreglos en un sp en postgres, que realmente no es complicado y pueden llegar a ser muy utiles, también aqui aparte de ver como se usan , también haré un ejemplo de como usarlos y preparar el arreglo desde PHP que también puede llegarles a ser muy util. Read more…

Muchas veces nos topamos con la tarea de saber cuales valores de cierta columna(s) son distintos de un conjunto de registros.

Normalmente lo hacemos con la clausula distinct, pues nos ahorra un par de palabras a la hora de escribir el query. Algo como:

select distinct(firstname) from test

Pero el mismo resultado obtenemos si lo hacemos de la siguiente forma:

select firstname from test group by firstname

Entonces, ¿cuál es la diferencia? La diferencia es que con una cantidad considerable de registros, podríamos reducir hasta 1 segundo en el query. Veámoslo con un ejemplo:

Tenemos la tabla test con los siguientes datos (Mostraré solo una parte de la tabla, para ver todos los datos vean el script):

Nombre
codigo1
codigo2
codigo3
Juan 2565368308 35763
Juan 87728636342562659891 2565188276 35801
Mohamad 25653346402565334648 2565334647 35801
Joel 2059330320 2059336400 35205
William 2058700123 2058700227 35209
William 2059789339 2058700227 35594
William 2058700123 2058700227 35202
Elia 2516079797 2058700227 36608
Elia 2516079797 2516390940 36532
Elia 2516079797 2516390940 36608
Elia 2516079797 2516390940 36545

Esta tabla tiene 100 registros y cuando hacemos un DISTINCT sobre la columna firstname el query tarda 110ms en promedio
mientras si lo hacemos con GROUP BY 93 ms. ¿17 ms se preguntarán? Pero si eso sucede con 100 registros y sin nadie más usando la BD, imagínense lo significativo que es el cambio con 2 millones de registros y varias personas haciendo el mismo query al mismo tiempo.

¿A que se debe la diferencia?

Si vemos el plan de ejecución podríamos observar lo siguientes:


-- query con DISTINCT
'Unique (cost=37.41..39.91 rows=200 width=32) (actual time=0.380..0.462 rows=49 loops=1)'
' -> Sort (cost=37.41..38.66 rows=500 width=32) (actual time=0.379..0.409 rows=100 loops=1)'
' Sort Key: firstname'
' -> Seq Scan on test (cost=0.00..15.00 rows=500 width=32) (actual time=0.015..0.093 rows=100 loops=1)'
'Total runtime: 0.495 ms'

Aquí el costo de hacer un recorrido secuencial y luego sort, unique es el que aumenta en total el tiempo de ejecución, mientras


-- Haciendo un query con GROUP BY
'HashAggregate (cost=16.25..18.25 rows=200 width=32) (actual time=0.145..0.165 rows=49 loops=1)'
' -> Seq Scan on test (cost=0.00..15.00 rows=500 width=32) (actual time=0.013..0.052 rows=100 loops=1)'
'Total runtime: 0.201 ms'

Se realiza un recorrido secuencial pero se utiliza una función Hash que es más rápida que el sort y el unique.

Así que aunque nos lleve un poco más de tiempo escribir un query con GROUP BY, vamos a recuperarlo cada vez que se ejecute dicho query.

Bueno, pues, una vez más, espero que les sirva y hasta la próxima.

Por cierto, esperen una oleada de artículos entre hoy y mañana pues vamos tratar de subir el nivel del Blog.

Algo muy común en Bases de Datos es importar datos de una a otra, por ejemplo de MySQL a postgres, SQL Server a Oracle, etc. El proceso más simple a mi forma de ver es exportar todo a archivos de texto y luego importarlo de nuevo.

A veces nos topamos con el pequeño problema de que el econding del archivo esta por ejemplo en Latin1 y nuestra base de datos tiene otro encoding (UTF-8 por ejemplo).

Yo me he topado con este problema varias veces, pero usualmente usaba un editor de texto como el textpad para poder guardarlo con otra codificación, y mucho antes de darme cuenta que tenía esa opción, cambiaba caracteres como tildes, ñ, etc con un search and replace. :S.

Pero les cuento que en linux existe un comando que nos soluciona esta tarea.

El comando es iconv :

Simplemente tienen que escribir

iconv myarchivo.txt -f [encoding_actual] -t [encoding_deseado] > mynuevoarchivo.txt

Soporta larga cantidad de encodings, yo creo que todos ( pero no estoy seguro), para ver el listado escriban.

iconv -l

De todos modos lean la documentación oficial , o un simple iconv –help.

Bueno, como siempre espero que les sirva y hasta la próxima, que será dentro de unas cuantas horas. :)

Cuando usamos GROUP BY en un Query, usualmente usamos las funciones de agrupamiento o funciones agregadas, tales como count(), avg(), sum()

Pero a veces se quedan cortas estas operaciones ya que no necesitamos sumar sino concatenar, o la suma no nos sirve sino necesitamos sacar una suma de cuadrados.

Pues en postgres esto es muy sencillo y les voy a dejar un par de ejemplos usando textos:

Vamos a tener 2 tablas (objeto y etiqueta_objeto) con los siguientes datos:

idobjeto
nombre
1
casa
2
perro
3
carro
4
lapiz
5
zapato

idobjeto
etiqueta
1
grande
1
blanca
1
vieja
2
bueno
2
fiel
3
viejo
3
rojo
4
amarillo
4
No 2
5
rojo
5
derecho

Vamos a crear una función para poder concatenar 2 textos usando una "," y luego la vamos a usar en una función agregada :

CREATE OR REPLACE FUNCTION concat(text, text)
RETURNS text AS
$BODY$
DECLARE
t text;
BEGIN
IF character_length($1) > 0 THEN
IF character_length($2) > 0 THEN
t = $1 ||’, ‘|| $2;
else
t = $1;
end if;
ELSE
t = $2;
END IF;
RETURN t;
END;
$BODY$
LANGUAGE ‘plpgsql’ VOLATILE;

Y la función agregada es:

CREATE AGGREGATE concatena(
BASETYPE=text,
SFUNC=concat,
STYPE=text
);

Si quisieramos listar todas las etiquetas de un objeto tendríamos que hacer algo como

SELECT nombre,etiqueta as etiquetas FROM objeto JOIN etiqueta_objeto USING (id_objeto) GROUP BY nombre,etiqueta ORDER BY nombre;

Con esto obtendríamos:

objeto
etiqueta
carro rojo
carro viejo
casa blanca
casa grande
casa vieja
lapiz No 2
lapiz amarillo
perro bueno
perro fiel
zapato derecho
zapato rojo

Si quisieramos obtener un listado con filas con el nombre y el listado de etiquetas separado por comas ("casa";"blanca,grande,vieja"). tendríamos que concatenar cada etiqueta del lado de nuestra progra, o de alguna otra forma (que ahora no se me ocurre, :P). Pero usando la función agregada en el query tenemos:

SELECT nombre,concatena(etiqueta) AS etiquetas FROM objeto JOIN etiqueta_objeto USING (id_objeto) GROUP BY nombre ;

objeto
etiquetas
lapiz amarillo, No 2
zapato rojo, derecho
carro viejo, rojo
perro bueno, fiel
casa grande, blanca, vieja

Esta otra función con hace algo parecido pero en vez de dejarlo en un campo de tipo texto, crea un arreglo con todo las etiquetas (esto por si lo van a utlizar como arreglo, etc).

CREATE AGGREGATE array_accum(
BASETYPE=anyelement,
SFUNC=array_append,
STYPE=anyarray,
INITCOND='{}’
);

SELECT nombre,array_accum(etiqueta) AS etiquetas FROM objeto JOIN etiqueta_objeto USING (id_objeto) GROUP BY nombre ;

Aquí esta la documentación oficial para crear funciones agregadas en PostgreSQL.

Bueno… pues allí les dejo los Script para probar, y espero que les sirva el ejemplo.

Hasta la próxima y haber quien deja el query para que las etiquetas salgan en orden alfabético, se los dejo de tarea :). Saludos!

Cuando un query tarda mucho tiempo a pesar de que la columna sobre la que se filtra tiene un índice es porque estamos pasando por alto algún detalle.

Hace poco en la oficina estábamos haciendo un query sobre una tabla que tenía poco menos que 1 millón de registros, la cuestión es que a pesar de que la columna sobre la que filtrábamos estaba indexada, la consulta seguía tardando aproximadamente 7 segundos, lo cual mataba nuestra aplicación.

El problema fue causado no por ignorancia, ni por deficiencias en el desempeño del servidor, sino más bien por un descuido, ya que el campo por el que filtraba era de tipo texto y el query lo hacíamos así: Read more…

Capitalizar es un anglicismo que proviene del verbo Capitalize, y que en inglés significa pasar a mayúsculas una palabra o pasar a mayúscula la primera letra de una palabra dejando el resto en minúsculas. Y es a esto último lo que se me refiero.

En PostgreSQL existen varias funciones para manejo de strings de uso común, como lower() y upper(), pero no existe una para Capitalizar (gracias a Cristian por la aclaración), por lo que debido a la necesidad, escribí una haciendo uso de las funciones.

  • regexp_replace
  • string_to_array
  • array_dims
  • upper
  • lower

Yo quería hacerlo utilizando backreferences en expresiones regulares, pero no pude aplicar una transformación a la backreference, por lo que si a alguien tiene una mejor forma de hacerlo, espero sus comentario. 😛

Aquí les dejo el código:


CREATE OR REPLACE FUNCTION capitalize(_texto text)
RETURNS text AS
$BODY$
DECLARE
fin INTEGER;
temporal RECORD;
retval TEXT;
BEGIN
retval = ' ';
FOR temporal IN SELECT string_to_array (_texto,' ') AS arreglo LOOP
SELECT regexp_replace(
regexp_replace(
array_dims(temporal.arreglo),'[^1-9]','','g')
,'.','')::integer into fin;
FOR i IN 1..fin LOOP
retval = retval
|| upper(substring(temporal.arreglo[i],1,1))
|| lower(regexp_replace(temporal.arreglo[i],'(.)(.*)','\\2'))
||' ';
END LOOP;
END LOOP;
RETURN trim(retval);
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Como siempre, espero que les sirva, y Hasta la próxima!

Muy a menudo hacemos queries usando select … where id in (…), por ejemplo:

SELECT id,nombre FROM ejemplo_select_in WHERE edad IN (20,25,50);

Esto es distinto cuando lo queremos hacer en un Store Procedure (SP), y queremos usar un arreglo como parámetro, ya que no usamos la clausula IN, veamos como hacerlo.

Usemos la siguiente tabla para el ejemplo:

id
nombre
edad
1
nombre 1
20
2
nombre 2
20
3
nombre 3
20
7
nombre 7
25
4
nombre 4
50
5
nombre 5
50
6
nombre 6
25

ejemplo:

Si quisieramos obtener los que tengan 20 años haríamos un SP de la siguiente manera: (el ejemplo es trivial pero práctico)

CREATE OR REPLACE FUNCTION sp_get_personas(_edad INTEGER) RETURNS SETOF type_select_in AS
$BODY$
DECLARE
retval record;
BEGIN
FOR retval IN
SELECT * FROM ejemplo_select_in WHERE edad = _edad LOOP
RETURN NEXT retval;
END LOOP ;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Al ejecutar SELECT * FROM sp_get_personas(25) obtendríamos:

id
nombre
edad
7
nombre 7
25
6
nombre 6
25

Para poder obtener aquellos que tengan 20 y 25 años cambiaríamos el SP y en vez de usar select in usaríamos la función ANY():

CREATE OR REPLACE FUNCTION sp_get_personas(_edad INTEGER[]) RETURNS SETOF type_select_in AS
$BODY$
DECLARE
retval record;
BEGIN
FOR retval IN
SELECT * FROM ejemplo_select_in WHERE edad = ANY(_edad) LOOP
RETURN NEXT retval;
END LOOP ;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Al ejecutar SELECT * FROM sp_get_personas(25,50) obtendríamos:

id
nombre
edad
7
nombre 7
25
4
nombre 4
50
5
nombre 5
50
6
nombre 6
25

La función ANY(), no es de uso común y en estos casos es la mejor opción.

Les dejo con los Scripts .
Espero que les sirva, Hasta la próxima!.