Sta per arrivare la nuova versione di PostgreSQL, la 9 ¹. Come in pas­sato, anche se più sinteticamente, faccio una carrellata delle principali novità.

Binary replication

La novità più importante è l’aggiunta di una soluzione di replica utilizzabile da subito, senza componenti aggiuntive. La versione “di sistema” offre la pos­sibilità di replicare attraverso l’invio del WAL ² ai nodi secondari, che pos­sono essere configurati in modalità Hot ³ o Warm ⁴ Standby.

Nel caso del Warm Standby il nodo principale spedisce ai secondari un intero segmento di WAL alla volta, mentre nel caso dell’Hot Standby la granularità dell’invio scende al livello del record, riducendo ulteriormente la finestra di scopertura.

Funzioni, programmazione

È stato aggiunto il supporto per le funzioni anonime. Non c’è molto da aggiungere in questo caso, perché l’uso è veramente semplice⁵.

È pos­sibile usare i nomi dei para­metri delle funzioni anche nell’invocazione, rendendo più chiaro il codice nel caso in cui ce ne siano molti ⁶.

Dettaglio carino e utile: adesso è pos­sibile settare la variabile application_name.

SQL

Ora pos­siamo restringere l’esecuzione dei trigger a una singola colonna. ⁷. Inoltre, pos­siamo specificare la clausola when per restringere la loro attivazione solo nei casi realmente neces­sari ⁸.

Adesso è pos­sibile creare dei constraint che vadano al di là della semplice verifica di unicità, specificando l’operatore usato per il pre­dicato di confronto fra due valori. L’esempio riportato nella documentazione è quello che specifica il divieto di avere due circonferenze che si intersecano ⁹. Sempre a proposito dei vincoli di unicità, adesso pos­sono essere specificati come deferrable.

Con PostgreSQL, vista l’abbondanza di linguaggi che pos­sono essere usati per creare le funzioni, è spesso molto conveniente spostare molta di quella logica che solitamente gira nell’application server direttamente nel database.

Tuttavia, uno dei limiti nell’usare le procedure in PostgreSQL è che i vincoli sui datatype restituiti dalle funzioni sono piuttosto rigidi. In pratica, il sistema deve sapere al momento della compilazione tutti i tipi di valori restituiti. Perciò, o dichiariamo i para­metri OUT e nella clausola di return usiamo record, oppure, sempre nella clausola di return usiamo una tabella/view/tipo composito già pre­sente nel catalogo. #

Di solito pre­ferisco la secondo soluzione perché in questo modo si crea una dipendenza esplicita fra la funzione e la tabella/view/tipo composito restituito.

Questo però vuol dire che non pos­siamo scrivere una funzione generica che possa essere usata su più tabelle. Tuttavia, con i cursori pos­siamo ottenere praticamente la stessa cosa.

Ad es, basta una funzione come questa:

CREATE OR REPLACE FUNCTION get(IN table_name text) RETURNS refcursor AS $$
BEGIN
    OPEN c FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
    RETURN c;
END;
$$ language plpgsql;

per poi:

BEGIN;
SELECT get('table');
FETCH ALL FROM result;
COMMIT;

L’unico svantaggio è il dover aprire sempre una transazione ma mi sembra un prezzo ragionevole.

Le sequence sono un supporto, fornito dalla stragrande maggioranza dei database, per generare dei valori numerici univoci, che pos­sono poi essere usati per identificare ogni singola riga di una tabella. In PostgreSQL, la sequence risulta in pratica essere un numero intero sempre crescente e che non viene più riutilizzato: se il numero n, che otteniamo dal sistema, viene utilizzato in una transazione che poi sarà abortita, il pros­simo numero sarà comunque almeno n+1.

Ci sono, però, situazioni in cui non è sufficiente che la numerazione sia sempre crescente ma che non pre­senti “buchi”; e questo non è pos­sibile “right out of the box” con PostgreSQL.

Che fare?

In questo articolo, la soluzione proposta consiste nel creare una tabella di appoggio, che una funzione si occuperà di aggiornare; questa funzione sarà quella usata come default per la tabella vera e propria che, come è lecito aspettarsi, non consentirà cancellazioni.

I dettagli sono nell’articolo e dovrebbero essere piuttosto chiari; in casi di dubbi i commenti sono a disposizione.

A volte cápita di dover numerare i record durante la creazione di report. Una soluzione abbastanza nota è quella di tenere a disposizione una tabella (eventualmente temporanea) che non contiene altro che una sequenza di numeri a 1 a n.

Con PostgreSQL esiste una soluzione più elegante: generate_series. Con questa funzione pos­siamo scrivere una query come questa:

 

SELECT pdays.doy, SUM(o.order_total) As sales_total
FROM (SELECT CAST('2007-01-01' As date) + n As doy
		FROM generate_series(0,364) n ) pdays
			INNER JOIN orders o ON o.sale_date = pdays.doy;
GROUP BY pdays.doy
ORDER BY pdays.doy

 

dove pdays è la sequenza di tutti i giorni a partire a partire dal primo Gennaio 2007.

Per gli aficionados di PostgreSQL c’è un articolo da non perdere su High Scalability che analizza l’architettura adottata da Skype, basata appunto su pgSQL, che è data per scalabile fino ad 1 miliardo di utenti.

Ci sono diversi aspetti rilevanti, fra cui il fatto che l’accesso al database è completamente incapsulato attraverso le stored procedures. Nel mio piccolo è un approccio che sto provando anch’io con un progetto che iniziato qualche mese fa e su cui posterò qualche info più approfondita quando sarà andato tutto in porto (si spera!).

L’idea che mi attira di questo approccio è che l’interfaccia con il databae risulta drasticamente semplificata: ci sono solo i para­metri d’ingresso e d’uscita. Senza contare i risparmi in termini di prestazioni.

Ad oggi, il difetto fondamentale di questa soluzione è che non può essere estesa anche alle select, dato che il planner di PostgreSQL non è in grado di analizzare la query in modo ottimale per poter usare la strategia più performante ma qualche miglioria è in arrivo con la versione 8.4.

E pensare che una volta ero convinto che le stored procedures fos­sero l’incarnazione del male… :-D

Le ultime versioni di PostgreSQL pre­vedono la pos­sibilità di effettuare automaticamente il “vacuuming” delle tabelle. Questa operazione serve per reclamare lo spazio su disco non più utilizzato, aggiornare le statistiche e pre­venire che gli ID delle transazioni ripartano dall’inizio (quello che viene chiamato transaction ID wraparound).

Per verificare, da una ses­sione con psql, la situazione delle operazioni di vacuum, si può usare un’interrogazione del tipo:

SELECT schemaname, relname, last_autovacuum, last_autoanalyze FROM pg_stat_all_tables;

Le righe vuote sono relative alle tabelle poco utilizzate e che quindi non hanno bisogno di questo tipo manutenzione.

Invece, per verificare le impostazioni:

SELECT name, setting FROM pg_settings WHERE name LIKE '%autovacuum%';

Il risultato dovrebbe essere una cosa del tipo:

name | setting
---------------------------------+-----------
autovacuum | on
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 250
autovacuum_freeze_max_age | 200000000
autovacuum_naptime | 60
autovacuum_vacuum_cost_delay | -1
autovacuum_vacuum_cost_limit | -1
autovacuum_vacuum_scale_factor | 0.2
autovacuum_vacuum_threshold | 500