pgsocket is an extension for PostgreSQL server to send bytes to remote TCP/IP socket server. For the first version only single function provided for one way data send in bytearray.

This extension is compiled in Linux against PostgreSQL version 10.

Download source code from https://github.com/AbdulYadi/pgsocket. Build in Linux as usual:
$ make clean
$ make
$ make install

On successful compilation, install this extension in PostgreSQL environment
$ create extension pgsocket

Let us send bytes to –for example– host with IP address nnn.nnn.nnn.nnn, port 9090, send time out 30 seconds, messages “Hello”
$ select pgsocketsend(‘nnn.nnn.nnn.nnn’, 9090, 30, (E’\\x’ || encode(‘Hello’, ‘hex’))::bytea);

Or using address host name instead of IP address
$ select pgsocketsend(‘thesocketserver’, 9090, 30, (E’\\x’ || encode(‘Hello’, ‘hex’))::bytea);

Now, sending text from a table to remote TCP/IP socket server is easy. Assuming there is a table words:
id    txt
——————–
1    Life is easy
2    with PostgreSQL
just do:
$ select pgsocketsend(‘thesocketserver’, 9090, 30, (E’\\x’ || encode(t.txt, ‘hex’))::bytea)
from words t WHERE t.id = 1;

Advertisements

Upsert feature in PostgreSQL drove me to shorten SQL statement in my user defined functions. But somehow I overlook PostgreSQL version 10 documentation explaining trigger behavior:

“…If an INSERT contains an ON CONFLICT DO UPDATE clause, it is possible that the effects of row-level BEFORE INSERT triggers and row-level BEFORE UPDATE triggers can both be applied in a way that is apparent from the final state of the updated row, if an EXCLUDED column is referenced…”

For example, I have 2 tables:

CREATE TABLE public.sample
(
      id integer NOT NULL,
      code text NOT NULL,
      CONSTRAINT “sample: id must be unique” PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE TABLE public.audit
(
      id integer NOT NULL,
      codechanges integer NOT NULL DEFAULT 0,
      CONSTRAINT “audit: id must be unique” PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

Any record inserted into public.sample will be inserted into public.audit with codechanges 0. Any code changes to existing record in public.sample will increment codechanges in public.audit. For any reason, I implement the behavior in trigger:

CREATE OR REPLACE FUNCTION public.trig_sample() RETURNS trigger AS
$BODY$
BEGIN
      IF TG_WHEN=’BEFORE’ THEN
            IF TG_OP=’INSERT’ THEN
                  INSERT INTO audit (id) VALUES (NEW.id);
            ELSIF TG_OP=’UPDATE’ AND OLD.code IS DISTINCT FROM NEW.code THEN
                  UPDATE audit SET codechanges=codechanges+1 WHERE id = OLD.id;
            END IF;
      END IF;
      RETURN NEW;
END$BODY$
LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER trig_sample_before BEFORE INSERT OR UPDATE ON public.sample FOR EACH ROW EXECUTE PROCEDURE public.trig_sample();

And a user defined function to encapsulate the complexity:

Old Style Update/Insert

CREATE OR REPLACE FUNCTION public.sample_update(IN i_id integer, IN t_code text)
RETURNS void AS
$BODY$
BEGIN
      UPDATE public.sample SET code = t_code WHERE id = i_id;
      IF NOT FOUND THEN
            INSERT INTO public.sample (id, code) VALUES (i_id, t_code);
      END IF;
      RETURN;
END;
$BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION public.sample_update(integer, text) TO public;

Let us give it a try:
SELECT public.sample_update(1, ‘TEST-1’);
SELECT * FROM public.sample;
      id      code
      1      “TEST-1”
SELECT * FROM public.audit;
      id      codechanges
      1      0

SELECT public.sample_update(1, ‘TEST-2’);
SELECT * FROM public.sample;
      id      code
      1      “TEST-2”
SELECT * FROM public.audit;
      id      codechanges
      1      1

Go Shorter with Upsert Version

Now, I modify user defined function with Upsert feature:

CREATE OR REPLACE FUNCTION public.sample_update(IN i_id integer, IN t_code text) RETURNS void AS
$BODY$
      INSERT INTO public.sample (id, code) VALUES ($1, $2)
      ON CONFLICT (id) DO UPDATE SET code = EXCLUDED.code;
$BODY$ LANGUAGE sql VOLATILE SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION public.sample_update(integer, text) TO public;

And repeat the test:
SELECT public.sample_update(1, ‘TEST-3’);

But PostgreSQL complains:

ERROR: duplicate key value violates unique constraint “audit: id must be unique”
DETAIL: Key (id)=(1) already exists.
CONTEXT: SQL statement “INSERT INTO audit (id) VALUES (NEW.id)”
PL/pgSQL function trig_sample() line 5 at SQL statement
SQL function “sample_update” statement 1

I realize that I overlook the Overview of Trigger Behavior clause in PostgreSQL documentation. So, I modify my trigger, move the logic from BEFORE to AFTER:

CREATE OR REPLACE FUNCTION public.trig_sample() RETURNS trigger AS
$BODY$
BEGIN
      /*IF TG_WHEN=’BEFORE’ THEN*/
      IF TG_WHEN=’AFTER’ THEN
            IF TG_OP=’INSERT’ THEN
                  INSERT INTO audit (id) VALUES (NEW.id);
            ELSIF TG_OP=’UPDATE’ AND OLD.code IS DISTINCT FROM NEW.code THEN
                  UPDATE audit SET codechanges=codechanges+1 WHERE id = OLD.id;
            END IF;
      END IF;
      RETURN NEW;
END$BODY$ LANGUAGE plpgsql VOLATILE;

DROP TRIGGER trig_sample_before ON public.sample;

CREATE TRIGGER trig_sample_after AFTER INSERT OR UPDATE ON public.sample FOR EACH ROW EXECUTE PROCEDURE public.trig_sample();

Try the test once more:
SELECT public.sample_update(1, ‘TEST-3’);

And no more complains:
SELECT * FROM public.sample;
      id      code
      1      “TEST-3”
SELECT * FROM public.audit;
      id      codechanges
      1      2

A lesson learned.

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;

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