Re: problem with using fetch and a join.

Noel <noel.faux@med.monash.edu.au> writes:
> begin;
> declare test cursor for
> select protein.accession
> from protein, region
> where protein.accession like '%1245%'
> and protein.accession = region.accession
> and region.protein_database = 1;
> fetch forward 2 in test;
> fetch backward 1 in test;

FETCH (or MOVE) BACKWARD doesn't work with most join plan types (or
indeed anything much more complex than a simple seqscan or indexscan).
There ought to be some logic in there to detect and complain about the
non-working cases, but right now I fear you just get the wrong answer
when any given routine doesn't pay attention to the direction flag :-(

I believe it will work when the top plan node is a Sort, so one possible
workaround is to add an explicit ORDER BY to the query.

            regards, tom lane