This is a follow up to the last connection pooling blog entry. If it seems like I am spending an undue amount of time on this topic then you feel the same way as I do.
I’ve grown wary of node-postgres‘ built-in pooling and have decided to implement my own connection pooling on top of it. In production, I was seeing that connections would “leak” from the pool, causing the pool to fill and time out all further requests. The only fix I had was to have a periodic health check that rebooted the process: obviously this wouldn’t work for launch. The issue is documented here: https://github.com/brianc/node-postgres/issues/137 but most of the discussed solutions won’t work with my pooled decorator pattern.
Luckily, new versions of node-pool has the pattern built in and baking your own pool is a couple of lines. Let’s create a pool:
pg = require('pg').native # Or non-native, if you prefer poolModule = require 'generic-pool' connectionString = "tcp://user:pass@localhost/your_database" pgPool = poolModule.Pool name: 'postgres' create: (cb) -> client = new pg.Client connectionString client.connect (err) -> return cb(err) if err? client.on "error", (err) -> console.log "Error in postgres client, removing from pool" pgPool.destroy(client) client.pauseDrain() #Make sure internal pooling is disabled cb null, client destroy: (client) -> client.end() max: 10 idleTimeoutMillis : 30 * 1000 log: true #remove me if you aren't debugging
pgPool is a pool of up to ten postgres clients. Calling
pgPool.acquire will give you a native postgres client to work with, while calling
pgPool.release will return it back to the pool. Lastly, calling
pgPool.pooled and passing in a function will “decorate” your function so that it auto-acquires on call, and auto-releases on callback. See the pooled function decoration documentation.
Note that in
create we call the
pg.Client constructor directly instead of using
pg.connect returns a proxy object that does internal connection pooling.
Okay! That was pretty basic. To spice this post up a bit, here’s a little class that helps out with transactions:
class Transaction #Unmanaged client (not auto-released) @startInClient: (pgClient, releaseOnCompletion, callback) -> [callback, releaseOnCompletion] = [releaseOnCompletion, false] if typeof releaseOnCompletion == 'function' (new Transaction(pgClient, releaseOnCompletion)).startTransaction callback #Managed client (auto-released on commit / rollback) @start: (callback) -> pgPool.acquire (err, pgClient) -> return callback(err) if err? Transaction.startInClient pgClient, true, callback constructor: (@pgClient, @releaseOnComplete) -> startTransaction: (cb) -> @pgClient.query "BEGIN", (err) => cb(err, @) rollback: (cb) -> @pgClient.query "ROLLBACK", (err) => pgPool.release @pgClient if @releaseOnComplete cb(err, @) if cb? commit: (cb) -> @pgClient.query "COMMIT", (err) => pgPool.release @pgClient if @releaseOnComplete cb(err, @) if cb? wrapCallback: (cb) -> (err) => callerArguments = arguments if err? @rollback() return cb callerArguments... else @commit (commitErr) -> return cb(commitErr) if commitErr? cb callerArguments...
Transaction.start acquires a new connection from the pool, begins a transaction and returns an object that can be used to manage the transaction. You can either
commit the transaction. One more sophisticated function is the
wrapCallback function. This is best understood with an example:
# Start a transaction and do something with it Transaction.startTransaction (err, t) => return console.log "Couldn't start transaction" if err? callback = t.wrapCallback(callback) pgClient = t.pgClient pgClient.query "INSERT INTO fun(id) VALUES(1)", (err) -> return callback(err) if err? pgClient.query "INSERT INTO people(id,fun_id) VALUES (1,1)", callback
The above code transactionally inserts an item into the
fun table and the
people table. Beyond
Transaction.startTransaction it never mentions the transaction again. The magic here is the
t.wrapCallback function: it produces a wrapped callback that
rollbacks when called with an error (the first parameter isn’t undefined/null) and
In the languages I know, the “gold standard” for transactions is when they are as transparent as possible. Sometimes (i.e. Spring with JDBC in Java), this means resorting to thread-local contexts. I think the above transaction class matches this standard, even if the
wrapCallback is a little tricky to understand.
And with that, we’re back to coding. If you like postgres and silly faces, try out the Silly Face Society on the iPhone.