Horses are terrible people

This evening I rolled out our first use of plProxy tonight to facilitate cross PostgreSQL server communication. It was a bumpy start going from development to production so I thought I would detail a few problems I ran into:

• The version of flex on RHEL 4.0 (don’t ask - it’s related to vendor support issues with our SAN) doesn't like the plProxy install process. scanner.l doesn't parse properly and thus scanner.c is never made. I got around this by running it on a more current Gentoo based system. I then tested the compiled version of plproxy on our failover/standby PostgreSQL server.

• Our older primary production database is still SQL_ASCII due to the amount of work involved migrating it to UTF-8, which is one of our mid-term projects. plProxy will not communicate between databases if the encodings do not match. I wrote a wrapper function that sets client_encoding to SQL_ASCII on our UTF-8 database sessions for the function that is being proxied. Here's an example of what I ended up doing:

  CREATE OR REPLACE FUNCTION myFunction(IN uid1 int, IN uid2 int) RETURNS bool AS $$
      CONNECT 'host=pgbouncer01 dbname=production user=www port=6000';
  $$ LANGUAGE plproxy;
  
  CREATE OR REPLACE FUNCTION myFunctionWithEncoding(IN uid1 int, IN uid2 int) RETURNS bool AS $$
  DECLARE
    result RECORD;
  BEGIN
      SET client_encoding='SQL_ASCII';
      SELECT INTO result * FROM myFunction(uid1, uid2);
      SET client_encoding='UTF-8';
      RETURN result.boolcol;
  END;
  $$ LANGUAGE plpgsql;
  

  This is a pretty dirty hack, but it gets the job done. In addition, I probably didn’t need to set the encoding back to UTF-8 but I thought it better to be safe. I'll be happy once I get our production database set to UTF-8. The main gotcha I found in plProxy is this encoding issue. Something that will be resolved for us in the long run, but will have to be a hack in the short run.

Per Skype’s setup, we use pgBouncer between the servers for plProxy and cut down on the overhead required for startups. One of the nice things about this is since the pgBouncer instance is only used for inter-PostgreSQL server communication, I’ve been able to determine that we’re doing approximately 100 executions per second. While this is pretty low compared to our PostgreSQL usage in other places, it gives me a good feeling about our strategy with plProxy. After closely watching machine stats for the first 30 minutes of production, it appears that plProxy has very little if any impact on machine resources in our infrastructure.