Retrospection : pg_stat_statements

Let’s do a retrospection on what we have learned so far with the famous pg_stat_statements extension

STEP 1: Create extension

postgres=# create extension pg_stat_statements;
CREATE EXTENSION
postgres=#

Extension got created, which means

• it has 2 mandatory files called .control, .SQL under share/extension
• if it is using the C program, it must have .so file under lib/ directory

STEP 2: use the extension

postgres=# select * from pg_stat_statements;
ERROR: pg_stat_statements must be loaded via shared_preload_libraries
postgres=#

I got an error because it needs to be preloaded during system startup.

[postgres@prim ~]$ cat /u01/pgsql/data/postgresql.conf | grep shared_preload_libraries
shared_preload_libraries = ‘pg_stat_statements,repmgr’ # (change requires restart)
[postgres@prim ~]$

After restarting

Check the backend files on this extension

SQL and Control file

[postgres@prim extension]$ ls -lrt pg_stat_statements*
…
-rw-r–r–. 1 root root 191 Feb 12 02:47 pg_stat_statements.control
-rw-r–r–. 1 root root 376 Feb 12 02:47 pg_stat_statements–1.5–1.6.sql
-rw-r–r–. 1 root root 1427 Feb 12 02:47 pg_stat_statements–1.4.sql

….

Here, 1.4 is the first version and latest is 1.6.

A code snipped of pg_stat_statements–1.4.sql is

— Register functions.
CREATE FUNCTION pg_stat_statements_reset()
RETURNS void
AS ‘MODULE_PATHNAME’
LANGUAGE C PARALLEL SAFE;

which means it used C language code, i.e it must have .so file under /lib.

[postgres@prim lib]$ pwd
/usr/pgsql-10/lib

[postgres@prim lib]$ ls -lrt pg_stat*
-rwxr-xr-x. 1 root root 33928 Feb 12 02:47 pg_stat_statements.so
[postgres@prim lib]$

As it is a shared object file, we cannot see the contents of .so but the code snippet of

pg_stat_statements.c is something like

{
/*
* In order to create our shared memory area, we have to be loaded via
* shared_preload_libraries. If not, fall out without hooking into any of
* the main system. 
*/
if (!process_shared_preload_libraries_in_progress)
return;