pgmemcache is a set of PostgreSQL user-defined functions that provide an interface to memcached. Installing pgmemcache is easy, but does have a few trivial requirements.

• PostgreSQL 9.1 or newer
• libmemcached 0.38 or OMcache 0.2.0 or newer

Always prefer the latest versions.

pgmemcache uses libmemcached by default, but support for an alternative memcache client library, OMcache, was added during pgmemcache 2.2.x development. pgmemcache can be built with OMcache instead of libmemcached by passing USE_OMCACHE=1 argument to make.

pgmemcache uses the memcache binary protocol by default, this is required for the "increment / decrement with initial" operations pgmemcache uses.

1. Install PostgreSQL with PGXS (if not already installed)
2. Install libmemcached or OMcache
3. make
4. sudo make install

If necessary, update LD_LIBRARY_CACHE for the PostgreSQL server owner and restart PostgreSQL. Also make sure that the pg_config binary is in your PATH (or edit the Makefile).

1. sudo make build-dep
2. make deb9.2 (or deb9.3, deb9.4, etc depending on your PostgreSQL version)
3. sudo dpkg -i ../postgresql-pgmemcache-*.deb

1. make rpm
2. sudo rpm -Uvh $(rpm --eval '%{_rpmdir}/%{_arch}')/pgmemcache-*.rpm

% psql <mydbname> -c 'create extension pgmemcache'

It is often more convenient to specify a list of memcached servers to connect to in postgresql.conf, rather than calling memcache_server_add() in each new client connection. This can be done as follows:

1. Edit postgresql.conf
2. Append "pgmemcache" to shared_preload_libraries

(If using PostgreSQL 9.1 or earlier versions)

3. Append "pgmemcache" to custom_variable_classes

(Optional parts)

4. Set the "pgmemcache.default_servers" custom GUC variable to a comma-separated list of 'host:port' pairs (the port is optional).

5. Set the pgmemcache.default_behavior flags to suit your needs. The format is a comma-separated list of memcached behavior (optional) of behavior_flag:behavior_data. The flags correspond with libmemcached behavior flags. Check the libmemcached documentation for those. As an example behavior you might have the following line in your postgresql.conf:

   pgmemcache.default_behavior='DEAD_TIMEOUT:2'

In case your system has SELinux please install the required SELinux policy:

/usr/bin/checkmodule -M -m -o pgmemcache.mod pgmemcache.te
/usr/bin/semodule_package -o pgmemcache.pp -m pgmemcache.mod
/usr/sbin/semodule -i pgmemcache.pp

memcache_server_add('hostname:port'::TEXT)
memcache_server_add('hostname'::TEXT)

Adds a server to the list of available servers. If the port is not specified, the memcached default port (11211) is used. This should only be done in one central place in the code (normally wrapped in an IF statement).

memcache_add(key::TEXT, value::TEXT, expire::TIMESTAMPTZ)
memcache_add(key::TEXT, value::TEXT, expire::INTERVAL)
memcache_add(key::TEXT, value::TEXT)

Adds a key to the cache cluster, if the key does not already exist.

newval = memcache_decr(key::TEXT, decrement::INT8)
newval = memcache_decr(key::TEXT)

If key exists and is an integer, atomically decrements by the value specified (default decrement is one). Returns INT value after decrement.

memcache_delete(key::TEXT, hold_timer::INTERVAL)
memcache_delete(key::TEXT)

Deletes a given key. If a hold timer is specified, key with the same name can't be added until the hold timer expires.

memcache_flush_all()

Flushes (drops) all data on all servers in the memcache cluster.

value = memcache_get(key::TEXT)

Fetches a key out of the cache. Returns NULL if the key does not exist; otherwise, it returns the value of the key as TEXT. Note that zero-length values are allowed.

memcache_get_multi(keys::TEXT[])
memcache_get_multi(keys::BYTEA[])

SELECT key, value FROM memcache_get_multi('{qwerty,asdfg}'::TEXT[]);

Fetches an ARRAY of keys from the cache, returns a list of RECORDs for the found keys, with the columns titled key and value.

newval = memcache_incr(key::TEXT, increment::INT8)
newval = memcache_incr(key::TEXT)

If key exists and is an integer, atomically increment by the value specified (the default increment is one). Returns INT value after increment.

memcache_replace(key::TEXT, value::TEXT, expire::TIMESTAMPTZ)
memcache_replace(key::TEXT, value::TEXT, expire::INTERVAL)
memcache_replace(key::TEXT, value::TEXT)

Replaces an existing key's value if the key already exists.

memcache_set(key::TEXT, value::TEXT, expire::TIMESTAMPTZ)
memcache_set(key::TEXT, value::TEXT, expire::INTERVAL)
memcache_set(key::TEXT, value::TEXT)

Regardless of whether the specified key already exists, set its current value to "value", replacing the previous value if any.

stats = memcache_stats()

Returns a TEXT string with all of the stats from all servers in the server list.

Most installations will need a few functions to allow pgmemcache to work correctly. Here are a few example functions that should get most people off the ground and running.

The following function is an example of a trigger function that is used to replace the value of something in the cache with its new value. :

CREATE OR REPLACE FUNCTION auth_passwd_upd()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF OLD.passwd <> NEW.passwd THEN
        PERFORM memcache_replace('user_id_' || NEW.user_id || '_password', NEW.passwd);
    END IF;
    RETURN NEW;
END;
$$;

Activate the trigger for UPDATEs:

CREATE TRIGGER auth_passwd_upd_trg AFTER UPDATE ON passwd
    FOR EACH ROW EXECUTE PROCEDURE auth_passwd_upd();

The above is not transaction safe, however. A better approach is to have pgmemcache invalidate the cached data, but not replace it.

CREATE OR REPLACE FUNCTION auth_passwd_upd()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF OLD.passwd <> NEW.passwd THEN
        PERFORM memcache_delete('user_id_' || NEW.user_id || '_password');
    END IF;
    RETURN NEW;
END;
$$;

Here's an example delete trigger:

CREATE OR REPLACE FUNCTION auth_passwd_del()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    PERFORM memcache_delete('user_id_' || OLD.user_id || '_password');
    RETURN OLD;
END;
$$;

Activate the trigger for DELETEs:

CREATE TRIGGER auth_passwd_del_trg AFTER DELETE ON passwd
    FOR EACH ROW EXECUTE PROCEDURE auth_passwd_del();

pgmemcache is released under an MIT-style license (BSD without advertising clause). For the exact license terms, see the file "LICENSE".

pgmemcache is currently maintained by Hannu Valtonen and Oskari Saarenmaa from Ohmu Ltd, they can be contacted at <pgmemcache@ohmu.fi>.

Bug reports and patches are very welcome; issues should be reported in GitHub's issue interface (https://github.com/ohmu/pgmemcache) and patches and other enhancement proposals should be submitted as GitHub pull requests.

pgmemcache was originally written by Sean Chittenden. Version 1.x series was maintained by Neil Conway and sponsored by the Open Technology Group, Inc. Version 2.0 was rewritten to work on top of libmemcached and the maintainership moved to Hannu Valtonen.

Suzuki Hironobu contributed major patches for the 2.0 series, among other things, support for libmemcached configuration settings. F-Secure Corporation contributed extension support and bug fixes for version 2.1.

See https://github.com/ohmu/pgmemcache/graphs/contributors for the list of recent contributors.