Thread: Proposal of new PostgreSQL Extension - PGSpiderExt

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

> On 10 Sep 2020, at 07:54, Taiga KATAYAMA <taiga.katayama@toshiba.co.jp> wrote:

> 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?

FWIW I would like to see fewer modules in contrib rather than gaining more, and
(I can't stress this enough) that's not in any way a comment on the quality or
the usefulness of the extension in question here.

The outside-of-core ecosystem for postgres extensions has been discussed in
various threads lately, and improving that seems a more sustainable solution
than bundling more.  That's no doubt a lot easier said than done, but I think
it's extremely important for the community.

cheers ./daniel

Thank you for your comment.

Please let me know if anyone has any other comments.

Regards,
Taiga Katayama

On 2020/09/10 21:09, Daniel Gustafsson wrote:
>> On 10 Sep 2020, at 07:54, Taiga KATAYAMA <taiga.katayama@toshiba.co.jp> wrote:
> 
>> 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?
> 
> FWIW I would like to see fewer modules in contrib rather than gaining more, and
> (I can't stress this enough) that's not in any way a comment on the quality or
> the usefulness of the extension in question here.
> 
> The outside-of-core ecosystem for postgres extensions has been discussed in
> various threads lately, and improving that seems a more sustainable solution
> than bundling more.  That's no doubt a lot easier said than done, but I think
> it's extremely important for the community.
> 
> cheers ./daniel
> 

On Thu, Sep 10, 2020 at 02:09:11PM +0200, Daniel Gustafsson wrote:
> FWIW I would like to see fewer modules in contrib rather than gaining more.

Agreed here.
--
Michael

On 16.09.2020 16:01, Michael Paquier wrote:
> On Thu, Sep 10, 2020 at 02:09:11PM +0200, Daniel Gustafsson wrote:
>> FWIW I would like to see fewer modules in contrib rather than gaining more.
> Agreed here.
> --
> Michael

The intention to limit the number of contrib modules seems to be obvious 
because
community will have to support all this modules.

But it will be great to have some have some common "playground" for all 
Postgres extension, so that everybody can easily
look for available extensions, check their status: which Postgres 
version are supported, last update time, OS compatibility, author's 
contacts...
I think that it will be enough just to have common git repository for 
them + may be some rules/tools which simplify search of extensions.

 From my point of view if will be very helpful if such "PgExt Store" 
will be available.
May be such resources already exists, but I do not know about them.
There is the following page:
https://www.postgresql.org/download/products/6-postgresql-extensions/
but there are just 13 extension listed here. And I failed to add my 
product in it.

As author of several Postgres extensions, I very interesting in having 
some common
database of all available Postgres extension and be able to publish my  
extensions there.

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On Wed, Sep 16, 2020 at 4:17 PM Konstantin Knizhnik
<k.knizhnik@postgrespro.ru> wrote:
>
> On 16.09.2020 16:01, Michael Paquier wrote:
> > On Thu, Sep 10, 2020 at 02:09:11PM +0200, Daniel Gustafsson wrote:
> >> FWIW I would like to see fewer modules in contrib rather than gaining more.
> > Agreed here.
> > --
> > Michael
>
> The intention to limit the number of contrib modules seems to be obvious
> because
> community will have to support all this modules.
>
> But it will be great to have some have some common "playground" for all
> Postgres extension, so that everybody can easily
> look for available extensions, check their status: which Postgres
> version are supported, last update time, OS compatibility, author's
> contacts...
> I think that it will be enough just to have common git repository for
> them + may be some rules/tools which simplify search of extensions.
>
>  From my point of view if will be very helpful if such "PgExt Store"
> will be available.
> May be such resources already exists, but I do not know about them.
> There is the following page:
> https://www.postgresql.org/download/products/6-postgresql-extensions/
> but there are just 13 extension listed here. And I failed to add my
> product in it.
>
> As author of several Postgres extensions, I very interesting in having
> some common
> database of all available Postgres extension and be able to publish my
> extensions there.

That's the goal of https://pgxn.org/.  The major problem is that those
extensions are usually not available on the various clouds, and/or
don't have the same trust level as for official contribs.

>  From my point of view if will be very helpful if such > "PgExt Store" 

> will be available.

> May be such resources already exists, but I do not > know about them.

https://pgxn.org/

Thank you.
Shame on me, that I have forgot about it.
Actually I have even uploaded one of my extension (IMCS) to PGXN.

May be I am wrong, but it seems to me that not so much people know about pgxn.org
Before writing this mail I have tried to locate such resource in Google and didn't succeed.

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company 

On Wed, Sep 16, 2020 at 05:40:30PM +0200, Pavel Stehule wrote:
>     May be I am wrong, but it seems to me that not so much people know about
>     pgxn.org
>     Before writing this mail I have tried to locate such resource in Google and
>     didn't succeed.
> 
> yes, It is not strongly joined with the Postgres community, and it doesn't
> deploy binary (compiled) code if I know it. So for some people it is not
> usable.
> 
> But anytime this and similar repositories will have problems, because the
> extensions there are not reviewed, nobody did security check, nobody did QA.
> 
> This is useful for Postgres developers, for very advanced users, or for very
> fearless users :).

I think if PGXN had more must-have extensions, its popularity would
increase.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee