Postgresql

La diferencia entre los dos tipos es cómo se interpreta la hora.
TIMESTAMP WITHOUT TIME ZONE (TIMESTAMP, que es un alias) asume que tú sabes perfectamente de qué TZ (time zone) es la hora que le estás pasando, y que no necesitas que sea consistente con los TZs de otros valores de ese mismo tipo; o bien, que tienes 100% seguridad que siempre estarán todos en el mismo TZ. Cosa que, es difícil de asegurar puesto que uno nunca tiene claridad de las líneas de desarrollo futuro de sus sistemas.

En cambio, un TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ) asume que la hora está en el TZ local, configurado con la variable TimeZone (que puedes cambiar en el archivo postgresql.conf o usando la orden SET). Para almacenarla, le quita la diferencia de horas de GMT (es decir, la lleva a GMT) y lo guarda de esa forma; es decir, en el almacenamiento, todos los valores de una columna de tipo timestamptz van a estar en GMT. Al momento de desplegar un valor, se vuelve a rotar desde GMT hasta el TZ actual agregándole la diferencia de horas de GMT.

Si se piensa detenidamente, notaremos que esto significa que si se tiene un usuario en un TZ que ingresa una hora, y luego viene otro usuario en un TZ distinto que la examina, el valor de la hora va a ser distinto para cada uno de ellos: para cada uno, va a estar en su TZ local.

Esta es una característica muy útil y generalmente mal comprendida.

Por lo tanto, lo recomendable es utilizar el TZ que se debe aplicar en cada momento (por ej. si acceden al sistema desde una provincia que tenga diferencia horaria con otra) y se realicen los correctos seteos en el parámetro TimeZone del cliente, de manera que todas las horas del sistema sean consistentes y coherentes, sobre todo si los usuarios interactúan con otros que están en TZs distintos.

Haciéndolo de esta forma se ahorra mucho código del lado de la aplicación, dado a que se evita tener que implementar la rotaciones horarias.

Recomendación personal: nunca uses los tipos TIMESTAMP WITHOUT TIME ZONE ni TIMESTAMP (a menos que se entienda muy bien el por qué)

Ejemplo:
CREATE TABLE public.prueba_time (t1 TIMESTAMPTZ, t2 TIMESTAMP);

INSERT INTO public.prueba_time VALUES (TIMESTAMPTZ ’2010-12-27 20:38:40′, TIMESTAMP ’2010-12-27 20:38:40′);

SELECT * from public.prueba_time;
–”2010-12-27 20:38:40-03″ ; “2010-12-27 20:38:40″

SET timezone = ‘MST’; –seteo otro TZ

SELECT * from public.prueba_time;
–”2010-12-27 16:38:40-07″ ; “2010-12-27 20:38:40″

Texto basado en respuestas de listas de mails (Alvaro Herrera) y documentación oficial de PostgreSQL

Buenas! Otra corta entrega en este blog.

EXPLAIN a tenido mejoras en 9.0, sin embargo aún acepta su antigua sintáxis.

La siguiente consulta nos devuelve un explain analyze, pero además nos devuelve en formato XML, con los buffers utilizados en la consulta y los costes adicionales:

EXPLAIN (ANALYZE true, BUFFERS true, COSTS true, FORMAT xml)
SELECT random(), i from generate_series(1,1000) i(i);

También acepta formato normal (text), JSON y YAML.

Estas mejoras permiten medir la cantidad de buffers que entran en el caché y por supuesto saber cual será la sobrecarga que tendrá el bgwriter para determinadas consultas pesadas, además de logicamente detectar problemas de estadísticas y rutas no muy convincentes.

La nueva versión de Postgresql trae cambios importantes en cuanto a su arquitectura interna. Apunta a contrarrestar el principal defecto que tenia postgres hasta ahora: la replicación.

En su fase de RC (release candidate), está disponible para descarga y prueba pero aún no recomendada para producción. Sin embargo las espectativas son muy buenas ya que han aumentado los tests sobre las nuevas versiones.

Podemos revisar las notas de versión en este link:
Release Notes

En esta versión hubo cambios en los catálogos de sistema por lo que es necesario una recarga total del data para ponerlo en funcionamiento si utilizamos versiones más antiguas. Pero, en esta ocasión ya disponemos de la herramienta pg_upgrade, la cual permite la migración sin volcado de datos, haciéndolo más rápido y efectivo.

Otra de las carácterísticas importantes de esta versión es que soporta Windows 64 bits en su totalidad.

También se incorpora disparadores por columna, bloques de codigos anónimos, restricciones en llaves únicas ahora pueden ser omitidas sin ningún truco, VACUUM FULL mejorado, EXPLAIN en XML y JSON, PL/pgsql está instalado por defecto, entre otros.

En los próximos artículos trataremos de incorporar ejemplos para que puedan apreciarlos.

Enjoy!!

Es muy común que muchos sysadmins prefieran herramientas por consola a la hora de monitorear postgres. Aqui les dejo un script .sql para poder monitorear de manera sencilla un server.

Tienen 2 opciones: tener un web server corriendo y que este script tire un html que peude ser accedido desde cualquier browser o directamente desde la linea de comando que lo ejectua, mostar el contenido.

BEGIN;
\o /tmp/salida.html
\pset footer
--\H 
 
--SELECT pg_size_pretty(pg_database_size(current_database())) as "Tamaño de la base";
SELECT count(procpid) AS "Num Backends Open",
pg_size_pretty(pg_database_size(current_database())) AS "Tamaño de la base",
(SELECT setting FROM pg_settings WHERE name = 'max_connections') AS "Máximas conexiones", substr(current_query,0,40) AS "Estado y consulta"
FROM pg_stat_activity WHERE datname = current_database() GROUP BY current_query;
SELECT pg_xlogfile_name(pg_current_xlog_location()) AS "Nombre del segmento actual",
(pg_xlogfile_name_offset(pg_current_xlog_location())).file_offset AS "Offset Actual";
SELECT txid_current() AS "Txid actual", txid_current_snapshot() AS "Transacciones actuales";
SELECT pg_stat_get_db_blocks_fetched((SELECT datid FROM pg_stat_database WHERE datname = current_database())) AS "Bloques leídos";
SELECT pg_stat_get_db_blocks_hit((SELECT datid FROM pg_stat_database WHERE datname = current_database())) AS "Bloques leídos y en caché";
SELECT pg.relname AS "Nombre de la tabla", pg_size_pretty((relpages*8)::bigint*1024) AS "Tamaño estimado",
reltuples AS "Tuplas estimadas", relfilenode AS "Archivo en el filesystem",
n_dead_tup AS "Tuplas muertas"
FROM pg_class pg JOIN pg_stat_all_tables psat
ON (pg.relname = psat.relname) ORDER BY relpages DESC LIMIT 30;
END;

Para ejecutar la consulta desde un archivo, debemos hacer lo siguiente:

watch --interval=1 'psql base -i archivo.sql '

Si miran, en el archivo .sql, hay una opción \o que indica la salida a un archivo. Si poseen en ese
server un web server (preferentemente un lighthttp), pueden cambiar la ruta a un directorio del mismo
y acceder a la monitorización desde cualquier explorador a la IP del server. si solo quieren la salida
en texto, saquen la opción \H y a la linea de ejecución agreguen:

watch --interval=1 'psql base -i archivo.sql && cat /tmp/salida.txt'

Este script también puede ser útil para monitorear el estabo de los segmentos de la WAL, en el caso que quieran configurar un servidor Warm Standby (para saber la ubicación y nombre en el segmento).

Muchas veces observamos que la performance de nuestro sistema se degrada bajo ciertas circunstancias o servicios que utilizamos. Comúnmente recurrimos a verificar los parámetros de configuración del motor y no observamos una posible mejora. Un explain analyze sobre las consultas que resuelven el servicio nos puede dar gran información sobre que es realmente lo que esta sucediendo. La escritura de este post estuvo motivada por un caso en particular, en el cual, un servicio demoraba un tiempo bastante elevado. El primer paso fue armar las consultas que resolvían el servicio y analizarlas mediante el uso de explain analyze. De acá pudimos deducir que tardaba mucho en utilizar un índice. Al verificar el tamaño del mismo, notamos que el índice ocupaba la mitad del tamaño de la tabla. Si hablamos de que la tabla ocupaba alrededor de 2GB y su índice 1GB, el tamaño del índice es muy grande y seria un punto interesante para analizar y si realmente se justifica que el mismo involucre gran cantidad de campos de la tabla.

Un punto interesante y que vale la pena visualizar, fue la forma en que se obtuvieron los tamaños de los elementos de la base de datos. Para esto, recurrimos a documentación contenida en la siguiente wiki: http://wiki.postgresql.org/wiki/Disk_Usage

Con el siguiente query se obtienen los tamaños de los componentes desglosados en tablas con almacenamiento en linea, fuera de linea (llamado TOAST) e índices.

SELECT nspname || '.' || relname AS "relacion",
pg_size_pretty(pg_relation_size(C.oid))  AS "tamaño"
FROM pg_class C LEFT JOIN pg_namespace N ON  (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog',  'information_schema')
ORDER BY pg_relation_size(C.oid) DESC;

Salida:

Si lo que queremos es obtener el tañamo total que ocupa nuestra tabla:

SELECT nspname || '.' || relname AS "relacion",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "tamaño_total"
FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC;

Los querys expuestos son compatibles con Postgres 8.1 o superior.

(Este artículo fue publicado en la wiki oficial de Postgres, artículo)

Si bien el ‘IF’ en una consulta SQL puede hacerse sencillamente con CASE, puede que algunos para mantener la compatibilidad con Access o similares, necesiten o quieran agregar la función iff al catálogo de funciones de postgres.

La función iif recibe como primer parámetro un valor booleano, esto quiere decir: un valor true-false o una expresión que retorna un booleano (7=9,’t'=’t', 4>3,etc). En el caso de que el primer parámetro retorne ‘true’ devuelve el 2do parámetro, caso contrario el 3ro.

El presente blog solo desea mostrar la versatilidad de postgres en cuanto a lenguajes, pero para condicionales y mantener la estandarización, se ruega utilizar CASE.

Veamos un ejemplo en SQL:

CREATE OR REPLACE FUNCTION iif_sql(BOOLEAN, anyelement, anyelement) returns anyelement AS
$body$ SELECT case $1 when true then $2 else $3 end $body$
LANGUAGE sql IMMUTABLE;

En PL/Pgsql:

CREATE OR REPLACE FUNCTION iif_(BOOLEAN, double precision, double precision) RETURNS double precision AS
$body$
DECLARE
	rtVal double precision;
BEGIN
	rtVal := (SELECT case $1 when true then $2 else $3 end);
	RETURN rtVal;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

En Pl/Perl utilizaremos una via diferente para el mismo problema, puede hacerlo exactamente igual que la versión plpgsql usando la función spi_query_exec():

CREATE OR REPLACE FUNCTION iif_perl(boolean, double precision, double precision) RETURNS double precision AS
$body$
if($_[0] =~ /t/){
	return $_[1];
}else{
	return $_[2];
}
$body$
LANGUAGE 'plperl' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER;

Como un ‘shared object’ en C:

#include 
#include "postgres.h"
#include "executor/executor.h"
 
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
 
PG_FUNCTION_INFO_V1(iif_c);
 
Datum
iif_c (PG_FUNCTION_ARGS){
   bool message = PG_GETARG_BOOL(0);
   float8 first = PG_GETARG_FLOAT8(1);
   float8 second = PG_GETARG_FLOAT8(2);
 
   if(message == 1){
      PG_RETURN_FLOAT8(first);
   }else{
      PG_RETURN_FLOAT8(second);
   }
}

Adicionalmente a esta forma de, debemos crear la función utilizando:

postgres@pgsql:/usr/local/pgsql84/$ gcc -I include/server/ --shared iif_c.c

…luego:

CREATE OR REPLACE FUNCTION iif_c(BOOLEAN, double precision, double precision) RETURNS double precision
     AS '/usr/local/pgsql84/compiled/a.out', 'iif_c'
     LANGUAGE C STRICT;
 
-- Pruebe!
SELECT iif_c(9<8,0,9);

En la forma ‘C’, ud. debe modificar el tipo de datos de los parámetros ya que no hay conversión iterna de tipos en esta función (puede ud. desarrollar una que lo haga, en lo personal no lo creo conveniente).

En pruebas personales he visto mayor performance con la función en C. El resto posee una performance similar entre sí.

Saludos!

Hola mortales!

Existe un thread interesante respecto de como es la construcción de estos llamativos tipos de datos: ENUM

Podemos agregar elementos de manera ‘harcodeada’. Sigamos el siguiente ejemplo:

Tabla A1:

modeloscomunes=# select enumtypid::regtype,* from pg_enum;
   enumtypid   | enumtypid |            enumlabel            
---------------+-----------+---------------------------------
 genero2       |     25132 | Masculino
 genero2       |     25132 | Femenino
 genero2       |     25132 | Desconocido
(...)

Sabemos entonces que tenemos el tipo genero2, que incluye los elementos detallados en la tabla A1. Haremos un par de inserciones en una tabla con este tipo para ver de que viene la cosa:

CREATE TABLE tabla_gen ( genero genero2);
INSERT INTO tabla_gen VALUES('Masculino'::genero2), ('Femenino'::genero2);
INSERT INTO pg_enum VALUES('genero2'::regtype, 'Nuevo');
INSERT INTO tabla_gen VALUES('Masculino'::genero2), ('Nuevo'::genero2);

Revisemos como quedó la tabla

modeloscomunes=# select * from tabla_geN;
  genero   
-----------
 Masculino
 Femenino
 Masculino
 Nuevo
(4 filas)

Que tal humanos

Una vista materializada en Postgresql es posible implementarla a través de disparadores. Este tipo de cosas puede ser encontradas en cualquier tutorial de vistas materializadas.

Queremos hacer una vista similar a:

postgres=# select id, array_agg(banco) from tp1_bcra group by id;
 id | array_agg 
  3 | {a,g}
  2 | {a}

El contenido de la tabla tp1_bcra es:

postgres=# select * from tp1_bcra;
 id | banco | periodo | situacion | monto 
  2 | a     |       2 |         3 |   313
  3 | a     |       2 |         3 |   314
  3 | g     |       2 |         3 |   314
(3 filas)

Sencillo? Muy.

Básicamente optaremos por utilizar la función array_agg (viene incluída en 8.4). Si posees una versión menor a 8.4 deberás correr la siguiente sentencia:

CREATE AGGREGATE array_agg(anyelement)
	(
	        sfunc=array_append,
	        stype=anyarray,
	        initcond='{}'
	);

También utilizaremos una función para eliminar elementos de un array (gracias por el aporte de Depesz => arrays):

CREATE OR REPLACE FUNCTION array_remove_sql(text[], text) RETURNS text[] AS $$
SELECT array(SELECT $1[i] 
                FROM generate_series(array_lower($1,1), array_upper($1,1)) i 
                WHERE $1[i]  $2);
$$ LANGUAGE SQL IMMUTABLE;

Utilizaremos disparadores AFTER. Podríamos utilizar BEFORE, pero para este caso en particular consideré que la consistencia de esta vista no era ‘determinante’. Para esto utilizaremos una función:

CREATE OR REPLACE FUNCTION actualizar_MV() RETURNS TRIGGER AS $BODY$
DECLARE
BEGIN
    IF TG_OP = 'DELETE' THEN
        UPDATE tp1_bcra_mv SET banco = array_remove_sql(banco, OLD.banco::text) WHERE id = OLD.id;
    ELSE 
	    UPDATE tp1_bcra_mv SET banco = array_append(banco, NEW.banco::text) WHERE id = NEW.id ;
	   IF NOT FOUND THEN
		    INSERT INTO tp1_bcra_mv VALUES(NEW.id,array_append('{}',NEW.banco));
	    END IF;
    END IF;
	RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql;
 
DROP TRIGGER act_tpc1_MV ON tp1_bcra;
CREATE TRIGGER act_tpc1_MV AFTER INSERT OR UPDATE OR DELETE ON tp1_bcra FOR EACH ROW EXECUTE PROCEDURE actualizar_MV();

La idea es uqe se vayan actualizando los arrays de la tabla ‘vista’. El único problema será que cuando no queden bancos por un determinado id, este no se borrará en la tabla vista, solo quedará un array ‘{}’. Este esquema fue hecho para un Datawarehouse, por lo que verán ausencias de primary key y constraints y checks.

Si queremos que cuando el valor del array esté vacio, sea NULL podremos utilizar en el update un CASE:

UPDATE tp1_bcra_mv 
    SET banco = CASE array_remove_sql(banco, OLD.banco::text) = '{}' 
                               WHEN true THEN NULL 
                               ELSE array_remove_sql(banco, OLD.banco::text) END  WHERE id = OLD.id;

En el caso de que tengamos que utlizar valores acumulables que sean enteros, quizás puedan apreciar intarray

Tendremos un inconveniente si queremos utilizar la última versión de Postgis en Ubuntu ya que este no incluye libgeos-3.1.1.1, por lo que tendremos que compilar postgis + las librerías.

Descargas:

geos-3.1.1
postgis

apt-get install  proj-bin libproj-dev gdal-bin libgdal-dev libgeos-dev build-essential
apt-get install xml2 libwbxml2-utils libxml2-dev libxml2-utils

Primero compilamos geos, con ./configure, make, make install.
Luego podremos compilar Postgis de la misma manera.

Creamos una base para template de postgis y luego, dentro del directorio de instalación de postgis, debemos correr el .sql:

psql postgis_template < postgis.sql

Para los que están apurados en ver la versión 9.0 con postgis, puede que si compilan sin redireccionar las librerías, les puede tirar este error:


ERROR: incompatible library "/opt/pgsql9beta2/lib/postgis-1.5.so": version mismatch
DETAIL: Server is version 9.0, library is version 8.4.


Los Advisory Locks nos permiten bloquear la ejecución paralela de un determinado procedimiento. Esto es útil cuando la función ejecuta tareas de mucha exigencia en el servidor o cuando queremos realizar actualizaciones.

Nada mejor que un ejemplo. Para ello creamos la función:

CREATE OR REPLACE FUNCTION f_adv() RETURNS void AS $BODY$
DECLARE
foid oid:= 'f_adv'::regproc::oid;
BEGIN
IF pg_try_advisory_lock(foid::bigint) THEN
RAISE NOTICE 'Bloqueo: %', foid;
perform pg_sleep(20);
perform pg_advisory_unlock(foid::bigint);
ELSE
PERFORM 'select * from pg_locks where objid = ' || foid || ';';
RAISE NOTICE 'Se esta ejecutando la funcion: %', foid;
END IF;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql';

Para probar, abra dos terminales y ejecute en cada una:

SELECT f_adv();

¿Como podemos implementar de manera real esto? Aqui otro ejemplo:

CREATE FUNCTION sui_localidades ( p_localidad      mug_localidades.localidad%TYPE
,p_nombre                  mug_localidades.nombre%TYPE
,p_nombre_abreviado  mug_localidades.nombre_abreviado%TYPE
,p_dpto_partido      mug_localidades.dpto_partido%TYPE
,p_ddn                mug_localidades.ddn%TYPE
,p_update           char(1) DEFAULT 'S')
RETURNS void AS $BODY$
DECLARE
foid oid:= 'siu_localidades'::regproc::oid;
BEGIN
IF pg_try_advisory_lock(foid::bigint) THEN
--perform pg_sleep(10);
RAISE NOTICE 'siu_localidades empezara la actualizacion';
ELSE
RAISE NOTICE 'siu_localidades esta siendo ejecutada en otro proceso.';
RETURN;
END IF;
BEGIN
INSERT INTO mug_localidades ( localidad, nombre, nombre_abreviado, dpto_partido, ddn )
VALUES ( p_localidad, p_nombre, p_nombre_abreviado, p_dpto_partido, p_ddn );
EXCEPTION WHEN UNIQUE_VIOLATION THEN
UPDATE mug_localidades
SET nombre           = p_nombre,
nombre_abreviado = p_nombre_abreviado,
dpto_partido     = p_dpto_partido,
ddn              = p_ddn
WHERE localidad      = p_localidad;
END;
pg_advisory_unlock(foid::bigint); --quitamos el lock
END;
$BODY$
AS LANGUAGE 'plpgsql';