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) RETURNS int AS $$ SELECT 1; $$ LANGUAGE 'sql'; CREATE FUNCTION
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; CREATE EXTENSION 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.
Related blog post available – Stored Procedures in PostgreSQL – Getting Started by Hans-Jürgen Schönig