Month: December 2010

Two years of explain.depesz.com

First of all – just today I committed patch for Pg::Explain – which is the workhorse behind explain.depesz.com.

This patch fixes calculation of exclusive time for explain nodes, and the best thing about it is – I didn't write it. It's full patch provided by someone else – Filip Rembiałkowski – my former colleague, friend, and PostgreSQL DBA (not olny PG!).

This is (as far as I recall) first patch that was provided to me for this library, and I'm really grateful for the contribution.

New version of Pg::Explain will hit CPAN mirrors shortly (it's already uploaded, now we're waiting for CPAN mirrors to get it).

When I was updating it, I checked state of database. And I learned that explain.depesz.com is over 2 years now! Some stats follow.

Continue reading Two years of explain.depesz.com

Waiting for 9.1 – KNNGIST

On 4th of December, Tom Lane committed really cool patch:

KNNGIST, otherwise known as order-by-operator support for GIST.

This commit represents a rather heavily editorialized version of
Teodor's builtin_knngist_itself-0.8.2 and builtin_knngist_proc-0.8.1
patches.  I redid the opclass API to add a separate Distance method
instead of turning the Consistent method into an illogical mess,
fixed some bit-rot in the rbtree interfaces, and generally worked over
the code style and comments.
 
There's still no non-code documentation to speak of, but I'll work on
that separately.  Some contrib-module changes are also yet to come
(right now, point <-> point is the only KNN-ified operator).
 
Teodor Sigaev and Tom Lane

Continue reading Waiting for 9.1 – KNNGIST

Named interfaces for OpenVPN and Cisco VPN

I use quite a lot of vpns. On any given moment I have between 3 and 10 active vpn connections from the machine I'm working on.

I generally tend to use OpenVPN, but I also do use vpnc (Cisco VPN client).

One thing that I noticed is not very commonly known, and ( in my case – helps a lot ), is that you don't have to have your tunnel interfaces named tun0, tun1, tun2 and so on.

You can do something like this:

in /etc/vpnc/tunnel.conf, add such line:

Interface name <strong>depesz</strong>

and enable the tunnel – you will see that instead of boring, and somewhat cryptic “tunX" interface you'll have interface named “depesz“.

Similar thing for openvpn – in it's conf file for tunnel add these 2 lines:

dev-type tun
dev <strong>smart</strong>

First line is new one (usually), and is required so that OpenVPN can know if you want TUN or TAP tunnels. “dev" is usually “tun", but it can be changed, and with above lines, will make the tunnel interface named “smart“.

Auto refreshing password file for pgbouncer

As you perhaps know I'm fan of pgbouncer – connection pooling solution for PostgreSQL.

It can do many really cool things, but has one slight issue.

Since it can reuse connections – it has to provide a way to check if user supplied password is correct without consulting database. And it lately (since 9.0 to be exact) became somewhat of a problem.

Continue reading Auto refreshing password file for pgbouncer