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.
Table of Contents
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:
|
1 2 3 4 5 6 |
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:
|
1 2 3 4 5 6 7 |
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.
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:
|
1 2 3 4 5 6 7 8 9 |
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.
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
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 Facebook or LinkedIn.
+43 (0) 2622 93022-0
office@cybertec.at
You are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Interesting. Does this also mean that the slave cannot have its own library configuration, since it is replicated from the master? I mean, what if the library is installed in $libdir/slave/pg_trgm?
Do you believe that, having binary compatibilies and a good network, sharing $libdir could help mitigate the problem?