Re: [HACKERS] Signature change for SPI_cursor_open

[Tom Lane]

Thomas Hallgren [EMAIL PROTECTED] 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: Redesign query-snapshot timing
so that volatile functions in READ COMMITTED mode see a fresh
snapshot for each command in the function, rather than using the
latest interactive command's snapshot.  Also, suppress fresh
snapshots as well as CommandCounterIncrement inside STABLE and
IMMUTABLE functions, instead using the snapshot taken for the most
closely nested regular query.  (This behavior is only sane for
read-only functions, so the patch also enforces that such functions
contain only SELECT commands.) As per my proposal of 6-Sep-2004; I
note that I floated essentially the same proposal on 19-Jun-2002,
but that discussion tailed off without any action.  Since 8.0 seems
like the right place to be taking possibly nontrivial backwards
compatibility hits, let's get it done now.

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [HACKERS] Signature change for SPI_cursor_open

[Thomas Hallgren]

Tom Lane wrote:
 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.

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

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [HACKERS] Signature change for SPI_cursor_open

[Tom Lane]

Thomas Hallgren [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 8: explain analyze is your friend