Extension Development - Hooks, types, and FDWs

PostgreSQL Extension Development

This module covers building PostgreSQL extensions — from simple functions to complex custom types, operators, and index access methods. Extensions are the primary way to add functionality to PostgreSQL.

Target Audience: Extension developers, PostgreSQL contributors
Prerequisites: C programming, PostgreSQL internals basics
Source Directories: contrib/, src/include/, src/backend/
Interview Relevance: Database product development, senior PostgreSQL roles

Part 1: Extension Architecture

┌─────────────────────────────────────────────────────────────────────────────┐
│                    EXTENSION ARCHITECTURE                                    │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│   Extension = Shared library + SQL scripts + Control file                   │
│                                                                              │
│   ┌─────────────────────────────────────────────────────────────────────┐   │
│   │  myext.control          │ Extension metadata                        │   │
│   ├─────────────────────────┼───────────────────────────────────────────┤   │
│   │  myext--1.0.sql         │ Initial installation script               │   │
│   ├─────────────────────────┼───────────────────────────────────────────┤   │
│   │  myext--1.0--1.1.sql    │ Upgrade from 1.0 to 1.1                   │   │
│   ├─────────────────────────┼───────────────────────────────────────────┤   │
│   │  myext.so (Unix)        │ Compiled C code                           │   │
│   │  myext.dll (Windows)    │                                           │   │
│   └─────────────────────────┴───────────────────────────────────────────┘   │
│                                                                              │
│   Extension Loading Flow:                                                    │
│   ─────────────────────────                                                  │
│   1. CREATE EXTENSION myext;                                                 │
│   2. PostgreSQL reads myext.control                                         │
│   3. Executes myext--1.0.sql                                                │
│   4. SQL calls LOAD 'myext' (loads .so)                                     │
│   5. Calls _PG_init() in the library                                        │
│   6. Functions registered, hooks installed                                   │
│                                                                              │
│   Extension Types:                                                           │
│   ┌─────────────────┬──────────────────────────────────────────────────┐    │
│   │ Type            │ Examples                                         │    │
│   ├─────────────────┼──────────────────────────────────────────────────┤    │
│   │ Function libs   │ pgcrypto, uuid-ossp                              │    │
│   │ Data types      │ PostGIS (geometry), hstore, ltree               │    │
│   │ Index methods   │ bloom, btree_gin, btree_gist                    │    │
│   │ FDWs            │ postgres_fdw, file_fdw                          │    │
│   │ PLs             │ plpython, plv8, pljava                          │    │
│   │ Hooks           │ pg_stat_statements, auto_explain                 │    │
│   └─────────────────┴──────────────────────────────────────────────────┘    │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘

Part 2: Basic Extension Structure

2.1 The Control File

# myext.control
comment = 'My PostgreSQL Extension'
default_version = '1.0'
module_pathname = '$libdir/myext'
relocatable = true
requires = 'plpgsql'
superuser = false
trusted = true
schema = myext

Control file options:

comment: Description shown in pg_extension
default_version: Installed when version not specified
module_pathname: Path to shared library
relocatable: Can be moved to different schema
requires: Extension dependencies
superuser: Requires superuser to install
trusted: Can be installed by non-superuser (PG13+)
schema: Default schema for objects

2.2 SQL Script

-- myext--1.0.sql

-- No transaction control in extension scripts!
-- \echo Use "CREATE EXTENSION myext" to load this file. \quit

-- Create function that calls C code
CREATE FUNCTION my_add(a integer, b integer)
RETURNS integer
AS 'MODULE_PATHNAME', 'my_add'
LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;

-- Create SQL helper functions
CREATE FUNCTION my_sum(a integer, b integer, c integer)
RETURNS integer
AS $$
  SELECT my_add(my_add(a, b), c);
$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;

-- Create operator
CREATE OPERATOR +++ (
    LEFTARG = integer,
    RIGHTARG = integer,
    FUNCTION = my_add,
    COMMUTATOR = +++
);

-- Create table for extension data
CREATE TABLE myext.config (
    key text PRIMARY KEY,
    value text
);

-- Mark table for pg_dump
SELECT pg_catalog.pg_extension_config_dump('myext.config', '');

2.3 Makefile

# Makefile for myext extension

MODULES = myext
EXTENSION = myext
DATA = myext--1.0.sql myext--1.0--1.1.sql
PGFILEDESC = "myext - my custom extension"

# Add include paths
PG_CPPFLAGS = -I$(libdir)

# Add optimization and warning flags
PG_CFLAGS = -O2 -Wall -Werror

# For regression tests
REGRESS = myext_test

# Use PGXS for out-of-tree builds
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

2.4 C Source Code

/* myext.c */

#include "postgres.h"
#include "fmgr.h"
#include "utils/builtins.h"

PG_MODULE_MAGIC;  /* Required for all extensions */

/* Called when module is loaded */
void _PG_init(void);

/* Called when module is unloaded (optional) */
void _PG_fini(void);

void
_PG_init(void)
{
    /* Initialize extension */
    elog(LOG, "myext extension loaded");
}

void
_PG_fini(void)
{
    /* Cleanup (rarely used - modules usually not unloaded) */
}

/* Declare function for PostgreSQL */
PG_FUNCTION_INFO_V1(my_add);

Datum
my_add(PG_FUNCTION_ARGS)
{
    int32 a = PG_GETARG_INT32(0);
    int32 b = PG_GETARG_INT32(1);
    
    PG_RETURN_INT32(a + b);
}

Part 3: Function Manager Interface (fmgr)

3.1 Argument Handling

#include "fmgr.h"

/* Version 1 calling convention (standard) */
PG_FUNCTION_INFO_V1(my_function);

Datum
my_function(PG_FUNCTION_ARGS)
{
    /* Get arguments by position (0-indexed) */
    
    /* Integer types */
    int16 arg_int2 = PG_GETARG_INT16(0);
    int32 arg_int4 = PG_GETARG_INT32(1);
    int64 arg_int8 = PG_GETARG_INT64(2);
    
    /* Float types */
    float4 arg_float4 = PG_GETARG_FLOAT4(3);
    float8 arg_float8 = PG_GETARG_FLOAT8(4);
    
    /* Boolean */
    bool arg_bool = PG_GETARG_BOOL(5);
    
    /* Text (variable length) */
    text *arg_text = PG_GETARG_TEXT_PP(6);       /* Packed/Plain */
    char *arg_cstring = text_to_cstring(arg_text);
    
    /* Or get as detoasted copy */
    text *arg_detoasted = PG_GETARG_TEXT_P_COPY(6);
    
    /* Bytea (binary) */
    bytea *arg_bytea = PG_GETARG_BYTEA_PP(7);
    char *data = VARDATA_ANY(arg_bytea);
    int len = VARSIZE_ANY_EXHDR(arg_bytea);
    
    /* OID */
    Oid arg_oid = PG_GETARG_OID(8);
    
    /* Check for NULL */
    if (PG_ARGISNULL(0))
        PG_RETURN_NULL();
    
    /* Return values */
    PG_RETURN_INT32(42);
    PG_RETURN_FLOAT8(3.14);
    PG_RETURN_BOOL(true);
    PG_RETURN_TEXT_P(cstring_to_text("hello"));
    PG_RETURN_NULL();
}

3.2 Working with Complex Types

#include "utils/array.h"
#include "catalog/pg_type.h"

PG_FUNCTION_INFO_V1(sum_int_array);

Datum
sum_int_array(PG_FUNCTION_ARGS)
{
    ArrayType  *arr = PG_GETARG_ARRAYTYPE_P(0);
    int        *data;
    int         nelems;
    int64       sum = 0;
    
    /* Check it's a 1-D array of int4 */
    if (ARR_NDIM(arr) != 1)
        ereport(ERROR,
                (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
                 errmsg("expected 1-dimensional array")));
    
    if (ARR_ELEMTYPE(arr) != INT4OID)
        ereport(ERROR,
                (errcode(ERRCODE_DATATYPE_MISMATCH),
                 errmsg("expected integer array")));
    
    /* Get pointer to data */
    data = (int *) ARR_DATA_PTR(arr);
    nelems = ArrayGetNItems(ARR_NDIM(arr), ARR_DIMS(arr));
    
    /* Note: This ignores NULLs - real code should use array_iterate */
    for (int i = 0; i < nelems; i++)
        sum += data[i];
    
    PG_RETURN_INT64(sum);
}

/* Working with composite types (records) */
#include "funcapi.h"
#include "access/htup_details.h"

PG_FUNCTION_INFO_V1(make_pair);

Datum
make_pair(PG_FUNCTION_ARGS)
{
    int32       first = PG_GETARG_INT32(0);
    text       *second = PG_GETARG_TEXT_PP(1);
    
    TupleDesc   tupdesc;
    Datum       values[2];
    bool        nulls[2] = {false, false};
    HeapTuple   tuple;
    
    /* Build tuple descriptor for output */
    if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
        ereport(ERROR,
                (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                 errmsg("function returning record called in context "
                        "that cannot accept type record")));
    
    /* Build the tuple */
    tupdesc = BlessTupleDesc(tupdesc);
    
    values[0] = Int32GetDatum(first);
    values[1] = PointerGetDatum(second);
    
    tuple = heap_form_tuple(tupdesc, values, nulls);
    
    PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
}

3.3 Set-Returning Functions (SRFs)

#include "funcapi.h"

PG_FUNCTION_INFO_V1(generate_series_int);

Datum
generate_series_int(PG_FUNCTION_ARGS)
{
    FuncCallContext *funcctx;
    int32           start, stop, current;
    
    /* First call: initialize */
    if (SRF_IS_FIRSTCALL())
    {
        MemoryContext   oldcontext;
        
        funcctx = SRF_FIRSTCALL_INIT();
        
        /* Switch to multi-call memory context */
        oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
        
        /* Store state */
        start = PG_GETARG_INT32(0);
        stop = PG_GETARG_INT32(1);
        
        funcctx->max_calls = stop - start + 1;
        funcctx->user_fctx = (void *)(intptr_t)start;
        
        MemoryContextSwitchTo(oldcontext);
    }
    
    /* Each call */
    funcctx = SRF_PERCALL_SETUP();
    
    if (funcctx->call_cntr < funcctx->max_calls)
    {
        start = (int32)(intptr_t)funcctx->user_fctx;
        current = start + funcctx->call_cntr;
        
        SRF_RETURN_NEXT(funcctx, Int32GetDatum(current));
    }
    else
    {
        SRF_RETURN_DONE(funcctx);
    }
}

/* Usage: SELECT * FROM generate_series_int(1, 10); */

Part 4: Custom Data Types

4.1 Defining a New Type

/* complex.c - Complex number type */

#include "postgres.h"
#include "fmgr.h"
#include "libpq/pqformat.h"  /* For send/receive */

PG_MODULE_MAGIC;

/* Define the type structure */
typedef struct Complex
{
    double      real;
    double      imag;
} Complex;

/* Input function: text -> internal */
PG_FUNCTION_INFO_V1(complex_in);

Datum
complex_in(PG_FUNCTION_ARGS)
{
    char       *str = PG_GETARG_CSTRING(0);
    double      real, imag;
    Complex    *result;
    
    if (sscanf(str, "(%lf,%lf)", &real, &imag) != 2)
        ereport(ERROR,
                (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
                 errmsg("invalid input syntax for type complex: \"%s\"",
                        str)));
    
    result = (Complex *) palloc(sizeof(Complex));
    result->real = real;
    result->imag = imag;
    
    PG_RETURN_POINTER(result);
}

/* Output function: internal -> text */
PG_FUNCTION_INFO_V1(complex_out);

Datum
complex_out(PG_FUNCTION_ARGS)
{
    Complex    *complex = (Complex *) PG_GETARG_POINTER(0);
    char       *result;
    
    result = psprintf("(%g,%g)", complex->real, complex->imag);
    
    PG_RETURN_CSTRING(result);
}

/* Binary send: internal -> bytea (for COPY BINARY) */
PG_FUNCTION_INFO_V1(complex_send);

Datum
complex_send(PG_FUNCTION_ARGS)
{
    Complex    *complex = (Complex *) PG_GETARG_POINTER(0);
    StringInfoData buf;
    
    pq_begintypsend(&buf);
    pq_sendfloat8(&buf, complex->real);
    pq_sendfloat8(&buf, complex->imag);
    
    PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
}

/* Binary receive: bytea -> internal */
PG_FUNCTION_INFO_V1(complex_recv);

Datum
complex_recv(PG_FUNCTION_ARGS)
{
    StringInfo  buf = (StringInfo) PG_GETARG_POINTER(0);
    Complex    *result;
    
    result = (Complex *) palloc(sizeof(Complex));
    result->real = pq_getmsgfloat8(buf);
    result->imag = pq_getmsgfloat8(buf);
    
    PG_RETURN_POINTER(result);
}

4.2 SQL Type Definition

-- complex--1.0.sql

-- Create shell type first
CREATE TYPE complex;

-- Create I/O functions
CREATE FUNCTION complex_in(cstring)
RETURNS complex
AS 'MODULE_PATHNAME'
LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;

CREATE FUNCTION complex_out(complex)
RETURNS cstring
AS 'MODULE_PATHNAME'
LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;

CREATE FUNCTION complex_send(complex)
RETURNS bytea
AS 'MODULE_PATHNAME'
LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;

CREATE FUNCTION complex_recv(internal)
RETURNS complex
AS 'MODULE_PATHNAME'
LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;

-- Complete the type definition
CREATE TYPE complex (
    INTERNALLENGTH = 16,        -- sizeof(Complex)
    INPUT = complex_in,
    OUTPUT = complex_out,
    RECEIVE = complex_recv,
    SEND = complex_send,
    ALIGNMENT = double,
    STORAGE = plain             -- No TOAST compression
);

4.3 Operators for Custom Types

/* Operator functions for complex type */

PG_FUNCTION_INFO_V1(complex_add);

Datum
complex_add(PG_FUNCTION_ARGS)
{
    Complex *a = (Complex *) PG_GETARG_POINTER(0);
    Complex *b = (Complex *) PG_GETARG_POINTER(1);
    Complex *result;
    
    result = (Complex *) palloc(sizeof(Complex));
    result->real = a->real + b->real;
    result->imag = a->imag + b->imag;
    
    PG_RETURN_POINTER(result);
}

PG_FUNCTION_INFO_V1(complex_eq);

Datum
complex_eq(PG_FUNCTION_ARGS)
{
    Complex *a = (Complex *) PG_GETARG_POINTER(0);
    Complex *b = (Complex *) PG_GETARG_POINTER(1);
    
    PG_RETURN_BOOL(a->real == b->real && a->imag == b->imag);
}

PG_FUNCTION_INFO_V1(complex_lt);

Datum
complex_lt(PG_FUNCTION_ARGS)
{
    Complex *a = (Complex *) PG_GETARG_POINTER(0);
    Complex *b = (Complex *) PG_GETARG_POINTER(1);
    double   mag_a = sqrt(a->real * a->real + a->imag * a->imag);
    double   mag_b = sqrt(b->real * b->real + b->imag * b->imag);
    
    PG_RETURN_BOOL(mag_a < mag_b);
}

/* Comparison function for B-tree (returns -1, 0, +1) */
PG_FUNCTION_INFO_V1(complex_cmp);

Datum
complex_cmp(PG_FUNCTION_ARGS)
{
    Complex *a = (Complex *) PG_GETARG_POINTER(0);
    Complex *b = (Complex *) PG_GETARG_POINTER(1);
    double   mag_a = sqrt(a->real * a->real + a->imag * a->imag);
    double   mag_b = sqrt(b->real * b->real + b->imag * b->imag);
    
    if (mag_a < mag_b)
        PG_RETURN_INT32(-1);
    else if (mag_a > mag_b)
        PG_RETURN_INT32(1);
    else
        PG_RETURN_INT32(0);
}

-- Operator definitions

CREATE FUNCTION complex_add(complex, complex) RETURNS complex
    AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;

CREATE FUNCTION complex_eq(complex, complex) RETURNS boolean
    AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;

CREATE FUNCTION complex_lt(complex, complex) RETURNS boolean
    AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;

CREATE FUNCTION complex_cmp(complex, complex) RETURNS integer
    AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;

-- Create operators
CREATE OPERATOR + (
    LEFTARG = complex,
    RIGHTARG = complex,
    FUNCTION = complex_add,
    COMMUTATOR = +
);

CREATE OPERATOR = (
    LEFTARG = complex,
    RIGHTARG = complex,
    FUNCTION = complex_eq,
    COMMUTATOR = =,
    NEGATOR = <>,
    RESTRICT = eqsel,
    JOIN = eqjoinsel,
    HASHES,
    MERGES
);

CREATE OPERATOR < (
    LEFTARG = complex,
    RIGHTARG = complex,
    FUNCTION = complex_lt,
    COMMUTATOR = >,
    NEGATOR = >=,
    RESTRICT = scalarltsel,
    JOIN = scalarltjoinsel
);

-- Create operator class for B-tree indexing
CREATE OPERATOR CLASS complex_ops
    DEFAULT FOR TYPE complex USING btree AS
        OPERATOR 1 <,
        OPERATOR 2 <=,
        OPERATOR 3 =,
        OPERATOR 4 >=,
        OPERATOR 5 >,
        FUNCTION 1 complex_cmp(complex, complex);

Part 5: Hook System

5.1 Available Hooks

/*
 * PostgreSQL hooks allow extensions to intercept and modify behavior.
 * Hooks are function pointers - set them in _PG_init().
 */

/* Query execution hooks */
extern PGDLLIMPORT ExecutorStart_hook_type ExecutorStart_hook;
extern PGDLLIMPORT ExecutorRun_hook_type ExecutorRun_hook;
extern PGDLLIMPORT ExecutorFinish_hook_type ExecutorFinish_hook;
extern PGDLLIMPORT ExecutorEnd_hook_type ExecutorEnd_hook;

/* Planner hooks */
extern PGDLLIMPORT planner_hook_type planner_hook;
extern PGDLLIMPORT create_upper_paths_hook_type create_upper_paths_hook;
extern PGDLLIMPORT set_rel_pathlist_hook_type set_rel_pathlist_hook;

/* Parser hooks */
extern PGDLLIMPORT post_parse_analyze_hook_type post_parse_analyze_hook;

/* Utility command hooks */
extern PGDLLIMPORT ProcessUtility_hook_type ProcessUtility_hook;

/* Authentication hooks */
extern PGDLLIMPORT ClientAuthentication_hook_type ClientAuthentication_hook;

/* Object access hooks */
extern PGDLLIMPORT object_access_hook_type object_access_hook;

/* Emit log hooks */
extern PGDLLIMPORT emit_log_hook_type emit_log_hook;

/* Shared memory hooks */
extern PGDLLIMPORT shmem_startup_hook_type shmem_startup_hook;

5.2 Implementing a Hook

/* query_logger.c - Log all queries with their execution time */

#include "postgres.h"
#include "executor/executor.h"
#include "utils/guc.h"
#include <time.h>

PG_MODULE_MAGIC;

/* Save previous hooks */
static ExecutorStart_hook_type prev_ExecutorStart = NULL;
static ExecutorEnd_hook_type prev_ExecutorEnd = NULL;

/* Our state */
typedef struct {
    struct timespec start_time;
} QueryState;

/* GUC variable */
static bool query_logger_enabled = true;

void _PG_init(void);
void _PG_fini(void);

static void
my_ExecutorStart(QueryDesc *queryDesc, int eflags)
{
    QueryState *state;
    
    /* Call previous hook first */
    if (prev_ExecutorStart)
        prev_ExecutorStart(queryDesc, eflags);
    else
        standard_ExecutorStart(queryDesc, eflags);
    
    if (!query_logger_enabled)
        return;
    
    /* Record start time */
    state = (QueryState *) MemoryContextAlloc(queryDesc->estate->es_query_cxt,
                                               sizeof(QueryState));
    clock_gettime(CLOCK_MONOTONIC, &state->start_time);
    queryDesc->totaltime = (void *) state;  /* Stash our state */
}

static void
my_ExecutorEnd(QueryDesc *queryDesc)
{
    if (query_logger_enabled && queryDesc->totaltime)
    {
        QueryState *state = (QueryState *) queryDesc->totaltime;
        struct timespec end_time;
        double elapsed_ms;
        
        clock_gettime(CLOCK_MONOTONIC, &end_time);
        elapsed_ms = (end_time.tv_sec - state->start_time.tv_sec) * 1000.0 +
                     (end_time.tv_nsec - state->start_time.tv_nsec) / 1000000.0;
        
        elog(LOG, "Query executed in %.3f ms: %s",
             elapsed_ms, queryDesc->sourceText);
    }
    
    /* Call previous hook */
    if (prev_ExecutorEnd)
        prev_ExecutorEnd(queryDesc);
    else
        standard_ExecutorEnd(queryDesc);
}

void
_PG_init(void)
{
    /* Define GUC variable */
    DefineCustomBoolVariable(
        "query_logger.enabled",
        "Enable query logging",
        NULL,
        &query_logger_enabled,
        true,
        PGC_SUSET,          /* Requires superuser */
        0,
        NULL,               /* Check hook */
        NULL,               /* Assign hook */
        NULL                /* Show hook */
    );
    
    /* Install hooks, saving previous values */
    prev_ExecutorStart = ExecutorStart_hook;
    ExecutorStart_hook = my_ExecutorStart;
    
    prev_ExecutorEnd = ExecutorEnd_hook;
    ExecutorEnd_hook = my_ExecutorEnd;
    
    elog(LOG, "query_logger extension loaded");
}

void
_PG_fini(void)
{
    /* Restore previous hooks */
    ExecutorStart_hook = prev_ExecutorStart;
    ExecutorEnd_hook = prev_ExecutorEnd;
}

5.3 pg_stat_statements Pattern

/*
 * pg_stat_statements is the canonical example of hook usage.
 * It tracks execution statistics for all SQL statements.
 *
 * Key patterns:
 * 1. Uses shared memory for cross-backend statistics
 * 2. Hooks into executor and utility processing
 * 3. Normalizes queries (removes literals)
 * 4. Hash-based deduplication
 */

/* Shared memory initialization */
static void
pgss_shmem_startup(void)
{
    bool found;
    
    /* Call previous hook */
    if (prev_shmem_startup_hook)
        prev_shmem_startup_hook();
    
    /* Request shared memory */
    LWLockAcquire(AddinShmemInitLock, LW_EXCLUSIVE);
    
    pgss = ShmemInitStruct("pg_stat_statements",
                           sizeof(pgssSharedState),
                           &found);
    
    if (!found)
    {
        /* Initialize shared state */
        pgss->lock = &(GetNamedLWLockTranche("pg_stat_statements"))->lock;
        pgss->cur_median_usage = ASSUMED_MEDIAN_INIT;
        /* ... */
    }
    
    /* Create hash table in shared memory */
    pgss_hash = ShmemInitHash("pg_stat_statements hash",
                               pgss_max, pgss_max,
                               &info,
                               HASH_ELEM | HASH_BLOBS);
    
    LWLockRelease(AddinShmemInitLock);
}

/* Request shared memory at startup */
void
_PG_init(void)
{
    /* Request shared memory */
    RequestAddinShmemSpace(pgss_memsize());
    RequestNamedLWLockTranche("pg_stat_statements", 1);
    
    /* Install hooks */
    prev_shmem_startup_hook = shmem_startup_hook;
    shmem_startup_hook = pgss_shmem_startup;
    /* ... other hooks ... */
}

Part 6: Background Workers

6.1 Creating a Background Worker

/* my_worker.c - Background worker example */

#include "postgres.h"
#include "postmaster/bgworker.h"
#include "storage/ipc.h"
#include "storage/latch.h"
#include "storage/lwlock.h"
#include "storage/proc.h"
#include "storage/shmem.h"
#include "utils/guc.h"
#include "pgstat.h"

PG_MODULE_MAGIC;

void _PG_init(void);
PGDLLEXPORT void my_worker_main(Datum main_arg);

/* Configuration */
static int worker_naptime = 10;  /* seconds */

void
_PG_init(void)
{
    BackgroundWorker worker;
    
    /* Define GUC */
    DefineCustomIntVariable(
        "my_worker.naptime",
        "Time between worker iterations",
        NULL,
        &worker_naptime,
        10,
        1,
        3600,
        PGC_SIGHUP,
        GUC_UNIT_S,
        NULL, NULL, NULL
    );
    
    /* Set up background worker */
    memset(&worker, 0, sizeof(worker));
    snprintf(worker.bgw_name, BGW_MAXLEN, "my_worker");
    snprintf(worker.bgw_type, BGW_MAXLEN, "my_worker");
    worker.bgw_flags = BGWORKER_SHMEM_ACCESS |
                       BGWORKER_BACKEND_DATABASE_CONNECTION;
    worker.bgw_start_time = BgWorkerStart_RecoveryFinished;
    worker.bgw_restart_time = 10;  /* Restart after 10s if crashes */
    sprintf(worker.bgw_library_name, "my_worker");
    sprintf(worker.bgw_function_name, "my_worker_main");
    worker.bgw_main_arg = (Datum) 0;
    worker.bgw_notify_pid = 0;
    
    RegisterBackgroundWorker(&worker);
}

void
my_worker_main(Datum main_arg)
{
    /* Connect to a database */
    BackgroundWorkerInitializeConnection("postgres", NULL, 0);
    
    /* Set up signal handlers */
    pqsignal(SIGTERM, die);
    pqsignal(SIGHUP, SignalHandlerForConfigReload);
    BackgroundWorkerUnblockSignals();
    
    elog(LOG, "my_worker started");
    
    while (!got_sigterm)
    {
        int rc;
        
        /* Wait for naptime or signal */
        rc = WaitLatch(MyLatch,
                       WL_LATCH_SET | WL_TIMEOUT | WL_EXIT_ON_PM_DEATH,
                       worker_naptime * 1000L,
                       PG_WAIT_EXTENSION);
        
        ResetLatch(MyLatch);
        
        /* Check for config reload */
        if (got_sighup)
        {
            got_sighup = false;
            ProcessConfigFile(PGC_SIGHUP);
        }
        
        /* Do the work */
        CHECK_FOR_INTERRUPTS();
        
        /* Start transaction for database access */
        SetCurrentStatementStartTimestamp();
        StartTransactionCommand();
        SPI_connect();
        PushActiveSnapshot(GetTransactionSnapshot());
        
        /* Execute some SQL */
        int ret = SPI_execute("SELECT count(*) FROM pg_stat_activity", 
                              true, 0);
        if (ret == SPI_OK_SELECT && SPI_processed > 0)
        {
            bool isnull;
            int64 count = DatumGetInt64(
                SPI_getbinval(SPI_tuptable->vals[0],
                             SPI_tuptable->tupdesc,
                             1, &isnull));
            elog(LOG, "my_worker: %ld active connections", count);
        }
        
        SPI_finish();
        PopActiveSnapshot();
        CommitTransactionCommand();
    }
    
    elog(LOG, "my_worker shutting down");
    proc_exit(0);
}

6.2 Dynamic Background Workers

/* Launch workers dynamically */

PG_FUNCTION_INFO_V1(start_my_worker);

Datum
start_my_worker(PG_FUNCTION_ARGS)
{
    BackgroundWorker worker;
    BackgroundWorkerHandle *handle;
    BgwHandleStatus status;
    pid_t pid;
    
    memset(&worker, 0, sizeof(worker));
    snprintf(worker.bgw_name, BGW_MAXLEN, "dynamic_worker_%d", 
             (int) MyProcPid);
    worker.bgw_flags = BGWORKER_SHMEM_ACCESS |
                       BGWORKER_BACKEND_DATABASE_CONNECTION;
    worker.bgw_start_time = BgWorkerStart_RecoveryFinished;
    worker.bgw_restart_time = BGW_NEVER_RESTART;  /* Don't restart */
    sprintf(worker.bgw_library_name, "my_extension");
    sprintf(worker.bgw_function_name, "dynamic_worker_main");
    worker.bgw_main_arg = Int32GetDatum(PG_GETARG_INT32(0));
    worker.bgw_notify_pid = MyProcPid;
    
    if (!RegisterDynamicBackgroundWorker(&worker, &handle))
        ereport(ERROR,
                (errcode(ERRCODE_INSUFFICIENT_RESOURCES),
                 errmsg("could not register background worker")));
    
    /* Wait for it to start */
    status = WaitForBackgroundWorkerStartup(handle, &pid);
    
    if (status == BGWH_STARTED)
        PG_RETURN_INT32(pid);
    else
        ereport(ERROR,
                (errcode(ERRCODE_INSUFFICIENT_RESOURCES),
                 errmsg("could not start background worker")));
}

Part 7: Foreign Data Wrappers

7.1 FDW Architecture

/*
 * Foreign Data Wrappers allow PostgreSQL to query external data sources.
 *
 * Components:
 * 1. FDW handler - Returns function pointers
 * 2. FDW validator - Validates options
 * 3. FDW routines - Implement data access
 */

#include "foreign/fdwapi.h"
#include "foreign/foreign.h"

PG_MODULE_MAGIC;

/* Forward declarations */
Datum my_fdw_handler(PG_FUNCTION_ARGS);
Datum my_fdw_validator(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(my_fdw_handler);

Datum
my_fdw_handler(PG_FUNCTION_ARGS)
{
    FdwRoutine *fdwroutine = makeNode(FdwRoutine);
    
    /* Planning functions */
    fdwroutine->GetForeignRelSize = myGetForeignRelSize;
    fdwroutine->GetForeignPaths = myGetForeignPaths;
    fdwroutine->GetForeignPlan = myGetForeignPlan;
    
    /* Execution functions */
    fdwroutine->BeginForeignScan = myBeginForeignScan;
    fdwroutine->IterateForeignScan = myIterateForeignScan;
    fdwroutine->ReScanForeignScan = myReScanForeignScan;
    fdwroutine->EndForeignScan = myEndForeignScan;
    
    /* Optional: Explain */
    fdwroutine->ExplainForeignScan = myExplainForeignScan;
    
    /* Optional: Modify (INSERT/UPDATE/DELETE) */
    fdwroutine->AddForeignUpdateTargets = myAddForeignUpdateTargets;
    fdwroutine->PlanForeignModify = myPlanForeignModify;
    fdwroutine->BeginForeignModify = myBeginForeignModify;
    fdwroutine->ExecForeignInsert = myExecForeignInsert;
    fdwroutine->ExecForeignUpdate = myExecForeignUpdate;
    fdwroutine->ExecForeignDelete = myExecForeignDelete;
    fdwroutine->EndForeignModify = myEndForeignModify;
    
    PG_RETURN_POINTER(fdwroutine);
}

7.2 FDW Implementation

/* Simplified file_fdw-like implementation */

typedef struct MyFdwState {
    FILE       *file;
    char       *filename;
    int         current_line;
} MyFdwState;

static void
myGetForeignRelSize(PlannerInfo *root,
                    RelOptInfo *baserel,
                    Oid foreigntableid)
{
    /* Estimate the size of the foreign table */
    /* Read table options to get file path */
    baserel->rows = 1000;  /* Estimate 1000 rows */
}

static void
myGetForeignPaths(PlannerInfo *root,
                  RelOptInfo *baserel,
                  Oid foreigntableid)
{
    /* Create scan path */
    Path *path = (Path *) create_foreignscan_path(
        root, baserel,
        NULL,           /* default pathtarget */
        baserel->rows,  /* rows */
        1,              /* startup cost */
        baserel->rows,  /* total cost (1 per row) */
        NIL,            /* no pathkeys */
        NULL,           /* no required outer */
        NULL,           /* no extra plan */
        NIL);           /* no fdw_private */
    
    add_path(baserel, path);
}

static ForeignScan *
myGetForeignPlan(PlannerInfo *root,
                 RelOptInfo *baserel,
                 Oid foreigntableid,
                 ForeignPath *best_path,
                 List *tlist,
                 List *scan_clauses,
                 Plan *outer_plan)
{
    /* Build ForeignScan plan node */
    scan_clauses = extract_actual_clauses(scan_clauses, false);
    
    return make_foreignscan(tlist,
                            scan_clauses,
                            baserel->relid,
                            NIL,    /* fdw_exprs */
                            NIL,    /* fdw_private */
                            NIL,    /* fdw_scan_tlist */
                            NIL,    /* fdw_recheck_quals */
                            outer_plan);
}

static void
myBeginForeignScan(ForeignScanState *node, int eflags)
{
    MyFdwState *state;
    ForeignTable *table;
    
    if (eflags & EXEC_FLAG_EXPLAIN_ONLY)
        return;
    
    state = palloc0(sizeof(MyFdwState));
    
    /* Get table options */
    table = GetForeignTable(RelationGetRelid(node->ss.ss_currentRelation));
    state->filename = get_option_value(table->options, "filename");
    
    /* Open file */
    state->file = fopen(state->filename, "r");
    if (!state->file)
        ereport(ERROR,
                (errcode_for_file_access(),
                 errmsg("could not open file \"%s\"", state->filename)));
    
    state->current_line = 0;
    node->fdw_state = state;
}

static TupleTableSlot *
myIterateForeignScan(ForeignScanState *node)
{
    MyFdwState *state = (MyFdwState *) node->fdw_state;
    TupleTableSlot *slot = node->ss.ss_ScanTupleSlot;
    char line[1024];
    
    ExecClearTuple(slot);
    
    if (fgets(line, sizeof(line), state->file) == NULL)
        return slot;  /* EOF */
    
    state->current_line++;
    
    /* Parse line and fill slot */
    /* ... parsing logic ... */
    
    ExecStoreVirtualTuple(slot);
    return slot;
}

static void
myEndForeignScan(ForeignScanState *node)
{
    MyFdwState *state = (MyFdwState *) node->fdw_state;
    
    if (state && state->file)
        fclose(state->file);
}

Part 8: Testing Extensions

8.1 Regression Tests

sql/
  myext.sql       -- Test input
expected/
  myext.out       -- Expected output
Makefile          -- Include REGRESS = myext

-- sql/myext.sql

-- Load extension
CREATE EXTENSION myext;

-- Test functions
SELECT my_add(1, 2);
SELECT my_add(0, 0);
SELECT my_add(-5, 10);

-- Test NULL handling
SELECT my_add(NULL, 1);

-- Test operators
SELECT '(1,2)'::complex + '(3,4)'::complex;

-- Cleanup
DROP EXTENSION myext;

-- expected/myext.out

-- Load extension
CREATE EXTENSION myext;
-- Test functions
SELECT my_add(1, 2);
 my_add 
--------
      3
(1 row)

SELECT my_add(0, 0);
 my_add 
--------
      0
(1 row)

-- ... etc ...

8.2 Running Tests

# Run regression tests
make installcheck

# Run with specific database
make installcheck PGDATABASE=testdb

# Run with verbose output
make installcheck PROVE_FLAGS="--verbose"

# Use TAP tests (Perl-based)
make check

# Individual test
pg_regress --inputdir=. --bindir=/usr/bin myext

Part 9: Interview Questions

Q: How would you implement a custom aggregate function?

Answer:Aggregates require three or four functions:

/* State transition function */
PG_FUNCTION_INFO_V1(my_agg_sfunc);
Datum my_agg_sfunc(PG_FUNCTION_ARGS)
{
    int64 state = PG_GETARG_INT64(0);  /* Current state */
    int32 val = PG_GETARG_INT32(1);    /* New value */
    PG_RETURN_INT64(state + val);       /* New state */
}

/* Final function (optional) */
PG_FUNCTION_INFO_V1(my_agg_ffunc);
Datum my_agg_ffunc(PG_FUNCTION_ARGS)
{
    int64 state = PG_GETARG_INT64(0);
    PG_RETURN_FLOAT8((double) state);  /* Convert to result type */
}

/* Combine function for parallel aggregation */
PG_FUNCTION_INFO_V1(my_agg_combinefunc);
Datum my_agg_combinefunc(PG_FUNCTION_ARGS)
{
    int64 state1 = PG_GETARG_INT64(0);
    int64 state2 = PG_GETARG_INT64(1);
    PG_RETURN_INT64(state1 + state2);
}

CREATE AGGREGATE my_sum(int) (
    SFUNC = my_agg_sfunc,
    STYPE = int8,
    COMBINEFUNC = my_agg_combinefunc,
    FINALFUNC = my_agg_ffunc,
    INITCOND = '0',
    PARALLEL = SAFE
);

Q: What's the difference between IMMUTABLE, STABLE, and VOLATILE functions?

Answer:IMMUTABLE: Same inputs always produce same outputs. No side effects.

Examples: abs(), lower(), mathematical operators
Can be pre-evaluated at plan time with constant inputs
Can be used in index expressions
Safe for parallel execution

STABLE: Same results within a single query/statement.

Examples: now(), current_user, lookups in other tables
May depend on database state but won’t modify it
Cannot be pre-evaluated (values might change between queries)
Safe for index scans (evaluated once per scan)

VOLATILE: Results can change even within one query.

Examples: random(), nextval(), functions that modify data
Called for every row
Cannot be used in index expressions
May prevent certain optimizations

Important: Mislabeling volatility can cause incorrect query results. If you mark random() as IMMUTABLE, the planner might call it once and reuse the value!

Q: How do PostgreSQL extensions handle version upgrades?

Answer:Upgrade path scripts:

myext--1.0.sql          # Initial version
myext--1.0--1.1.sql     # Upgrade from 1.0 to 1.1
myext--1.1--1.2.sql     # Upgrade from 1.1 to 1.2
myext--1.0--1.2.sql     # Direct upgrade (optional)

Upgrade command:

ALTER EXTENSION myext UPDATE TO '1.2';

PostgreSQL finds the shortest path from current to target version.Best practices:

Never modify released upgrade scripts
Make upgrades idempotent where possible
Use IF NOT EXISTS for new objects
Use CREATE OR REPLACE for functions
Handle schema changes carefully (may need data migration)

Common pitfalls:

Changing function signatures (need DROP + CREATE, breaks dependencies)
Removing objects that users depend on
Not testing the upgrade path from each previous version

Next Steps

PostgreSQL Source Code

Navigate and understand the source

Catalog System

System catalog internals

Testing & Code Quality

Crash Courses

AI Engineering

Math for ML - Understanding Linear Algebra

Probability & Statistics for ML

Math for ML - Understanding Calculus

ML Mastery

Deep Learning Mastery

NestJS Mastery

Microservices Mastery

Low Level Design

OOP Concepts

SOLID Principles

Design Patterns

LLD Case Studies

System Design (HLD)

Senior Level (L5+/Staff)

HLD Case Studies

Engineering Fundamentals

DevOps & Operations

AWS Cloud

AWS Monitoring & Observability

AWS Security Services

AWS Serverless

AWS Operations

AWS Advanced

AWS Case Studies

DevOps Tools

Database Engineering

HIPAA Compliance Mastery

Operating Systems

Linux Internals

Distributed Systems

Networking Mastery

Build Your Own X

Go Lang Mastery

C Programming

Distributed System Tools

​PostgreSQL Extension Development

​Part 1: Extension Architecture

​Part 2: Basic Extension Structure

​2.1 The Control File

​2.2 SQL Script

​2.3 Makefile

​2.4 C Source Code

​Part 3: Function Manager Interface (fmgr)

​3.1 Argument Handling

​3.2 Working with Complex Types

​3.3 Set-Returning Functions (SRFs)

​Part 4: Custom Data Types

​4.1 Defining a New Type

​4.2 SQL Type Definition

​4.3 Operators for Custom Types

​Part 5: Hook System

​5.1 Available Hooks

​5.2 Implementing a Hook

​5.3 pg_stat_statements Pattern

​Part 6: Background Workers

​6.1 Creating a Background Worker

​6.2 Dynamic Background Workers

​Part 7: Foreign Data Wrappers

​7.1 FDW Architecture

​7.2 FDW Implementation

​Part 8: Testing Extensions

​8.1 Regression Tests

​8.2 Running Tests

​Part 9: Interview Questions

​Next Steps

PostgreSQL Source Code

Catalog System

PostgreSQL Extension Development

Part 1: Extension Architecture

Part 2: Basic Extension Structure

2.1 The Control File

2.2 SQL Script

2.3 Makefile

2.4 C Source Code

Part 3: Function Manager Interface (fmgr)

3.1 Argument Handling

3.2 Working with Complex Types

3.3 Set-Returning Functions (SRFs)

Part 4: Custom Data Types

4.1 Defining a New Type

4.2 SQL Type Definition

4.3 Operators for Custom Types

Part 5: Hook System

5.1 Available Hooks

5.2 Implementing a Hook

5.3 pg_stat_statements Pattern

Part 6: Background Workers

6.1 Creating a Background Worker

6.2 Dynamic Background Workers

Part 7: Foreign Data Wrappers

7.1 FDW Architecture

7.2 FDW Implementation

Part 8: Testing Extensions

8.1 Regression Tests

8.2 Running Tests

Part 9: Interview Questions

Next Steps