Questa nuova funzionalità permette di eliminare un indice evitando di acquisire un lock di tipo esclusivo sull’intera tabella, migliorando la disponibilità della base dati. In pratica, la cancellazione di un indice non bloccherà operazioni di UPDATE, DELETE, INSERT o SELECT sulla tabella, come avveniva con il comando DROP INDEX.

Nel dettaglio, il tipo di lock richiesto da DROP INDEX CONCURRENTLY è ShareUpdateExclusiveLock, mentre DROP INDEX richiede un lock di tipo AccessExclusiveLock.

Come descritto nella documentazione del comando DROP INDEX, la rimozione concorrente è atomica e non può avvenire all’interno di un blocco di transazione (pertanto non è possibile specificare più di un indice per volta).

PostgreSQL 9.2 introduce alcune nuove statistiche alla vista del catalogo pg_stat_database.

Le statistiche aggiunte sono effettivamente presenti anche nei log di Postgres, ma inserendole nel catalogo risultano molto più semplici da consultare.

Una di queste è il numero di deadlock rilevati per ogni database.

L’altra informazione statistica aggiunta riguarda la creazione di file temporanei. Postgres utilizza dei file temporanei su disco ogni volta che la memoria non è sufficiente per eseguire operazioni come ordinamenti o join. La dimensione massima di memoria utilizzabile da Postgres è specificata dal parametro di configurazione work_mem.

Le colonne aggiunte a pg_stat_database sono denominate temp_files e temp_bytes ed indicano rispettivamente il numero di file temporanei creati e la loro dimensione totale. Questi valori non sono istantanei ma vengono incrementati nel tempo.

Ecco un esempio di come consultare le nuove statistiche:

SELECT datname, deadlocks, temp_files, temp_bytes FROM pg_stat_database
    WHERE datname='nome_del_database';

E come appare il risultato:

       datname      | deadlocks | temp_files | temp_bytes
 -------------------+-----------+------------+------------
  nome_del_database |         3 |          2 |  280000000
 (1 row)

Inserendo queste statiatiche direttamente nel catalogo, è stata facilitata l’integrazione con strumenti di monitoraggio di sistema. Risulterà semplice, ad esempio, produrre grafici per tenere traccia dei deadlock e dei file temporanei.

pg_basebackup si comporta come un normale client del database e possiede le opzioni tipiche della libpq. Lavora “a caldo” (gli altri client connessi al server non subiranno nessuna conseguenza durante l’esecuzione di pg_basebackup).

Ricordatevi inoltre che non è possibile eseguire dei base backup di singoli database con questo strumento – per effettuare backup di singoli database avrete bisogno di uno strumento come pg_dump.

Dalla versione 9.2 di Postgres, è possibile eseguire il base backup di un server che è in esecuzione come slave in un cluster replicato.

Ad esempio, assumiamo di avere in piedi un cluster replicato.

Il server slave di Postgres è in esecuzione sulla porta 5433:

 postgres=# SELECT current_setting('port') AS port, pg_is_in_recovery() AS standby;
  port | standby
 ------+---------
  5433 | t
 (1 row)

Proviamo ad eseguire pg_basebackup, usando:

$ pg_basebackup -vD /destinazione/del/base_backup -p 5433

Su Postgres 9.1 questo comando restituirà il seguente errore:

pg_basebackup: could not connect to server: FATAL:  recovery is still in progress, can't accept WAL streaming connections

Mentre su Postgres 9.2 il backup andrà a buon fine:

pg_basebackup: base backup completed

È possibile controllare il contenuto di /destinazione/del/base_backup, per notare che rappresenta una data directory di Postgres perfettamente valida.

Questa nuova caratteristica permetterà di avere a disposizione dei base backup dell’intero cluster aggiornati senza gravare sul server master.

Ecco un’ulteriore funzionalità che contribuisce a rendere sempre più utile il ruolo del server standby ed a migliorare la scalabilità orizzontale di un cluster PostgreSQL in continuità operativa.

I valori ai limiti dell’intervallo possono essere considerati o meno parte di esso. Similmente a quanto avviene in ambito matematico, si utilizzano le parentesi quadre in caso di limite incluso, quelle tonde in caso di limite escluso.

La sintassi per definire un dato di tipo RANGE è:

-- Usando il CAST da una stringa
SELECT '(5,100)'::numrange;

-- Usando l'apposito costruttore
SELECT tstzrange(now(), now() + '1 day'::INTERVAL, '()');

A corredo del tipo di dato, sono stati introdotti numerosi operatori e alcune funzioni di utilità.

Ad esempio, è possibile controllare facilmente che un valore sia contenuto o meno in un dato intervallo.

Ecco un esempio di utilizzo banale:

BEGIN;

CREATE TABLE prodotti AS
    SELECT md5(id::TEXT) AS codice_prodotto,
        round(random() * 1000)+1 AS prezzo
    FROM generate_series(1,10000) id;

-- Seleziona prodotti con prezzo da 1 a 50 €
SELECT codice_prodotto FROM prodotti
    WHERE '(1,50)'::numrange @> prezzo::NUMERIC;

-- Seleziona prodotti con prezzo da 10 a 50 €
SELECT codice_prodotto FROM prodotti
    WHERE '(10,50)'::numrange @> prezzo::NUMERIC;

-- Seleziona prodotti con prezzo da 50 a 1000 €
SELECT codice_prodotto FROM prodotti
    WHERE '(50,1000)'::numrange @> prezzo::NUMERIC;

ROLLBACK;

Nella prima riga, apriamo una transazione con BEGIN;.

Successivamente, creiamo una tabella contente dei dati generati casualmente. La tabella rappresenta i prodotti di un negozio, e contiene un codice_prodotto alfanumerico e un prezzo compreso tra 1 e 1000 euro.

Le successive tre SELECT servono a selezionare i prodotti in base alla fascia di prezzo, sfruttando l’operatore @>, che indica l’appartenenza ad un range di un valore scalare.

La lista completa di operatori e funzioni disponibili è disponibile nella documentazione ufficiale.

Non è difficile immaginare l’impatto che questa funzionalità, unica di Postgres, avrà in ambito data warehousing, scientifico, finanziaria e organizzativo (e.g. calendari).

La partecipazione è gratuita, ma è richiesta la registrazione per motivi organizzativi e logistici.

Avremo modo di presentare in quell’occasione un intervento su PostgreSQL 9.2, uscito il 10 settembre scorso.

È PostgreSQL 9.2, il cui sviluppo si è concentrato sul consolidamento delle funzionalità introdotte in 9.0 e 9.1 e sul miglioramento delle performance e della scalabilità. Non mancano poi importanti novità per gli sviluppatori e per gli utilizzatori in campo business intelligence e data warehousing.

Alcune fra le principali novità:

• replica in streaming a cascata, per scalare orizzontalmente e geograficamente i cluster PostgreSQL
• covering index, grazie alla tecnologia index-only scan
• supporto per il tipo di dato JSON (sviluppatori di web application, divertitevi!)
• supporto per i tipi di dato intervallo, con range numerici e temporali (che spasso per il data warehouse e la storicizzazione!)

Per maggiori informazioni e per un elenco dettagliato delle novità, rimando al comunicato ufficiale: http://www.postgresql.org/about/press/presskit92/it/

Inoltre, puoi consultare tutti i nostri articoli di blog sulle novità introdotte in Postgres 9.2.

PostgreSQL 9.2 sarà ovviamente il protagonista principale del PGDay italiano 2012, che si terrà il 23 novembre prossimo a Prato. È ancora aperta la call for paper e la registrazione early bird è attiva fino ai primi di ottobre (affrettatevi!).

JSON, acronimo di JavaScript Object Notation, è un formato ideato per lo scambio di dati in applicazioni client-server.

PostgreSLQ 9.2 introduce il supporto al tipo di dato JSON.

Per verificarne la presenza, è sufficiente digitare da psql il comando \dTS pg_catalog.json.

Un valore di tipo JSON è in grado di accettare soltanto stringhe JSON valide. Inserire una stringa JSON non valida in un dato di tipo JSON genera un’eccezione.

Vi sono inoltre due funzioni per la conversione a JSON di array e righe, chiamate rispettivamente array_to_json e row_to_json.

L’esempio sottostante mostra come sia possibile convertire in JSON una riga del catalogo di PostgreSQL, in particolare quella che contiene informazioni circa il database postgres.

select row_to_json(d) from pg_database d WHERE datname='postgres';

Il risultato sarà qualcosa di simile a:

       row_to_json

 -----------------------------------------------------------------------------------------------------------
 -----------------------------------------------------------------------------------------------------------
 -------------------------
  {"datname":"postgres","datdba":10,"encoding":6,"datcollate":"en_US.UTF-8","datctype":"en_US.UTF-8","datist
emplate":false,"datallowconn":true,"datconnlimit":-1,"datlastsysoid":12002,"datfrozenxid":"671","dattablesp
ace":1663,"datacl":null}
 (1 row)

Abbastanza illeggibile, vero?

Per avere una formattazione più carina, basterà aggiungere un parametro booleano impostato a True alla funzione row_to_json:

select row_to_json(d, 'True') from pg_database d WHERE datname='postgres';

Che restituirà qualcosa di simile a:

         row_to_json
------------------------------
 {"datname":"postgres",      +
  "datdba":10,               +
  "encoding":6,              +
  "datcollate":"en_US.UTF-8",+
  "datctype":"en_US.UTF-8",  +
  "datistemplate":false,     +
  "datallowconn":true,       +
  "datconnlimit":-1,         +
  "datlastsysoid":12002,     +
  "datfrozenxid":"671",      +
  "dattablespace":1663,      +
  "datacl":null}
(1 row)

Decisamente meglio.

Uno dei vantaggi del formato JSON è rappresentato dall’elevato numero di applicazioni esistenti che lo supportano. Risulterà quindi estremamente semplice convertire una stringa JSON in formati diversi, ad esempio una tabella HTML. Inoltre, sarà interessante valutare sviluppi futuri di questa funzionalità insieme alla tecnologia websocket di HTML5.

Per adesso il supporto a JSON è limitato. Mancano infatti funzioni per la visita di singole coppie chiave/valore, anche se sono state poggiate le basi per evoluzioni future.

A differenza di strumenti di analisi del log di Postgres come il popolare pgFouine, questa estensione svolge il proprio lavoro direttamente all’interno del database.

A partire da PostgreSQL 9.2, è stata aggiunta la normalizzazione delle query SQL. Eventuali valori costanti e stringhe all’interno di una query vengono identificati e assimilati a parametri, al fine di determinare l’impronta della query e aggregare le statistiche di utilizzo.

Per vedere all’opera questa novità, è necessario attivare il modulo. Modificare il file di configurazione di Postgres aggiungendo la riga:

shared_preload_libraries = 'pg_stat_statements'

Questo parametro di configurazione necessita del riavvio del server Postgres.

A riavvio avvenuto, collegarsi ad un database utilizzando psql e creare l’estensione pg_stat_statements. Questa operazione necessita dei privilegi di superutente:

db=# CREATE extension pg_stat_statements;

A questo punto creiamo una tabella contenente dati generati casualmente. La utilizzeremo come fonte per raccogliere statistiche.
Eseguire tramite psql il seguente codice SQL:

CREATE TABLE test AS
    SELECT
        x AS id,
        md5(random()::text) AS p,
        (SELECT count(*) FROM pg_statistic) s
    FROM generate_series(1,10000000) x;

Le statistiche vengono raccolte in una vista chiamata pg_stat_statements.

Tra le nuove metriche aggiunte a questa vista troviamo blk_read_time e blk_write_time.
Queste due colonne sono in grado di mostrare il tempo che PostgreSQL impiega in operazioni di lettura e scrittura su disco (in gergo I/O) per ogni istruzione eseguita.

La raccolta di questo tipo di informazioni ha tuttavia un costo per il server in termini di prestazioni.
Per questo motivo è stato introdotto un nuovo parametro di configurazione che serve ad attivare/disattivare il calcolo di I/O: track_io_timing.

Per attivarlo, eseguiamo con privilegi di superutente:

SET track_io_timing = TRUE;

A questo punto, eseguiamo la seguente query 3 volte, cambiando l’identificativo richiesto:

SELECT * FROM test WHERE id = 1000;
SELECT * FROM test WHERE id = 2000;
SELECT * FROM test WHERE id = 3000;

Consultando la vista pg_stat_statements, è possibile scoprire informazioni sull’utilizzo delle query appena eseguite:

db=# SELECT query, calls, total_time, blk_read_time, blk_write_time FROM pg_stat_statements WHERE query ~ 'SELECT \*';
-[ RECORD 1 ]--+---------------------------------
query          | SELECT * FROM test WHERE id = ?;
calls          | 3
total_time     | 2459.014
blk_read_time  | 313.811
blk_write_time | 0

È importante notare che PostgreSQL è stato in grado di identificare la stessa impronta per le 3 query:

SELECT * FROM test WHERE id = ?;

Il carattere ? indica la presenza di un parametro. Il campo calls conterrà in effetti il valore 3, corrispondente al numero di volte in cui la query è stata esguita.

Questo miglioramento nella raccolta di statistiche è un altro passo avanti incontro alle esigenze degli amministratori di database Postgres.

Questo comando espone varie informazioni riguardanti un’eccezione intercettata in PL/pgSQL. In particolare:

• RETURNED_SQLSTATE: codice dell’errore
• MESSAGE_TEXT: messaggio dell’errore
• PG_EXCEPTION_DETAIL: dettagli aggiuntivi sull’errore (ove disponibili)
• PG_EXCEPTION_HINT: suggerimenti sulle possibili cause (ove disponibili)
• PG_EXCEPTION_CONTEXT: contesto in cui si è verificato l’errore

Questo comando è un’estensione del già presente GET DIAGNOSTICS.

Avere a disposizione tutte queste informazioni rende il debug del proprio codice estremamente migliore.

L’esempio sottostante permette di vedere il comando all’opera, accedendo all’interno della funzione debug a informazioni estese riguardanti l’eccezione.

BEGIN;

CREATE TABLE t (
    id INTEGER PRIMARY KEY,
    mess TEXT
);

CREATE OR REPLACE FUNCTION debug(
    i_sql TEXT
)
RETURNS BOOLEAN AS
$BODY$

    DECLARE
        v_state   TEXT;
        v_msg     TEXT;
        v_detail  TEXT;
        v_hint    TEXT;
        v_context TEXT;
    BEGIN
        BEGIN
            EXECUTE i_sql;
        EXCEPTION WHEN others THEN
            GET STACKED DIAGNOSTICS
                v_state   = RETURNED_SQLSTATE,
                v_msg     = MESSAGE_TEXT,
                v_detail  = PG_EXCEPTION_DETAIL,
                v_hint    = PG_EXCEPTION_HINT,
                v_context = PG_EXCEPTION_CONTEXT;
            raise notice E'Got exception:
                state  : %
                message: %
                detail : %
                hint   : %
                context: %', v_state, v_msg, v_detail, v_hint, v_context;
            RETURN False;
        END;
        RETURN True;
    END;

$BODY$
LANGUAGE plpgsql;

-- Un'istruzione SQL valida
SELECT debug('SELECT 1');
-- Un'istruzione SQL valida
SELECT debug('INSERT INTO t VALUES(1)');
-- Un errore (chiave duplicata)
SELECT debug('INSERT INTO t VALUES(1)');
-- Un errore di sintassi
SELECT debug('INSERT t VALUES(2)');

ROLLBACK;

La versione 9.2 di psql introduce una piccola funzionalità che porterà grandi vantaggi all’organizzazione dei propri script SQL: la possibilità di includere file con percorsi relativi al corrente grazie al meta comando \ir.

Il principale vantaggio di questa novità riguarda l’opportunità di eseguire un file con psql da qualsiasi posizione sul file system.

Supponiamo di voler scrivere delle funzioni SQL tenendo traccia degli unit test relativi (usate pgTap, non è vero? :)). Scriviamo le funzioni (nel nostro caso una sola, per semplicità) in un file chiamato functions.sql, che si preoccuperà di includere lo script con i test da una sottodirectory.

Ecco un semplice esempio:

BEGIN; 

CREATE OR REPLACE FUNCTION f(_x INTEGER)
RETURNS BOOLEAN LANGUAGE SQL IMMUTABLE 
AS $BODY$

    SELECT CASE
        WHEN $1 < 10
            THEN True
            ELSE False
        END;

$BODY$;

-- Ecco l'inclusione relativa!!!
\ir pgtap/unittest.sql

ROLLBACK;

Il file unittests.sql conterrà invece il codice pgTap per eseguire i test:

SELECT plan(5);

SELECT is(f(1), True);
SELECT is(f(5), True);
SELECT is(f(10), False);
SELECT is(f(11), False);
SELECT is(f(34), False);
        
SELECT * FROM finish();

Possiamo eseguire lo script da qualunque posizione del file system, con:

psql -f /path/to/functions.sql

Il file con i test verrà incluso con un percorso relativo a quello di functions.sql.

In conclusione, il comando \ir permette inoltre di organizzare in maniera più elegante il codice SQL, sfruttando l'inclusione in directory relative in base alle esigenze.