Re: Proposal: include PL/Proxy into core

Ühel kenal päeval, R, 2007-03-30 kell 13:36, kirjutas Marko Kreen:
> 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 name
> CLUSTER 'namestr';
>
> -- calculate int4 based on function paramenters
> -- and use that to pick a partition
> RUN 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.

current pl/proxy proposed here for inclusion is already an almost
complete redesign and rewrite based on our experiences of using the
initial version in production databases, so you can expect ver 2.x
robustness, maintainability and code cleanness from it.

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

Also, it is actively used serving thousands of requests per second in a
24/7 live environment, which means that it should be reasonably well
tested.

Together with our lightweight connection pooler
https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer pl/proxy
can be used to implement the vision of building a "DB-bus" over a
database farm of diverse postgresql servers as shown in SLIDE3: of
https://developer.skype.com/SkypeGarage/DbProjects/SkypePostgresqlWhitepaper .

The connection pooler is not strictly needed and can be left out for
smaller configurations with maybe less than about 10 databases and/or
concurrent db connections.

(btw, the connection poolers name PgBouncer comes from its initial focus
of "bouncing around" single-transaction db calls.)

--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com