/usr/share/doc/postgresql-9.4-plproxy/tutorial.html

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
    "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
<meta http-equiv="Content-Type" content="application/xhtml+xml; charset=UTF-8" />
<meta name="generator" content="AsciiDoc 8.6.9" />
<title>PL/Proxy tutorial</title>
<style type="text/css">
/* Shared CSS for AsciiDoc xhtml11 and html5 backends */

/* Default font. */
body {
  font-family: Georgia,serif;
}

/* Title font. */
h1, h2, h3, h4, h5, h6,
div.title, caption.title,
thead, p.table.header,
#toctitle,
#author, #revnumber, #revdate, #revremark,
#footer {
  font-family: Arial,Helvetica,sans-serif;
}

body {
  margin: 1em 5% 1em 5%;
}

a {
  color: blue;
  text-decoration: underline;
}
a:visited {
  color: fuchsia;
}

em {
  font-style: italic;
  color: navy;
}

strong {
  font-weight: bold;
  color: #083194;
}

h1, h2, h3, h4, h5, h6 {
  color: #527bbd;
  margin-top: 1.2em;
  margin-bottom: 0.5em;
  line-height: 1.3;
}

h1, h2, h3 {
  border-bottom: 2px solid silver;
}
h2 {
  padding-top: 0.5em;
}
h3 {
  float: left;
}
h3 + * {
  clear: left;
}
h5 {
  font-size: 1.0em;
}

div.sectionbody {
  margin-left: 0;
}

hr {
  border: 1px solid silver;
}

p {
  margin-top: 0.5em;
  margin-bottom: 0.5em;
}

ul, ol, li > p {
  margin-top: 0;
}
ul > li     { color: #aaa; }
ul > li > * { color: black; }

.monospaced, code, pre {
  font-family: "Courier New", Courier, monospace;
  font-size: inherit;
  color: navy;
  padding: 0;
  margin: 0;
}
pre {
  white-space: pre-wrap;
}

#author {
  color: #527bbd;
  font-weight: bold;
  font-size: 1.1em;
}
#email {
}
#revnumber, #revdate, #revremark {
}

#footer {
  font-size: small;
  border-top: 2px solid silver;
  padding-top: 0.5em;
  margin-top: 4.0em;
}
#footer-text {
  float: left;
  padding-bottom: 0.5em;
}
#footer-badges {
  float: right;
  padding-bottom: 0.5em;
}

#preamble {
  margin-top: 1.5em;
  margin-bottom: 1.5em;
}
div.imageblock, div.exampleblock, div.verseblock,
div.quoteblock, div.literalblock, div.listingblock, div.sidebarblock,
div.admonitionblock {
  margin-top: 1.0em;
  margin-bottom: 1.5em;
}
div.admonitionblock {
  margin-top: 2.0em;
  margin-bottom: 2.0em;
  margin-right: 10%;
  color: #606060;
}

div.content { /* Block element content. */
  padding: 0;
}

/* Block element titles. */
div.title, caption.title {
  color: #527bbd;
  font-weight: bold;
  text-align: left;
  margin-top: 1.0em;
  margin-bottom: 0.5em;
}
div.title + * {
  margin-top: 0;
}

td div.title:first-child {
  margin-top: 0.0em;
}
div.content div.title:first-child {
  margin-top: 0.0em;
}
div.content + div.title {
  margin-top: 0.0em;
}

div.sidebarblock > div.content {
  background: #ffffee;
  border: 1px solid #dddddd;
  border-left: 4px solid #f0f0f0;
  padding: 0.5em;
}

div.listingblock > div.content {
  border: 1px solid #dddddd;
  border-left: 5px solid #f0f0f0;
  background: #f8f8f8;
  padding: 0.5em;
}

div.quoteblock, div.verseblock {
  padding-left: 1.0em;
  margin-left: 1.0em;
  margin-right: 10%;
  border-left: 5px solid #f0f0f0;
  color: #888;
}

div.quoteblock > div.attribution {
  padding-top: 0.5em;
  text-align: right;
}

div.verseblock > pre.content {
  font-family: inherit;
  font-size: inherit;
}
div.verseblock > div.attribution {
  padding-top: 0.75em;
  text-align: left;
}
/* DEPRECATED: Pre version 8.2.7 verse style literal block. */
div.verseblock + div.attribution {
  text-align: left;
}

div.admonitionblock .icon {
  vertical-align: top;
  font-size: 1.1em;
  font-weight: bold;
  text-decoration: underline;
  color: #527bbd;
  padding-right: 0.5em;
}
div.admonitionblock td.content {
  padding-left: 0.5em;
  border-left: 3px solid #dddddd;
}

div.exampleblock > div.content {
  border-left: 3px solid #dddddd;
  padding-left: 0.5em;
}

div.imageblock div.content { padding-left: 0; }
span.image img { border-style: none; vertical-align: text-bottom; }
a.image:visited { color: white; }

dl {
  margin-top: 0.8em;
  margin-bottom: 0.8em;
}
dt {
  margin-top: 0.5em;
  margin-bottom: 0;
  font-style: normal;
  color: navy;
}
dd > *:first-child {
  margin-top: 0.1em;
}

ul, ol {
    list-style-position: outside;
}
ol.arabic {
  list-style-type: decimal;
}
ol.loweralpha {
  list-style-type: lower-alpha;
}
ol.upperalpha {
  list-style-type: upper-alpha;
}
ol.lowerroman {
  list-style-type: lower-roman;
}
ol.upperroman {
  list-style-type: upper-roman;
}

div.compact ul, div.compact ol,
div.compact p, div.compact p,
div.compact div, div.compact div {
  margin-top: 0.1em;
  margin-bottom: 0.1em;
}

tfoot {
  font-weight: bold;
}
td > div.verse {
  white-space: pre;
}

div.hdlist {
  margin-top: 0.8em;
  margin-bottom: 0.8em;
}
div.hdlist tr {
  padding-bottom: 15px;
}
dt.hdlist1.strong, td.hdlist1.strong {
  font-weight: bold;
}
td.hdlist1 {
  vertical-align: top;
  font-style: normal;
  padding-right: 0.8em;
  color: navy;
}
td.hdlist2 {
  vertical-align: top;
}
div.hdlist.compact tr {
  margin: 0;
  padding-bottom: 0;
}

.comment {
  background: yellow;
}

.footnote, .footnoteref {
  font-size: 0.8em;
}

span.footnote, span.footnoteref {
  vertical-align: super;
}

#footnotes {
  margin: 20px 0 20px 0;
  padding: 7px 0 0 0;
}

#footnotes div.footnote {
  margin: 0 0 5px 0;
}

#footnotes hr {
  border: none;
  border-top: 1px solid silver;
  height: 1px;
  text-align: left;
  margin-left: 0;
  width: 20%;
  min-width: 100px;
}

div.colist td {
  padding-right: 0.5em;
  padding-bottom: 0.3em;
  vertical-align: top;
}
div.colist td img {
  margin-top: 0.3em;
}

@media print {
  #footer-badges { display: none; }
}

#toc {
  margin-bottom: 2.5em;
}

#toctitle {
  color: #527bbd;
  font-size: 1.1em;
  font-weight: bold;
  margin-top: 1.0em;
  margin-bottom: 0.1em;
}

div.toclevel0, div.toclevel1, div.toclevel2, div.toclevel3, div.toclevel4 {
  margin-top: 0;
  margin-bottom: 0;
}
div.toclevel2 {
  margin-left: 2em;
  font-size: 0.9em;
}
div.toclevel3 {
  margin-left: 4em;
  font-size: 0.9em;
}
div.toclevel4 {
  margin-left: 6em;
  font-size: 0.9em;
}

span.aqua { color: aqua; }
span.black { color: black; }
span.blue { color: blue; }
span.fuchsia { color: fuchsia; }
span.gray { color: gray; }
span.green { color: green; }
span.lime { color: lime; }
span.maroon { color: maroon; }
span.navy { color: navy; }
span.olive { color: olive; }
span.purple { color: purple; }
span.red { color: red; }
span.silver { color: silver; }
span.teal { color: teal; }
span.white { color: white; }
span.yellow { color: yellow; }

span.aqua-background { background: aqua; }
span.black-background { background: black; }
span.blue-background { background: blue; }
span.fuchsia-background { background: fuchsia; }
span.gray-background { background: gray; }
span.green-background { background: green; }
span.lime-background { background: lime; }
span.maroon-background { background: maroon; }
span.navy-background { background: navy; }
span.olive-background { background: olive; }
span.purple-background { background: purple; }
span.red-background { background: red; }
span.silver-background { background: silver; }
span.teal-background { background: teal; }
span.white-background { background: white; }
span.yellow-background { background: yellow; }

span.big { font-size: 2em; }
span.small { font-size: 0.6em; }

span.underline { text-decoration: underline; }
span.overline { text-decoration: overline; }
span.line-through { text-decoration: line-through; }

div.unbreakable { page-break-inside: avoid; }


/*
 * xhtml11 specific
 *
 * */

div.tableblock {
  margin-top: 1.0em;
  margin-bottom: 1.5em;
}
div.tableblock > table {
  border: 3px solid #527bbd;
}
thead, p.table.header {
  font-weight: bold;
  color: #527bbd;
}
p.table {
  margin-top: 0;
}
/* Because the table frame attribute is overriden by CSS in most browsers. */
div.tableblock > table[frame="void"] {
  border-style: none;
}
div.tableblock > table[frame="hsides"] {
  border-left-style: none;
  border-right-style: none;
}
div.tableblock > table[frame="vsides"] {
  border-top-style: none;
  border-bottom-style: none;
}


/*
 * html5 specific
 *
 * */

table.tableblock {
  margin-top: 1.0em;
  margin-bottom: 1.5em;
}
thead, p.tableblock.header {
  font-weight: bold;
  color: #527bbd;
}
p.tableblock {
  margin-top: 0;
}
table.tableblock {
  border-width: 3px;
  border-spacing: 0px;
  border-style: solid;
  border-color: #527bbd;
  border-collapse: collapse;
}
th.tableblock, td.tableblock {
  border-width: 1px;
  padding: 4px;
  border-style: solid;
  border-color: #527bbd;
}

table.tableblock.frame-topbot {
  border-left-style: hidden;
  border-right-style: hidden;
}
table.tableblock.frame-sides {
  border-top-style: hidden;
  border-bottom-style: hidden;
}
table.tableblock.frame-none {
  border-style: hidden;
}

th.tableblock.halign-left, td.tableblock.halign-left {
  text-align: left;
}
th.tableblock.halign-center, td.tableblock.halign-center {
  text-align: center;
}
th.tableblock.halign-right, td.tableblock.halign-right {
  text-align: right;
}

th.tableblock.valign-top, td.tableblock.valign-top {
  vertical-align: top;
}
th.tableblock.valign-middle, td.tableblock.valign-middle {
  vertical-align: middle;
}
th.tableblock.valign-bottom, td.tableblock.valign-bottom {
  vertical-align: bottom;
}


/*
 * manpage specific
 *
 * */

body.manpage h1 {
  padding-top: 0.5em;
  padding-bottom: 0.5em;
  border-top: 2px solid silver;
  border-bottom: 2px solid silver;
}
body.manpage h2 {
  border-style: none;
}
body.manpage div.sectionbody {
  margin-left: 3em;
}

@media print {
  body.manpage div#toc { display: none; }
}


</style>
<script type="text/javascript">
/*<![CDATA[*/
var asciidoc = {  // Namespace.

/////////////////////////////////////////////////////////////////////
// Table Of Contents generator
/////////////////////////////////////////////////////////////////////

/* Author: Mihai Bazon, September 2002
 * http://students.infoiasi.ro/~mishoo
 *
 * Table Of Content generator
 * Version: 0.4
 *
 * Feel free to use this script under the terms of the GNU General Public
 * License, as long as you do not remove or alter this notice.
 */

 /* modified by Troy D. Hanson, September 2006. License: GPL */
 /* modified by Stuart Rackham, 2006, 2009. License: GPL */

// toclevels = 1..4.
toc: function (toclevels) {

  function getText(el) {
    var text = "";
    for (var i = el.firstChild; i != null; i = i.nextSibling) {
      if (i.nodeType == 3 /* Node.TEXT_NODE */) // IE doesn't speak constants.
        text += i.data;
      else if (i.firstChild != null)
        text += getText(i);
    }
    return text;
  }

  function TocEntry(el, text, toclevel) {
    this.element = el;
    this.text = text;
    this.toclevel = toclevel;
  }

  function tocEntries(el, toclevels) {
    var result = new Array;
    var re = new RegExp('[hH]([1-'+(toclevels+1)+'])');
    // Function that scans the DOM tree for header elements (the DOM2
    // nodeIterator API would be a better technique but not supported by all
    // browsers).
    var iterate = function (el) {
      for (var i = el.firstChild; i != null; i = i.nextSibling) {
        if (i.nodeType == 1 /* Node.ELEMENT_NODE */) {
          var mo = re.exec(i.tagName);
          if (mo && (i.getAttribute("class") || i.getAttribute("className")) != "float") {
            result[result.length] = new TocEntry(i, getText(i), mo[1]-1);
          }
          iterate(i);
        }
      }
    }
    iterate(el);
    return result;
  }

  var toc = document.getElementById("toc");
  if (!toc) {
    return;
  }

  // Delete existing TOC entries in case we're reloading the TOC.
  var tocEntriesToRemove = [];
  var i;
  for (i = 0; i < toc.childNodes.length; i++) {
    var entry = toc.childNodes[i];
    if (entry.nodeName.toLowerCase() == 'div'
     && entry.getAttribute("class")
     && entry.getAttribute("class").match(/^toclevel/))
      tocEntriesToRemove.push(entry);
  }
  for (i = 0; i < tocEntriesToRemove.length; i++) {
    toc.removeChild(tocEntriesToRemove[i]);
  }

  // Rebuild TOC entries.
  var entries = tocEntries(document.getElementById("content"), toclevels);
  for (var i = 0; i < entries.length; ++i) {
    var entry = entries[i];
    if (entry.element.id == "")
      entry.element.id = "_toc_" + i;
    var a = document.createElement("a");
    a.href = "#" + entry.element.id;
    a.appendChild(document.createTextNode(entry.text));
    var div = document.createElement("div");
    div.appendChild(a);
    div.className = "toclevel" + entry.toclevel;
    toc.appendChild(div);
  }
  if (entries.length == 0)
    toc.parentNode.removeChild(toc);
},


/////////////////////////////////////////////////////////////////////
// Footnotes generator
/////////////////////////////////////////////////////////////////////

/* Based on footnote generation code from:
 * http://www.brandspankingnew.net/archive/2005/07/format_footnote.html
 */

footnotes: function () {
  // Delete existing footnote entries in case we're reloading the footnodes.
  var i;
  var noteholder = document.getElementById("footnotes");
  if (!noteholder) {
    return;
  }
  var entriesToRemove = [];
  for (i = 0; i < noteholder.childNodes.length; i++) {
    var entry = noteholder.childNodes[i];
    if (entry.nodeName.toLowerCase() == 'div' && entry.getAttribute("class") == "footnote")
      entriesToRemove.push(entry);
  }
  for (i = 0; i < entriesToRemove.length; i++) {
    noteholder.removeChild(entriesToRemove[i]);
  }

  // Rebuild footnote entries.
  var cont = document.getElementById("content");
  var spans = cont.getElementsByTagName("span");
  var refs = {};
  var n = 0;
  for (i=0; i<spans.length; i++) {
    if (spans[i].className == "footnote") {
      n++;
      var note = spans[i].getAttribute("data-note");
      if (!note) {
        // Use [\s\S] in place of . so multi-line matches work.
        // Because JavaScript has no s (dotall) regex flag.
        note = spans[i].innerHTML.match(/\s*\[([\s\S]*)]\s*/)[1];
        spans[i].innerHTML =
          "[<a id='_footnoteref_" + n + "' href='#_footnote_" + n +
          "' title='View footnote' class='footnote'>" + n + "</a>]";
        spans[i].setAttribute("data-note", note);
      }
      noteholder.innerHTML +=
        "<div class='footnote' id='_footnote_" + n + "'>" +
        "<a href='#_footnoteref_" + n + "' title='Return to text'>" +
        n + "</a>. " + note + "</div>";
      var id =spans[i].getAttribute("id");
      if (id != null) refs["#"+id] = n;
    }
  }
  if (n == 0)
    noteholder.parentNode.removeChild(noteholder);
  else {
    // Process footnoterefs.
    for (i=0; i<spans.length; i++) {
      if (spans[i].className == "footnoteref") {
        var href = spans[i].getElementsByTagName("a")[0].getAttribute("href");
        href = href.match(/#.*/)[0];  // Because IE return full URL.
        n = refs[href];
        spans[i].innerHTML =
          "[<a href='#_footnote_" + n +
          "' title='View footnote' class='footnote'>" + n + "</a>]";
      }
    }
  }
},

install: function(toclevels) {
  var timerId;

  function reinstall() {
    asciidoc.footnotes();
    if (toclevels) {
      asciidoc.toc(toclevels);
    }
  }

  function reinstallAndRemoveTimer() {
    clearInterval(timerId);
    reinstall();
  }

  timerId = setInterval(reinstall, 500);
  if (document.addEventListener)
    document.addEventListener("DOMContentLoaded", reinstallAndRemoveTimer, false);
  else
    window.onload = reinstallAndRemoveTimer;
}

}
asciidoc.install(2);
/*]]>*/
</script>
</head>
<body class="article">
<div id="header">
<h1>PL/Proxy tutorial</h1>
<div id="toc">
  <div id="toctitle">Table of Contents</div>
  <noscript><p><b>JavaScript must be enabled in your browser to display the table of contents.</b></p></noscript>
</div>
</div>
<div id="content">
<div id="preamble">
<div class="sectionbody">
<div class="paragraph"><p>This section explains how to use PL/Proxy to proxy queries across a set of
remote databases. For purposes of this intro we assume
each remote database has a "user" table that contains a username and an email
column.</p></div>
<div class="paragraph"><p>We also assume that the data is partitioned across remote databases by taking
a hash of the username and assigning it to one of 2 servers. Real applications
should use a partitioning scheme that is appropriate to their requirements.</p></div>
<div class="paragraph"><p>For the purposes of this example assume that the partition databases part00
and part01 both contain a table resembling</p></div>
<div class="listingblock">
<div class="content">
<pre><code>CREATE TABLE users (
    username text,
    email text
);</code></pre>
</div></div>
</div>
</div>
<div class="sect1">
<h2 id="_installation">Installation</h2>
<div class="sectionbody">
<div class="olist arabic"><ol class="arabic">
<li>
<p>
Download PL/Proxy from <a href="http://pgfoundry.org/projects/plproxy">http://pgfoundry.org/projects/plproxy</a> and extract.
</p>
</li>
<li>
<p>
Build PL/Proxy by running <code>make</code> and <code>make install</code> inside of the plproxy
    directory. If your having problems make sure that pg_config from the
    postgresql bin directory is in your path.
</p>
</li>
<li>
<p>
To install PL/Proxy in a database execute the commands in the <code>plproxy.sql</code>
    file.  For example <code>psql -f $SHAREDIR/contrib/plproxy.sql mydatabase</code>
</p>
</li>
</ol></div>
<div class="paragraph"><p>Steps 1 and 2 can be skipped if your installed pl/proxy from a packaging system
such as RPM.</p></div>
</div>
</div>
<div class="sect1">
<h2 id="_simple_remote_function_call">Simple remote function call</h2>
<div class="sectionbody">
<div class="paragraph"><p>Here we will create a plproxy function that will run on the proxy database which
will connect to a remote database named
<em>part00</em> and return a users email address.</p></div>
<div class="paragraph"><p>This example  uses plproxy in CONNECT mode, it will
connect to <code>dbname=part00</code> and run following SQL there:</p></div>
<div class="listingblock">
<div class="content">
<pre><code>CREATE FUNCTION get_user_email(i_username text)
RETURNS SETOF text AS $$
    CONNECT 'dbname=part00';
    SELECT email FROM users WHERE username = $1;
$$ LANGUAGE plproxy;

SELECT * from get_user_email($1);</code></pre>
</div></div>
<div class="paragraph"><p>The above example uses plproxy to proxy the query to the remote database but
doesn&#8217;t handle partitioning of data.  It assumes that the entire users table is
in the remote users database.  The next few steps will describe how to partition
data with PL/Proxy.</p></div>
</div>
</div>
<div class="sect1">
<h2 id="_create_configuration_functions">Create  configuration functions</h2>
<div class="sectionbody">
<div class="paragraph"><p>Using PL/Proxy for partitioning requires setting up some configuration functions.
Alternatively, if you are running PostgreSQL 8.4 or above you can take advantage
of the SQL/MED connection management facilities. See below.</p></div>
<div class="paragraph"><p>When a query needs to be forwarded to a remote database the function
plproxy.get_cluster_partitions(cluster) is invoked by plproxy to get the
connection string to use for each partition.</p></div>
<div class="paragraph"><p>The following is an example</p></div>
<div class="listingblock">
<div class="content">
<pre><code>CREATE OR REPLACE FUNCTION plproxy.get_cluster_partitions(cluster_name text)
RETURNS SETOF text AS $$
BEGIN
    IF cluster_name = 'usercluster' THEN
        RETURN NEXT 'dbname=part00 host=127.0.0.1';
        RETURN NEXT 'dbname=part01 host=127.0.0.1';
        RETURN;
    END IF;
    RAISE EXCEPTION 'Unknown cluster';
END;
$$ LANGUAGE plpgsql;</code></pre>
</div></div>
<div class="paragraph"><p>A production application might query some configuration tables to return the
connstrings. The number of partitions must be a power of 2.</p></div>
<div class="paragraph"><p>Next define a plproxy.get_cluster_version(cluster_name) function.  This is
called on each request and determines if the output from a cached
result from plproxy.get_cluster_partitions can be reused.</p></div>
<div class="listingblock">
<div class="content">
<pre><code>CREATE OR REPLACE FUNCTION plproxy.get_cluster_version(cluster_name text)
RETURNS int4 AS $$
BEGIN
    IF cluster_name = 'usercluster' THEN
        RETURN 1;
    END IF;
    RAISE EXCEPTION 'Unknown cluster';
END;
$$ LANGUAGE plpgsql;</code></pre>
</div></div>
<div class="paragraph"><p>We also need to provide a plproxy.get_cluster_config function, ours will provide
a value for the connection lifetime.  See the configuration section for details
on what this function can do.</p></div>
<div class="listingblock">
<div class="content">
<pre><code>CREATE OR REPLACE FUNCTION plproxy.get_cluster_config(
    in cluster_name text,
    out key text,
    out val text)
RETURNS SETOF record AS $$
BEGIN
    -- lets use same config for all clusters
    key := 'connection_lifetime';
    val := 30*60; -- 30m
    RETURN NEXT;
    RETURN;
END;
$$ LANGUAGE plpgsql;</code></pre>
</div></div>
<div class="paragraph"><p>The config section contains more information on all of these functions.</p></div>
</div>
</div>
<div class="sect1">
<h2 id="_configuring_pl_proxy_clusters_with_sql_med">Configuring Pl/Proxy clusters with SQL/MED</h2>
<div class="sectionbody">
<div class="paragraph"><p>First we need a foreign data wrapper. This is mostly a placeholder, but can
be extended with a validator function to verify the cluster definition.
See <a href="http://www.postgresql.org/docs/8.4/static/sql-createforeigndatawrapper.html">http://www.postgresql.org/docs/8.4/static/sql-createforeigndatawrapper.html</a>
for additional details of how to manage the SQL/MED catalog.</p></div>
<div class="listingblock">
<div class="content">
<pre><code>CREATE FOREIGN DATA WRAPPER plproxy;</code></pre>
</div></div>
<div class="paragraph"><p>Then the actual cluster with its configuration options and partitions:</p></div>
<div class="listingblock">
<div class="content">
<pre><code>CREATE SERVER usercluster FOREIGN DATA WRAPPER plproxy
OPTIONS (connection_lifetime '1800',
         p0 'dbname=part00 host=127.0.0.1',
         p1 'dbname=part01 host=127.0.0.1' );</code></pre>
</div></div>
<div class="paragraph"><p>We also need a user mapping that maps local PostgreSQL users to remote
partitions. It is possible to create PUBLIC mapping that applies for
all users in the local system:</p></div>
<div class="listingblock">
<div class="content">
<pre><code>CREATE USER MAPPING FOR PUBLIC SERVER usercluster;</code></pre>
</div></div>
<div class="paragraph"><p>Or a private mapping that can only be used by specific users:</p></div>
<div class="listingblock">
<div class="content">
<pre><code>CREATE USER MAPPING FOR bob SERVER usercluster
        OPTIONS (user 'plproxy', password 'salakala');</code></pre>
</div></div>
<div class="paragraph"><p>Finally we need to grant USAGE on the cluster to specific users:</p></div>
<div class="listingblock">
<div class="content">
<pre><code>GRANT USAGE ON SERVER usercluster TO bob;</code></pre>
</div></div>
</div>
</div>
<div class="sect1">
<h2 id="_partitioned_remote_call">Partitioned remote call</h2>
<div class="sectionbody">
<div class="paragraph"><p>Here we assume that the user table is spread over several databases based
on a hash of the username. The connection string for the partitioned databases
are contained in the get_cluster_partitions function described above.</p></div>
<div class="paragraph"><p>Below is a get_user_email function that is executed on the proxy server,which
will make a remote connection to the appropriate partitioned database. The
user&#8217;s email address will be returned.</p></div>
<div class="paragraph"><p>This function should be created in the proxy database.</p></div>
<div class="listingblock">
<div class="content">
<pre><code>CREATE OR REPLACE FUNCTION get_user_email(i_username text)
RETURNS SETOF text AS $$
    CLUSTER 'usercluster';
    RUN ON hashtext(i_username) ;
    SELECT email FROM users WHERE username = i_username;
$$ LANGUAGE plproxy;</code></pre>
</div></div>
</div>
</div>
<div class="sect1">
<h2 id="_inserting_into_the_proper_partition">Inserting into the proper partition</h2>
<div class="sectionbody">
<div class="paragraph"><p>Next we provide a simple INSERT function.</p></div>
<div class="paragraph"><p>Inserting data through plproxy requires functions to be defined on the proxy
databases that will perform the insert.</p></div>
<div class="paragraph"><p>We define this function on both part00 and part01</p></div>
<div class="listingblock">
<div class="content">
<pre><code>CREATE OR REPLACE FUNCTION insert_user(i_username text, i_emailaddress text)
RETURNS integer AS $$
       INSERT INTO users (username, email) VALUES ($1,$2);
       SELECT 1;
$$ LANGUAGE SQL;</code></pre>
</div></div>
<div class="paragraph"><p>Now we define a proxy function inside the proxy database to send the
INSERT&#8217;s to the appropriate target.</p></div>
<div class="listingblock">
<div class="content">
<pre><code>CREATE OR REPLACE FUNCTION insert_user(i_username text, i_emailaddress text)
RETURNS integer AS $$
    CLUSTER 'usercluster';
    RUN ON hashtext(i_username);
$$ LANGUAGE plproxy;</code></pre>
</div></div>
</div>
</div>
<div class="sect1">
<h2 id="_putting_it_all_together">Putting it all together</h2>
<div class="sectionbody">
<div class="paragraph"><p>Connect to the proxy database (The one we installed plproxy and the plproxy
functions on).</p></div>
<div class="listingblock">
<div class="content">
<pre><code>SELECT insert_user('Sven','sven@somewhere.com');
SELECT insert_user('Marko', 'marko@somewhere.com');
SELECT insert_user('Steve','steve@somewhere.cm');</code></pre>
</div></div>
<div class="paragraph"><p>Now connect to the plproxy_1 and plproxy_2 databases.  Sven and Marko should be
in plproxy_2 , and Steve should be in plproxy_1.</p></div>
<div class="paragraph"><p>When connected to the proxy user you can obtain data by doing</p></div>
<div class="listingblock">
<div class="content">
<pre><code>SELECT get_user_email('Sven');
SELECT get_user_email('Marko');
SELECT get_user_email('Steve');</code></pre>
</div></div>
</div>
</div>
<div class="sect1">
<h2 id="_connection_pooling">Connection pooling</h2>
<div class="sectionbody">
<div class="paragraph"><p>When used in partitioned setup, PL/Proxy somewhat wastes connections
as it opens connection to each partition from each backend process.
So it&#8217;s good idea to use a pooler that can take queries from several
connections and funnel them via smaller number of connections to actual
database.  We use and recommend
<a href="http://wiki.postgresql.org/wiki/PgBouncer">PgBouncer</a>
for that.</p></div>
</div>
</div>
<div class="sect1">
<h2 id="_more_resources">More resources</h2>
<div class="sectionbody">
<div class="paragraph"><p>Kristo Kaiv has his own take on tutorial here:
<a href="http://kaiv.wordpress.com/category/plproxy/">http://kaiv.wordpress.com/category/plproxy/</a></p></div>
</div>
</div>
</div>
<div id="footnotes"><hr /></div>
<div id="footer">
<div id="footer-text">
Last updated 2012-11-27 14:04:52 CET
</div>
</div>
</body>
</html>
postgresql-9.4-plproxy 2.5-5 / usr / share / doc / postgresql-9.4-plproxy / tutorial.html
