Обсуждение: Repeatable Read Isolation in SQL running via background worker
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
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