PostgreSQL Source Code  git master
worker_spi.c
Go to the documentation of this file.
1 /* -------------------------------------------------------------------------
2  *
3  * worker_spi.c
4  * Sample background worker code that demonstrates various coding
5  * patterns: establishing a database connection; starting and committing
6  * transactions; using GUC variables, and heeding SIGHUP to reread
7  * the configuration file; reporting to pg_stat_activity; using the
8  * process latch to sleep and exit in case of postmaster death.
9  *
10  * This code connects to a database, creates a schema and table, and summarizes
11  * the numbers contained therein. To see it working, insert an initial value
12  * with "total" type and some initial value; then insert some other rows with
13  * "delta" type. Delta rows will be deleted by this worker and their values
14  * aggregated into the total.
15  *
16  * Copyright (c) 2013-2020, PostgreSQL Global Development Group
17  *
18  * IDENTIFICATION
19  * src/test/modules/worker_spi/worker_spi.c
20  *
21  * -------------------------------------------------------------------------
22  */
23 #include "postgres.h"
24 
25 /* These are always necessary for a bgworker */
26 #include "miscadmin.h"
27 #include "postmaster/bgworker.h"
28 #include "postmaster/interrupt.h"
29 #include "storage/ipc.h"
30 #include "storage/latch.h"
31 #include "storage/lwlock.h"
32 #include "storage/proc.h"
33 #include "storage/shmem.h"
34 
35 /* these headers are used by this particular worker's code */
36 #include "access/xact.h"
37 #include "executor/spi.h"
38 #include "fmgr.h"
39 #include "lib/stringinfo.h"
40 #include "pgstat.h"
41 #include "utils/builtins.h"
42 #include "utils/snapmgr.h"
43 #include "tcop/utility.h"
44 
46 
48 
49 void _PG_init(void);
51 
52 /* GUC variables */
53 static int worker_spi_naptime = 10;
54 static int worker_spi_total_workers = 2;
55 static char *worker_spi_database = NULL;
56 
57 
58 typedef struct worktable
59 {
60  const char *schema;
61  const char *name;
63 
64 /*
65  * Initialize workspace for a worker process: create the schema if it doesn't
66  * already exist.
67  */
68 static void
70 {
71  int ret;
72  int ntup;
73  bool isnull;
75 
78  SPI_connect();
80  pgstat_report_activity(STATE_RUNNING, "initializing worker_spi schema");
81 
82  /* XXX could we use CREATE SCHEMA IF NOT EXISTS? */
83  initStringInfo(&buf);
84  appendStringInfo(&buf, "select count(*) from pg_namespace where nspname = '%s'",
85  table->schema);
86 
88  ret = SPI_execute(buf.data, true, 0);
89  if (ret != SPI_OK_SELECT)
90  elog(FATAL, "SPI_execute failed: error code %d", ret);
91 
92  if (SPI_processed != 1)
93  elog(FATAL, "not a singleton result");
94 
97  1, &isnull));
98  if (isnull)
99  elog(FATAL, "null result");
100 
101  if (ntup == 0)
102  {
103  debug_query_string = NULL;
104  resetStringInfo(&buf);
105  appendStringInfo(&buf,
106  "CREATE SCHEMA \"%s\" "
107  "CREATE TABLE \"%s\" ("
108  " type text CHECK (type IN ('total', 'delta')), "
109  " value integer)"
110  "CREATE UNIQUE INDEX \"%s_unique_total\" ON \"%s\" (type) "
111  "WHERE type = 'total'",
112  table->schema, table->name, table->name, table->name);
113 
114  /* set statement start time */
116 
117  debug_query_string = buf.data;
118  ret = SPI_execute(buf.data, false, 0);
119 
120  if (ret != SPI_OK_UTILITY)
121  elog(FATAL, "failed to create my schema");
122 
123  debug_query_string = NULL; /* rest is not statement-specific */
124  }
125 
126  SPI_finish();
129  debug_query_string = NULL;
131 }
132 
133 void
134 worker_spi_main(Datum main_arg)
135 {
136  int index = DatumGetInt32(main_arg);
137  worktable *table;
139  char name[20];
140 
141  table = palloc(sizeof(worktable));
142  sprintf(name, "schema%d", index);
143  table->schema = pstrdup(name);
144  table->name = pstrdup("counted");
145 
146  /* Establish signal handlers before unblocking signals. */
148  pqsignal(SIGTERM, die);
149 
150  /* We're now ready to receive signals */
152 
153  /* Connect to our database */
154  BackgroundWorkerInitializeConnection(worker_spi_database, NULL, 0);
155 
156  elog(LOG, "%s initialized with %s.%s",
157  MyBgworkerEntry->bgw_name, table->schema, table->name);
158  initialize_worker_spi(table);
159 
160  /*
161  * Quote identifiers passed to us. Note that this must be done after
162  * initialize_worker_spi, because that routine assumes the names are not
163  * quoted.
164  *
165  * Note some memory might be leaked here.
166  */
167  table->schema = quote_identifier(table->schema);
168  table->name = quote_identifier(table->name);
169 
170  initStringInfo(&buf);
171  appendStringInfo(&buf,
172  "WITH deleted AS (DELETE "
173  "FROM %s.%s "
174  "WHERE type = 'delta' RETURNING value), "
175  "total AS (SELECT coalesce(sum(value), 0) as sum "
176  "FROM deleted) "
177  "UPDATE %s.%s "
178  "SET value = %s.value + total.sum "
179  "FROM total WHERE type = 'total' "
180  "RETURNING %s.value",
181  table->schema, table->name,
182  table->schema, table->name,
183  table->name,
184  table->name);
185 
186  /*
187  * Main loop: do this until SIGTERM is received and processed by
188  * ProcessInterrupts.
189  */
190  for (;;)
191  {
192  int ret;
193 
194  /*
195  * Background workers mustn't call usleep() or any direct equivalent:
196  * instead, they may wait on their process latch, which sleeps as
197  * necessary, but is awakened if postmaster dies. That way the
198  * background process goes away immediately in an emergency.
199  */
200  (void) WaitLatch(MyLatch,
202  worker_spi_naptime * 1000L,
205 
207 
208  /*
209  * In case of a SIGHUP, just reload the configuration.
210  */
212  {
213  ConfigReloadPending = false;
215  }
216 
217  /*
218  * Start a transaction on which we can run queries. Note that each
219  * StartTransactionCommand() call should be preceded by a
220  * SetCurrentStatementStartTimestamp() call, which sets both the time
221  * for the statement we're about the run, and also the transaction
222  * start time. Also, each other query sent to SPI should probably be
223  * preceded by SetCurrentStatementStartTimestamp(), so that statement
224  * start time is always up to date.
225  *
226  * The SPI_connect() call lets us run queries through the SPI manager,
227  * and the PushActiveSnapshot() call creates an "active" snapshot
228  * which is necessary for queries to have MVCC data to work on.
229  *
230  * The pgstat_report_activity() call makes our activity visible
231  * through the pgstat views.
232  */
235  SPI_connect();
237  debug_query_string = buf.data;
239 
240  /* We can now execute queries via SPI */
241  ret = SPI_execute(buf.data, false, 0);
242 
243  if (ret != SPI_OK_UPDATE_RETURNING)
244  elog(FATAL, "cannot select from table %s.%s: error code %d",
245  table->schema, table->name, ret);
246 
247  if (SPI_processed > 0)
248  {
249  bool isnull;
250  int32 val;
251 
254  1, &isnull));
255  if (!isnull)
256  elog(LOG, "%s: count in %s.%s is now %d",
258  table->schema, table->name, val);
259  }
260 
261  /*
262  * And finish our transaction.
263  */
264  SPI_finish();
267  debug_query_string = NULL;
268  pgstat_report_stat(false);
270  }
271 
272  /* Not reachable */
273 }
274 
275 /*
276  * Entrypoint of this module.
277  *
278  * We register more than one worker process here, to demonstrate how that can
279  * be done.
280  */
281 void
282 _PG_init(void)
283 {
284  BackgroundWorker worker;
285  unsigned int i;
286 
287  /* get the configuration */
288  DefineCustomIntVariable("worker_spi.naptime",
289  "Duration between each check (in seconds).",
290  NULL,
291  &worker_spi_naptime,
292  10,
293  1,
294  INT_MAX,
295  PGC_SIGHUP,
296  0,
297  NULL,
298  NULL,
299  NULL);
300 
302  return;
303 
304  DefineCustomIntVariable("worker_spi.total_workers",
305  "Number of workers.",
306  NULL,
307  &worker_spi_total_workers,
308  2,
309  1,
310  100,
312  0,
313  NULL,
314  NULL,
315  NULL);
316 
317  DefineCustomStringVariable("worker_spi.database",
318  "Database to connect to.",
319  NULL,
320  &worker_spi_database,
321  "postgres",
323  0,
324  NULL, NULL, NULL);
325 
326  /* set up common data for all our workers */
327  memset(&worker, 0, sizeof(worker));
332  sprintf(worker.bgw_library_name, "worker_spi");
333  sprintf(worker.bgw_function_name, "worker_spi_main");
334  worker.bgw_notify_pid = 0;
335 
336  /*
337  * Now fill in worker-specific data, and do the actual registrations.
338  */
339  for (i = 1; i <= worker_spi_total_workers; i++)
340  {
341  snprintf(worker.bgw_name, BGW_MAXLEN, "worker_spi worker %d", i);
342  snprintf(worker.bgw_type, BGW_MAXLEN, "worker_spi");
343  worker.bgw_main_arg = Int32GetDatum(i);
344 
345  RegisterBackgroundWorker(&worker);
346  }
347 }
348 
349 /*
350  * Dynamically launch an SPI worker.
351  */
352 Datum
354 {
355  int32 i = PG_GETARG_INT32(0);
356  BackgroundWorker worker;
357  BackgroundWorkerHandle *handle;
359  pid_t pid;
360 
361  memset(&worker, 0, sizeof(worker));
366  sprintf(worker.bgw_library_name, "worker_spi");
367  sprintf(worker.bgw_function_name, "worker_spi_main");
368  snprintf(worker.bgw_name, BGW_MAXLEN, "worker_spi worker %d", i);
369  snprintf(worker.bgw_type, BGW_MAXLEN, "worker_spi");
370  worker.bgw_main_arg = Int32GetDatum(i);
371  /* set bgw_notify_pid so that we can use WaitForBackgroundWorkerStartup */
372  worker.bgw_notify_pid = MyProcPid;
373 
374  if (!RegisterDynamicBackgroundWorker(&worker, &handle))
375  PG_RETURN_NULL();
376 
377  status = WaitForBackgroundWorkerStartup(handle, &pid);
378 
379  if (status == BGWH_STOPPED)
380  ereport(ERROR,
381  (errcode(ERRCODE_INSUFFICIENT_RESOURCES),
382  errmsg("could not start background process"),
383  errhint("More details may be available in the server log.")));
384  if (status == BGWH_POSTMASTER_DIED)
385  ereport(ERROR,
386  (errcode(ERRCODE_INSUFFICIENT_RESOURCES),
387  errmsg("cannot start background processes without postmaster"),
388  errhint("Kill all remaining database processes and restart the database.")));
389  Assert(status == BGWH_STARTED);
390 
391  PG_RETURN_INT32(pid);
392 }
void DefineCustomIntVariable(const char *name, const char *short_desc, const char *long_desc, int *valueAddr, int bootValue, int minValue, int maxValue, GucContext context, int flags, GucIntCheckHook check_hook, GucIntAssignHook assign_hook, GucShowHook show_hook)
Definition: guc.c:8920
Datum worker_spi_launch(PG_FUNCTION_ARGS)
Definition: worker_spi.c:353
#define PG_GETARG_INT32(n)
Definition: fmgr.h:269
#define pg_attribute_noreturn()
Definition: c.h:167
BgwHandleStatus WaitForBackgroundWorkerStartup(BackgroundWorkerHandle *handle, pid_t *pidp)
Definition: bgworker.c:1082
void RegisterBackgroundWorker(BackgroundWorker *worker)
Definition: bgworker.c:834
int MyProcPid
Definition: globals.c:40
int errhint(const char *fmt,...)
Definition: elog.c:1149
#define WL_TIMEOUT
Definition: latch.h:127
void ProcessConfigFile(GucContext context)
const char * quote_identifier(const char *ident)
Definition: ruleutils.c:10934
bool process_shared_preload_libraries_in_progress
Definition: miscinit.c:1596
#define DatumGetInt32(X)
Definition: postgres.h:472
int SPI_connect(void)
Definition: spi.c:90
void pgstat_report_activity(BackendState state, const char *cmd_str)
Definition: pgstat.c:3278
PG_MODULE_MAGIC
Definition: worker_spi.c:45
void worker_spi_main(Datum)
Definition: worker_spi.c:50
void SignalHandlerForConfigReload(SIGNAL_ARGS)
Definition: interrupt.c:56
char * pstrdup(const char *in)
Definition: mcxt.c:1187
void CommitTransactionCommand(void)
Definition: xact.c:2948
int SPI_finish(void)
Definition: spi.c:177
#define PG_RETURN_INT32(x)
Definition: fmgr.h:354
SPITupleTable * SPI_tuptable
Definition: spi.c:46
int bgw_restart_time
Definition: bgworker.h:94
int errcode(int sqlerrcode)
Definition: elog.c:691
BackgroundWorker * MyBgworkerEntry
Definition: postmaster.c:194
worktable
Definition: worker_spi.c:62
void PopActiveSnapshot(void)
Definition: snapmgr.c:759
#define LOG
Definition: elog.h:26
void _PG_init(void)
Definition: worker_spi.c:282
HeapTuple * vals
Definition: spi.h:26
#define BGWORKER_SHMEM_ACCESS
Definition: bgworker.h:52
Snapshot GetTransactionSnapshot(void)
Definition: snapmgr.c:250
char bgw_function_name[BGW_MAXLEN]
Definition: bgworker.h:96
uint64 SPI_processed
Definition: spi.c:45
void ResetLatch(Latch *latch)
Definition: latch.c:588
signed int int32
Definition: c.h:417
int WaitLatch(Latch *latch, int wakeEvents, long timeout, uint32 wait_event_info)
Definition: latch.c:390
Definition: type.h:89
#define sprintf
Definition: port.h:217
Datum bgw_main_arg
Definition: bgworker.h:97
void appendStringInfo(StringInfo str, const char *fmt,...)
Definition: stringinfo.c:91
#define ERROR
Definition: elog.h:43
#define FATAL
Definition: elog.h:52
Datum SPI_getbinval(HeapTuple tuple, TupleDesc tupdesc, int fnumber, bool *isnull)
Definition: spi.c:1101
#define DatumGetInt64(X)
Definition: postgres.h:607
static char * buf
Definition: pg_test_fsync.c:68
void PushActiveSnapshot(Snapshot snap)
Definition: snapmgr.c:680
PG_FUNCTION_INFO_V1(worker_spi_launch)
#define SPI_OK_UTILITY
Definition: spi.h:56
#define SIGHUP
Definition: win32_port.h:159
#define SPI_OK_UPDATE_RETURNING
Definition: spi.h:65
#define BGW_NEVER_RESTART
Definition: bgworker.h:84
void resetStringInfo(StringInfo str)
Definition: stringinfo.c:75
BgwHandleStatus
Definition: bgworker.h:102
Definition: guc.h:72
void initStringInfo(StringInfo str)
Definition: stringinfo.c:59
const char * debug_query_string
Definition: postgres.c:88
static void initialize_worker_spi(worktable *table)
Definition: worker_spi.c:69
void BackgroundWorkerInitializeConnection(const char *dbname, const char *username, uint32 flags)
Definition: postmaster.c:5683
void DefineCustomStringVariable(const char *name, const char *short_desc, const char *long_desc, char **valueAddr, const char *bootValue, GucContext context, int flags, GucStringCheckHook check_hook, GucStringAssignHook assign_hook, GucShowHook show_hook)
Definition: guc.c:8980
uintptr_t Datum
Definition: postgres.h:367
#define PG_WAIT_EXTENSION
Definition: pgstat.h:860
TupleDesc tupdesc
Definition: spi.h:25
#define SPI_OK_SELECT
Definition: spi.h:57
#define ereport(elevel,...)
Definition: elog.h:155
pqsigfunc pqsignal(int signum, pqsigfunc handler)
Definition: signal.c:170
char bgw_name[BGW_MAXLEN]
Definition: bgworker.h:90
#define Assert(condition)
Definition: c.h:800
#define BGWORKER_BACKEND_DATABASE_CONNECTION
Definition: bgworker.h:59
void StartTransactionCommand(void)
Definition: xact.c:2847
#define BGW_MAXLEN
Definition: bgworker.h:85
BgWorkerStartTime bgw_start_time
Definition: bgworker.h:93
bool RegisterDynamicBackgroundWorker(BackgroundWorker *worker, BackgroundWorkerHandle **handle)
Definition: bgworker.c:918
const char * name
Definition: encode.c:561
#define Int32GetDatum(X)
Definition: postgres.h:479
char bgw_type[BGW_MAXLEN]
Definition: bgworker.h:91
void SetCurrentStatementStartTimestamp(void)
Definition: xact.c:833
void * palloc(Size size)
Definition: mcxt.c:950
int errmsg(const char *fmt,...)
Definition: elog.c:902
pid_t bgw_notify_pid
Definition: bgworker.h:99
#define elog(elevel,...)
Definition: elog.h:228
volatile sig_atomic_t ConfigReloadPending
Definition: interrupt.c:26
int i
struct Latch * MyLatch
Definition: globals.c:54
#define PG_FUNCTION_ARGS
Definition: fmgr.h:193
#define CHECK_FOR_INTERRUPTS()
Definition: miscadmin.h:99
static void static void status(const char *fmt,...) pg_attribute_printf(1
Definition: pg_regress.c:227
char bgw_library_name[BGW_MAXLEN]
Definition: bgworker.h:95
#define snprintf
Definition: port.h:215
#define WL_LATCH_SET
Definition: latch.h:124
long val
Definition: informix.c:664
#define die(msg)
Definition: pg_test_fsync.c:97
#define PG_RETURN_NULL()
Definition: fmgr.h:345
#define WL_EXIT_ON_PM_DEATH
Definition: latch.h:129
int SPI_execute(const char *src, bool read_only, long tcount)
Definition: spi.c:497
void BackgroundWorkerUnblockSignals(void)
Definition: postmaster.c:5735
void pgstat_report_stat(bool force)
Definition: pgstat.c:850