Proposal: include PL/Proxy into core - Mailing list pgsql-hackers

PL/Proxy is a small PL whose goal is to allow creating
"proxy functions" that call actual functions in remote database.
Basic design is:

Function body describes how to deduce final database.  Its either
CONNECT 'connstr';   -- connect to exactly this db

or when partitioning is used:
-- partitons are described under that nameCLUSTER 'namestr';
-- calculate int4 based on function paramenters-- and use that to pick a partitionRUN ON hashtext(username);


Actual function call info (arguments, result fields) are deduced
from looking at its own signature.

so function "foo(int4, text) returns setof text" will result in
query "select * from foo($1::int4, $2::text)" to be executed.


Announcement with more examples:
 http://archives.postgresql.org/pgsql-announce/2007-03/msg00005.php

Documentation:
 https://developer.skype.com/SkypeGarage/DbProjects/PlProxy

Patch:
 http://plproxy.projects.postgresql.org/plproxy_core.diff.gz


Now, why put it into core?

1) Much simpler replacement for various other clustering solutions
that try to cluster regular SQL.

2) Nicer replacement for dblink.

3) PLs need much more intimate knowledge of the PostgreSQL core
then regular modules.  API for PLs has been changing every
major revision of PostgreSQL.

4) It promotes the db-access-thru-functions design to databases, that
has proven to be killer feature of PostgreSQL.  In a sense it is
using PostgreSQL as appserver which provides fixed API via
functions for external users, but hides internal layout from them,
so it can be changed invisibly to external users.

5) The language is ready feature-wise - theres no need for it to grow
into "Remote PLPGSQL", because all logic can be put into remote function.


Some objections that may be:

1) It is not a universal solves-everything tool for remote access/clustering.

But those solves-everything tools have very hard time maturing,
and will be not exactly simple.   Much better is to have simple
tool that works well.

2) You cant use it for all thing you can use dblink.

PL/Proxy is easier to use for simple result fetching.  For complicated
access using full-blown PLs (plperl, plpython) is better.  From such
POV dblink is replaced.

3) It is possible for PL to live outside, The pain is not that big.

Sure its possible.  We just feel that its usefulness : lines-of-code ratio
is very high, so its worthy of being builtin into PostgreSQL core,
thus also giving PostgreSQL opportunity to boast being
clusterable out-of-box.

4) What about all the existing apps that dont access database thru functions?

Those are target for "solves-everything" tool...

5) It it too new product.

We think this is offset by the small scope of the task it takes,
and it already works well in that scope.


-- 
marko