Thread: Signature change for SPI_cursor_open

Apparently the signature for function SPI_cursor_open got an additional 
"read_only" parameter starting with 8.0.0beta3. The documentation states 
that this flag should be "true for read-only execution".

I can't see any mention of this on the hackers list nor in the change logs.

Is it correct to assume that this flag implies that some kind of 
optimization has been added and that if this flag is "false", the 
execution of the statement will take somewhat longer? I need some advice 
in how to handle this.

In PL/Java, I use this method in my JDBC implementation and have little 
knowledge of what the intentions are at that point in time since I don't 
write the actual code that will use it.

A couple of questions:

Besides from the plain obvious like INSERT, UPDATE, and DELETE, any 
SELECT that calls a function may potentially change something right?

What are the implications of setting this flag to false always?

Why doesn't SPI_prepare figure this out with some help from attributes 
that defines the characteristics of involved functions? I.e. why isn't 
the "read-only" a property of the statement itself rather than something 
to be provided when the statement is executed.

Can one single statement be executed in both read-only and not read-only 
mode?

Regards,
Thomas Hallgren

Thomas Hallgren <thhal@mailblocks.com> writes:
> Apparently the signature for function SPI_cursor_open got an additional 
> "read_only" parameter starting with 8.0.0beta3. The documentation states 
> that this flag should be "true for read-only execution".

> Is it correct to assume that this flag implies that some kind of 
> optimization has been added and that if this flag is "false", the 
> execution of the statement will take somewhat longer? I need some advice 
> in how to handle this.

You should set the flag if and only if you are executing a pl/java
function that has a provolatile setting of "stable" or "immutable".
The new rule is that only functions declared "volatile" are allowed
to have side effects on the database.  See pghackers discussions in
early September about updating snapshots, doing CommandCounterIncrement,
and so forth within functions.

For code examples see the commits in the pl languages here:

2004-09-13 16:07  tgl
* src/: include/executor/execdesc.h, include/executor/executor.h,include/executor/spi.h,
include/tcop/pquery.h,include/tcop/utility.h,include/utils/tqual.h, pl/plperl/plperl.c,pl/plperl/spi_internal.c,
pl/plperl/spi_internal.h,pl/plpgsql/src/pl_comp.c,pl/plpgsql/src/pl_exec.c,pl/plpgsql/src/plpgsql.h,
pl/plpython/plpython.c,pl/tcl/pltcl.c,test/regress/expected/transactions.out,test/regress/sql/transactions.sql:
Redesignquery-snapshot timingso that volatile functions in READ COMMITTED mode see a freshsnapshot for each command in
thefunction, rather than using thelatest interactive command's snapshot.    Also, suppress freshsnapshots as well as
CommandCounterIncrementinside STABLE andIMMUTABLE functions, instead using the snapshot taken for the mostclosely
nestedregular query.  (This behavior is only sane forread-only functions, so the patch also enforces that such
functionscontainonly SELECT commands.) As per my proposal of 6-Sep-2004; Inote that I floated essentially the same
proposalon 19-Jun-2002,but that discussion tailed off without any action.  Since 8.0 seemslike the right place to be
takingpossibly nontrivial backwardscompatibility hits, let's get it done now.
 
        regards, tom lane

Tom Lane wrote:> You should set the flag if and only if you are executing a pl/java> function that has a provolatile
settingof "stable" or "immutable".> The new rule is that only functions declared "volatile" are allowed> to have side
effectson the database.  See pghackers discussions in> early September about updating snapshots, doing
CommandCounterIncrement,>and so forth within functions.>
 
Ok, now I understand. Thanks for the explanation. I guess that if 
read_only is set to true and an attempt is made to execute a plan that 
has side effect, that will cause an ERROR?

Regards,
Thomas Hallgren

Thomas Hallgren <thhal@mailblocks.com> writes:
> Ok, now I understand. Thanks for the explanation. I guess that if 
> read_only is set to true and an attempt is made to execute a plan that 
> has side effect, that will cause an ERROR?

Right, it'll bounce anything except a SELECT query.  (This test is not
watertight; consider SELECT nextval() ... but on the other hand that's a
non-transactional operation that does not care about snapshots anyway.)
        regards, tom lane