Pasar al contenido principal

¿Qué es nuevo en PostgreSQL 9.0?

Con el lanzamiento de la versión 9.0 se han introducido una serie de nuevas características y cambios con respecto a la version 8.4, que en muchos casos harán la vida más fácil a todos los usuarios que usen esta base de datos.

Este artículo está basado prácticamente en su totalidad en información publicada en el wiki oficial de PostgreSQL y en las pruebas realizadas durante el periodo de desarrollo de la versión 9.0.

El número de mejoras introducidas en la versión 9.0 son mas de 200, en este artículo solamente vamos a nombrar las más importantes y destacadas. Para una lista completa consultar "Release Notes 9.0" en la documentación oficial de PostgreSQL.

Para empezar tenemos que hablar de tres características que muchos usuarios y potenciales usuarios de PostgreSQL han estado esperando durante mucho tiempo y que por fin ya están implementadas en el núcleo de PostgreSQL. Se afirma en muchos medios que por la funcionalidad implementada con estas tres nuevas características, muchos usuarios se decidirán por fin a migrar a PostgreSQL.

 

  • Streaming replication (SR): Esta nueva funcionalidad nos permite transferir asincrónicamente registros WAL sobre la marcha (record-based log shipping) entre un servidor maestro y uno/varios esclavos.
  • Hot Standby (HS): Esta nueva funcionalidad nos permite acceder en modo de solo-lectura a todos los datos disponibles en el servidor esclavo en donde estamos replicando nuestras bases de datos desde un servidor maestro.
  • Actualización "in-situ" con pg_upgrade: Con esta fucionalidad se podra actualizar a la versión 9.0 "in-situ" desde cualquier sistema que use PostgreSQL 8.3 og 8.4, sin necesidad de realizar un volcado completo y posterior carga de los datos. Esta funcionalidad será de gran ayuda a todos los usuarios que tengan bases de datos muy grandes, y acortara los tiempos de parada cuando tengan que actualizar a nuevas versiones de PostgreSQL.

Teneis un artículo de introducción sobre como configurar y usar "Hot Standby y Streaming replication" en esta web. En breve intentaremos escribir uno sobre pg_upgrade.

A continuación enumeramos detalladamente el resto de características más importantes organizadas por temas:

Seguridad y autentificación

GRANT/REVOKE IN SCHEMA: Por fin se pueden definir privilegios para todas las tablas, secuencias y funciones en un esquema (schema) con un solo comando SQL. Hasta la versión 8.4, esto solo se podia hacer mediante un script ó un procedimiento almacenado.

 Ejemplos:

 postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO asuario;
 postgres=# GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO asuario;
 postgres=# GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO asuario;

Nota: Estos comandos solamente afectan a los objetos que existan en el momento de ejecutar el comando y no a los que se creen en un futuro. Para definir los privilegios por defecto de objetos que se creen en el futuro podemos utilizar ALTER DEFAULT PRIVILEGES

ALTER DEFAULT PRIVILEGES: Con este comando podremos definir los privilegios por defecto que se usarán para un determinado usuario/rol.

 postgres=# ALTER DEFAULT PRIVILEGES FOR ROLE usuario GRANT SELECT ON TABLES TO public;
 postgres=# CREATE TABLE test_priv (a int);
 postgres=# \z test_priv
                                 Access privileges
   Schema |    Name    | Type  |    Access privileges    | Column access privileges
  --------+------------+-------+-------------------------+--------------------------
   public | test_priv  | table | =r/usuario             +|
          |            |       | usuario=arwdDxt/usuario |
  

Características SQL

Disparadores de columnas: Los disparadores de columnas se ejecutan cuando una ó varias columnas definidas por el usuario se actualizan. Con este tipo de disparador nos ahorraremos el tener que utilizar lógica condicional y comparación de valores en las funciones usadas por nuestro disparador, como se ha tenido que hacer hasta la versión 8.4.

 # CREATE TRIGGER prueba BEFORE UPDATE OF una_columna ON tabla1 FOR EACH ROW EXECUTE PROCEDURE mi_disparador();
  

Disparadores WHEN: Con los disparadores condicionales se puede definir una condición en la definición del disparador. De esta manera nos ahorramos el tener que usar una condición IF...THEN dentro del disparador, como se ha tenido que hacer hasta la version 8.4. Esto puede reducir mucho el numero de disparadores que se tienen que ejecutar con la consiguiente disminución del uso de CPU en el servidor.

 En este ejemplo solamente se ejecutará la función check_account_update()
 si el valor de OLD.balance es diferente al de NEW.balance

 postgres=# CREATE TRIGGER check_update
            BEFORE UPDATE ON accounts
            FOR EACH ROW
            WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
            EXECUTE PROCEDURE check_account_update();
  

DEFERRABLE CONSTRAINTS: Con esta nueva funcionalidad, la unicidad de una restricción es forzada al final de la sentencia SQL ejecutada y no en cada tupla actualizada.

Un ejemplo nos ayudará a entenderlo mejor.

 postgres=# CREATE TABLE test (a int primary key);
 postgres=# INSERT INTO test values (1), (2);
 postgres=# UPDATE test set a = a+1;

            ERROR:  duplicate key value violates unique constraint "test_pkey"
            DETAIL:  Key (a)=(2) already exists.

Esto es lo que ocurriria hasta la versión 8.4. Una sentencia SQL que a su termino dejara la base de datos en un estado consistente y perfectamente válido en relación a las restricciones definidas, fallaria y no se podria ejecutar sin hacer malabarismos con el orden de ejecución, etc.

Con la versión 9.0 podemos hacer lo siguiente:

 postgres=# CREATE TABLE test (a int primary key deferrable);
 postgres=# INSERT INTO test values (1), (2);
 postgres=# UPDATE test set a = a+1;
            UPDATE 2

También es posible forzar la unicidad de una restricción al final de una transacción en vez de al final de una sentencia SQL. De esta manera podremos realizar actualizaciones "conflictivas" que necesiten de mas de una sentencia SQL.

 postgres=# CREATE TABLE test (a int primary key deferrable, b text);
 postgres=# INSERT INTO test values (1, 'x'), (2, 'y');
 postgres=# BEGIN;
 postgres=# SET CONSTRAINTS ALL DEFERRED;
 postgres=# UPDATE test SET a = 2 WHERE b = 'x';
 postgres=# UPDATE test SET a = 1 WHERE b = 'y';
 postgres=# COMMIT;

Si no queremos tener que activar manualmente el uso de restricciones aplazadas con SET CONSTRAINTS, podemos usar INITIALLY DEFERRED cuando declaremos la restricción.

 postgres=# CREATE TABLE test (a int PRIMARY KEY DEFERRABLE INITIALLY DEFERRED);

Hay que tener en cuenta que la lista de comprobaciones a realizar al final de la sentencia ó transacción ocupará recursos en nuestro servidor. Es por ello que, por defecto, las restricciones aplazadas no están activadas y hay que tener precaución cuando vayamos a realizar actualizaciones de millones de tuplas a la vez.

Ordenación en funciones agregadas: El resultado de una función agregada del tipo array_agg o string_agg, dependerá del orden en que reciba los datos. Sin el uso de la ordenación en la función agregada obtendriamos este resultado.

 Tabla ejemplo:

 postgres=# SELECT * FROM salary ;

 entity     | name      | salary  | start_date
 -----------+-----------+---------+---------------
 R&D        | marc      |  700.00 | 2010-02-15
 Accounting | jack      |  800.00 | 2010-05-01
 R&D        | maria     |  700.00 | 2009-01-01
 R&D        | kevin     |  500.00 | 2009-05-01
 R&D        | john      | 1000.00 | 2008-07-01
 R&D        | tom       | 1100.00 | 2005-01-01
 Accounting | millicent |  850.00 | 2006-01-01

 postgres=# SELECT entity,string_agg(name,', ') FROM salary GROUP BY entity;

 entity     |          string_agg          
 -----------+-------------------------------
 Accounting | stephanie, etienne
 R&D        | marc, maria, kevin, john, tom

Con la versión 9.0 podemos hacer uso de un ORDER BY dentro de la función agregada para ordenar el resultado.

 postgres=# SELECT entity,string_agg(name,', ' ORDER BY name) FROM salary GROUP BY entity;

 entity     |          string_agg          
 -----------+-------------------------------
 Accounting | etienne, stephanie
 R&D        | john, kevin, marc, maria, tom
  

Administración de bases de datos

Con la versión 9.0 también se ha introducido nueva funcionalidad enfocada expresamente a los administradores de bases de datos.

VACUUM FULL mejorado: Hasta la versión 8.4, VACUUM FULL ha sido una operación muy lenta y que ha consumido muchos recursos. Esta lentitud ha sido debida al tipo de ejecución interna de este comando, en donde las tuplas se han copiado una a una a espacios libres al principio de la tabla, creando mucho IO aleatorio. A su vez, los índices tenian que mantenerse, creando una fragmentación de los mismos, por lo que siempre se ha recomendado ejecutar un REINDEX después de ejecutar VACUUM FULL.

Con la versión 9.0, VACUUM FULL crea una nueva tabla copiando sequencialmente todas la tuplas de la tabla original. Cuando la nueva tabla está terminada, crea automáticamente los índices y borra la tabla antigua. De esta manera, VACUUM FULL funciona mucho más rápido en la versión 9.0. Pero todavia necesita un AccessExclusiveLock mientras que se ejecuta, con lo que es imposible acceder a la tabla en cuestión durante la ejecución del comando.

De todas maneras, VACUUM FULL es un comando que no se deberia de ejecutar en sistemas en producción, y si alguien necesita utilizarlo es probablemente porque no tiene una política de VACUUM adecuada en su sistema.

En este ejemplo podeis ver la diferencia de tiempos entre la versión 8.4 y 9.0:

 postgres=# CREATE TABLE test (a int);
 CREATE TABLE
 postgres=# CREATE INDEX idxtsta on test (a);
 CREATE INDEX
 postgres=# INSERT INTO test SELECT generate_series(1,1000000);
 INSERT 0 1000000
 postgres=# DELETE FROM test where a%3=0; -- making holes everywhere
 DELETE 333333
 postgres=# VACUUM test;
 VACUUM

Con 8.4, unos 8 segundos:

 postgres=# \timing
 Timing is on.
 postgres=# VACUUM FULL test;
 VACUUM
 Time: 6306,603 ms
 postgres=# REINDEX TABLE test;
 REINDEX
 Time: 1799,998 ms

Con 9.0, alrededor de 2,5 segundos:

 postgres=# \timing
 Timing is on.
 postgres=# VACUUM FULL test;
 VACUUM
 Time: 2563,467 ms

application_name en pg_stat_activity: Con esta nueva funcionalidad podemos definir el nombre de la aplicación que está accediendo el servidor. Para ello hay que definir la variable application_name en la aplicación/sesión en cuestion.

 postgres=# SELECT * from pg_stat_activity where procpid= 5991;
 datid | datname | procpid | usesysid | usename | application_name | client_addr | client_port |         backend_start         | xact_start | query_start | waiting | current_query
 ------+---------+---------+----------+---------+------------------+-------------+-------------+-------------------------------+------------+-------------+---------+----------------
 16384 | postgres|    5991 |       10 | postgres| psql             |             |          -1 | 2010-05-16 13:48:10.154113+02 |            |             | f       | 
 (1 row)

 postgres=# SET application_name TO 'mi_aplicacion';
 SET

 postgres=# SELECT * from pg_stat_activity where procpid= 5991;
 datid | datname | procpid | usesysid | usename | application_name | client_addr | client_port |         backend_start         | xact_start |          query_start          | waiting | current_query
 ------+---------+---------+----------+---------+------------------+-------------+-------------+-------------------------------+------------+-------------+---------+-----------------+----------------
 16384 | postgres|    5991 |       10 | postgres| mi_aplicacion    |             |          -1 | 2010-05-16 13:48:10.154113+02 |            | 2010-05-16 13:49:13.107413+02 | f       | 
 (1 row)

Configuración per bases de datos/rol: Con la versión 9.0 podemos definir parametros de configuración para un determinado usuario en una determinada base de datos.

 postgres=# ALTER ROLE postgres IN database postgres set log_statement to 'all';
 ALTER ROLE

 postgres=# \drds
         List of settings
   role   |   database   |     settings
 ---------+--------------+-----------------
 postgres | postgres     | log_statement=all
 (1 row)

Registrar todos los parametros cambiados después de una recarga de postgresql.conf: Con esta función podemos estar seguros de los parámetros de configuración que hemos cambiado en el fichero postgresql.conf.

 Ejemplo cuando hemos cambiado el parametro 'log_line_prefix'

 LOG:  received SIGHUP, reloading configuration files
 LOG:  parameter "log_line_prefix" changed to "<%u%%%d> "

Mensajes de error mejorados al usar restricciones únicas: Con esto será más fácil diagnosticar errores generados cuando se viola una restricción única.

Con 8.4:

 postgres=# INSERT INTO test VALUES (1);
 ERROR:  duplicate key value violates unique constraint "test_a_key"

Con 9.0:

 postgres=# INSERT INTO test VALUES (1);
 ERROR:  duplicate key value violates unique constraint "test_a_key"
 DETAIL:  Key (a)=(1) already exists.

vacuumdb --analyze-only: El programa vacuumdb tiene ahora una nueva opción disponible para ejecutar ANALYZE solamente.

Rendimiento

Binarios de 64 bit para Windows: Con la versión 9.0, los usuarios que quieran ejecutar una versión de PostgreSQL de 64 bit en sistemas Windows podrán hacerlo sin problemas. Con esta versión se consigue mejor rendimiento en operaciones con números de 64 bit, la posibilidad de utilizar más de 2GB de work_mem, y una mejora en la compatibilidad con versiones de PostgreSQL de 64 bit en Linux (Importante cuando se use Hot Standby)

Eliminación de Joins: Esta nueva optimización permite eliminar automáticamente JOINS innecesarios en los planes de ejecución SQL. Cuando se usan sentencias SQL generadas automáticamente, por ejemplo, generadas por herramientas OCRs (Object Relation Mapping), es posible y común que la sentencia SQL generada no sea óptima. Con la eliminación automática de joins que no se necesitan, los planes de consultas internos se pueden mejorar sustancialmente.

Otro caso típico que se verá afectado por esta nueva mejora es cuando se usen VIEWS que contengan JOINS. En el siguiente ejemplo podeis ver como se elimina el join definido en la vista 'test' cuando elegimos solamente los atributos perteneciente a la tabla 'registro'.

 postgres=# SELECT * from registro ;
  id |  nombre  | estatus 
 ----+----------+---------
   1 | nombre-1 |       1
   2 | nombre-2 |       1
   3 | nombre-3 |       2
 (3 rows)

 postgres=# SELECT * from estatus ;
  id |  estatus  
 ----+-----------
   1 | no activo
   2 | activo
 (2 rows)

 postgres=# \d+ test
                    View "public.test"
  Column  |  Type   | Modifiers | Storage  | Description 
 ---------+---------+-----------+----------+-------------
  id      | integer |           | plain    | 
  nombre  | text    |           | extended | 
  estatus | text    |           | extended | 
 View definition:
  SELECT registro.id, registro.nombre, estatus.estatus
    FROM registro
    LEFT JOIN estatus ON registro.estatus = estatus.id; 

 postgres=# SELECT * from test ;
  id |  nombre  |  estatus  
 ----+----------+-----------
   1 | nombre-1 | no activo
   2 | nombre-2 | no activo
   3 | nombre-3 | activo
 (3 rows)

Si elegimos todos los atributos de la vista 'test', se ejecutará el join definido en la misma:

postgres=# EXPLAIN ANALYZE SELECT * from test ;
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=37.67..75.22 rows=1160 width=68) (actual time=0.017..0.019 rows=3 loops=1)
   Hash Cond: (registro.estatus = estatus.id)
   ->  Seq Scan on registro  (cost=0.00..21.60 rows=1160 width=40) (actual time=0.003..0.003 rows=3 loops=1)
   ->  Hash  (cost=22.30..22.30 rows=1230 width=36) (actual time=0.004..0.004 rows=2 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 1kB
         ->  Seq Scan on estatus  (cost=0.00..22.30 rows=1230 width=36) (actual time=0.001..0.001 rows=2 loops=1)
 Total runtime: 0.038 ms
(7 rows)

Si elegimos, por ejemplo, los atributos de la vista 'test' pertenecientes a la tabla 'registro', no se ejecutará el join porque no hace falta realizar esta operación para devolver el resultado correcto:

postgres=# EXPLAIN ANALYZE SELECT id,nombre from test ;
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Seq Scan on registro  (cost=0.00..21.60 rows=1160 width=40) (actual time=0.004..0.004 rows=3 loops=1)
 Total runtime: 0.018 ms
(2 rows)

Uso de índices con IS NOT NULL: La versión 9.0 solamente tiene que buscar los valores no nulos (not-null) en el índice, en vez de tener que comprobar los valores en la tabla.

Con 8.4:

 postgres=# EXPLAIN ANALYZE SELECT max(a) from test01;
 QUERY PLAN                                                                  
 ------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.03..0.04 rows=1 width=0) (actual time=281.320..281.321 rows=1 loops=1)
 InitPlan 1 (returns $0)
   ->  Limit  (cost=0.00..0.03 rows=1 width=4) (actual time=281.311..281.313 rows=1 loops=1)
   ->  Index Scan Backward using idxa on test  (cost=0.00..29447.36 rows=1001000 width=4) (actual time=281.307..281.307 rows=1 loops=1)
 Filter: (a IS NOT NULL)
 Total runtime: 281.360 ms
 (6 rows)

Con 9.0:

postgres=# EXPLAIN ANALYZE SELECT max(a) from test01;
 QUERY PLAN                                                                
 --------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.08..0.09 rows=1 width=0) (actual time=0.100..0.102 rows=1 loops=1)
 InitPlan 1 (returns $0)
   ->  Limit  (cost=0.00..0.08 rows=1 width=4) (actual time=0.092..0.093 rows=1 loops=1)
   ->  Index Scan Backward using idxa on test  (cost=0.00..84148.06 rows=1001164 width=4) (actual time=0.089..0.089 rows=1 loops=1)
 Index Cond: (a IS NOT NULL)
 Total runtime: 0.139 ms
 (6 rows)

Segun los casos, la mejora del rendimiento puede ser bastante grande.

Nueva sintaxis opcional para EXPLAIN: El comando EXPLAIN acepta ahora nuevos parámetros que nos ayudarán a obtener información más detallada y presentar esta información en diferentes formatos:

EXPLAIN [ ( { ANALYZE boolean | VERBOSE boolean | COSTS boolean | BUFFERS boolean | 
              FORMAT { TEXT | XML | JSON | YAML } } [, ...] ) ] instruccion
postgres=# EXPLAIN (ANALYZE true, VERBOSE true, BUFFERS true, COSTS true, FORMAT TEXT   ) SELECT * from test ;
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=37.67..75.22 rows=1160 width=68) (actual time=0.016..0.017 rows=3 loops=1)
   Output: registro.id, registro.nombre, estatus.estatus
   Hash Cond: (registro.estatus = estatus.id)
   Buffers: shared hit=2
   ->  Seq Scan on public.registro  (cost=0.00..21.60 rows=1160 width=40) (actual time=0.004..0.005 rows=3 loops=1)
         Output: registro.id, registro.nombre, registro.estatus
         Buffers: shared hit=1
   ->  Hash  (cost=22.30..22.30 rows=1230 width=36) (actual time=0.005..0.005 rows=2 loops=1)
         Output: estatus.estatus, estatus.id
         Buckets: 1024  Batches: 1  Memory Usage: 1kB
         Buffers: shared hit=1
         ->  Seq Scan on public.estatus  (cost=0.00..22.30 rows=1230 width=36) (actual time=0.002..0.003 rows=2 loops=1)
               Output: estatus.estatus, estatus.id
               Buffers: shared hit=1
 Total runtime: 0.038 ms
(15 rows)

Per tablespace seq_page_cost/random_page_cost: Ahora podemos definir diferentes valores para los parámetros seq_page_cost y random_page_cost per tablespace. De esta manera podemos decirle a PostgreSQL que no todas nuestras tablespaces se comportan de la misma manera y que pueden tener diferentes rendimientos.

postgres=# ALTER TABLESPACE pg_default SET ( random_page_cost = 10, seq_page_cost=5);
 ALTER TABLESPACE

Forzar estadísticas distintas en un columna: Con esta funcionalidad podremos definir explícitamente el número de valores distintos en una columna. Este valor será el usado por las estadísticas internas para generar el plan de ejecución a usar. Hay que tener mucho cuidado si usamos esta funcionalidad porque podemos obtener unos rendimientos bastantes malos si no sabemos lo que hacemos.

A continuación teneis un ejemplo de lo que no se deberia de hacer con esta funcionalidad. Vamos a definir el número de valores diferentes en una columna igual a 2 cuando en realidad existen 370.000. Este cambio haria que el plan de ejecución elegido y el rendimiento no fuesen óptimos.

 postgres=# ALTER TABLE test ALTER COLUMN a SET (n_distinct = 2);
 ALTER TABLE

 postgres=# ANALYZE test;
 ANALYZE

 postgres=# EXPLAIN SELECT distinct * from test;
 QUERY PLAN                           
 ------------------------------------------------------------------
 HashAggregate  (cost=6263.00..6263.02 rows=2 width=8)
 ->  Seq Scan on test  (cost=0.00..5338.00 rows=370000 width=8)
 (2 rows)

Registro de sentencias SQL con auto_explain: El módulo contrib 'auto_explain' presenta ahora la sentencia ejecutada junto con el plan de ejecución usado.

Contabilización de buffers con pg_stat_statements: El modulo contrib 'pg_stat_statements' presenta también ahora información sobre los buffers usados por las diferentes sentencias ejecutadas. Con este módulo podremos obtener información del sistema con la que contestar a estas preguntas:

  • ¿Qué consulta tiene el mayor tiempo de ejecución acumulado?
  • ¿Qué consultas generan más IO en el sistema?
  • ¿Qué consultas usan el cache más a menudo?
  • ¿Qué consulta modifica más bloques de datos?

Aquí teneis un ejemplo de como se presenta la información con este módulo:

 postgres=# SELECT * from pg_stat_statements order by total_time desc limit 2;
 -[ RECORD 1 ]-------+---------------------
 userid              | 10
 dbid                | 16485
 query               | SELECT * from table1 ;
 calls               | 2
 total_time          | 0.491229
 rows                | 420000
 shared_blks_hit     | 61
 shared_blks_read    | 2251
 shared_blks_written | 0
 local_blks_hit      | 0
 local_blks_read     | 0
 local_blks_written  | 0
 temp_blks_read      | 0
 temp_blks_written   | 0
 -[ RECORD 2 ]-------+---------------------
 userid              | 10
 dbid                | 16485
 query               | SELECT * from table2;
 calls               | 2
 total_time          | 0.141445
 rows                | 200000
 shared_blks_hit     | 443
 shared_blks_read    | 443
 shared_blks_written | 0
 local_blks_hit      | 0
 local_blks_read     | 0
 local_blks_written  | 0
 temp_blks_read      | 0
 temp_blks_written   | 0

Procedimientos almacenados

PL/pgSQL por defecto: A partir de ahora no tendremos que cargar el lenguaje PL/pgSQL explícitamente en cada base de datos que necesite acceder a este lenguaje de programación. PL/pgSQL se encuentra disponible por defecto y no hay que instalarlo/activarlo.

Mejoras en lenguajes PL: Muchos otros lenguajes PL han sido mejorados. Especialmente PL/perl y PL/Python.

Funciones anónimas (bloques anónimos): Esta nueva funcionalidad permite crear 'funciones de una sola ejecución'. Podremos ejecutar desde la linea de comandos ó scripts código de procedimientos sin necesidad de crear y destruir una función para ello. A continuación teneis un ejemplo:

 DO language plpgsql $$
 DECLARE
 vr record;
 BEGIN 
 FOR vr IN SELECT tablename FROM pg_tables WHERE tableowner = 'marc' 
           AND schemaname NOT IN ('pg_catalog','information_schema')
 LOOP
   EXECUTE 'GRANT SELECT ON ' || vr.tablename || ' TO toto';
 END LOOP;
 END
 $$;

Llamadas con nombres de parámetros: Permite definir el nombre de los parámetros cuando se hace una llamada a una función y los hace coincidir con parametros definidos en la propia función.

Por ejemplo, si hemos definido esta función:

 CREATE FUNCTION test (a int, b text) RETURNS text AS $$
 DECLARE
   value text;
 BEGIN
   value := 'a is ' || a::text || ' and b is ' || b;
   RETURN value;
 END;
 $$ LANGUAGE plpgsql;

Con 8.4 tendriamos que ejecutarla así:

 postgres=# SELECT test(1,'foo');
 test          
 -------------------------
 a is 1 and b is foo
 (1 row)

Pero con 9.0 podemos ejecutarla de estas maneras:

 postgres=# SELECT test(  a:=1, b:='foo');
 test          
 -------------------------
 a is 1 and b is foo
 (1 row)

 postgres=# SELECT test( b:='foo', a:=1);
 test          
 -------------------------
 a is 1 and b is foo
 (1 row)

A causa de esta nueva característica, no se podrá cambiar la definición de los parámetros de una función con REPLACE, hay que borrar y crear la función con la nueva definición.

Palabra clave ALIAS: ALIAS se puede utilizar ahora para crear un alias no solo a parámetros definidos con $ (por ejemplo, $1), sino a cualquier variable usada en una función. Con esto evitamos el tener que usar RENAME.

 
new_value ALIAS FOR new

 

Características varias

Paso de mensajes con NOTIFY/pg_notify: Ahora podemos enviar mensajes cuando usemos NOTIFY/pg_notify. Por ejemplo:

Sesión 1 - Suscribir la sesión 1 a la cola 'mensajes'

 postgres=# LISTEN mensajes;
 LISTEN

Sesión 2 - Mandar una notificación con mensaje a la cola 'mensajes'

 postgres=# NOTIFY mensajes, 'Has recibido un mensaje';
 NOTIFY

Sesión 1 - Comprobar la cola 'mensajes'

 postgres=# LISTEN mensajes;
 LISTEN
 Asynchronous notification "mensajes" with payload "Has recibido un mensaje" received from server process with PID 5941

También podriamos haber usado la función pg_notify() para mandar una notificación con mensaje.

 SELECT pg_notify('mensajes','Has recibido un mensaje');

Mejoras en el módulo contrib Hstore: Este módulo contrib que se puede utilizar para trabajar con datos del tipo llave-valor ha sido mejorado con:

  • El limite de tamaño de datos del tipo llave-valor ya no existe.
  • GROUP BY y DISTINCT se pueden usar con datos del tipo llave-valor
  • Nuevos operadores y funciones

Compatibilidad hacia atrás y problemas de actualización

Se han realizado dos cambios en el lenguaje PL/pgSQL que pueden afectar a la compatibilidad con versiones anteriores. Es por ello que se aconseja revisar el código pl/pgsql en uso con versiones anteriores de PostgreSQL antes de actualizar a la versión 9.0.

Eliminación de la ambiguedad en el nombre de columnas / variables: Hasta la versión 8.4, a igual nombre, los nombres de variables en PL/pgSQL tenian preferencia sobre los nombres de columnas. Por defecto la versión 9.0 genera un error si se da este caso.

 postgres=# DO LANGUAGE plpgsql
 $$
 DECLARE
   a int;
 BEGIN
   SELECT a FROM test;
 END
 $$
 ;
 ERROR:  column reference "a" is ambiguous
 LINE 1: select a from test
 DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
 QUERY:  select a from test
 CONTEXT:  PL/pgSQL function "inline_code_block" line 4 at SQL statement

Este comportamiento se puede alterar globalmente ó per función con la variable 'variable_conflict'

Palabras reservadas: A partir de la versión 9.0 no se pueden utilizar palabras reservadas como variables sin definirlas entre comillas.

 postgres=# DO LANGUAGE plpgsql
 $$
 DECLARE
 table int;
 BEGIN
 table :=table+1;
 END
 $$
 ;
 ERROR:  syntax error at or near "table"
 LINE 6: table :=table+1;

La sintaxis correcta es:

 postgres=# DO LANGUAGE plpgsql
 $$
 DECLARE
 "table" int;
 BEGIN
 "table" :="table"+1;
 END
 $$
 ;
 DO

Aunque lo mejor es no utilizar palabras reservadas como variables.

Añadir nuevo comentario

Código de idioma del comentario.

Texto sin formato

  • No se permiten etiquetas HTML.
  • Saltos automáticos de líneas y de párrafos.
  • Las direcciones de correos electrónicos y páginas web se convierten en enlaces automáticamente.