Call Memory Cache Functionality from Within Stored Procedure (Part 2)

Cache Invalidation
My previous post with the same title leaves a question: how to invalidate cache and deal with race condition. Colin ‘t Hart suggested to implement “on commit trigger” which is not available in PostgreSQL currently.

In a good book “PostgreSQL Server Programming” by Jim Mlodgenski, Hannu Krosing and Kirk Roybal, there is a statement that we can register C-language function to be called on COMMIT or ROLLBACK events.

I have an idea to have such a C function by which I can store keys for invalidation and later call pgmemcache’s memcache_delete in respond to COMMIT event:

CREATE OR REPLACE FUNCTION mcache_delete(IN t_keys text[])
AS '$libdir/mcache', 'mcache_delete' LANGUAGE C IMMUTABLE;
psql=# begin;
psql=# ... some work ...
psql=# select mcache_delete(ARRAY['dcb50cad', '1f19bee7']); --store keys for invalidation
psql=# ... other work ...
psql=# select mcache_delete(ARRAY['91f29028']); --store other keys for invalidation
psql=# commit; --delete memcache keys

My code:

#include <postgres.h>
#include <fmgr.h>
#include <utils/array.h>
#include <utils/builtins.h>
#include <catalog/pg_type.h>
#include <access/xact.h>
#include <utils/memutils.h>


//declare pgmemcahe functionality
extern Datum memcache_delete(PG_FUNCTION_ARGS);

void _PG_init(void);
void _PG_fini(void);
static void mcache_xact_callback(XactEvent event, void *arg);
static void mcache_reset(void);

static int mcache_count;
static text** mcache_keys;

Datum mcache_delete(PG_FUNCTION_ARGS);

Datum mcache_delete(PG_FUNCTION_ARGS) {
  ArrayType* keys;
  Datum* elems;
  bool* nulls;
  int n, i;
  MemoryContext oldCtx;

  if(PG_NARGS() != 1)
    elog(ERROR, "1 argument expected");

  if( !PG_ARGISNULL(0) ){
    keys = PG_GETARG_ARRAYTYPE_P(0);
    keys, TEXTOID, -1, false, 'i',
    &elems, &nulls, &n);
      oldCtx = MemoryContextSwitchTo(TopTransactionContext);
      if(mcache_count == 0)
        mcache_keys = (text**)palloc(n * sizeof(text*));
        mcache_keys = (text**)repalloc( (void*)mcache_keys, (mcache_count+n) * sizeof(text*));
      for(i=0; i<n ; i++)
        mcache_keys[mcache_count+i] = nulls[i] ? NULL : DatumGetTextPCopy(elems[i]);
      mcache_count += n;



void _PG_init(void) {
  RegisterXactCallback(mcache_xact_callback, NULL);

void _PG_fini(void) {

static void mcache_xact_callback(XactEvent event, void *arg) {
  int i;
  if( event == XACT_EVENT_COMMIT ) {//commit
    for(i=0; i<mcache_count; i++)
        DirectFunctionCall1(//call pgmemcache function
          PointerGetDatum(mcache_keys[i]) );
    //note: mcache_keys are allocated in TopTransactionContext
    //and implicitly released by internal Postgresql framework
  } else if ( event == XACT_EVENT_ABORT ) //rollback

static void mcache_reset(void) {

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: