Обсуждение: SPI isolation changes

Поиск
Список
Период
Сортировка

SPI isolation changes

От
Seino Yuki
Дата:
Hi,

When I read the documents and coding of SPI, [1]
I found that the following the SPI_start_transaction does not support
transaciton_mode(ISOLATION LEVEL, READ WRITE/READ ONLY) like BEGIN 
command. [2]
Is there a reason for this?

I would like to be able to set transaciton_mode in 
SPI_start_transaction.
What do you think?

[1]
https://www.postgresql.org/docs/devel/spi-spi-start-transaction.html

[2]
https://www.postgresql.org/docs/devel/sql-begin.html

Regards,
-- 
Seino Yuki
NTT DATA CORPORATION

Re: SPI isolation changes

От
Heikki Linnakangas
Дата:
On 30/06/2023 17:15, Seino Yuki wrote:
> Hi,
> 
> When I read the documents and coding of SPI, [1]
> I found that the following the SPI_start_transaction does not support
> transaciton_mode(ISOLATION LEVEL, READ WRITE/READ ONLY) like BEGIN
> command. [2]
> Is there a reason for this?

Per the documentation for SPI_start_transaction that you linked to:

"SPI_start_transaction does nothing, and exists only for code 
compatibility with earlier PostgreSQL releases."

I haven't tested it, but perhaps you can do "SET TRANSACTION ISOLATION 
LEVEL" in the new transaction after calling SPI_commit() though. Or "SET 
DEFAULT TRANSACTION ISOLATION LEVEL" before committing.

-- 
Heikki Linnakangas
Neon (https://neon.tech)

Re: SPI isolation changes

От
Seino Yuki
Дата:
Thanks for the reply!

On 2023-06-30 23:26, Heikki Linnakangas wrote:
> On 30/06/2023 17:15, Seino Yuki wrote:
>> Hi,
>> 
>> When I read the documents and coding of SPI, [1]
>> I found that the following the SPI_start_transaction does not support
>> transaciton_mode(ISOLATION LEVEL, READ WRITE/READ ONLY) like BEGIN
>> command. [2]
>> Is there a reason for this?
> 
> Per the documentation for SPI_start_transaction that you linked to:
> 
> "SPI_start_transaction does nothing, and exists only for code
> compatibility with earlier PostgreSQL releases."
> 
> I haven't tested it, but perhaps you can do "SET TRANSACTION ISOLATION
> LEVEL" in the new transaction after calling SPI_commit() though. Or
> "SET DEFAULT TRANSACTION ISOLATION LEVEL" before committing.

I understand that too.
However, I thought SPI_start_transaction was the function equivalent to 
BEGIN (or START TRANSACTION).
Therefore, I did not understand why the same option could not be 
specified.

I also thought that using SPI_start_transaction would be more readable
than using SPI_commit/SPI_rollback to implicitly start a transaction.
What do you think?

Regards,
-- 
Seino Yuki
NTT DATA CORPORATION

Re: SPI isolation changes

От
Tom Lane
Дата:
Seino Yuki <seinoyu@oss.nttdata.com> writes:
> I also thought that using SPI_start_transaction would be more readable
> than using SPI_commit/SPI_rollback to implicitly start a transaction.
> What do you think?

I think you're trying to get us to undo commit 2e517818f, which
is not going to happen.  See the threads that led up to that:

Discussion: https://postgr.es/m/3375ffd8-d71c-2565-e348-a597d6e739e3@enterprisedb.com
Discussion: https://postgr.es/m/17416-ed8fe5d7213d6c25@postgresql.org

It looks to me like you can just change the transaction property
settings immediately after SPI_start_transaction if you want to.
Compare this bit in SnapBuildExportSnapshot:

    StartTransactionCommand();

    /* There doesn't seem to a nice API to set these */
    XactIsoLevel = XACT_REPEATABLE_READ;
    XactReadOnly = true;

Also look at the implementation of SPI_commit_and_chain,
particularly RestoreTransactionCharacteristics.

            regards, tom lane

Re: SPI isolation changes

От
Seino Yuki
Дата:
On 2023-07-01 00:06, Tom Lane wrote:
> Seino Yuki <seinoyu@oss.nttdata.com> writes:
>> I also thought that using SPI_start_transaction would be more readable
>> than using SPI_commit/SPI_rollback to implicitly start a transaction.
>> What do you think?
> 
> I think you're trying to get us to undo commit 2e517818f, which
> is not going to happen.  See the threads that led up to that:
> 
> Discussion:
> https://postgr.es/m/3375ffd8-d71c-2565-e348-a597d6e739e3@enterprisedb.com
> Discussion: https://postgr.es/m/17416-ed8fe5d7213d6c25@postgresql.org
> 
> It looks to me like you can just change the transaction property
> settings immediately after SPI_start_transaction if you want to.
> Compare this bit in SnapBuildExportSnapshot:
> 
>     StartTransactionCommand();
> 
>     /* There doesn't seem to a nice API to set these */
>     XactIsoLevel = XACT_REPEATABLE_READ;
>     XactReadOnly = true;
> 
> Also look at the implementation of SPI_commit_and_chain,
> particularly RestoreTransactionCharacteristics.
> 
>             regards, tom lane

Thanks for sharing past threads.
I was understand how SPI_start_transaction went no-operation.

I also understand how to set the transaction property.
However, it was a little disappointing that the transaction property
could not be changed only by SPI commands.

Of course, executing SET TRANSACTION ISOLATION LEVEL with SPI_execute 
will result in error.
---
SPI_execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE", false, 0);

(Log Output)
ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query
CONTEXT:  SQL statement "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"
---

Thanks for answering.

Re: SPI isolation changes

От
Tom Lane
Дата:
Seino Yuki <seinoyu@oss.nttdata.com> writes:
> Of course, executing SET TRANSACTION ISOLATION LEVEL with SPI_execute 
> will result in error.
> ---
> SPI_execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE", false, 0);

> (Log Output)
> ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query
> CONTEXT:  SQL statement "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"

Even if you just did SPI_commit?  That *should* fail if you just do
it right off the bat in a SPI-using procedure, because you're already
within the transaction that called the procedure.  But I think it
will work if you do SPI_commit followed by this SPI_execute.

            regards, tom lane

Re: SPI isolation changes

От
Seino Yuki
Дата:
On 2023-07-01 01:47, Tom Lane wrote:
> Seino Yuki <seinoyu@oss.nttdata.com> writes:
>> Of course, executing SET TRANSACTION ISOLATION LEVEL with SPI_execute
>> will result in error.
>> ---
>> SPI_execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE", false, 0);
> 
>> (Log Output)
>> ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any 
>> query
>> CONTEXT:  SQL statement "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"
> 
> Even if you just did SPI_commit?  That *should* fail if you just do
> it right off the bat in a SPI-using procedure, because you're already
> within the transaction that called the procedure.  But I think it
> will work if you do SPI_commit followed by this SPI_execute.
> 
>             regards, tom lane

I'm sorry. I understood wrongly.
SPI_execute(SET TRANSACTION ISOLATION LEVEL ~ ) after executing 
SPI_commit succeeded.

Thank you. My problem is solved.

Regards,
-- 
Seino Yuki
NTT DATA CORPORATION