To: Hiroshi Inoue <Inoue@tpf.co.jp>
Cc: Bruce Momjian <pgman@candle.pha.pa.us>,
	pgsql-hackers@postgresql.org
Subject: Re: MOVE LAST: why? 
In-reply-to: <22435.1042065746@sss.pgh.pa.us> 
References: <200301072329.h07NT2X28922@candle.pha.pa.us>
	<27934.1041984327@sss.pgh.pa.us> <3E1B9DCD.75859501@tpf.co.jp>
	<501.1041997832@sss.pgh.pa.us> <3E1BA5E6.9463718F@tpf.co.jp>
	<863.1042000401@sss.pgh.pa.us> <3E1BAFF6.B0114A67@tpf.co.jp>
	<1082.1042002375@sss.pgh.pa.us> <3E1BEFFE.88B57516@tpf.co.jp>
	<22435.1042065746@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Wed, 08 Jan 2003 17:42:26 -0500"
Date: Wed, 08 Jan 2003 18:05:11 -0500
Message-ID: <22682.1042067111@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

I said:
> Yeah, backwards scan is not implemented for quite a large number of plan
> node types :-(.  I am not sure that it is practical to fix them all.
> I have been toying with the notion of making cursors on complex plans
> safe for FETCH BACKWARD by sticking a MATERIAL node atop the plan, if
> the top plan node isn't one that can handle backwards scan.

I forgot to mention plan B: make use of ReScan.  This could work like
so:

1. Cursor keeps track of row number (number of rows it's fetched).

2. To scan backwards when top plan type doesn't handle it, rewind all
the way with ReScan, then move forward the appropriate number of rows.

This would avoid any added overhead in the case where a backwards move
is never requested, and it also would support MOVE BACKWARD ALL quite
efficiently (much more so than now).  On the other hand, it'd really
suck if the user asks for backwards scan from a point far into the
output.

Perhaps we could do something with a hybrid technique: don't materialize
the cursor output unless user actually asks for backwards scan.  If he
does, then create a tuplestore and put the data into it (rescanning the
query output to do so), and finally supply the tuples from the tuplestore.

			regards, tom lane