Horses are terrible people

Portal was an amazingly fun game. I can’t overstate how enjoyable it was. And if you, like me, love your weighted companion cube, you can download fun wallpapers here.

I'm at ZendCon in San Francisco today and just sat down at the second session for me of the day - "Zend Framework Quick Start." After initial reservations of sitting through sessions about technologies that don't interest me, it occurred to me that with Zend behind it, the Zend Framework will likely become the 800 pound gorilla in the framework space. Ultimately I decided to go so I could see how they handle MVC and what kind of things I could take from their implementation. Interestingly, the presenter started off that Zend Framework isn't something a site like Tagged would want to use.

The first session I went to, which I will not name, was so poor that I walked out. I had hoped to get something from it, but between first language communication issues and an over simplified approach to the subject, there was no real information to be pulled but what the concept it covered was.

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.

In my previous blog post about Parallel Reindexing, Robert Treat asked about locking issues, and as my follow-up comment stated, the script wasn't engineered with locking in mind. It was built for offline reindexing. Part of the issue with Reindexing is while you can create an index concurrent with normal database operation in PostgreSQL 8.2, you can not reindex concurrently. This feature is currently on the PostgreSQL Todo List.

As I thought about the problem, it occurred to me that on the surface this was a fairly trivial problem to solve. Get the schema for the indexes of a table, create new indexes currently with a temporary name, drop the old index and rename the new index. There might be some locking issues with ALTER INDEX ... RENAME TO, but overall it seemed like the easiest and best strategy to go with. That is, until I started prototyping. Then it occurred to me - Primary Keys. Primary keys differ from normal indexes in that they are also constraints on the table. This is also true of unique indexes. By adding constraints to the mix, and having to remove and re-add primary keys, the equation just became more complex.

Take the given pg_constraint table from the pg_catalog schema:

    Table "pg_catalog.pg_constraint"
    Column     |    Type    | Modifiers 
---------------+------------+-----------
 conname       | name       | not null
 connamespace  | oid        | not null
 contype       | "char"     | not null
 condeferrable | boolean    | not null
 condeferred   | boolean    | not null
 conrelid      | oid        | not null
 contypid      | oid        | not null
 confrelid     | oid        | not null
 confupdtype   | "char"     | not null
 confdeltype   | "char"     | not null
 confmatchtype | "char"     | not null
 conkey        | smallint[] | 
 confkey       | smallint[] | 
 conbin        | text       | 
 consrc        | text       | 
Indexes:
    "pg_constraint_oid_index" UNIQUE, btree (oid)
    "pg_constraint_conname_nsp_index" btree (conname, connamespace)
    "pg_constraint_conrelid_index" btree (conrelid)
    "pg_constraint_contypid_index" btree (contypid)

This would appear to be the primary source of internal data organization for table related constraints, and while it most likely is, merely removing a row from this table and inserting a new one for the new index doesn't give us the ability to remove a primary key and make a newly constructed index the new primary key for a table.

This is where I've explored to with regard to the index related constraints. As I explore further in the pg_catalog schema and how an index is stored as a primary key, I will detail it further here.