Archive

PostgreSQL

For those who interested in cash spelling in Indonesian, here I share PLPGSQL code:

Usage:
SELECT cash_spell(1200000000.45, true);
'Satu Milyar Dua Ratus Juta Rupiah Empat Puluh Lima Sen'

Code:
CREATE OR REPLACE FUNCTION int_spell(i_val integer)
RETURNS text LANGUAGE 'plpgsql' IMMUTABLE STRICT
AS $BODY$
DECLARE
   _t text:=''::text;
   _SYMBOL text[]:=ARRAY['satu', 'dua', 'tiga', 'empat', 'lima', 'enam', 'tujuh', 'delapan', 'sembilan', 'sepuluh'
, 'sebelas', 'dua belas', 'tiga belas', 'empat belas', 'lima belas', 'enam belas', 'tujuh belas'
, 'delapan belas', 'sembilan belas'];
   _val integer:=i_val;
   _i integer;
BEGIN
   IF _val<1 OR _val>999 THEN
      RAISE EXCEPTION 'outside boundaries';
   END IF;

   _i:=(_val - (_val % 100)) / 100;
   IF _i>0 THEN
      IF _i>9 THEN
         RAISE EXCEPTION 'hundreds failed';
      END IF;
      IF _i = 1 THEN
         _t:='seratus';
      ELSE
         _t:=_SYMBOL[_i] || ' ratus';
      END IF;
      _val:=_val - (_i*100);
   END IF;

   IF _val>0 AND _val<20 THEN
      IF LENGTH(_t) > 0 THEN
         _t:= _t || ' ';
      END IF;
      _t:=_t || _SYMBOL[_val];
      _val:=0;
   END IF;

   _i:=(_val - (_val % 10)) / 10;
   IF _i>0 THEN
      IF _i>9 THEN
         RAISE EXCEPTION 'tenth failed';
      END IF;
      IF LENGTH(_t) > 0 THEN
         _t:= _t || ' ';
      END IF;
      IF _i = 1 THEN
         _t:= _t || 'sepuluh';
      ELSE
         _t:= _t || _SYMBOL[_i] || ' puluh';
      END IF;
      _val:=_val - (_i*10);
   END IF;

   IF _val>0 THEN
      IF LENGTH(_t) > 0 THEN
         _t:= _t || ' ';
      END IF;
      _t:=_t || _SYMBOL[_val];
   END IF;

   RETURN _t;
END;
$BODY$;
GRANT EXECUTE ON FUNCTION int_spell(integer) TO public;

CREATE OR REPLACE FUNCTION cash_spell(n numeric, b_initcap boolean)
RETURNS text LANGUAGE 'plpgsql' IMMUTABLE STRICT
AS $BODY$
DECLARE
   _t text:=''::text;
   _n numeric:=n;
   _frac numeric;
   _cent integer;
   _val integer;
   _suffix text[];
   _s text;
   _unit numeric;
BEGIN
   IF _n > 9999999999999999999999999999999999::numeric THEN
      RAISE EXCEPTION 'outside boundaries';
   END IF;

   IF _n < 0::numeric THEN
      RAISE EXCEPTION 'negative value';
   END IF;

   IF _n = 0::numeric THEN
      _t:='nol rupiah';
      IF b_initcap THEN
         _t:=initcap(_t);
      END IF;
      RETURN _t;
   END IF;

   _frac:= _n - floor(_n);
   _cent:= floor(_frac*100)::integer;

   _n:= _n - _frac; --remove fraction

   _suffix:=ARRAY['desiliun', 'noniliun', 'oktiliun', 'septiliun', 'sekstiliun', 'kuantiliun', 'kuadriliun',
'triliun', 'milyar', 'juta', 'ribu'];
   _unit:=1000000000000000000000000000000000::numeric;
   FOREACH _s IN ARRAY _suffix LOOP
      _val:=((_n - (_n % _unit)) / _unit)::integer;
      IF _val > 0 THEN
         IF LENGTH(_t)>0 THEN
            _t:=_t || ' ';
         END IF;
         IF _s = 'ribu' AND _val=1 THEN
            _t:= _t || 'seribu';
         ELSE
            _t:=_t || int_spell(_val) || ' ' || _s;
         END IF;
         _n:=_n - (_val * _unit);
      END IF;
      _unit:=_unit/1000;
   END LOOP;

   IF _n>0 THEN
      IF LENGTH(_t)>0 THEN
         _t:=_t || ' ';
      END IF;
      _t:=_t || int_spell(_n::integer);
   END IF;

   IF _cent>0 AND LENGTH(_t)=0 THEN
      _t:='nol';
   END IF;

   _t:=_t || ' rupiah';

   IF _cent>0 THEN
      _t:=_t || ' ';
      _t:=_t || int_spell(_cent) || ' sen';
   END IF;

   IF b_initcap THEN
      _t:=initcap(_t);
   END IF;

   RETURN _t;
END;
$BODY$;
GRANT EXECUTE ON FUNCTION cash_spell(numeric, boolean) TO public;

Advertisements

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.

Snapshot
GetActiveSnapshot(void)
{
...
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 :

...
PushActiveSnapshot(GetTransactionSnapshot());
if (conflict)
{

...
if (apply_update)
{

...
UserTableUpdateOpenIndexes(estate, newslot);
...

}

}
else
{

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

}
PopActiveSnapshot();
...

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)
RETURNS text AS
$BODY$
SELECT ($1+1)::text || '.' || '5' || (CASE WHEN $3=2::smallint THEN '1' ELSE '' END) || '.' || LPAD($2::text, 6, '0') || '/' || $4;
$BODY$
LANGUAGE sql IMMUTABLE STRICT;

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)
RETURNS text AS
$BODY$
BEGIN
RETURN (a+1)::text || '.' || '5' || (CASE WHEN c=2::smallint THEN '1' ELSE '' END) || '.' || LPAD(b::text, 6, '0') || '/' || d;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE STRICT;

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

but
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.

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

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

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

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

    ...
    dtables=NULL;
    dtablessize=dtablescount=0;

    }

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

    oldcontext=MemoryContextSwitchTo(MessageContext);
    //nspnamelen and relnamelen is the length of the name including terminating zero
    *dtablename=(char*)palloc( (nspnamelen+relnamelen) * sizeof(char) );
    MemoryContextSwitchTo(oldcontext);
    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

    oldcontext=MemoryContextSwitchTo(MessageContext);
    pfree(t);
    MemoryContextSwitchTo(oldcontext);
    return;

    }

    }
    if(dtablessize<++dtablescount) {

    dtablessize+=DTABLECHUNK;
    allocsize=dtablessize * sizeof(char*);
    oldcontext=MemoryContextSwitchTo(MessageContext);
    if(dtables==NULL)

    dtables=(char*)palloc(allocsize);

    else

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

    MemoryContextSwitchTo(oldcontext);

    }
    dtables[dtablescount-1]=t;

    }

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