Proposal of new PostgreSQL Extension - PGSpiderExt - Mailing list pgsql-hackers

I would like to propose new PostgreSQL Extension - PGSpiderExt.

* What is PGSpiderExt
This extension makes it possible to treat multiple tables having the same schema as a single virtual table.
We call this table as a multi-tenant table.
If a foreign table has a key column identifying a table, you can realize it by using conventional declarative
partitioningfeature.
 
But, even if the foreign table does not have the key column, this extension can create such virtual table without
changingtable structure on foreign table.
 

* Example of execution
Data of foreign table1:
       i | t
----+---
      10 | a
      11 | b

Data of foreign table2:
       i | t
----+---
      20 | c
      21 | d

(Example1) Query and result for multi tenant table:
't1' is a multi-tenant table having 'table1' and 'table2'.
'node' is a key column of which values are 'node1' and 'node2' representing 'table1' and 'table2' respectively.

SELECT * FROM t1;
       i | t | node
----+---+-------
      10 | a | node1
      11 | b | node1
      20 | c | node2
      21 | d | node2

(Example2) Query and result for multi tenant table:
SELECT * FROM t1 WHERE node = 'node1';
       i | t | node
----+---+-------
      10 | a | node1
      11 | b | node1


* How to create a multi-tenant table
pgspider_ext is one of foreign data wrapper for creating intermediate tables between a partition parent table and
foreigntables.
 
Firstly, you create foreign tables using data source FDW such as postgres_fdw as usual.
Then create a partition table using declarative partitioning feature.
This table has a partition key column of text type in addition to same columns as the foreign table, and is partitioned
byList.
 
After that, you create child tables of partition by using pgspider_ext.
You can define a value of partition key arbitrarily.

* Internal mechanism of accessing multi-tenant table PostgreSQL core
separates a query into queries for intermediate tables by partitioning feature.
pgspider_ext receives and analyzes the query, then passes query information to data source FDW.
More specifically, pgspider_ext creates information so that data source FDW can create a plan.
Then, creates a plan of intermediate table based on a plan created by data source FDW.
At that time, pgspider_ext does not pass query information about a partition key(like target list and WHERE condition)
todata source FDW.
 
When executing the query, data source FDW accesses to a foreign server and fetch a result set.
pgspider_ext receives and return it to PostgreSQL core by adding information of a partition key.

* Example of usage
CREATE EXTENSION pgspider_ext;
CREATE EXTENSION postgres_fdw;

-- Create a server for pgspider_ext.
CREATE SERVER mtsrv FOREIGN DATA WRAPPER pgspider_ext;

-- Define data sources. pgsrv1 has a table 't1_pg1' and pgsrv2 has a table 't1_pg2'.
-- These tables have 2 columns: 'i' integer and 't' text.
CREATE SERVER pgsrv1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS(host
'127.0.0.1', port '5433', dbname 'postgres'); CREATE SERVER pgsrv2
FOREIGN DATA WRAPPER postgres_fdw OPTIONS(host '127.0.0.1', port
'5434', dbname 'postgres');

CREATE USER MAPPING FOR CURRENT_USER SERVER mtsrv; CREATE USER
MAPPING FOR CURRENT_USER SERVER pgsrv1 OPTIONS(user 'user', password
'pass'); CREATE USER MAPPING FOR CURRENT_USER SERVER pgsrv2
OPTIONS(user 'user', password 'pass');

-- Create foreign tables as usual using data source FDW.
CREATE FOREIGN TABLE t1_pg1_ft (i int, t text) SERVER pgsrv1 OPTIONS
(table_name 't1_pg1'); CREATE FOREIGN TABLE t1_pg2_ft (i int, t text)
SERVER pgsrv2 OPTIONS (table_name 't1_pg2');

-- Define a partition table and child tables using pgspider_ext.
-- Partition key column is 'node' which does not exist on foreign table.
CREATE TABLE t1(i int, t integer, node text) PARTITION BY LIST
(node); CREATE FOREIGN TABLE t1_pg1_tenant PARTITION OF t1 FOR VALUES
IN
('node1') SERVER mtsrv OPTIONS (child_name 't1_pg1_ft'); CREATE
FOREIGN TABLE t1_pg2_tenant PARTITION OF t1 FOR VALUES IN ('node2')
SERVER mtsrv OPTIONS (child_name 't1_pg2_ft');

Then, you can access t1 by SELECT query.

*
We hope to be incorporated this extension into PostgreSQL as one of
contrib module, and would like to try to propose to Commit Fest.
Could you kindly advise me and share your opinion?

Regards,
Taiga Katayama