Repeatable Read Isolation in SQL running via background worker

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Repeatable Read Isolation in SQL running via background worker

Jeremy Finzel
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
Reply | Threaded
Open this post in threaded view
|

Re: Repeatable Read Isolation in SQL running via background worker

Jeremy Finzel
On Thu, Aug 9, 2018 at 4:34 PM, Jeremy Finzel <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Repeatable Read Isolation in SQL running via background worker

Robert Haas
On Mon, Aug 13, 2018 at 10:52 AM, Jeremy Finzel <[hidden email]> wrote:
> On Thu, Aug 9, 2018 at 4:34 PM, Jeremy Finzel <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: Repeatable Read Isolation in SQL running via background worker

Jeremy Finzel


On Tue, Aug 14, 2018 at 11:18 AM, Robert Haas <[hidden email]> wrote:
On Mon, Aug 13, 2018 at 10:52 AM, Jeremy Finzel <[hidden email]> wrote:
> On Thu, Aug 9, 2018 at 4:34 PM, Jeremy Finzel <[hidden email]> 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
Previous Thread Next Thread