Repeatable Read Isolation in SQL running via background worker

On Thu, Aug 9, 2018 at 4:34 PM, Jeremy Finzel <finzelj@gmail.com> wrote:

I am using worker_spi as a model to run a SQL statement inside a
background worker. From my browsing of the Postgres library, I believe
that if I want repeatable read isolation level, the proper way for me to
attain this is to add this line after StartTransactionCommand()
in worker_spi_main:

XactIsoLevel = XACT_REPEATABLE_READ;

Or - am I mistaken? Does PushActiveSnapshot already ensure I will get the
same snapshot of the data within this transaction?

Can anyone help me if this is accurate or if there are any other gotchas I
should be aware of?

The SQL statement will be run every minute for example, and each time with
this isolation level. At least, that is my goal.

Any help is much appreciated.

Thanks,
Jeremy

It seems to be working. If anyone could provide any feedback though I
would be very appreciative.

On Mon, Aug 13, 2018 at 10:52 AM, Jeremy Finzel <finzelj@gmail.com> wrote:

On Thu, Aug 9, 2018 at 4:34 PM, Jeremy Finzel <finzelj@gmail.com> wrote:

I am using worker_spi as a model to run a SQL statement inside a
background worker. From my browsing of the Postgres library, I believe that
if I want repeatable read isolation level, the proper way for me to attain
this is to add this line after StartTransactionCommand() in worker_spi_main:

XactIsoLevel = XACT_REPEATABLE_READ;

It's usually a good idea to only change GUCs through the GUC machinery
i.e. use SetConfigOption().

Are you using StartTransactionCommand() and CommitTransactionCommand()
to manage transaction boundaries? If not, maybe you should.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Aug 14, 2018 at 11:18 AM, Robert Haas <robertmhaas@gmail.com> wrote:

On Mon, Aug 13, 2018 at 10:52 AM, Jeremy Finzel <finzelj@gmail.com> wrote:

On Thu, Aug 9, 2018 at 4:34 PM, Jeremy Finzel <finzelj@gmail.com> wrote:

I am using worker_spi as a model to run a SQL statement inside a
background worker. From my browsing of the Postgres library, I believe

that

if I want repeatable read isolation level, the proper way for me to

attain

this is to add this line after StartTransactionCommand() in

worker_spi_main:

XactIsoLevel = XACT_REPEATABLE_READ;

It's usually a good idea to only change GUCs through the GUC machinery
i.e. use SetConfigOption().

Are you using StartTransactionCommand() and CommitTransactionCommand()
to manage transaction boundaries? If not, maybe you should.

Many thanks for the reply. Yes, I am using StartTransactionCommand and
Commit just like in worker_spi.c. Here is the relevant section of code:

SetCurrentStatementStartTimestamp();
StartTransactionCommand();
XactIsoLevel = XACT_REPEATABLE_READ;
SPI_connect();
PushActiveSnapshot(GetTransactionSnapshot());
pgstat_report_activity(STATE_RUNNING, buf.data);

/* We can now execute queries via SPI */
SPI_execute(buf.data, false, 0);

/*
* And finish our transaction.
*/
SPI_finish();
PopActiveSnapshot();
CommitTransactionCommand();

So if you are saying it would be better to use SetConfigOption() there I
will look into that. Thanks!
Jeremy