Writable Foreign Tables (with transactions!!!)

One of the great features coming in PostgreSQL 9.3 is writable foreign tables. Before 9.3, foreign tables were strictly read-only, which is still very cool, but it really just made PostgreSQL a tool for query federation. Starting in 9.3, you can now execute inserts, updates and deletes in addition to selects, if the foreign data wrapper supports it. Also in 9.3 is a new contrib module implementing a long awaited PostgreSQL FDW which does fully support remote writes was added. While playing around with it a bit, I realized that now only does it support remote writes, it also supports transactions as well.

I tried the following little test fully expecting that the insert into the foreign table would be committed, but was pleasantly surprised that it wasn’t.

test=# \det
 List of foreign tables
 Schema | Table | Server 
--------+------------------+-------------
 public | pgbench_accounts | pg_server
 public | pgbench_branches | pg_server
 public | pgbench_tellers | pg_server
(3 rows)
test=# select * from pgbench_branches ;
 bid | bbalance | filler 
-----+----------+--------
 1 | 0 | 
(1 row)
test=# BEGIN;
BEGIN
test=# INSERT INTO pgbench_branches VALUES (2, 0, 'filler');
INSERT 0 1
test=# ROLLBACK;
ROLLBACK
test=# select * from pgbench_branches ;
 bid | bbalance | filler 
-----+----------+--------
 1 | 0 | 
(1 row)

How cool is that? The FDW starts a remote transaction and then honors the rollback. That just took the new PostgreSQL FDW from a interesting tool to a truly great new feature.