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.

In Postgres è possibile decidere quali informazioni aggiungere al file dei LOG grazie alle numerose opzioni presenti nel file di configurazione del server (postgresql.conf).

Postgres inoltre si integra alla perfezione con demoni di log di sistema come syslog (in ambienti UNIX).

Compito frequente del DBA è leggere i file di log di un server, interpretandoli secondo la propria esperienza.

A volte, specialmente se le informazioni inserite nel log sono tante, i file generati dal server risultano di difficile comprensione se analizzati con strumenti come un semplice editor di testo.

Per facilitare il compito dei DBA sono stati sviluppati strumenti che hanno il compito specifico di analizzare file di log e presentare statistiche utili in un formato facilmente consultabile.

Il più popolare, ad oggi, è sicuramente pgFouine. Vediamo come usarlo e quali alternative vi sono.

pgFouine

URL di riferimento: http://pgfouine.projects.postgresql.org

pgFouine è il più completo analizzatore di log presente ad oggi. Produce report in formato HTML con statistiche generali su query, checkpoint ed errori.

Caratteristiche principali:

• Scritto in PHP (ad oggetti)
• Facilmente estensibile se si ha bisogno di aggiungere report specifici
• Rilasciato con licenza GPL
• Tempo di esecuzione per l’analisi di un log di 52MB: 52.388s

PRO

• Ottimo formato di output
• Statistiche complete sulle query eseguite, sia generali che divise per tipo
• Livello di dettaglio del report selezionabile

CONTRO

• Estrema lentezza ed elevato consumo di memoria

pg_query_analyser

URL di riferimento: https://github.com/WoLpH/pg_query_analyser

pg_query_analyser è un clone C++ di PgFouine. È stato sviluppato da Rick van Hattem, un collega di 2ndQuadrant.

Caratteristiche principali:

• Scritto in C++
• Tempo di esecuzione per l’analisi di un log di 52MB: 4.113s

PRO

• Ottimo formato di output (identico a quello di pgFouine)
• Velocità di esecuzione e consumo ridotto di memoria
• Rilasciato con licenza GPL3

CONTRO

• Raccoglie solo statistiche sulle query (è possibile scegliere il tipo di query da riportare)
• Mancanza di un sistema di templating per personalizzazione dell’output (codice HTML hard-coded nel sorgente C++)

pgbadger

URL di riferimento: https://github.com/dalibo/pgbadger

È l’ennesimo clone di pgFouine. Il suo punto di forza sono sicuramente i grafici, realizzati con una libreria Javascript e di ottima qualità.

Caratteristiche principali

• Scritto in Perl
• Tempo di esecuzione per l’analisi di un log di 52MB: 11.544s

PRO:

• Ottimo formato di output
• Statistiche complete sulle query eseguite, e non solo:
  • Errori
  • Lock
  • File temporanei
  • Sessioni
  • Connessioni
• Modalità watch per riportare solo errori, come avviene nel popolare logwatch (www.logwatch.org)
• Ottima qualità dei grafici
• Rilasciato con licenza BSD

CONTRO

• Nessuno

Conclusioni

Dai test che ho effettuato, pgbadger è risultato essere un ottimo compromesso tra prestazioni nell’analisi e qualità del report.

Se le vostre esigenze sono di analizzare esclusivamente le query, terrei in considerazione pg_query_analyser, che esegue un ottimo lavoro usando un quantitativo di risorse esiguo.

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).

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.

Questa vista espone vari campi, si veda la documentazione ufficiale per maggiori dettagli sul significato di ognuno di essi.

Il campo oggetto del miglioramento è current_query. A partire da PostgreSQL 9.2, questo campo è stato sostituito da due campi distinti, uno chiamato state (che indica lo stato della query corrente), l’altro denominato query (che riporta la query vera e propria).

Per capire meglio come questo cambiamento si riflette nel monitoraggio delle attività del database, vediamo come questa vista si comporta in PostgreSQL 9.1 e come è stata modificata in PostgreSQL 9.2.

Ecco una serie di operazioni da eseguire assumendo che ci sia un server PostgreSQL 9.1 in esecuzione sulla porta 5491 ed un server 9.2 in esecuzione sulla porta 5492.

Collegatevi ad entrambi i server con psql. Avviate una transazione (senza chiuderla!) ed ottenete l’ID del processo (PID o pid):

BEGIN;
SELECT pg_backend_pid();

Nota: usare l’opzione -p per specificare la porta relativa al server a cui connettersi.

Possiamo pensare a queste transazioni aperte come ad applicazioni che stanno “lavorando” sul database. Il nostro scopo è monitorare ciò che stanno facendo.

Su PostgreSQL 9.1:

$ psql -p 5491 -tqc "SELECT current_query FROM pg_stat_activity WHERE procpid=13788"

Su PostgreSQL 9.2:

$ psql -p 5492 -tqc "SELECT query,state FROM pg_stat_activity WHERE pid=13465"

Nota: sostituire i PID nelle clausole WHERE con quelli ottenuti precedentemente con SELECT pg_backend_pid().

In PostgreSQL 9.1 il valore di current_query è usato per indicare anche le transazioni in stato idle (<IDLE>).

In PostgreSQL 9.2 il campo query contiene invece l’ultima query eseguita dal backend, mentre state contiene lo stato (molto più dettagliato che in precedenza, con ben 6 stati).

Importante: notare come la colonna procpid sia stata rinominata in un più standard pid.

Gli amminsitratori di database PostgreSQL saranno felicissimi di questa migliorìa, che agevola non poco il compito di monitoring dell’attività del database.