Ereditarietà in PostgreSQL

Vediamo innanzitutto il significato di ereditarietà all’interno del contesto di un database relazionale.

L’ereditarietà permette a una tabella, detta “figlia”, di acquisire tutte le caratteristiche di una tabella già esistente, detta “padre”. In questo modo la tabella “figlia” possiede la stessa struttura e gli stessi vincoli della tabella “padre” in aggiunta alle colonne proprie della sua struttura. Effettuando una query sulla tabella “padre” otteniamo come risultato i record combinati della tabella “padre” e “figlia”, interrogando la tabella “figlia” non è, però, possibile ottenere i record della tabella da cui eredita. Questo fornisce alcune interessanti opzioni quando si tratta di progettare un database.

Piccolo esempio di ereditarietà

Supponiamo di avere una tabella che contenga dati anagrafici di attori, chiamata “actors”, e una tabella che contenga i dati anagrafici degli attori vincitori di un premio Oscar, chiamata “oscar_actors”. È facile dedurre che tutti gli attori presenti nella seconda tabella appartengono legittimamente anche alla prima, ma non il contrario.

Le due tabelle sono state create con i seguenti statement:

CREATE TABLE actors (
    id serial primary key,
    first_name text not null,
    last_name text not null
);

CREATE TABLE oscar_actors (
    id serial primary key,
    first_name text not null,
    last_name text not null,
    oscar_prize text not null
    );

Nota Bene: Tutti gli esempi presenti in questo articolo usano come utente di riferimento “postgres”. Questa è una scelta effettuata per motivi di semplicità e per non deviare dall’argomento trattato ed è una pessima scelta in fatto di sicurezza del vostro database. Vi suggerisco di utilizzare un utente specifico per la vostra applicazione.

Per semplificare la consultazione delle due tabelle possiamo creare una vista che le metta in correlazione tramite una query che usa l’operatore ‘UNION’. Una soluzione alternativa prevede che le tabelle siano strutturate in maniera diversa, usando l’ereditarietà.

CREATE TABLE actors (
    id serial primary key,
    first_name text not null,
    last_name text not null
);

CREATE TABLE oscar_actors (
    oscar_prize text not nul
    ) INHERITS (actors);

Come spiegato, la tabella “oscar_actors” eredita tutte le colonne della tabella “actors”. La tabella “oscar_actors” possiede inoltre la colonna aggiuntiva “oscar_prize” che mostra quale è il premio oscar vinto dall’attore.

Eseguendo una query sulla tabella “actors”, per esempio cercando tutti gli attori di nome Michael presenti nel nostro set di dati, il risultato è la combinazione dei contenuti delle due tabelle, “padre” e “figlia”.

Interrogando la tabella “oscar_actors”, invece, otterremmo i nomi di tutti i Michael che hanno vinto un premio Oscar.

Ereditarietà e tabelle esterne

È possibile usare l’ereditarietà per “partizionare” una tabella in maniera “orizzontale”, ovvero per suddividere una tabella in sottotabelle più piccole. Ogni sottotabella contiene un insieme di record suddivisi secondo un criterio comune (ad esempio il mese di inserimento nel caso di una tabella storicizzata).

L’inserimento delle tabelle esterne in questo scenario rende tutto ancora più interessante!

Le varie sottotabelle possono risiedere tutte su database separati, rendendo possibile l’implementazione di una tecnica di partizionamento nota come sharding.

Implementazione dello sharding tramite tabelle esterne

Vediamo ora come è possibile attuare lo sharding tramite tabelle esterne. Supponiamo di avere un database di nome “master” 4 database di nome “db1”, “db2”, “db3” e “db4”. Su tutti i database creiamo una tabella con questa struttura:

CREATE TABLE products (
    id serial PRIMARY KEY,
    name TEXT NOT NULL);

Connettiamoci quindi al database “master”, creiamo una tabella “products” e produciamo quindi 4 tabelle esterne che ereditano da questa:

CREATE TABLE products (id serial PRIMARY KEY, name TEXT NOT NUll);
CREATE EXTENSION postgres_fdw;
CREATE SERVER db1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS( dbname 'db1' );
CREATE SERVER db2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS( dbname 'db2' );
CREATE SERVER db3 FOREIGN DATA WRAPPER postgres_fdw OPTIONS( dbname 'db3' );
CREATE SERVER db4 FOREIGN DATA WRAPPER postgres_fdw OPTIONS( dbname 'db4' );
CREATE USER MAPPING FOR POSTGRES SERVER db1 OPTIONS ( user 'postgres' );
CREATE USER MAPPING FOR POSTGRES SERVER db2 OPTIONS ( user 'postgres' );
CREATE USER MAPPING FOR POSTGRES SERVER db3 OPTIONS ( user 'postgres' );
CREATE USER MAPPING FOR POSTGRES SERVER db4 OPTIONS ( user 'postgres' );
CREATE FOREIGN TABLE products_db1 () INHERITS (products) SERVER db1 OPTIONS ( table_name 'products' );
CREATE FOREIGN TABLE products_db2 () INHERITS (products) SERVER db2 OPTIONS ( table_name 'products' );
CREATE FOREIGN TABLE products_db3 () INHERITS (products) SERVER db3 OPTIONS ( table_name 'products' );
CREATE FOREIGN TABLE products_db4 () INHERITS (products) SERVER db4 OPTIONS ( table_name 'products' );

Creiamo quindi 4 sequenze su “master” per l’assegnazione degli id alle tabelle esterne.

CREATE SEQUENCE products_id_seq_db1 INCREMENT BY 4 RESTART WITH 1;
CREATE SEQUENCE products_id_seq_db2 INCREMENT BY 4 RESTART WITH 2;
CREATE SEQUENCE products_id_seq_db3 INCREMENT BY 4 RESTART WITH 3;
CREATE SEQUENCE products_id_seq_db4 INCREMENT BY 4 RESTART WITH 4;
ALTER FOREIGN TABLE products_db1 ALTER COLUMN id SET DEFAULT nextval('products_id_seq_db1');
ALTER FOREIGN TABLE products_db2 ALTER COLUMN id SET DEFAULT nextval('products_id_seq_db2');
ALTER FOREIGN TABLE products_db3 ALTER COLUMN id SET DEFAULT nextval('products_id_seq_db3');
ALTER FOREIGN TABLE products_db4 ALTER COLUMN id SET DEFAULT nextval('products_id_seq_db4');

Le sequenze degli id delle varie tabelle sono state alterate in modo che abbiano un incremento di 4 e uno sfasamento nel numero di partenza. Gli id generati per le 4 tabelle sono quindi diversi.

Inseriamo, quindi, 4 prodotti direttamente nelle tabelle esterne:

INSERT INTO products_db1 (name) VALUES ('playstation');
INSERT INTO products_db2 (name) VALUES ('xbox');
INSERT INTO products_db3 (name) VALUES ('wii');
INSERT INTO products_db4 (name) VALUES ('saturn');

Facendo una SELECT sulla tabella “products” troviamo tutti i prodotti che abbiamo appena inserito:

master=# SELECT * FROM products;
 id |    name
----+-------------
  1 | playstation
  2 | xbox
  3 | wii
  4 | saturn
(4 rows)

Eseguendo un EXPLAIN ANALIZE possiamo vedere l’esecuzione delle scansioni sulle tabelle esterne:

master=# EXPLAIN ANALYZE SELECT * FROM products;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..603.80 rows=5461 width=36) (actual time=0.693..2.682 rows=4 loops=1)
   ->  Seq Scan on products  (cost=0.00..0.00 rows=1 width=36) (actual time=0.001..0.001 rows=0 loops=1)
   ->  Foreign Scan on products_db1  (cost=100.00..150.95 rows=1365 width=36) (actual time=0.689..0.690 rows=1 loops=1)
   ->  Foreign Scan on products_db2  (cost=100.00..150.95 rows=1365 width=36) (actual time=0.668..0.669 rows=1 loops=1)
   ->  Foreign Scan on products_db3  (cost=100.00..150.95 rows=1365 width=36) (actual time=0.665..0.666 rows=1 loops=1)
   ->  Foreign Scan on products_db4  (cost=100.00..150.95 rows=1365 width=36) (actual time=0.650..0.652 rows=1 loops=1)
 Planning time: 0.316 ms
 Execution time: 4.601 ms
(8 rows)

è possibile modificare anche i record delle tabelle figlie, per esempio con facendo una UPDATE:

master=# EXPLAIN ANALYZE UPDATE products SET name = 'wii-u' WHERE ID = 4 AND name LIKE 'wii';
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Update on products  (cost=0.00..587.52 rows=5 width=10) (actual time=3.387..3.387 rows=0 loops=1)
   Update on products
   Foreign Update on products_db1
   Foreign Update on products_db2
   Foreign Update on products_db3
   Foreign Update on products_db4
   ->  Seq Scan on products  (cost=0.00..2.91 rows=1 width=10) (actual time=0.018..0.018 rows=0 loops=1)
         Filter: ((name ~~ 'wii'::text) AND (id = 4))
         Rows Removed by Filter: 1
   ->  Foreign Scan on products_db1  (cost=100.00..146.15 rows=1 width=10) (actual time=0.878..0.878 rows=0 loops=1)
   ->  Foreign Scan on products_db2  (cost=100.00..146.15 rows=1 width=10) (actual time=0.861..0.861 rows=0 loops=1)
   ->  Foreign Scan on products_db3  (cost=100.00..146.15 rows=1 width=10) (actual time=0.759..0.759 rows=0 loops=1)
   ->  Foreign Scan on products_db4  (cost=100.00..146.15 rows=1 width=10) (actual time=0.866..0.866 rows=0 loops=1)
 Planning time: 0.786 ms
 Execution time: 5.718 ms
(15 rows)

Lo sharding non è ancora completo. Ogni volta che facciamo una query viene eseguita una scansione di tutte le tabelle, anche quando non sarebbe necessario.

master=# EXPLAIN ANALYZE SELECT * FROM products WHERE name LIKE 'xbox';
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..508.81 rows=29 width=36) (actual time=1.478..2.845 rows=1 loops=1)
   ->  Seq Scan on products  (cost=0.00..0.00 rows=1 width=36) (actual time=0.002..0.002 rows=0 loops=1)
         Filter: (name ~~ 'xbox'::text)
   ->  Foreign Scan on products_db1  (cost=100.00..127.20 rows=7 width=36) (actual time=0.740..0.740 rows=0 loops=1)
   ->  Foreign Scan on products_db2  (cost=100.00..127.20 rows=7 width=36) (actual time=0.734..0.736 rows=1 loops=1)
   ->  Foreign Scan on products_db3  (cost=100.00..127.20 rows=7 width=36) (actual time=0.631..0.631 rows=0 loops=1)
   ->  Foreign Scan on products_db4  (cost=100.00..127.20 rows=7 width=36) (actual time=0.733..0.733 rows=0 loops=1)
 Planning time: 0.459 ms
 Execution time: 4.878 ms
(9 rows)

Il problema è risolvibile affidandosi ai meccanismi di partizionamento già presenti in PostgreSQL. Aggiungiamo quindi alle tabelle esterne un constraint di tipo CHECK, definendo così quali siano le chiavi che appartengono a quella partizione

ALTER FOREIGN TABLE products_db1 ADD CHECK ( id % 4 = 1 );
ALTER FOREIGN TABLE products_db2 ADD CHECK ( id % 4 = 2 );
ALTER FOREIGN TABLE products_db3 ADD CHECK ( id % 4 = 3 );
ALTER FOREIGN TABLE products_db4 ADD CHECK ( id % 4 = 0 );

In questo caso abbiamo scelto come discriminante un criterio numerico basato sull’operazione di modulo 4 (perché 4 sono le tabelle) del campo id.

master=# EXPLAIN ANALYZE SELECT * FROM products WHERE id = 4 AND (id % 4) = (4 % 4) ;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..137.13 rows=2 width=36) (actual time=0.913..0.915 rows=1 loops=1)
   ->  Seq Scan on products  (cost=0.00..3.22 rows=1 width=36) (actual time=0.013..0.013 rows=0 loops=1)
         Filter: ((id = 4) AND ((id % 4) = 0))
         Rows Removed by Filter: 1
   ->  Foreign Scan on products_db4  (cost=100.00..133.91 rows=1 width=36) (actual time=0.900..0.901 rows=1 loops=1)
 Planning time: 0.525 ms
 Execution time: 1.591 ms
(7 rows)

PostgreSQL in questo modo identifica immediatamente a quale tabella appartiene l’id che stiamo cercando e evita di fare la scansione delle altre.

Gli inserimenti sono ancora non “automatici” ma basta creare un trigger BEFORE INSERT per la tabella users che applichi gli stessi criteri applicati per i vincoli usati per le tabelle esterne alle linee in inserimento.

Conclusioni

La partecipazione delle tabelle esterne all’ereditarietà è una feature di grande importanza. Finora per poter partizionare orizzontalmente le tabelle era necessario ricorrere a strumenti esterni a PostgreSQL come Pg_shard o PL/Proxy. Dalla 9.5 tutto quello che serve per costruire l’architettura per lo sharding è presente all’interno del core di PostgreSQL.

Sono molteplici i benefici che è possibile trarre da una attenta pianificazione del partizionamento delle tabelle. È possibile migliorare le prestazioni delle query su tabelle di grandi dimensioni, riducendone il set di dati da analizzare. Inoltre, ripartendo il carico delle dimensioni di alcune tabelle molto grandi su diverse istanze di PostgreSQL, si riduce lo spazio occupato sul disco, non solo dal database ma anche dai suoi backup, rendendo più rapide se necessario le operazioni di ripristino dei cluster.

I Foreign Data Wrapper (FDW)

Prima dell’introduzione dei Foreign Data Wrapper l’unico modo per connettere un database Postgres con una fonte dati esterna era il modulo dblink.
Nel 2003 viene definito all’interno del linguaggio SQL l’insieme di regole per la gestione in maniera standard di fonti di dati esterne: SQL/MED (management of external Data).

All’interno di PostgreSQL 9.1, una prima implementazione dello standard SQL/MED viene introdotta con i Foreign Data Wrapper, fornendo a Postgres l’accesso diretto a fonti di dati come file o altri database (Oracle, Mysql…), permettendone l’utilizzo come tabelle.
Il vantaggio di questo approccio è evidente: la possibilità di interrogare una fonte di dati esterna per poterne estrarre dati in maniera nativa eseguendo una semplice query. Il non dover ricorrere a moduli esterni per ottenere questo risultato è una notevole semplificazione del lavoro per i DBA.

Per saperne di più, potete dare una occhiata all’articolo pubblicato nel 2011, prima dell’uscita di PostgreSQL 9.1,  sul nostro blog: “PostgreSQL 9.1: Tabelle esterne con SQL/MED“.

Piccolo esempio di uso di un FDW

PostgreSQL 9.3 introduce il supporto ai foreign data wrapper in scrittura ed aggiunge anche il supporto al foreign data wrapper per PostgreSQL. Vediamo adesso un semplice esempio di utilizzo di un FDW connettendo fra loro due database Postgres.
Creiamo due database:

CREATE DATABASE source;
CREATE DATABASE destination;

All’interno di source creiamo una tabella di test con dei dati di test:

\c source
CREATE TABLE test1 AS SELECT id, md5(random()::text) FROM generate_series(1,5) id;

Connettiamoci adesso al db di destinazione e connettiamo i due database:

\c destination
CREATE EXTENSION postgres_fdw ;
CREATE SERVER src_srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS( dbname 'source' );
CREATE USER MAPPING FOR postgres SERVER src_srv OPTIONS ( user 'postgres' );

Qualcuno di voi, giustamente, si sarà alzato in piedi, lamentandosi per la pessima scelta in fatto di sicurezza! Molto bene!

Per semplicità, ho infatti deciso di connetterci con l’utente amministratore “postgres” – anche per non deviare troppo dall’argomento principale dell’articolo. Sappiate che in un ambiente di produzione, per motivi di sicurezza, dovrete prendere altre scelte – ad esempio utilizzando un utente specifico per la vostra applicazione.

Ad ogni modo, una volta stabilita la connessione, possiamo creare sul database di destinazione una tabella esterna che punti a test1 sul database source:

CREATE FOREIGN TABLE test1_ft (id integer, md5 text) server src_srv options(table_name 'test1');

Possiamo adesso confrontare il contenuto fra le due tabelle di test:

select * from test1_ft ;
 id |               md5
----+----------------------------------
  1 | 63e5bc545b45f5c3961522f2609bedd9
  2 | d74af95e495d946d4a0887c51eb2cbe2
  3 | acce7cba66967332d01d51b74eb293f7
  4 | c5bb57ca54036004de334cf793792d4e
  5 | 02f32751b09042cf28b78cc29321a32e
(5 rows)

\c source

select * from test1 ;
 id |               md5
----+----------------------------------
  1 | 63e5bc545b45f5c3961522f2609bedd9
  2 | d74af95e495d946d4a0887c51eb2cbe2
  3 | acce7cba66967332d01d51b74eb293f7
  4 | c5bb57ca54036004de334cf793792d4e
  5 | 02f32751b09042cf28b78cc29321a32e
(5 rows)

È evidente, osservando questo esempio, che uno dei più grandi limiti all’utilizzo dei Foreign Data Wrapper è la necessità di definire separatamente, conoscendone la struttura, ogni tabella.
L’accesso a dati esterni quindi risulta laborioso qualora si voglia importare tabelle più complesse o, addirittura, interi schemi.

Fino ad adesso, operazioni del genere venivano fatte per mezzo di script in grado di connettersi al database sorgente e generare la struttura delle tabelle esterne in maniera automatica.
Fortunatamente la funzionalità IMPORT FOREIGN SCHEMA, presente nella prossima release di PostgreSQL, ci viene in aiuto.

IMPORT FOREIGN SCHEMA: sinossi

L’istruzione IMPORT FOREIGN SCHEMA, permette di importare uno schema intero da una fonte dati esterna, senza dover specificare la struttura di ogni singola tabella:

IMPORT FOREIGN SCHEMA remote_schema_name
FROM SERVER server_name INTO destination_schema;

Qualora non sia necessario importare uno schema intero, è possibile usare la clausola LIMIT TO e circoscrivere l’importazione unicamente alle tabelle a cui siamo interessati:

IMPORT FOREIGN SCHEMA remote_schema_name LIMIT TO (table_name, table_name, ...)
FROM SERVER server_name INTO destination_schema;

Altrimenti, se siamo interessati a escludere solo alcune tabelle dallo schema, è possibile filtrarle con la clausola EXCLUDE:

IMPORT FOREIGN SCHEMA remote_schema_name EXCLUDE (table_name, table_name, ...)
FROM SERVER server_name INTO destination_schema;

Esempio

Vediamo nel dettaglio come utilizzare questo comando, andando a estendere l’esempio usato in precedenza.
Connettiamoci al database sorgente e aggiungiamo due tabelle a quella che già è presente:

\c source
create table test2 as select id, md5(random()::text) from generate_series(1,20) as id;
create table test3 as select id, md5(random()::text) from generate_series(1,50) as id;

Creiamo adesso nel database di destinazione uno schema che useremo come target dell’istruzione IMPORT FOREIGN SCHEMA:

\c destination
create schema imported;

Adesso possiamo importare lo schema che abbiamo appena ampliato, contando sulla connessione aperta nell’esempio precedente:

IMPORT FOREIGN SCHEMA public FROM SERVER src_srv INTO imported;

Facciamo una rapida ispezione di tutte le tabelle sul database di destinazione per osservare il risultato dell’importazione dello schema:

\dE *.*

               List of relations
  Schema  |   Name   |     Type      |  Owner
----------+----------+---------------+----------
 imported | test1    | foreign table | postgres
 imported | test2    | foreign table | postgres
 imported | test3    | foreign table | postgres
 public   | test1_ft | foreign table | postgres

All’interno dello schema public notiamo la tabella che abbiamo creato in precedenza, mentre il risultato dell’importazione “in massa” è visibile nello schema imported.

Con questo esempio è possibile constatare quanto è più veloce e immediato l’utilizzo delle tabelle esterne con IMPORT FOREIGN SCHEMA.

Conclusioni

Con PostgreSQL 9.5, grazie a questa nuova funzionalità, le migrazioni dei dati diventeranno sempre più semplici e veloci.
Attualmente l’istruzione IMPORT FOREIGN SCHEMA è supportata solo da postgres_fdw e richiede che gli sviluppatori dei singoli driver la implementino nel modo più consono alla fonte di dati.
Aumentando il numero di driver in grado di supportare questa funzionalità si aprono scenari sempre più interessanti per PostgreSQL e per l’integrazione dei dati.

Nei mesi scorsi, 2ndQuadrant aveva pubblicato una serie di articoli su alcune delle novità di PostgreSQL 9.1, fra cui tabelle unlogged, tabelle esterne ed estensioni.

"PostgreSQL 9.1 fornisce alcune delle più avanzate funzionalità a disposizione di tutti i database open source ed è sostenuto da una comunità attiva e innovativa, con dimostrata esperienza di successo in ambito aziendale. PostgreSQL è inoltre ben posizionato per costruire ed eseguire applicazioni nel cloud", afferma Charles Fan (Vice presidente, Dipartimento di Ricerca e sviluppo, VMware).

Risponde a importanti richieste degli utenti

La versione 9.1 introduce molte funzionalità che per anni sono state richieste dagli utenti, rimuovendo diverse barriere nell’adozione di nuove applicazioni o nella migrazione a PostgreSQL. Fra le altre, sono degne di nota:

• replica sincrona: sicurezza e consistenza dei dati (perdita zero) all’interno di un cluster in alta disponibilità di server PostgreSQL;
• collation a livello di colonna: definizione delle regole di ordinamento delle stringhe a livello di database, tabella o colonna;
• tabelle "unlogged": miglioramenti prestazionali per dati volatili (i.e. caching o sessioni web).

"Heroku offre il più grande servizio cloud per PostgreSQL del mondo come ‘database-as-a-service’", svela James Lindenbaum, co-fondatore di Heroku. "Il rilascio della replica sincrona dei dati nella 9.1 fornisce ai nostri clienti modalità innovative per la protezione di dati mission-critical. Consacra inoltre PostgreSQL come una delle soluzioni di memorizzazione dati più dinamiche al momento disponibili".

Avanza lo stato dell’arte

La nostra comunità di sviluppatori è in grado di innovare con funzionalità all’avanguardia. La versione 9.1 ne comprende diverse che rappresentano vere e proprie novità nell’industria dei database anche non open source, come:

• indicizzazione "K-Nearest-Neighbor": indici basati sul concetto di distanza, che permettono interrogazioni più veloci nel campo delle ricerche testuali e geografiche;
• "Serializable Snapshot Isolation": garanzia di consistenza fra transazioni concorrenti senza necessariamente doverle bloccare, mettendo in pratica il concetto di "true serializability";
• "Writeable Common Table Expression": esecuzione di complessi aggiornamenti ai dati su più passi, utilizzando una singola query;
• "Security-Enhanced Postgres": implementa la sicurezza di livello militare e il controllo d’accesso vincolato ("Mandatory Access Control").

"OpenERP si è sempre avvalso delle funzionalità di classe enterprise di PostgreSQL per dotare di una base veloce, affidabile e scalabile le applicazioni che aiutano a gestire le operazioni quotidiane delle aziende nostre clienti. L’integrità dei dati in contesti altamente concorrenti e transazionali è un argomento di importanza critica per noi, e siamo veramente entusiasti della funzionalità ‘Serializable Snapshot Isolation’ introdotta in PostgreSQL 9.1!", aggiunge Olivier Dony, Community Manager di OpenERP.

Estende il motore del database

L’estensibilità di PostgreSQL consente agli utenti di aggiungere nuove funzionalità ad un database già in produzione e di sfruttarlo per compiti che nessun altro sistema di gestione di database è in grado di fare. La versione 9.1 aggiunge nuovi strumenti per l’estensibilità, fra cui:

• tabelle esterne tramite "Foreign Data Wrapper": permette di collegare e interrogare altri database direttamente da PostgreSQL;
• estensioni: rende più facile creare, caricare e gestire nuove funzionalità del database.

Tutte le funzionalità descritte sopra e molte altre sono descritte in dettaglio sia nella pagina del wiki di PostgreSQL intitolata "What’s New In 9.1" che nelle note di rilascio.

Nel 25° anniversario dello sviluppo del database PostgreSQL, la nostra comunità continua a migliorare la tecnologia dei database ad ogni rilascio annuale. Scarica subito la versione 9.1 e prova il più avanzato sistema di database open source al mondo.

Su PostgreSQL: PostgreSQL è il leader dei sistemi di gestione di database open source, con una comunità internazionale costituita da migliaia di utenti e sviluppatori nonché decine di aziende ed enti provenienti da tutte le parti del mondo. Il progetto PostgreSQL vanta 25 anni di attività di ingegneria del software, cominciata all’Università di California a Berkeley e oggi può vantare un ritmo di sviluppo senza uguali. La gamma di funzionalità mature messe a disposizione da PostgreSQL non soltanto è in grado di competere con quelle offerte da sistemi di database proprietari, ma le migliora in termini di funzionalità avanzate, estensibilità, sicurezza e stabilità. Scopri maggiori informazioni su PostgreSQL e partecipa attivamente alla nostra comunità su http://www.postgresql.org e, per l’Italia, http://www.itpug.org.

Per maggiori informazioni in italiano sul rilascio: Press Kit di PostgreSQL 9.1

Questo articolo mostra la procedura da seguire per creare una tabella esterna contenente dati ricavati da un file CSV locale e sfrutta un’altra caratteristica introdotta in PostgreSQL 9.1: le estensioni.

Prima di tutto, è necessario installare una estensione chiamata file_fdw distribuita nei moduli contrib di PostgreSQL 9.1.

Per installare i moduli contrib di PostgreSQL 9.1 dai sorgenti è sufficiente compilarli con gmake world e installarli con gmake install-world, oppure entrare nella directory contrib degli stessi sorgenti e digitare gmake install.

Una volta installati i moduli contrib, le estensioni si troveranno in ${postgresql_prefix}/share/extension. Controllate che esistano i file file_fdw.control e file_fdw--1.0.sql, che definiscono l’estensione (si rimanda all’articolo sulle estensioni per maggiori informazioni).

Creare l’estensione con:

CREATE EXTENSION file_fdw;

Il comando psql per mostrare le estensioni è dx, che possiede anche la variante dx+, che mostra maggiori dettagli:

test_db=# dx+ file_fdw
Objects in extension "file_fdw"
Object Description
-----------------------------------------
foreign-data wrapper file_fdw
function file_fdw_handler()
function file_fdw_validator(text[],oid)
(3 rows)

Si noti che questa estensione crea automaticamente un oggetto di tipo FOREIGN DATA WRAPPER di nome file_fdw, che tornerà utile in seguito.

Dato che SQL/MED può gestire la copia dei dati anche attraverso database remoti, è necessario creare un server usando l’oggetto file_fdw (questo passo è necessario anche nel caso si stiano caricando dati da un file locale, come in questo esempio):

CREATE SERVER file FOREIGN DATA WRAPPER file_fdw ;

A questo punto, è possibile creare la tabella esterna caricando i dati, con:

CREATE FOREIGN TABLE statistical_data (field1 numeric, field2 numeric)
SERVER file
OPTIONS (filename '/tmp/statistical_data.csv', format 'csv', delimiter ';') ;

Attualmente, è possibile eseguire solo query di sola lettura tramite SELECT sulle tabelle foreign. Le tabelle foreign inoltre, funzionano anche attraverso dblink, il tool che permette la comunicazione tra database remoti.

Concludendo, questa caratteristica va a completare una piccola lacuna che Postgres aveva rispetto ad altri database. Va pertanto accolta con grande interesse data la sua utilità, specialmente nel settore del data warehousing, in particolare dell’estrazione e del caricamento dati (ETL), e in più in generale per l’integrazione del database con altri sistemi e altre fonti di dati.

Per maggiori informazioni si rimanda alla documentazione di PostgreSQL sul comando CREATE FOREIGN TABLE.