Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34]) by hub.org (8.9.3/8.9.3) with ESMTP id TAA10127 for ; Mon, 8 Nov 1999 19:26:19 -0500 (EST) (envelope-from Inoue@tpf.co.jp) Received: from tpf.co.jp ([126.0.1.56] (may be forged)) by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with ESMTP id JAA03068; Tue, 09 Nov 1999 09:25:55 +0900 Sender: inoue@sd.tpf.co.jp Message-ID: <38276A2C.F1ECB937@tpf.co.jp> Date: Tue, 09 Nov 1999 09:26:20 +0900 From: Hiroshi Inoue X-Mailer: Mozilla 4.08 [muriyari-ja_euc] (X11; I; Linux 2.2.9 i686) MIME-Version: 1.0 To: Stoyan Genov CC: pgsql-sql@postgreSQL.org Subject: Re: [SQL] Move forward all (Was Re: How to get last 10 rows in a table on a large database?) References: <199911080927.LAA03731@lorna.digsys.bg> Content-Type: multipart/mixed; boundary="------------F70D67F244A3031122613AAA" This is a multi-part message in MIME format. --------------F70D67F244A3031122613AAA Content-Type: text/plain; charset=iso-2022-jp Content-Transfer-Encoding: 7bit Stoyan Genov wrote: > > > > > You are unable to say: > > > > > begin transaction; > > > > > declare tbl_cur cursor for .... ..... > > > > > move forward all in tbl_cur; > > > > > fetch backward 10 in tbl_cur; > > > > > ........... > > > > > end transaction; > > > > > because when you "move forward all" the result gets lost. > > > > > > > > Huh? It seems to work fine for me. > > > > > > Well, it does not seem to work for me neither on the 6.4.2 nor on the > > > 6.5.(1|2) > > > version. I'll try 6.5.3 as well... > > > Any ideas why this is so? > > > > > > > Doesn't your query have any qualification(WHERE clause) about > > columns in index ? > > If there's no qualification,PostgreSQL optimizer chooses sequential > > scan(however current developing tree probably chooses Index scan > > in case of ORDER BY). > > In case of Index scan,"fetch backward .." after "move forward all" is > > possible after 6.5 , > > But in case of sequential scan,maybe it's still impossible. > > I have tested this - for a sequental scan it is still impossible (talking > 6.4.2 and 6.5.* versions) > > > I knew the way to fix it but am not sure now. > > Do you really want to make it possible ? > > It isn't an appropriate way to get last rows because "move forward > > all" takes very long time. > > > > Of course it is not an appropriate way to get the last rows like this. > The "right" way IMHO is to reverse the order in the query and to get the > first > rows. > > It is just for the sake of truth... > OK Attached is a patch. Could you apply to 6.5.3 ? Regards. Hiroshi Inoue Inoue@tpf.co.jp --------------F70D67F244A3031122613AAA Content-Type: text/plain; charset=iso-2022-jp; name="backcsr.diff" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="backcsr.diff" *** access/heap/heapam.c.orig Mon Aug 2 14:56:36 1999 --- access/heap/heapam.c Tue Nov 9 09:06:58 1999 *************** *** 833,842 **** ReleaseBuffer(scan->rs_pbuf); scan->rs_ptup.t_data = NULL; scan->rs_pbuf = InvalidBuffer; - if (BufferIsValid(scan->rs_nbuf)) - ReleaseBuffer(scan->rs_nbuf); - scan->rs_ntup.t_data = NULL; - scan->rs_nbuf = InvalidBuffer; return NULL; } --- 833,838 ---- *************** *** 915,924 **** ReleaseBuffer(scan->rs_nbuf); scan->rs_ntup.t_data = NULL; scan->rs_nbuf = InvalidBuffer; - if (BufferIsValid(scan->rs_pbuf)) - ReleaseBuffer(scan->rs_pbuf); - scan->rs_ptup.t_data = NULL; - scan->rs_pbuf = InvalidBuffer; HEAPDEBUG_6; /* heap_getnext returning EOS */ return NULL; } --- 911,916 ---- --------------F70D67F244A3031122613AAA--