Thread: BUG #6360: with hold cursor, cause function executed twice and wrong results

The following bug has been logged on the website:

Bug reference:      6360
Logged by:          ctwang
Email address:      wcting163@163.com
PostgreSQL version: 9.0.5
Operating system:   linux 2.6.18-128.7AXS3=20
Description:=20=20=20=20=20=20=20=20

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=3Did*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 =3D 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?

wcting163@163.com writes:
> 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.

Yup.  I don't particularly see this as a bug.  If you were to manually
rewind and rescan the cursor (ie, MOVE BACKWARD ALL and re-fetch),
the function would be executed multiple times too.  If you don't want
that to happen, the best way would be to commit the transaction
immediately, not fetch some rows and then commit.

            regards, tom lane

At 2011-12-28 01:47:20,"Tom Lane" tgl@sss.pgh.pa.us> wrote:
Yup.  I don't particularly see this as a bug.  If you were to manually
>rewind and rescan the cursor (ie, MOVE BACKWARD ALL and re-fetch),
>the function would be executed multiple times too.  If you don't want
>that to happen, the best way would be to commit the transaction
>immediately, not fetch some rows and then commit.
>
> regards, tom lane

Why *with hold cursor* acts as *MOVE BACKWARD ALL & FETCH*?
Is this right or necessary? maybe they are not comparable, i doubt this analogy.

MOVE BACKWARD ALL & FETCH will cause cursor re-fetch?
 Maybe pepole have different opinions for MOVE BACKWARD ALL & FETCH:
 1. fetch from the old result-sets, not rescan(re-execute);
 2. re-fetch and generate the new result-sets, cause plan re-execute again;

Now, pg acts as the second;
*Move* is not SQL standard, and does not clearly documented in pg document,

If we create *DestTuplestore* when first fetch, not in commit transaction, then:
1. we always fetch results from *DestTuplestore*;
2. if *DestTuplestore* is empty, we execute-plan and fill the results into *DestTuplestore*;
3. when rewind, just rewind *DestTuplestore*, not executor;

then we can implement the first action?

So, there are two questions here:
1. the Standard action of *MOVE BACKWARD ALL & FETCH*;
2. *with hold cursor* acts as *MOVE BACKWARD ALL & FETCH*, is this right(necessary)?

On Tue, Dec 27, 2011 at 12:47 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> wcting163@163.com writes:
>> 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.
>
> Yup. =A0I don't particularly see this as a bug. =A0If you were to manually
> rewind and rescan the cursor (ie, MOVE BACKWARD ALL and re-fetch),
> the function would be executed multiple times too. =A0If you don't want
> that to happen, the best way would be to commit the transaction
> immediately, not fetch some rows and then commit.

Is that even per spec?  I would not expect the results, or the
side-effects, of a query to depend on the method used to retrieve its
results.

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company