Desde la versión 9.1 de Postgres existe la posibilidad de extender las estructuras de almacenamiento de Postgres de forma que algunas consultas puedan obtener datos de orígenes externos, como ficheros CSV, URLs en internet, otras bases de datos, etc.
El estándar SQL/MED define las interfaces a implementar para interactuar con orígenes de datos externos, aunque actualmente el soporte de Postgres no cumple con dicho estándar. SQL/MED viene definido en el apartado 9 de la ISO/IEC 9075-9:2001. Hay dos artículos relevantes sobre el estándar y su implementación ello en [METLTON01] y [MELTON02].
Puede encontrarse una lista de wrappers disponibles, algunos no muy estables en el wiki del sitio web de Postgres, entre los que podemos destacar:
Veremos a continuación un ejemplo de como utilizar un FDW que acceda a un fichero de texto plano desde una consulta en Postgres. Para ello definiremos una tabla mediante CREATE FOREIGN TABLE que contendrá las mismas columnas que el fichero de texto en formato CSV.
El modulo de Postgres file_fdw permite este acceso, utilizando internamente el API del comando COPY.
biblioteca=# CREATE EXTENSION file_fdw;
CREATE EXTENSION
biblioteca=# CREATE SCHEMA fdw;
CREATE SCHEMA
biblioteca=# CREATE SERVER libros_srv FOREIGN DATA WRAPPER file_fdw;
CREATE SERVER
biblioteca=# CREATE FOREIGN TABLE fdw.libro (isbn varchar(15), titulo varchar(512), autor varchar(512), stock int) SERVER libros_srv OPTIONS (filename '/Users/luis/Desarrollo/mcpyp/tbd/data/data.csv', format 'csv', delimiter '|');
CREATE FOREIGN TABLE
biblioteca=# SELECT * FROM fdw.libro LIMIT 2;
isbn | titulo | autor | stock
------------+-------------------+---------------+-------
1385014805 | Arrow of God | Chinua Achebe | 606
2791071715 | Things Fall Apart | Chinua Achebe | 480
(2 rows)
El funcionamiento de la tabla es similar a la de cualquier otra tabla convencional de Postgres:
biblioteca=# SELECT v.usuario, l.titulo FROM voto v INNER JOIN fdw.libro l ON v.libro = l.isbn; usuario | titulo ---------+-------------- mike | Arrow of God (1 row)
Al analizar una consulta sobre un FDW observamos que el planificador de consultas está utilizando un tipo de scan que no habíamos visto hasta ahora: Foreign Scan.
biblioteca=# explain select v.usuario, l.titulo from voto v inner join fdw.libro l on v.libro = l.isbn;
QUERY PLAN
----------------------------------------------------------------------------
Hash Join (cost=16.79..44.77 rows=278 width=564)
Hash Cond: ((v.libro)::text = (l.isbn)::text)
-> Seq Scan on voto v (cost=0.00..16.40 rows=640 width=96)
-> Hash (cost=15.70..15.70 rows=87 width=564)
-> Foreign Scan on libro l (cost=0.00..15.70 rows=87 width=564)
Foreign File: /Users/luis/Desarrollo/mcpyp/tbd/data/data.csv
Foreign File Size: 51278
(7 rows)
El planificador de consultas de Postgres no tiene control alguno sobre cómo recorrer las estructuras internas de las tablas definidas por FDW y por lo tanto delega los planes de ejecución al código del wrapper.
Al declarar el FDW en código es necesario definir una función, fileGetForeignPlan, para devolver el plan de consulta al planificador. En el caso concreto de file_fdw el código simplemente devuelve los filtros aplicados a la consulta sin ningún tipo de variación adicional.
static ForeignScan *
fileGetForeignPlan(PlannerInfo *root,
RelOptInfo *baserel,
Oid foreigntableid,
ForeignPath *best_path,
List *tlist,
List *scan_clauses)
{
Index scan_relid = baserel->relid;
scan_clauses = extract_actual_clauses(scan_clauses, false);
return make_foreignscan(tlist, scan_clauses, scan_relid,
NIL, best_path->fdw_private);
}
De igual forma, tanto para explain como para otros comandos disponibles, el módulo ha de implementar las funciones correspondientes. La estructura que guarda los planes de ejecución es bastante sencilla y puede encontrarse en plannodes.h
typedef struct ForeignScan
{
Scan scan;
List *fdw_exprs; /* expressions that FDW may evaluate */
List *fdw_private; /* private data for FDW */
bool fsSystemCol; /* true if any "system column" is needed */
} ForeignScan;
El estado de la implementación actual, si buen permite hacer algunas operaciones interesantes sobre el planificado, dista todavía bastante de lo disponible para relaciones nativas de Postgres.
Actualmente la limitación más importante es que únicamente se permiten operaciones de lectura frente orígenes externos. También existen limitaciones en la planificación de consultas, como veremos más adelante, debido principalmente a la información que ha de suministrar el código del wrapper para hacer eficiente la consulta.
Cualquier operación no permitida termina en un error en la consulta:
biblioteca=# COPY fdw.libro (isbn, titulo, autor, stock) FROM STDIN WITH DELIMITER '|'; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 9781593272838|Learn You a Haskell for Great Good!|Miran Lipovača|10 >> \. ERROR: cannot copy to foreign table "libro" biblioteca=# analyze fdw.libro; WARNING: skipping "libro" --- cannot analyze non-tables or special system tables ANALYZE
Existe un conjunto de librerías para facilitar la escritura de FDW llamado Multicorn. La implementación de las extensiones es en Python y está basado en el cliente para extensiones de Postgres pgxn.
La instalación es sencilla y puede realizarse mediante easy_install:
$ sudo easy_install pgxnclient $ sudo pgxn install multicorn --testing
Es necesario tener las librerías de desarrollo para Postgres 9.2 al alcance de make para que pueda compilar.
from . import ForeignDataWrapper
from .utils import log_to_postgres
from logging import WARNING
import csv
class CsvFdw(ForeignDataWrapper):
def __init__(self, fdw_options, fdw_columns):
super(CsvFdw, self).__init__(fdw_options, fdw_columns)
self.filename = fdw_options["filename"]
self.delimiter = fdw_options.get("delimiter", ",")
self.quotechar = fdw_options.get("quotechar", '"')
self.skip_header = int(fdw_options.get('skip_header', 0))
self.columns = fdw_columns
def execute(self, quals, columns):
with open(self.filename) as stream:
reader = csv.reader(stream, delimiter=self.delimiter)
count = 0
checked = False
for line in reader:
if count >= self.skip_header:
if not checked:
# On first iteration, check if the lines are of the
# appropriate length
checked = True
if len(line) > len(self.columns):
log_to_postgres("There are more columns than "
"defined in the table", WARNING)
if len(line) < len(self.columns):
log_to_postgres("There are less columns than "
"defined in the table", WARNING)
yield line[:len(self.columns)]
count += 1
En GitHub hay multitud de ejemplos de wrappers escritos para LDAP, IMAP, XML etc.