Re: Repeatable Read Isolation in SQL running via background worker

[Jeremy Finzel]

On Tue, Aug 14, 2018 at 11:18 AM, Robert Haas  wrote:

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

Re: Repeatable Read Isolation in SQL running via background worker

[Robert Haas]

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

Re: Repeatable Read Isolation in SQL running via background worker

[Jeremy Finzel]

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

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