Re: [GENERAL] Postgres 7.2 - Updating rows in cursor problem

I wrote:
> This is a bug in plgsql, or more precisely in SPI, I think. The FOR
> statement needs to restore its initial value of scanCommandId each time
> it resumes execution of the SELECT. Seems like that should be done down
> inside SPI. Comments?

More specifically, the problem is that plpgsql's FOR-over-a-select now
depends on a SPI cursor, and both SPI cursors and regular cursors are
broken in this regard. Observe the following misbehavior with a plain
cursor:

regression=# select * from foo;
f1 | f2
----+----
1 | 1
2 | 2
3 | 3
(3 rows)

regression=# begin;
BEGIN
regression=# declare c cursor for select * from foo;
SELECT
regression=# fetch 2 from c;
f1 | f2
----+----
1 | 1
2 | 2
(2 rows)

regression=# update foo set f2 = f2 + 1;
UPDATE 3
regression=# fetch all from c;
f1 | f2
----+----
1 | 2
2 | 3
3 | 4
(3 rows)

IMHO the cursor should not be able to see the rows inserted by the
subsequent UPDATE. (Certainly it should not return the updated versions
of rows it's already returned.) The SQL spec says that cursors declared
INSENSITIVE shall not observe changes made after they are opened --- and
it gives the implementation the option to make all cursors behave that
way. I think we should choose to do so.

I believe the correct fix for this is that Portal objects should store
the scanCommandId that was current when they were created, and restore
this scanCommandId whenever they are asked to run their plan. Comments?

regards, tom lane