Waiting for 9.3 – Support writable foreign tables.

On 10th of March, Tom Lane committed patch:

Support writable foreign tables.
 
This patch adds the core-system infrastructure needed to support updates
on foreign tables, and extends contrib/postgres_fdw to allow updates
against remote Postgres servers.  There's still a great deal of room for
improvement in optimization of remote updates, but at least there's basic
functionality there now.
 
KaiGai Kohei, reviewed by Alexander Korotkov and Laurenz Albe, and rather
heavily revised by Tom Lane.

Since 9.1 we had so called “Foreign Data Wrappers" – generally a way to bring other sources of data to PostgreSQL, as simple tables.

There are multiple wrappers (drivers?) – standard PostgreSQL comes with file_fdw (to access .csv files as tables). In 9.3 we'll get postgres_fdw, and there are many more on PGXN.

The issue was, that up until now, all these tables were read only. But now – the whole subsystem for foreign data supports writes. Of course – it will not work automatically in all fdw sources – you have to have your driver actually support it (file_fdw, as far as I know, currently doesn't support writes). But the new postgres_fdw does.

So, let's see how it works.

First, I connected to source database – this one will have the actual table.

And in there I do:

[SOURCE] $ CREATE TABLE some_table (id serial PRIMARY KEY, some_number int4);
CREATE TABLE
 
[SOURCE] $ INSERT INTO some_table (some_number)
    SELECT i * 15 FROM generate_series(1,3) i;
INSERT 0 3
 
[SOURCE] $ SELECT * FROM some_table;
 id | some_number
----+-------------
  1 |          15
  2 |          30
  3 |          45
(3 ROWS)

Nice. Now, in another, test, database, I can create the foreign table:

[test] $ CREATE extension postgres_fdw;
CREATE EXTENSION
 
[test] $ CREATE server SOURCE FOREIGN DATA wrapper postgres_fdw options( dbname 'source' );
CREATE SERVER
 
[test] $ CREATE USER mapping FOR depesz server SOURCE options ( USER 'depesz' );
CREATE USER MAPPING
 
[test] $ CREATE FOREIGN TABLE table_from_source (
    id int4 NOT NULL,
    some_number int4
    ) server SOURCE
    options ( TABLE_NAME 'some_table' );
CREATE FOREIGN TABLE

(The option table_name is needed because my source table is named “some_table", but foreign table has different name – table_from_source.

Anyway, with this in place, I can:

[test] $ \d
                  List OF relations
 Schema |       Name        |     TYPE      | Owner
--------+-------------------+---------------+--------
 public | table_from_source | FOREIGN TABLE | depesz
(1 ROW)
 
[test] $ SELECT * FROM table_from_source ;
 id | some_number
----+-------------
  1 |          15
  2 |          30
  3 |          45
(3 ROWS)
 
[test] $ EXPLAIN analyze SELECT * FROM table_from_source ;
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 FOREIGN Scan ON table_from_source  (cost=100.00..186.80 ROWS=2560 width=8) (actual TIME=0.525..0.526 ROWS=3 loops=1)
 Total runtime: 1.073 ms
(2 ROWS)

I can also change the data from test database:

[test] $ INSERT INTO table_from_source (id, some_number) VALUES (-1, 100);
INSERT 0 1
 
[test] $ SELECT * FROM table_from_source ;
 id | some_number
----+-------------
  1 |          15
  2 |          30
  3 |          45
 -1 |         100
(4 ROWS)
 
[test] $ UPDATE table_from_source SET id = 4 WHERE id = -1;
UPDATE 1
 
[test] $ SELECT * FROM table_from_source ;
 id | some_number
----+-------------
  1 |          15
  2 |          30
  3 |          45
  4 |         100
(4 ROWS)
 
[test] $ DELETE FROM table_from_source WHERE id < 4;
DELETE 3
 
[test] $ SELECT * FROM table_from_source ;
 id | some_number
----+-------------
  4 |         100
(1 ROW)

And how it looks in the source?

[SOURCE] $ SELECT * FROM some_table;
 id | some_number
----+-------------
  4 |         100
(1 ROW)

Nice. What's more – remote tables are transactional, like here:

[test] $ BEGIN;
BEGIN
 
[test] *$ DELETE FROM table_from_source;
DELETE 1
 
[test] *$ ROLLBACK;
ROLLBACK
 
[test] $ SELECT * FROM table_from_source;
 id | some_number
----+-------------
  4 |         100
(1 ROW)

The only nitpick I have, is that foreign tables do not show up in tab-completion for write commands (insert/update/delete), but I guess it will be fixed soon(ish).

This is really cool stuff. I do not have any databases that use FDWs currently, but ability to use it for RW access, and not only RO, is huge improvement, and it definitely opens a way for new, interesting, usecases.

3 thoughts on “Waiting for 9.3 – Support writable foreign tables.”

  1. The last example looks very much transactional. The data seems to be still there after the rollback.

  2. @MP:
    You are absolutely right, my bad. Must have been tired when I wrote that. Fixed, and thank you.

Comments are closed.