FOREIGN DATA WRAPPER

Well, saying that on particular date someone committed patch, wouldn't be really telling. In fact various bits and pieces of underlying logic have been committed for a long time, but now we finally have some functionality visible and available to end users.

Before I will show you what we can do with these two patches, let me step back a bit.

For a very long time (as far as I know forever) PostgreSQL had contrib module dblink.

This made it possible to run queries from one database using objects (tables, views, functions) in another.

DBlink limitation was that both ends of link had to be PostgreSQL databases. To avoid this limit there came dbi-link.

Thanks to Perl magic, it was now possible to make the link between PostgreSQL and virtually any other database.

This worked (and works), but it was custom addition, not standardized.

In the mean time (in 2003) new SQL standard, SQL 2003, described “SQL/MED", or “Management of External Data". Which was a standardized way of handling access to remote objects in SQL databases.

Foreign data wrappers in PostgreSQL are beginning of implementation of SQL/MED in PostgreSQL (beginning, as it's not fully functional as of now – for example – only read only queries work).

How to use it?

To use FDW we need some library that actually works as FDW, as the PostgreSQL built-in part, is just API. This is great because it will be possible (and simple, as far as I understand) to write additional wrapper, that will (for example) return google results, or tweets, or status updates from facebook, or anything else you can dream of.

Currently, though, we have only one such ready wrapper, quite useful – file_fdw. This wrapper lets you query files (which should be in “COPY"-able format).

So, let's test it:

$ create extension file_fdw;
CREATE EXTENSION

Now, I have all the logic ready in database.

And now I can add some foreign tables. Let's start with a simple one:

CREATE FOREIGN TABLE passwd (
username text,
pass text,
uid int4,
gid int4,
gecos text,
home text,
shell text
) SERVER file_server
OPTIONS (format 'text', filename '/etc/passwd', delimiter ':', null '');

Syntax seems simple enough, but there are important notes. All options depend on which FDW you're using. That is there are no standard ones, and you need to look in your wrapper documentation to get information on what options are available, and how to use them.

After running above query, PostgreSQL reported happily “CREATE FOREIGN TABLE". Does that mean I can query it? Let's see:

$ select * from passwd order by uid asc limit 3;
username | pass | uid | gid | gecos | home | shell
----------+------+-----+-----+--------+-----------+-----------
root | x | 0 | 0 | root | /root | /bin/bash
daemon | x | 1 | 1 | daemon | /usr/sbin | /bin/sh
bin | x | 2 | 2 | bin | /bin | /bin/sh
(3 rows)

Whoa. That's nice. What else can we see about it?

For starters \d output shows that it's remote table:

$ \d
List of relations
Schema | Name | Type | Owner
--------+--------+---------------+--------
public | passwd | foreign table | depesz
public | test | table | depesz
(2 rows)

$ \d passwd
Foreign table "public.passwd"
Column | Type | Modifiers
----------+---------+-----------
username | text |
pass | text |
uid | integer |
gid | integer |
gecos | text |
home | text |
shell | text |
Server: file_server

When explaining query with such table, we get some additional information:

$ explain analyze select * from passwd order by uid asc limit 3;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Limit (cost=2.24..2.25 rows=3 width=168) (actual time=0.078..0.079 rows=3 loops=1)
-> Sort (cost=2.24..2.27 rows=11 width=168) (actual time=0.078..0.078 rows=3 loops=1)
Sort Key: uid
Sort Method: top-N heapsort Memory: 25kB
-> Foreign Scan on passwd (cost=0.00..2.10 rows=11 width=168) (actual time=0.012..0.057 rows=44 loops=1)
Foreign File: /etc/passwd
Foreign File Size: 2115
Total runtime: 0.116 ms
(8 rows)

All in all – I'm extremely happy about it. I definitely don't understand all of it, but even the small parts that I do make me positively nervous about playing with it in future. Now, if only one could write wrappers in some language like Perl, and not C, it would be even better