Re: Problem with cursor

"Frank Millman" <frank@chagford.com> writes:
> [ this doesn't work: ]
> begin ;
> declare fm scroll cursor for select a.CompanyId, b.CompanyName
>   from SysUsersCompanies a, SysCompanies b
>   where UserId =3D 'Greer' and a.CompanyId =3D b.CompanyId
>   order by a.CompanyId ;
> move 1 in fm ;
> fetch next from fm ;
> move -2 in fm ;
> fetch next from fm ;

Yeah, there are known problems with running complex queries backwards
in existing releases.  Some plan types support this, and some don't
--- in particular, mergejoin doesn't handle it, which very likely is
the plan type you are getting here.

There is a fix in place for 7.4 but that won't help you today.  The only
workaround I can suggest is to force the system to adopt a plan that
involves a top-level sort.  You could probably do that by adding some
additional terms to the ORDER BY clause, but some experimentation with
EXPLAIN will be called for.

            regards, tom lane