Go deeper into BDR and patched-PostgreSQL 9.4.5, eventually I find out the root cause. When new record INSERT-ed, BDR replicates it to other nodes. After simple_heap_insert, BDR calls UserTableUpdateOpenIndexes. For index on epression written in SQL, BDR code routes to GetActiveSnapshot() which fires Segmentation fault because ActiveSnapShot is invalid.

return ActiveSnapshot->as_snap;//invalid ActiveSnapshot fires Segmentation fault

Goes to bdr_apply.c in BDR package, I found out that ActiveSnapshot is set and cleared with PushActiveSnapshot and PopActiveSnapshot calls respectively in process_remote_update and process_remote_delete functions but not in process_remote_insert.

Then, I apply the function pair in process_remote_insert at the point before and after UserTableUpdateOpenIndexes calls :

if (conflict)

if (apply_update)

UserTableUpdateOpenIndexes(estate, newslot);



simple_heap_insert(rel->rel, newslot->tts_tuple);
UserTableUpdateOpenIndexes(estate, newslot);


Rebuild and reinstall the BDR package. Now no more replication crashes caused by updating index on expression written in SQL language. Case closed.

What a sleepless night investigating why BDR crashes when updating index on expression with Segmentation Fault error. I agree with akretschmer‘s comment on my previous post that replacing functional index with column index is not considered as a solution. So I take a closer look at function associated with the problematic expression index:

The function is defined in SQL language:

CREATE OR REPLACE FUNCTION mybdr.funcidx(a smallint, b integer, c smallint, d text)
SELECT ($1+1)::text || '.' || '5' || (CASE WHEN $3=2::smallint THEN '1' ELSE '' END) || '.' || LPAD($2::text, 6, '0') || '/' || $4;

Then I find out that by converting it to PLPGSQL language, no more BDR crashes:

CREATE OR REPLACE FUNCTION mybdr.funcidx(a smallint, b integer, c smallint, d text)
RETURN (a+1)::text || '.' || '5' || (CASE WHEN c=2::smallint THEN '1' ELSE '' END) || '.' || LPAD(b::text, 6, '0') || '/' || d;

Need a deep investigation on that.

Giving BDR version 0.9.3 and patched-PostgreSQL 9.4.5 replication a shot. All runs smooth until I add an index expression to a replicated table then insert a record into. BDR crashes with error message:

LOG: worker process: bdr (6229655055721591121,1,16386,)->bdr (6229655055721591121,1, (PID 750) was terminated by signal 11: Segmentation fault
DETAIL: Failed process was running: bdr_apply: BEGIN origin(source, orig_lsn, timestamp): 0/9D50AD0, 2015-12-27 10:13:29.916096+07

Followed by series of:
FATAL: mismatch in worker state, got 0, expected 1

Dig into BDR C codes, I found out that the error is emitted from a point in bdr_apply.c:

function: process_remote_insert
simple_heap_insert(rel->rel, newslot->tts_tuple); --ok
UserTableUpdateOpenIndexes(estate, newslot); --failed

Go deeper into BDR-patched-PostgreSQL’s index.c, it is clear that BDR only crashes when updating index on expression (no problem with column index).

function: FormIndexDatum
iDatum = slot_getattr(slot, keycol, &isNull); --ok

iDatum = ExecEvalExprSwitchContext((ExprState *) lfirst(indexpr_item), GetPerTupleExprContext(estate),&isNull,NULL); --failed

Simple solution is removing the index expression and promote field (or new field) for regular column index instead.

I have gone through an interesting small size C code package for QR encoding at https://github.com/swex/QR-Image-embedded. Its logic is contained in two small files: QR_Encode.h (3.7KB) and QR_Encode.c (61KB). Then it is sun-shiny Saturday morning as I manage to add in-memory monochrome bitmap construction (1 bit per pixel) and wrap the whole package as PostgreSQL extension module. I share it at qrcode.tar.bz2.

Please modify PG_CONFIG = /opt/pgsql/9.4/bin/pg_config in Makefile as necessary. Then make and make install.

Connect to a database then invoke sql command CREATE EXTENSION qr;

SELECT qr('QR Code with PostgreSQL', 0, 0, 4);

  1. Text to be encoded.
  2. Error correction level (0 to 3).
  3. Accepted model number (0 to 2).
  4. Scale 4 means: a dot in QR image will be 4 pixel width and 4 pixel height.

It returns byte array representing monochrome bitmap. You can save it as a file or directly render it to HTML page.
QRCode Demo

On 16-17 October 2015, I have given PostgreSQL talks to high school and college teachers community attending ICTVET (International Conference on Technical and Vocational Education & Training) 2015 held by State University of Padang (West Sumatra, Indonesia). It was a good chance to promote PostgreSQL in the region where MySQL is more popular.
pgsql (Elephant picture at the background is a nice carving on Borobudur wall, Central Java, Indonesia).

At the occasion, I have presented PostgreSQL as abstraction layer for heterogenous data integration. Here I share paper and presentation slide.

My favourite feature of BDR by 2ndQuadrant is “multi master table level selective replication”. Currently, I am preparing a plan to switch from trigger based to low level BDR replication for better performance.

But, how can I detect which replicated tables got changes (needed for memcached invalidation, for example). I can not trap it inside always-enabled-trigger any longer. An alternative is to modify BDR plugin c source code. Thanks to 2ndQuadrant team for clear source code. Following code is my modification for bdr_apply.c:

  1. Module level variable and function declaration:
  2. #define DTABLECHUNK 5
    static char** dtables;
    static int dtablessize;
    static int dtablescount;
    static void append_dtables(char* t);
  3. Variable initialization at the end of process_remote_begin:
  4. static void
    process_remote_begin(StringInfo s) {


  5. Modify a bit declaration and definition of read_rel function to catch tablename:
  6. static BDRRelation *
    read_rel(StringInfo s, LOCKMODE mode, char** dtablename){
    int relnamelen;
    int nspnamelen;
    RangeVar* rv;
    Oid relid;
    MemoryContext oldcontext;

    rv = makeNode(RangeVar);

    nspnamelen = pq_getmsgint(s, 2);
    rv->schemaname = (char *) pq_getmsgbytes(s, nspnamelen);

    relnamelen = pq_getmsgint(s, 2);
    rv->relname = (char *) pq_getmsgbytes(s, relnamelen);

    //nspnamelen and relnamelen is the length of the name including terminating zero
    *dtablename=(char*)palloc( (nspnamelen+relnamelen) * sizeof(char) );
    sprintf(*dtablename, "%s.%s", rv->schemaname, rv->relname);

    relid = RangeVarGetRelidExtended(rv, mode, false, false, NULL, NULL);

    return bdr_heap_open(relid, NoLock);


  7. Declare variable char *tablename inside process_remote_insert, process_remote_update and process_remote_delete. Add its address (&tablename) as third argument for read_real function invocation. Then, register the tablename by calling my append_dtables just after simple_heap_insert, simple_heap_update and simple_heap_delete.
  8. Call outside function to notify which tables got changes inside process_remote_commit function, somewhere after a call to CommitTransactionCommand.
  9. for(i=0;dtables!=NULL && i<dtablescount;i++) {
    //any function call to notify dtables[i] got changes
  10. And finally, this is my append_dtables definition. Note that memory is allocated in MessageContext so it will be freed automatically in bdr_apply_work main loop.
  11. static void
    append_dtables(char* t) {
    int i, allocsize;
    MemoryContext oldcontext;

    for(i=0;i<dtablescount;i++) {

    if(!strcmp(dtables[i], t)) {//ensure unique table registration



    if(dtablessize<++dtablescount) {

    allocsize=dtablessize * sizeof(char*);



    dtables=(char**)repalloc(dtables, allocsize);




    What I love from open source project is that there is always a way to tweak to suit my development framework.

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) {