[Info-ingres] Database procedure for loops and commit/rollback

[Martin Bowes]

Hi All,

Is putting a commit or rollback in the processing of a for loop a good idea or not?

I've got several examples where it all works exactly as I would hope, and yet we've recently seen error 'E_US11FD The cursor is not currently positioned on a row.' on some specific cases.

The reason the commits are in place is that we use the for loop to process some data on the basis of which we may do an update into some table. The possibility exists that the update triggers a constraint or rules based violation. If so those violations will do a raise error and thus in the DEP all my statements get rolled back. Hence we want to commit after a successful update to prevent a subsequent failure from rolling it back.

Initial testing showed that the concept seemed sound...
Eg.
create procedure good_loop
as declare
    reltid integer4 not null not default;
    reltidx integer4 not null not default;
    relid   char(256) not null not default;
    relowner char(32) not null not default;

    iloop integer4 not null not default;
    ndone integer4 not null not default;
    nfail integer4 not null not default;
    enum  integer4 not null not default;
    msg   varchar(256) not null not default;
begin
    iloop = 0;
    ndone = 0;
    nfail = 0;
    for select reltid, reltidx, relid, relowner
        into :reltid, :reltidx, :relid, :relowner
        from iirelation
        order by 1, 2
    do
        iloop = iloop + 1;
        msg = '[' + varchar(:iloop) + '] for reltid/reltidx = ' + varchar(:reltid) + '/' + varchar(:reltidx);
        message :msg;

        select iierrornumber into :enum;
        if (:enum != 0) then
            nfail = nfail + 1;
            msg = 'Failed with enum = ' + varchar(:enum);
            message :msg;
            rollback;
        else
            ndone = ndone + 1;
            msg = 'Completed update of base';
            message :msg;
            commit;
        endif;

        if (iloop > 10) then
            -- 10 is sufficient to demonstrate the issue.
            endloop;
        endif;
    endfor;

    msg = 'good_loop(): Encountered ' + varchar(:iloop) + '. Updated ' + varchar(:ndone) +', Failed ' + varchar(:nfail);
    message :msg;
end;
Any ideas?

Marty
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20190606/e7351f97/attachment.html>