Skip site navigation (1) Skip section navigation (2)

Skype presents: PL/Proxy 2.0 - easy database partitioning

From: "Marko Kreen" <marko(dot)kreen(at)skype(dot)net>
To: pgsql-announce(at)postgresql(dot)org
Subject: Skype presents: PL/Proxy 2.0 - easy database partitioning
Date: 2007-03-13 15:55:15
Message-ID: 54335.194.126.108.9.1173801315.squirrel@mail.skype.net (view raw or flat)
Thread:
Lists: pgsql-announce
PL/Proxy is a language for creating proxy functions that call actual
functions
from a partitioned database.  The actual query to be executed on partition is
constructed by looking proxy function signature.

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

So, to call function "get_user_email(username text) returns text", the proxy
function need to be defined following:

    CREATE FUNCTION get_user_email(username text) RETURNS text AS $$
        CLUSTER 'userdb';
        RUN ON hashtext(username);
    $$ LANGUAGE plproxy;

assuming actual partitions are defined under cluster 'userdb' and
users are spread out between partitions by hashtext(name).

PL/Proxy can be used for simple remote queries, without need for any external
configuration:

    CREATE FUNCTION get_user_email(username text) RETURNS text AS $$
        CONNECT 'dbname=userdb host=1.2.3.4';
        SELECT email FROM users WHERE name = username;
    $$ LANGUAGE plproxy;

This also demonstrates that its possible to override default 'SELECT *
FROM func(...)'
query generation and to run user-specified SELECT statement on remote server.



pgsql-announce by date

Next:From: Toru SHIMOGAKIDate: 2007-03-14 04:05:51
Subject: pg_bulkload released
Previous:From: Marko KreenDate: 2007-03-13 15:54:08
Subject: Skype presents: PgBouncer 1.0 - lightweight connection pooler

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group