I have PostgreSQL 12.2 in my CentOS-7 laptop and create a table with single column:
create table public.test (mark integer not null);

Populate with ten million random numbers:
insert into public.test select (random() * 100)::integer from generate_series(1,10000000);

Update statistics:
analyze public.test;

Parallel query configuration setting is default:
show max_parallel_workers_per_gather;
2

Play with Query

Think of building histogram with 4 bins (25, 50, 75 and 100), let us check query performance using plain SQL versus PLPGSQL and SQL function (query structure here is just an example and you may have better one).

Plain SQL

select 25 as bin, count(*) as freq, avg(mark) from public.test where mark between 0 and 25
union
select 50, count(*), avg(mark) from public.test where mark between 26 and 50
union
select 75, count(*), avg(mark) from public.test where mark between 51 and 75
union
select 100, count(*), avg(mark) from public.test where mark between 76 and 100
order by 1;
bin | freq | avg
-----+---------+---------------------
25 | 2548432 | 12.7408167061157606
50 | 2501760 | 37.9986689370683039
75 | 2496096 | 63.0057958508006102
100 | 2453712 | 87.7504262928982700
(4 rows)

Time: 1917.847 ms (00:01.918)

Runs for 1.9 seconds. Explain the query plan in graphics and see that parallel query works:

PLPGSQL Function

create or replace function public.markstat_plpgsql(OUT bin integer, OUT freq bigint, OUT avg numeric)
returns setof record as
$body$
begin
return query
select 25 as bin, count(*) as freq, avg(mark) from public.test where mark between 0 and 25
union
select 50, count(*), avg(mark) from public.test where mark between 26 and 50
union
select 75, count(*), avg(mark) from public.test where mark between 51 and 75
union
select 100, count(*), avg(mark) from public.test where mark between 76 and 100
order by 1;
end;
$body$
language plpgsql stable;

select * from public.markstat_plpgsql();
bin | freq | avg
-----+---------+---------------------
25 | 2548432 | 12.7408167061157606
50 | 2501760 | 37.9986689370683039
75 | 2496096 | 63.0057958508006102
100 | 2453712 | 87.7504262928982700
(4 rows)

Time: 3444.977 ms (00:03.445)

The same result with longer run 3.4 seconds (1.8 times slower than plain SQL version).

SQL Function

create or replace function public.markstat_sql(OUT bin integer, OUT freq bigint, OUT avg numeric)
returns setof record as
$body$
select 25 as bin, count(*) as freq, avg(mark) from public.test where mark between 0 and 25
union
select 50, count(*), avg(mark) from public.test where mark between 26 and 50
union
select 75, count(*), avg(mark) from public.test where mark between 51 and 75
union
select 100, count(*), avg(mark) from public.test where mark between 76 and 100
order by 1;
$body$
language sql stable;

select * from public.markstat_sql();
bin | freq | avg
-----+---------+---------------------
25 | 2548432 | 12.7408167061157606
50 | 2501760 | 37.9986689370683039
75 | 2496096 | 63.0057958508006102
100 | 2453712 | 87.7504262928982700
(4 rows)

Time: 3448.760 ms (00:03.449)

3.4 seconds, almost as slow as PLPGSQL version.

Discussion

Plain SQL: 1.9 seconds
PLPGSQL function: 3.4 seconds
SQL function: 3.4 seconds

Why query runs in function is slower? CPU usage graph below clears up the situation.

With plain SQL (1), all of the CPU works indicating parallel query. While PLPGSQL (2) and SQL (3) functions only run on single CPU, not a parallel query.

Not much I can do with SQL function since the query structure is already exactly the same as the plain one. It looks like I can do more with PLPGSQL function. How if instead of ‘RETURN QUERY’ the heavy query directly, the result set is saved into temporary array in memory then export it out once finished.

Create composite type for temporary array:
create type public.stat AS (bin integer, freq bigint, avg numeric);

Create second PLPGSQL function version, call it public.markstat_plpgsql2.
create or replace function public.markstat_plpgsql2(OUT bin integer, OUT freq bigint, OUT avg numeric)
returns setof record as
$body$
declare
_stats public.stat[];
begin
select array_agg(t) into _stats from (
select 25 as bin, count(*) as freq, avg(mark) from public.test where mark between 0 and 25
union
select 50, count(*), avg(mark) from public.test where mark between 26 and 50
union
select 75, count(*), avg(mark) from public.test where mark between 51 and 75
union
select 100, count(*), avg(mark) from public.test where mark between 76 and 100
) t;
return query select s.bin, s.freq, s.avg from unnest(_stats) s order by s.bin;
end;
$body$
language plpgsql stable;

select * from public.markstat_plpgsql2();
bin | freq | avg
-----+---------+---------------------
25 | 2548432 | 12.7408167061157606
50 | 2501760 | 37.9986689370683039
75 | 2496096 | 63.0057958508006102
100 | 2453712 | 87.7504262928982700
(4 rows)

Time: 2063.029 ms (00:02.063)

Now, it is about 2.1 seconds, close to the fastest plain SQL 1.9 seconds.

Please take a look on CPU usage graph below:

Number 1, 2 and 3 are plain SQL, PLPGSQL function and SQL function, respectively. While number 4 is for my newly crafted PLPGSQL function called public.markstat_plpgsql2, runs on multiple CPU, parallel query is back.

pgstrtranslate extends it with multi-character replacement. It takes 4 arguments and returning a text.
CREATE OR REPLACE FUNCTION public.pgstrtranslate(
    fullsearch boolean,
    t text,
    search text[],
    replacement text[])
  RETURNS text AS
'$libdir/pgstrtranslate', 'pgstrtranslate'
    LANGUAGE c IMMUTABLE STRICT;

How it works

Non-fullsearch replacement:

select pgstrtranslate(false, --non-fullsearch
    'abcdefghijkl', --original string
    array['ab', 'efg', '2cd']::text[], --array of searchs
    array['012', '3', '78']::text[]); --array of replacement
translate
--------------
012cd3hijkl
‘abcdefghijkl’ -> ‘012cd3hijkl’
Note that ‘2cd’ does not match original string.

Fullsearch replacement:

select pgstrtranslate(true, --fullsearch
    'abcdefghijkl', --original string
    array['ab', 'efg', '2cd']::text[], --array of searchs
    array['012', '3', '78']::text[]); --array of replacement
translate
--------------
01783hijkl
Replace ‘ab’ with ‘012’: ‘abcdefghijkl’ -> ‘012cdefghijkl’
Replace ‘efg’ with ‘3’: ‘012cdefghijkl’ -> ‘012cd3hijkl’
Replace ‘2cd’ with ’78’: ’012cd3hijkl’ -> ’01783hijkl’

How to install

1. Clone or download source code from https://github.com/AbdulYadi/pgstrtranslate.git. Extract it.
2. If necessary, modify PG_CONFIG path according to your specific PostgreSQL installation location.
3. Build as usual:

$ make
$ make install

4. On successful compilation, install this extension in PostgreSQL environment:

$ create extension pgstrtranslate;

The more efficient approach is by implementing validation in user defined function. Superuser prevents regular users from issuing INSERT/UPDATE/DELETE directly to table by revoking their privileges. They must invoke provided function to perform those tasks.

But, if you are the function programmer or superuser, the biggest enemy is yourself. You can easily bypass the function, modify table directly and break validation rule that you have set.

I have written additional functionality in PostgreSQL version 12.1 version backend to prevent regular user and even superuser from modifying directly table created with “private_modify” option. He or she should call SQL, PLPGSQL or other SPI-based function to do that.

CREATE TABLE public.regular (id integer NOT NULL, label text NOT NULL);
CREATE TABLE public.test (id integer NOT NULL, label text NOT NULL)
  WITH(private_modify=true);


Check table options in system table:

SELECT n.nspname, relname, reloptions
FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname in('regular','test') AND n.nspname='public';

nspname | relname | reloptions
---------+---------+-----------------------
public | regular |
public | test | {private_modify=true}


Insert into public.regular directly and it works as usual.

INSERT INTO public.regular VALUES (1, 'abc');

Now, insert into public.test directly and you will have error message.

INSERT INTO public.test VALUES (1, 'abc');
ERROR: do not modify table with "private modify" option outside SQL, PLPGSQL or other SPI-based function

Anonymous block does not work too

DO $$
BEGIN
    INSERT INTO public.test VALUES (1, 'abc');
END$$;
ERROR: do not modify table with "private modify" option outside SQL, PLPGSQL or other SPI-based function

Update or delete will have the same error message.

So let us create function in SQL language and PLPGSQL language:

CREATE OR REPLACE FUNCTION public.testinsert_sql(i_id integer, t_label text)
RETURNS void AS
$BODY$
    /*do necessary validation*/
    INSERT INTO public.test (id, "label") VALUES ($1, $2);
$BODY$
LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION public.testinsert_plpgsql(i_id integer, t_label text)
RETURNS void AS
$BODY$
BEGIN
    /*do necessary validation*/
    INSERT INTO public.test (id, "label") VALUES ($1, $2);
RETURN;
END;$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;


And use those functions for table modifications without error:

SELECT public.testinsert_sql(1, 'abc');
SELECT public.testinsert_plpgsql(2, 'def');


Check the result:

SELECT * FROM public.test;
id | label
----+-------
1 | abc
2 | def


But, to not interrupt replication agents which set session_replication_role to ‘replica’ prior to table modifications (e.g. Slony and Bucardo), such restrictions should be relaxed:

SET session_replication_role TO 'replica';
INSERT INTO public.test VALUES (3, 'ghi');


How to apply patch and build PostgreSQL

1. Clone or download patch from https://github.com/AbdulYadi/postgresql-private. Extract as necessary.
2. Download PostgreSQL version 12.1 from https://ftp.postgresql.org/pub/source/v12.1/postgresql-12.1.tar.bz2 or https://ftp.postgresql.org/pub/source/v12.1/postgresql-12.1.tar.gz. Extract to your preferred directory.
3. Apply patch: change directory to the extracted PostgreSQL source code root and run patch -p0 < path-to-downloaded-patch-file.
4. Build as instructed in PostgreSQL’s INSTALL file.

Warning

This patch is still an experiment so do not put into production server.

Example

A zoo can only breed maximum of 5 elephants (‘e’) and 3 tigers (‘t’).

CREATE TABLE public.zoo
(
    id serial NOT NULL,
    animal char NOT NULL,
    "name" text NOT NULL,
    CONSTRAINT zoo_pkey PRIMARY KEY (id),
    CONSTRAINT animal_kind CHECK (animal IN ('e','t'))
)
WITH (private_modify=true);


Animal kind can be simply validated by CHECK constraint. Other then ‘e’ or ‘t’ will be rejected. How about the maximum number constrained without additional book-keeping table. Option “private_modify” come to the rescue. Regular user and superuser can no longer directly INSERT/UPDATE/DELETE to public.zoo table. Following user defined function is needed:

CREATE OR REPLACE FUNCTION public.zoo_insert(c_animal char, t_name text)
  RETURNS void AS
$BODY$
DECLARE
    _count integer;
BEGIN
    PERFORM * FROM public.zoo WHERE animal = c_animal FOR UPDATE;--prevent race condition
    SELECT COUNT(*) INTO _count FROM public.zoo WHERE animal = c_animal;
    IF c_animal = 'e' AND _count >= 5 THEN
        RAISE EXCEPTION '5 elephants maximum';
    ELSIF c_animal = 't' AND _count >= 3 THEN
        RAISE EXCEPTION '3 tigers maximum';
    END IF;
    INSERT INTO public.zoo (animal, "name") VALUES (c_animal, t_name);
    RETURN;
END;$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION public.zoo_insert(char, text) TO public;


Run following command one row at a time to add elephant. Notice that “ERROR: 5 elephants maximum” reported on sixth row:

SELECT public.zoo_insert('e', 'el-1');
SELECT public.zoo_insert('e', 'el-2');
SELECT public.zoo_insert('e', 'el-3');
SELECT public.zoo_insert('e', 'el-4');
SELECT public.zoo_insert('e', 'el-5');
SELECT public.zoo_insert('e', 'el-6');/*failing row*/


The same thing for tiger. Fourth row will fail with “ERROR: 3 tigers maximum”:

SELECT public.zoo_insert('t', 'ti-1');
SELECT public.zoo_insert('t', 'ti-2');
SELECT public.zoo_insert('t', 'ti-3');
SELECT public.zoo_insert('t', 'ti-4');/*failing row*/


1. No more relhasoids in pg_class.
2. No more cache_value, is_cycled, is_called in sequence object (since PostgreSQL 11).
3. No more adsrc in pg_attrdef, it should be calculated as pg_catalog.pg_get_expr(adbin, adrelid) instead.

This project adds 2 functionality to QR code generator from repository https://github.com/swex/QR-Image-embedded:

1. In-memory monochrome bitmap construction (1 bit per pixel).
2. Wrap the whole package as PostgreSQL extension.

This project has been compiled successfully in Linux against PostgreSQL version 11.
$ make clean
$ make
$ make install

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

Function pgqr has 4 parameters:

1. t text: text to be encoded.
2. correction_level integer: 0 to 3.
3. model_number integer: 0 to 2.
4. scale integer: pixels for each dot.

Let us create a QR Code
$ select pgqr(‘QR Code with PostgreSQL’, 0, 0, 4);
The output is a monochrome bitmap ready for display.

With PostgreSQL release 11.0, here is my patch file corresponding catalog table changes: pgadmin3-patch-text-file

First, clone pgAdmin3 project: clone git clone https://bitbucket.org/openscg/pgadmin3-lts.git

Then, apply the patch: patch -p0 -i [patch-text-file]

Oldies but goldies.

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;

“…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:

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.

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;

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

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

}
PopActiveSnapshot();
...

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