Обсуждение: SPI isolation changes
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
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)
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
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
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.
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
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