Re: Cursors in pl/pgsql

[Heikki Linnakangas]

(CC'ing gpdb-dev)

On 02/08/2017 07:29 PM, Chuck Litzell wrote:
> Hi. Word is that updatable cursors are allowed in GPDB 5.0. I want to add
> an example to the pl/pgsql doc, but it's not working for me. Interactive
> declare, fetch, and update where current of work fine, but in plpgsql I
> consistently get an error 'cursor "x" is not a simply updatable scan of
> table...'
>
> Is updatable cursor supported in pl/pgsql or any of the other pl/*?
>
> The following works in postgres but gets an error in GPDB.
>
> test=# \d test
> Table "public.test"
> Column | Type | Modifiers
> --------+---------+-----------
> id | integer |
> c1 | text |
> Distributed by: (id)
>
> create or replace function upd() returns void as
> $$
> DECLARE
> c cursor for select id, c1 from test;
> BEGIN
> open c;
> move next from c;
> execute 'UPDATE test SET c1=''xxx'' WHERE CURRENT OF c;';
> END;
> $$ LANGUAGE plpgsql;
>
> test=# select upd();
> ERROR: cursor "c" is not a simply updatable scan of table "test"
> CONTEXT: SQL statement "UPDATE test SET c1='xxx' WHERE CURRENT OF c;"
> PL/pgSQL function "upd" line 6 at EXECUTE statement

There is some GPDB-specific code, to add the "ctid" and "gp_segment_id"
system columns to the query, as so-called junk columns, when you do
"DECLARE c CURSOR FOR ...". Those columns are needed in order to to
update the cursor. In preptlist.c:

> /* simply updatable cursors */
> if (command_type == CMD_SELECT &&
> parse->utilityStmt &&
> IsA(parse->utilityStmt, DeclareCursorStmt) &&
> ((DeclareCursorStmt *) parse->utilityStmt)->is_simply_updatable)
> {
> tlist = supplement_simply_updatable_targetlist((DeclareCursorStmt *) parse->utilityStmt,
> range_table,
> tlist);
> }

That doesn't get done for cursors declared in PL/pgSQL functions. I'm
not sure if that's an oversight, or a conscious decision. The junk
columns add a little bit more data to the result set, that need to be
transferred between the nodes, so it's not entirely free.

So the short answer is that it's not supported for PL/pgSQL cursors. As
a work-around, this seems to work, although it's a bit weird:

create or replace function upd() returns void as
$$
DECLARE
c cursor for select id, c1 from test;
BEGIN
execute 'declare c cursor for select id, c1 from test';
move next from c;
execute 'UPDATE test SET c1=''xxx'' WHERE CURRENT OF c;';
END;
$$ LANGUAGE plpgsql;

Developers: should try to fix this?

- Heikki