When looking at our PostgreSQL support activities, we notice that people often ask about functions, stored procedures and replication. Are functions replicated? How are functions replicated? What happens to shared libraries and do we have to install an extension everywhere? These topics seems to be really interesting to many people and so I decided to write a small post about this issue to hopefully shed some light on it.

How PostgreSQL “stores” functions and procedures

To understand how PostgreSQL “replicates” functions, it is first important to understand how functions and procedures are actually stored. To demonstrate this, I have created a small function that just returns a number and quits:

test=# CREATE FUNCTION demo(int) 
$$ 	LANGUAGE 'sql';

PostgreSQL stores the code of a function in a system table. In case the function is written in an “interpreted” language, the code is just stored there in plain text. The following listing shows, how this works:

test=# SELECT proname, prolang, prokind, prosrc, probin 
		FROM 	pg_proc 
		WHERE proname = 'demo';
 proname | prolang | prokind |   prosrc    | probin 
 demo    |      14 | f       |  SELECT 1;  | 
(1 row)

What we see here is that “demo” has been written in language number 14 (which happens to be SQL), it is a “function” and not a procedure. Then there is the code of the function. Note that “probin” is empty – I will get back to that one a bit later. Now: What does that imply? If you use streaming replication (perhaps together with Patroni or some other tools) the system catalog will be replicated just like any other table. The function will therefore be sent to the remote side just like any other change. So, the function will be fully replicated and there is nothing else to do.

Dealing with compiled stored procedure languages

What happens if you want to use compiled functions? – Maybe a stored procedure written in C, PostGIS or any other extension that was not written in SQL or PL/pgSQL.
Here is what happens:

test=# CREATE EXTENSION pg_trgm;
test=# SELECT proname, prolang, prokind, prosrc, probin 
		FROM 	pg_proc 
		WHERE 	proname = 'show_trgm';
  proname  | prolang | prokind |  prosrc   |     probin      
 show_trgm |      13 | f       | show_trgm | $libdir/pg_trgm
(1 row)

To show how things work, I have enabled the pg_trgm extension that is shipped as part of PostgreSQL. What we see is that C code is not stored in prosrc. Instead, it contains the name of the function in the shared object listed in probin. If we add the extension and thus the function, the changes made to the system table are still replicated to your standbys, just like any other change.
But: YOU have to ensure that the shared object is actually installed on the other side. It is not enough to install, say, PostGIS binaries on the primary and magically hope that shared objects will be replicated too. That won’t happen and it never will.

It is important to mention: If you create a function on the primary written in C and your standby does not have the desired library, the changes in the system tables will be present – there is just no way to call these functions on the standby.

Conclusion – How are stored procedures and functions replicated?

Make sure that libraries are installed on all hosts in your cluster. Otherwise, things will break. PostgreSQL will replicate changes made to the system tables, but does not magically send libraries over the wire.

UPDATE 28.02.2023:

Related blog post available – Stored Procedures in PostgreSQL – Getting Started by Hans-Jürgen Schönig

If you want to learn about the addition of multiranges  as a range type in PostgreSQL 14, check out our post Multiranges in PostgreSQL 14.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.