Re: with hold cursor, cause function execute twice and wrong result

2011/12/27 wcting163 <wcting163(at)163(dot)com>:
> postgres=# select version();
>                                                           version
> ------------------------------------------------------------------------------------------------------------------------
> ---
>  PostgreSQL 9.0alpha5 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
> 4.1.2 20080704 (Asianux 3.0 4.1.2-44), 64-b
> it
>
> create table test_execute(id int,name varchar(40));
> insert into test_execute values(1,'jack');
> create or replace function p_test_execute() returns void
> as
> $$
> begin
>         raise notice 'hello world';
>         update test_execute set id=id*2;
> end;
> $$ LANGUAGE plpgsql;
>
> begin;
> declare JDBC_CURS_1 cursor with hold for select p_test_execute() from
> test_execute;
> fetch 50 from JDBC_CURS_1;
> NOTICE:  hello world
> end;
> NOTICE:  hello world
> COMMIT
> select * from test_execute;
>  id | name
> ----+------
>   4 | jack
>
> I expect id = 2, but it is **4** instead,
>
> The reason is that the function p_test_execute is executed twice, when
> *fetch*, it is first executed, and when transaction commit, because the
> cursor is a *holdable* cursor, it is executed again.
>
> I read the code, for holdable cursor, when commit, following call will
> execute:
>  CommitHoldablePortals-->PersistHoldablePortal-->ExecutorRewind.
>
> Is *ExecutorRewind* necessary, is it the root of this bug?
> Does *ExecutorRewind* cause plan re-execute?

while the current behavior isn't great, is this in fact a bug? there
is no guarantee that functions in the select list are executed once
per returned row anywhere else in the system.

note, the current best way to isolate yourself from this behavior,
stuffing the function call in a CTE, works they way you are intending:

declare JDBC_CURS_1 cursor with hold for with foo as (select
p_test_execute() from test_execute) select * from foo;

merlin