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 partitioning feature.
But, even if the foreign table does not have the key column, this extension can create such virtual table without changing table 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 foreign tables.
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 by List.
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) to data 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
Show quoted text
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
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.
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.
--
MichaelThe 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.
On 16.09.2020 17:23, Adam Brusselback wrote:
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.
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 <https://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
st 16. 9. 2020 v 17:08 odesílatel Konstantin Knizhnik <
k.knizhnik@postgrespro.ru> napsal:
On 16.09.2020 17:23, Adam Brusselback wrote:
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.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.
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 :).
Regards
Pavel
Show quoted text
--
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
so 19. 9. 2020 v 0:42 odesílatel Bruce Momjian <bruce@momjian.us> napsal:
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 inGoogle 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 didQA.
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.
There is nothing else. But it can be much more useful, if somebody does
review, or if allows compilation to target platform on server side, if has
integrated tests, ...
Regards
Pavel
Show quoted text
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.comThe usefulness of a cup is in its emptiness, Bruce Lee