/pgvihash

version-independent hash functions for PostgreSQL

Primary LanguageCOtherNOASSERTION

Version-independent hash functions for PostgreSQL
=================================================

This package contains a PostgreSQL extension function vihashtext()
that is similar to the built-in hashtext() but is guaranteed not to
change over major releases, so that it can be used safely for
partitioning or sharding, for example with PL/Proxy, in installations
involving multiple PostgreSQL major versions.

A PL/Proxy function usually looks something like this:

CREATE FUNCTION ...
LANGUAGE plproxy
AS $$
    CLUSTER '...';
    RUN ON hashtext(arg);
    SELECT ...
$$;

The problem is that the hashtext() function is an implementation
detail of PostgreSQL and may produce different results for the same
input in different PostgreSQL major versions.  When that happens, you
cannot upgrade your PL/Proxy installation to a new major PostgreSQL
version without redistributing the data.  A change like this has in
particular happened between PostgreSQL 8.3 and 8.4.  See also
<http://petereisentraut.blogspot.com/2009/10/attention-plproxy-users-hash-functions.html>.

This package basically just wraps the hashtext() function from
PostgreSQL 8.3 into a separate module.  This way, you have a hash
function that is backward compatible to the built-in hashtext() from
PostgreSQL 8.0 through 8.3, and that can be used without concerns in
the future.  If you are already using PostgreSQL 8.4 or later, you
cannot change to use this package without redistributing your data.

To use it, install it into the database by running

    CREATE EXTENSION vihash;

or, in versions before PostgreSQL 9.1,

    \i vihash.sql

and replace

    RUN ON hashtext(arg);

by

    RUN ON vihashtext(arg);

Note: This module is primarily intended as a migration aid.  New users
of PL/Proxy might want to look into pghashlib
(https://github.com/markokr/pghashlib) instead, which offers a wider
selection of stable hashing algorithms.  You can convert your vihash
usage like this:

PG <=8.3 hashtext = vihashtext = pghashlib "lookup3" algorithm
PG >=8.4 hashtext =              pghashlib "pgsql84" algorithm