From 04e1cbe49268e22886e2b30fe412fef0cb9dcc65 Mon Sep 17 00:00:00 2001 From: Craig Ringer Date: Mon, 16 Feb 2015 18:37:19 +1300 Subject: [PATCH 4/4] Document SPI use from bgworkers in greater detail --- doc/src/sgml/bgworker.sgml | 164 +++++++++++++++++++++++++++-------- doc/src/sgml/contrib.sgml | 1 + doc/src/sgml/example-worker-spi.sgml | 26 ++++++ doc/src/sgml/filelist.sgml | 1 + doc/src/sgml/spi.sgml | 151 +++++++++++++++++++++++++++++++- doc/src/sgml/xfunc.sgml | 54 ++++++++++++ 6 files changed, 357 insertions(+), 40 deletions(-) create mode 100644 doc/src/sgml/example-worker-spi.sgml diff --git a/doc/src/sgml/bgworker.sgml b/doc/src/sgml/bgworker.sgml index ef28f72..d0e7dc0 100644 --- a/doc/src/sgml/bgworker.sgml +++ b/doc/src/sgml/bgworker.sgml @@ -34,18 +34,21 @@ PostgreSQL is started by including the module name in shared_preload_libraries. A module wishing to run a background worker can register it by calling + RegisterBackgroundWorker RegisterBackgroundWorker(BackgroundWorker *worker) from its _PG_init(). Background workers can also be started after the system is up and running by calling the function + RegisterDynamicBackgroundWorker RegisterDynamicBackgroundWorker(BackgroundWorker *worker, BackgroundWorkerHandle **handle). Unlike RegisterBackgroundWorker, which can only be called from within the postmaster, RegisterDynamicBackgroundWorker must be - called from a regular backend. + called from a regular backend (which might be another background worker). - The structure BackgroundWorker is defined thus: + The structure BackgroundWorker + BackgroundWorker is defined thus: typedef void (*bgworker_main_type)(Datum main_arg); typedef struct BackgroundWorker @@ -64,22 +67,46 @@ typedef struct BackgroundWorker - bgw_name is a string to be used in log messages, process + bgw_namebgw_name is a string to be used in log messages, process listings and similar contexts. bgw_flags is a bitwise-or'd bit mask indicating the - capabilities that the module wants. Possible values are - BGWORKER_SHMEM_ACCESS (requesting shared memory access) - and BGWORKER_BACKEND_DATABASE_CONNECTION (requesting the - ability to establish a database connection, through which it can later run - transactions and queries). A background worker using - BGWORKER_BACKEND_DATABASE_CONNECTION to connect to - a database must also attach shared memory using - BGWORKER_SHMEM_ACCESS, or worker start-up will fail. + capabilities that the module wants. Possible values are: + + + + BGWORKER_SHMEM_ACCESS + + + BGWORKER_SHMEM_ACCESS + Requests shared memory access. This flag is also required to use + lightweight locks (LWlocks). For more information on shared memory access + from bgworkers and extensions see . + + + + + + BGWORKER_BACKEND_DATABASE_CONNECTION + + + BGWORKER_BACKEND_DATABASE_CONNECTION + Requests the ability to establish a database connection through which it + can later run transactions and queries. A background worker using + BGWORKER_BACKEND_DATABASE_CONNECTION to connect to a + database must also attach shared memory using + BGWORKER_SHMEM_ACCESS, or worker start-up will fail. + + + + + + + bgw_start_time is the server state during which postgres should start the process; it can be one of @@ -105,58 +132,113 @@ typedef struct BackgroundWorker - bgw_main is a pointer to the function to run when - the process is started. This function must take a single argument of type - Datum and return void. - bgw_main_arg will be passed to it as its only - argument. Note that the global variable MyBgworkerEntry - points to a copy of the BackgroundWorker structure - passed at registration time. bgw_main may be - NULL; in that case, bgw_library_name and - bgw_function_name will be used to determine - the entry point. This is useful for background workers launched after - postmaster startup, where the postmaster does not have the requisite - library loaded. + bgw_main specifies the function to run when the + background worker is started. If non-NULL, + bgw_main will take precedence over + bgw_library_name and + bgw_function_name. + + + Use of this field is deprecated. It should be set to + NULL then bgw_library_name + and bgw_function_name should be used instead. + + + Specifying the background worker entry point using a function pointer will + not work correctly on Windows (or with EXEC_BACKEND + defined). It may also malfunction when used to point to a function in a + dynamically loaded shared library used by a dynamic background worker. + + bgw_library_name is the name of a library in which the initial entry point for the background worker should be sought. - It is ignored unless bgw_main is NULL. - But if bgw_main is NULL, then the named library - will be dynamically loaded by the worker process and - bgw_function_name will be used to identify - the function to be called. + The named library will be dynamically loaded by the worker process and + bgw_function_name will be used to identify the + function to be called. bgw_library_name is + ignored if bgw_main is non-NULL. bgw_function_name is the name of a function in a dynamically loaded library which should be used as the initial entry point - for a new background worker. It is ignored unless + for a new background worker. + + This function must take a single argument of type Datum and return + void. bgw_main_arg will be passed to it + as its only argument. It signature is: + + + /* Declaration */ + PGDLLEXPORT extern void bgw_main_func_name(Datum main_arg); + + /* Definition */ + void bgw_main_func_name(Datum main_arg) + { + /* ... */ + } + + + Note that the global variable MyBgworkerEntry + points to a copy of the BackgroundWorker structure + passed at registration time. bgw_main should be + NULL; in that case, bgw_library_name and + bgw_function_name will be used to determine + the entry point. + + bgw_function_name is ignored unless bgw_main is NULL. + bgw_main_arg is the Datum argument + to the background worker main function. It should be set and fetched with + the Datum accessors, e.g. for an integer you should use + Int32GetDatum to set it and + DatumGetInt32 to read it. + + + On Windows (and anywhere else where EXEC_BACKEND is + defined) or in dynamic background workers it is not safe to pass a + Datum by reference, only by value. If an argument is required is + safest to pass an int32 or other small value and use that as an index into + an array allocated in shared memory. If a value like a cstring or + text is passed then the pointer won't be valid from the + postmaster and/or the new background worker process, leading to + crashes or undefined behavour. + + + + bgw_notify_pid is the PID of a PostgreSQL backend process to which the postmaster should send SIGUSR1 - when the process is started or exits. It should be 0 for workers registered - at postmaster startup time, or when the backend registering the worker does + when the process is started or exits, causing the notified process's latch + to be set. It should be 0 for workers registered + at postmaster startup time, when the backend registering the worker does not wish to wait for the worker to start up. Otherwise, it should be initialized to MyProcPid. Once running, the process can connect to a database by calling + BackgroundWorkerInitializeConnection BackgroundWorkerInitializeConnection(char *dbname, char *username) or + BackgroundWorkerInitializeConnectionByOid BackgroundWorkerInitializeConnectionByOid(Oid dboid, Oid useroid). This allows the process to run transactions and queries using the - SPI interface. If dbname is NULL or + the Server Programming Interface (SPI). See + for details. If dbname is NULL or dboid is InvalidOid, the session is not connected - to any particular database, but shared catalogs can be accessed. + to any particular database, but shared catalogs (tables where + relisshared is true in + pg_class) can be accessed. If username is NULL or useroid is InvalidOid, the process will run as the superuser created during initdb. A background worker can only call one of these two functions, and only - once. It is not possible to switch databases. + once. It is not possible to switch databases without exiting and restarting + the background worker. @@ -224,12 +306,22 @@ typedef struct BackgroundWorker - The worker_spi contrib module contains a working example, - which demonstrates some useful techniques. + Use of the SPI to perform queries and access + database tables in a database-connected background worker requires explicit + management of transactions and snapshots. See . + The same requirements apply to low-level access via direct scans. + + + + The worker-spi sample extension + contains a working example of a background worker that uses the + SPI and shared memory as well as some other useful + techniques. The maximum number of registered background workers is limited by . + diff --git a/doc/src/sgml/contrib.sgml b/doc/src/sgml/contrib.sgml index a698d0f..4564274 100644 --- a/doc/src/sgml/contrib.sgml +++ b/doc/src/sgml/contrib.sgml @@ -136,6 +136,7 @@ CREATE EXTENSION module_name FROM unpackaged; &seg; &sepgsql; &contrib-spi; + &example-worker-spi; &sslinfo; &tablefunc; &tcn; diff --git a/doc/src/sgml/example-worker-spi.sgml b/doc/src/sgml/example-worker-spi.sgml new file mode 100644 index 0000000..63d8f3d --- /dev/null +++ b/doc/src/sgml/example-worker-spi.sgml @@ -0,0 +1,26 @@ + + + + worker-spi example + + + Background worker with SPI + SPI in background worker + + + + The worker-spi module provides a complete and working + example of a background worker that uses the SPI to query databases and + uses shared memory for communication. It is not intended for use except + as an example for developers. + + + + It is located in src/test/modules/worker_spi. + + + + Relevant documentation for this example module is in , , and . + + + diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index f03b72a..7fa1896 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -142,6 +142,7 @@ + diff --git a/doc/src/sgml/spi.sgml b/doc/src/sgml/spi.sgml index cb85086..57914be 100644 --- a/doc/src/sgml/spi.sgml +++ b/doc/src/sgml/spi.sgml @@ -10,7 +10,8 @@ The Server Programming Interface (SPI) gives writers of user-defined - C functions the ability to run + C functions and + the ability to run SQL commands inside their functions. SPI is a set of interface functions to simplify access to the parser, planner, @@ -363,6 +364,7 @@ SPI_execute("INSERT INTO foo SELECT * FROM bar RETURNING *", false, 5); or the command counter, and it allows only plain SELECT commands to appear in the command string. The commands are executed using the snapshot previously established for the surrounding query. + Additional steps are required for . This execution mode is somewhat faster than the read/write mode due to eliminating per-command overhead. It also allows genuinely stable functions to be built: since successive executions @@ -822,7 +824,9 @@ int SPI_execute_with_args(const char *command, bool read_only - true for read-only execution + true for read-only execution with no snapshot + or command counter management. See SPI_execute + for details. @@ -3361,7 +3365,8 @@ char * SPI_getnspname(Relation rel) upper executor context, that is, the memory context that was current when SPI_connect was called, which is precisely the right context for a value returned from your - procedure. + procedure. For direct control over the context used for allocation, + MemoryContextAlloc may be used. @@ -4092,6 +4097,143 @@ INSERT INTO a SELECT * FROM a; + + Using the SPI from background workers + + + Using the SPI from background workers + requires some additional steps that are not required from SPI-using + procedures invoked via SQL from normal user backends. When using the SPI + from an SQL-callable function it is safe to assume that a transaction is + already open, there is already a snapshot, and that PostgreSQL's statistics + have been updated. None of these things may be assumed for use of the SPI + from a bgworker. + + + + To safely use the SPI from a bgworker you must ensure that: + + A transaction is open (in progress) + The SPI is connected + There is an active snapshot or read_only is set to false in SPI calls + + You should also update the statement start and end timestamps for use in + pg_stat_activity using pgstat_report_activity. + + + + Attempting to use the SPI without an open transaction or using the SPI + in read_only mode without setting a snapshot will + generally not cause obvious failures or, in a + --enable-cassert build, assertions. Instead it will + usually appear to work for simple cases but may produce incorrect + results or be unstable. Users of background workers should generally + prefix SPI use with + +Assert(IsTransactionState()); + + if the function does not its self establish a transaction. + + + + Typical SPI use from a background worker involves setting up a transaction, + setting the statement start timestamp, connecting to the SPI, updating + pg_stat_activity, establishing a snapshot, running the query/queries, + then performing matching cleanup actions. For example: + + + +const char * some_sql = "SELECT 1;"; + +/* setup */ +StartTransactionCommand(); +SetCurrentStatementStartTimestamp(); +SPI_connect(); +PushActiveSnapshot(GetTransactionSnapshot()); + +pgstat_report_activity(STATE_RUNNING, some_sql); + +/* Run queries with SPI, read_only = false ... */ +spi_ret = SPI_execute(some_sql, false); +if (spi_ret != SPI_OK_TUPLES) + elog(ERROR, "SPI error: %d", spi_ret); + +/* ... process results, run more queries, etc ... */ + +/* cleanup */ +SPI_finish(); +PopActiveSnapshot(); +CommitTransactionCommand(); +pgstat_report_activity(STATE_IDLE, NULL); + + + + In more complex cases the function may need to check if a transaction is + already open with IsTransactionState() and only perform + transaction management if there is no current transaction. If the SPI may + already connected by the caller the function should use + SPI_push_conditional before SPI_connect and + SPI_pop_conditional after SPI_finish. Snapshot + state may also be conditionally handled. The resulting procedure will + look more like: + + + +const char * some_sql = "SELECT 1;"; +bool tx_started = false, + snapshot_pushed = false, + spi_pushed; + +/* setup */ +if (!IsTransactionCommand()) +{ + StartTransactionCommand(); + tx_started = true; +} +SetCurrentStatementStartTimestamp(); +if (!ActiveSnapshotSet()) +{ + PushActiveSnapshot(GetTransactionSnapshot()); + snapshot_pushed = true; +} +spi_pushed = SPI_push_conditional(); +SPI_connect(); + +pgstat_report_activity(STATE_RUNNING, some_sql); + +/* Run queries with SPI, read_only = false... */ +spi_ret = SPI_execute(some_sql, false); +if (spi_ret != SPI_OK_TUPLES) + elog(ERROR, "SPI error: %d", spi_ret); + +/* ... process results, run more queries, etc ... */ + +/* cleanup */ +SPI_finish(); +if (snapshot_pushed) + PopActiveSnapshot(); +SPI_pop_conditional(spi_pushed); +if (tx_started) + CommitTransactionCommand(); +pgstat_report_activity(STATE_IDLE, NULL); + + + + If read_only is true for the SPI + execution functions then the caller should also manage the snapshot using + PushActiveSnapshot and + PopActiveSnapshot. If only read_only = + false SPI procedures are used then there is generally no need for + SPI users to explicitly manage snapshots. + + + + See the module for a more detailed example of + SPI use in background workers. + + + + Examples @@ -4103,7 +4245,8 @@ INSERT INTO a SELECT * FROM a; that were processed by the command. You can find more complex examples for SPI in the source tree in src/test/regress/regress.c and in the - module. + module. For background-worker + specific SPI examples see . diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml index 6437ab1..39e6a15 100644 --- a/doc/src/sgml/xfunc.sgml +++ b/doc/src/sgml/xfunc.sgml @@ -3357,6 +3357,60 @@ if (!ptr) } + + + Extensions registered to run at startup + using should instead install a + shmem_startup_hook from _PG_init. This will be + invoked once shared memory is ready but before user backends or background + workers get launched. The hook function invoked should use a pattern like + that shown for shared memory initialization above then call the next hook + function, e.g. declare: + +static shmem_startup_hook_type prev_shmem_startup_hook = NULL; + + then from _PG_init, after any + RequestAddinShmemSpace and RequestAddinLWLocks + install the hook: + + prev_shmem_startup_hook = shmem_startup_hook; + shmem_startup_hook = my_worker_shmem_startup; + + where my_worker_shmem_startup is a void function + with void arguments that calls the next hook (if any): + + if (prev_shmem_startup_hook != NULL) + prev_shmem_startup_hook(); + + then calls ShmemInitStruct per the example shown above. An + example of this usage can be found in the initialisation of the extension. + + + + Shared memory is zeroed on postmaster restart + + If the postmaster restarts - usually due to the crash of a backend + process like a user backend or a background worker - then it zeroes + shared memory on restart then re-invokes any shared memory setup hooks + that have been installed by extensions. Extensions using shared memory + must be prepared for this. + + + Background workers are not unregistered if the postmaster restarts + due to the crash of a background worker or user backend. This can lead + to background workers accessing shared memory that has been reinitialized. + Care must be taken to ensure that initialisation of shared memory produces + stable results when re-run or workers must detect the postmaster restart + using a generation counter and exit. In particular if parameters are being + passed to a bgworker via shared memory the background worker + bgw_main_arg should always be an index into an + array allocated in shared memory, rather than a pointer, and repeated + initialisations of the shared memory segment must produce the same range + of valid indexes in the same order. + + + -- 2.1.0