Re: problem with using fetch and a join.

Tom,
Many thanks for the tip. Yes adding the order by (id) to the statement
allowed the FETCH BACKWARD to work. Is there any reason why the FETCH
BACKWARD statement for such a join not to be supported in the future?

Once again,
Many Thanks
And have a great holiday season :D

Noel Faux
Department of Biochemistry and Molecluar Biology
Monash University
Clayton 3168
Victoria
Australia

----- Original Message -----
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Date: Saturday, December 21, 2002 1:47 am
Subject: Re: [NOVICE] problem with using fetch and a join.

> Noel <noel(dot)faux(at)med(dot)monash(dot)edu(dot)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
> possibleworkaround is to add an explicit ORDER BY to the query.
>
> regards, tom lane
>

Noel Faux <Noel(dot)Faux(at)med(dot)monash(dot)edu(dot)au> writes:
> Many thanks for the tip. Yes adding the order by (id) to the statement
> allowed the FETCH BACKWARD to work. Is there any reason why the FETCH
> BACKWARD statement for such a join not to be supported in the future?

If someone wants to make it happen ... my take is that actually making
it work in all cases would be lots more trouble than it's worth. I'd
settle for producing an error message in place of a wrong answer.

regards, tom lane

Hi Tom,

I'm also having problems with fecthing and moving backwards within a view.
ie. (comparison_region_protein is a view)

int result = stmt.executeUpdate("declare test cursor for select id from " +
"comparison_region_protein where db_db_comparison =
1 order by id");
I can move and fetch forward, but i can't fetch or move backwards. Is
this because of the reasons metioned before? Is there a work around?

Many thanks
Noel

Tom Lane wrote:

>Noel Faux <Noel(dot)Faux(at)med(dot)monash(dot)edu(dot)au> writes:
>
>
>>Many thanks for the tip. Yes adding the order by (id) to the statement
>>allowed the FETCH BACKWARD to work. Is there any reason why the FETCH
>>BACKWARD statement for such a join not to be supported in the future?
>>
>>
>
>If someone wants to make it happen ... my take is that actually making
>it work in all cases would be lots more trouble than it's worth. I'd
>settle for producing an error message in place of a wrong answer.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>