Skip to content

Instantly share code, notes, and snippets.

@ringerc
Last active September 9, 2021 04:06
Show Gist options
  • Save ringerc/0bdc679b66e161c287a1 to your computer and use it in GitHub Desktop.
Save ringerc/0bdc679b66e161c287a1 to your computer and use it in GitHub Desktop.
SPI bgworker boilerplate
/* Simpler version that assumes there is no active tx or snapshot and SPI isn't connected yet */
const char * some_sql = "SELECT 1;";
/* setup */
Assert(!IsTransactionState());
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);
/* More complex version that can handle already being in a tx and having a snapshot already */
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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment