SPI bgworker boilerplate

simple_spi_boilerplate.c

/* 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);

spi_boilerplate.c

/* 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);