Erlang and PostgreSQL Redux

I spent last weekend implementing encoding and decoding of PostgreSQL's date and time types. With this complete, epgsql is nearing a 1.0 release. Aside from bug fixes I've also implemented support for the 'returning' clause to return rows from an insert, update, or delete. The README file has been updated and includes a section describing how PostgreSQL types are represented in Erlang.

One thing you may notice is a lack of support for connection pooling, which I have remedied with epgsql_pool, available from this mercurial repository:

http://glozer.net/src/epgsql_pool

I debated including connection pools in epgsql itself, but I don't think they are the correct approach in Erlang. Instead I create pools of application-specific database accessor processes which are supervised and hold a persistent connection. This provides both a nice abstraction and the ability to prepare named statements during initialization rather than when a request comes in.

So use epgsql_pool at your own risk, I probably won't be maintaining it =)

Erlang and PostgreSQL

Despite the lack of blog posts, the last four months at heysan have been quite busy. Most of December was spent developing a new web application, entirely in Erlang, which compliments our existing service. This application makes extensive use of PostgreSQL 8.3, which eventually lead me to resurrecting and releasing my own database driver, available in this mercurial repository:

http://glozer.net/src/epgsql

Note: this is a 'static' HTTP repository, so if you're using a version of mercurial older than 1.1 the URL is static-http://glozer.net/src/epgsql.

Yes, I've inflicted yet-another PostgreSQL driver with an incompatible API (and even conflicting module names!) on the Erlang community. I'm very sorry =) But please, hear me out! I'd like to present my case and hopefully convince you to use my driver. While I must admit implementing PostgreSQL's network protocol was rather fun and entertaining, I did attempt to use the existing drivers before embarking on the project of writing a new one.

pgsql (originally from jungerl) is a small and simple driver, which was also forked by Process One for use in ejabberd. I was able to get up and running quickly with this driver, since it supports the "trusted" auth mechanism and has a simple API. On the other hand psql:squery/2 drops null columns from results and psql:pquery/3 fails during row decoding when nulls are present. All columns are returned as Erlang lists, which results in a lot of escaping overhead for bytea columns.

psql (originally from Erlang Consulting) is quite the opposite of pgsql with a very complicated API that requires you to configure connection pools using the application environment. This driver is also prone to hanging in various states, for example when trying to use an authentication method other than MD5. On the bright side it does decode column values into native Erlang types, but fails on very simple queries such as: psql:sql_query(C, "select 1, null").

Since neither driver was suitable for my needs, I spent Christmas vacation writing a new one, epgsql, which features a simple API, converts common db types to native Erlang types, and uses PostgreSQL's binary format for common types. For example the SQL statement "select 1" will return a numeric 1, "select true" will return the atom true, and "select 'hi'::bytea" will return a binary.

This type conversion is used in the extended query protocol, i.e. pgsql:equery and epgsql:parse, epgsql:bind, epgsql:execute:

{ok, C} = pgsql:connect("localhost", []),
{ok, Cols, Rows} = pgsql:equery(C, "select 1, null"),
[{column,<<"?column?">>,int4,4,-1,0}, {column,<<"?column?">>,unknown,-2,-1,0}] = Cols,
[{1,null}] = Rows.

The simple query protocol used by epgsql:squery returns values (other than null which is always the atom 'null') as Erlang binaries:

{ok, C} = pgsql:connect("localhost", []),
{ok, Cols, Rows} = pgsql:squery(C, "select 1, null"),
[{column,<<"?column?">>,int4,4,-1,0}, {column,<<"?column?">>,unknown,-2,-1,0}] = Cols,
[{<<"1">>,null}] = Rows.

Documentation for epgsql is a little sparse at the moment, but the README file covers the basics. I intend to continue developing and supporting this driver so please send me bug reports and enhancement requests!