Thread: How to get last 10 rows in a table on a large database?
Hi, Does anyone have a good idea to get the last 10 rows from a table? I tried SELECT * FROM table ORDER BY datetime DESC LIMIT 10 But my table has over 1 million rows, so this takes forever.. anyone has a better idea on how to do this? Alex -- Alex van Es, P.O. Box 698, 7300 AR Apeldoorn, The Netherlands. 8 Webcams, doorbell, fridge, barcodescanner.. all connected to the internet. Check http://www.icepick.com for details.. NEW! A Hyperwired cat.. http://www.blackie.com!
Hi. You should try this:begin transaction; declare tbl_cur cursor for select * from table order by datetime ASC; fetchforward 10 in tbl_cur;end transaction; This is true if you want THE LAST 10 rows from the selection - you order it in reverse and get the first 10. You are unable to say:begin transaction; declare tbl_cur cursor for .... ..... moveforward all in tbl_cur; fetch backward 10 in tbl_cur; ...........end transaction; because when you "move forward all" the result gets lost. If something is unclear, do write back... Stoyan Genov
Alex@Icepick.com (Alex) writes:
> Does anyone have a good idea to get the last 10 rows from a table? I
> tried
> SELECT * FROM table ORDER BY datetime DESC LIMIT 10
> But my table has over 1 million rows, so this takes forever.. anyone
> has a better idea on how to do this?
FWIW, that query should work the way you want in 7.0 (assuming you have
an index on datetime). 6.5.* isn't bright enough to use an index for
ORDER BY DESC, though. I think Stoyan's suggestion of declaring a
cursor is probably the best workaround for now --- or you could consult
the patches list archives for the patch that will make 6.5 use an index.
regards, tom lane
Stoyan Genov <genov@digsys.bg> writes:
> 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.
However, the "move" is a tad slow since the silly thing actually scans
over all the tuples in index order :-(. This might not be a useful
solution for Alex's problem after all, because of the time taken by
the "move".
regards, tom lane
On Sat, 6 Nov 1999, Tom Lane wrote: > Date: Sat, 06 Nov 1999 13:12:27 -0500 > From: Tom Lane <tgl@sss.pgh.pa.us> > To: Alex@Icepick.com > Cc: pgsql-sql@postgreSQL.org > Subject: Re: [SQL] How to get last 10 rows in a table on a large database? > > Alex@Icepick.com (Alex) writes: > > Does anyone have a good idea to get the last 10 rows from a table? I > > tried > > SELECT * FROM table ORDER BY datetime DESC LIMIT 10 > > But my table has over 1 million rows, so this takes forever.. anyone > > has a better idea on how to do this? > > FWIW, that query should work the way you want in 7.0 (assuming you have > an index on datetime). 6.5.* isn't bright enough to use an index for > ORDER BY DESC, though. I think Stoyan's suggestion of declaring a > cursor is probably the best workaround for now --- or you could consult > the patches list archives for the patch that will make 6.5 use an index. I attached patch for 6.5.3 Hiroshi kindly made on my request. It applies cleanly and seems works ok. Oleg > > regards, tom lane > > ************ > _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 *** backend/optimizer/plan/planner.c.org Mon Aug 2 15:27:02 1999 --- backend/optimizer/plan/planner.c Wed Nov 3 10:04:41 1999 *************** *** 39,45 **** static Plan *make_groupplan(List *group_tlist, bool tuplePerGroup, List *groupClause, AttrNumber*grpColIdx, Plan *subplan); ! static bool need_sortplan(List *sortcls, Plan *plan); static Plan *make_sortplan(List *tlist, List *sortcls, Plan *plannode); /***************************************************************************** --- 39,45 ---- static Plan *make_groupplan(List *group_tlist, bool tuplePerGroup, List *groupClause, AttrNumber*grpColIdx, Plan *subplan); ! static ScanDirection get_dir_to_omit_sortplan(List *sortcls, Plan *plan); static Plan *make_sortplan(List *tlist, List*sortcls, Plan *plannode); /***************************************************************************** *************** *** 303,310 **** } else { ! if (parse->sortClause && need_sortplan(parse->sortClause, result_plan)) ! return (make_sortplan(tlist, parse->sortClause, result_plan)); else return ((Plan *) result_plan); } --- 303,319 ---- } else { ! if (parse->sortClause) ! { ! ScanDirection dir = get_dir_to_omit_sortplan(parse->sortClause, result_plan); ! if (ScanDirectionIsNoMovement(dir)) ! return (make_sortplan(tlist, parse->sortClause, result_plan)); ! else ! { ! ((IndexScan *)result_plan)->indxorderdir = dir; ! return ((Plan *) result_plan); ! } ! } else return ((Plan *) result_plan); } *************** *** 822,828 **** /* ---------- ! * Support function for need_sortplan * ---------- */ static TargetEntry * --- 831,837 ---- /* ---------- ! * Support function for get scan direction to omit sortplan * ---------- */ static TargetEntry * *************** *** 845,855 **** * Check if a user requested ORDER BY is already satisfied by * the choosen index scan. * ! * Returns TRUE if sort is required, FALSE if can be omitted. * ---------- */ ! static bool ! need_sortplan(List *sortcls, Plan *plan) { Relation indexRel; IndexScan *indexScan; --- 854,866 ---- * Check if a user requested ORDER BY is already satisfied by * the choosen index scan. * ! * Returns the direction of Index scan to omit sort, ! * if sort is required returns NoMovementScanDirection ! * * ---------- */ ! static ScanDirection ! get_dir_to_omit_sortplan(List *sortcls, Plan *plan) { Relation indexRel; IndexScan *indexScan; *************** *** 858,870 **** HeapTuple htup; Form_pg_index index_tup; int key_no = 0; /* ---------- * Must be an IndexScan * ---------- */ if (nodeTag(plan) != T_IndexScan) ! return TRUE; indexScan = (IndexScan *) plan; --- 869,883 ---- HeapTuple htup; Form_pg_index index_tup; int key_no = 0; + ScanDirection dir, nodir = NoMovementScanDirection; + dir = nodir; /* ---------- * Must be an IndexScan * ---------- */ if (nodeTag(plan) != T_IndexScan) ! return nodir; indexScan = (IndexScan *) plan; *************** *** 873,888 **** * ---------- */ if (plan->lefttree != NULL) ! return TRUE; if (plan->righttree != NULL) ! return TRUE; /* ---------- * Must be a single index scan * ---------- */ if (length(indexScan->indxid)!= 1) ! return TRUE; /* ---------- * Indices can only have up to 8 attributes. So an ORDER BY using --- 886,901 ---- * ---------- */ if (plan->lefttree != NULL) ! return nodir; if (plan->righttree != NULL) ! return nodir; /* ---------- * Must be a single index scan * ---------- */ if (length(indexScan->indxid)!= 1) ! return nodir; /* ---------- * Indices can only have up to 8 attributes. So an ORDER BY using *************** *** 890,896 **** * ---------- */ if (length(sortcls) > 8) ! return TRUE; /* ---------- * The choosen Index must be a btree --- 903,909 ---- * ---------- */ if (length(sortcls) > 8) ! return nodir; /* ---------- * The choosen Index must be a btree *************** *** 902,908 **** if (strcmp(nameout(&(indexRel->rd_am->amname)), "btree") != 0) { heap_close(indexRel); ! return TRUE; } heap_close(indexRel); --- 915,921 ---- if (strcmp(nameout(&(indexRel->rd_am->amname)), "btree") != 0) { heap_close(indexRel); ! return nodir; } heap_close(indexRel); *************** *** 937,943 **** * Could this happen? * ---------- */ ! return TRUE; } if (nodeTag(tle->expr) != T_Var) { --- 950,956 ---- * Could this happen? * ---------- */ ! return nodir; } if (nodeTag(tle->expr) != T_Var) { *************** *** 946,952 **** * cannot be the indexed attribute * ---------- */ ! return TRUE; } var = (Var *) (tle->expr); --- 959,965 ---- * cannot be the indexed attribute * ---------- */ ! return nodir; } var = (Var *) (tle->expr); *************** *** 957,963 **** * that of the index * ---------- */ ! return TRUE; } if (var->varattno != index_tup->indkey[key_no]) --- 970,976 ---- * that of the index * ---------- */ ! return nodir; } if (var->varattno != index_tup->indkey[key_no]) *************** *** 966,972 **** * It isn't the indexed attribute. * ---------- */ ! return TRUE; } if (oprid(oper("<", resdom->restype, resdom->restype, FALSE)) != sortcl->opoid) --- 979,985 ---- * It isn't the indexed attribute. * ---------- */ ! return nodir; } if (oprid(oper("<", resdom->restype, resdom->restype, FALSE)) != sortcl->opoid) *************** *** 975,981 **** * Sort order isn't in ascending order. * ---------- */ ! return TRUE; } key_no++; --- 988,1006 ---- * Sort order isn't in ascending order. * ---------- */ ! if (ScanDirectionIsForward(dir)) ! return nodir; ! dir = BackwardScanDirection; ! } ! else ! { ! /* ---------- ! * Sort order is in ascending order. ! * ---------- ! */ ! if (ScanDirectionIsBackward(dir)) ! return nodir; ! dir = ForwardScanDirection; } key_no++; *************** *** 985,989 **** * Index matches ORDER BY - sort not required * ---------- */ ! return FALSE; } --- 1010,1014 ---- * Index matches ORDER BY - sort not required * ---------- */ ! return dir; } *** backend/executor/nodeIndexscan.c.org Wed May 26 01:08:43 1999 --- backend/executor/nodeIndexscan.c Wed Nov 3 10:04:41 1999 *************** *** 105,110 **** --- 105,117 ---- */ estate = node->scan.plan.state; direction = estate->es_direction; + if (ScanDirectionIsBackward(node->indxorderdir)) + { + if (ScanDirectionIsForward(direction)) + direction = BackwardScanDirection; + else if (ScanDirectionIsBackward(direction)) + direction = ForwardScanDirection; + } snapshot = estate->es_snapshot; scanstate = node->scan.scanstate; indexstate = node->indxstate; *************** *** 322,327 **** --- 329,336 ---- indxqual = node->indxqual; numScanKeys = indexstate->iss_NumScanKeys; indexstate->iss_IndexPtr= -1; + if (ScanDirectionIsBackward(node->indxorderdir)) + indexstate->iss_IndexPtr = numIndices; /* If this is re-scanning of PlanQual ... */ if (estate->es_evTuple!= NULL && *************** *** 972,977 **** --- 981,988 ---- } indexstate->iss_NumIndices = numIndices; + if (ScanDirectionIsBackward(node->indxorderdir)) + indexPtr = numIndices; indexstate->iss_IndexPtr = indexPtr; indexstate->iss_ScanKeys = scanKeys; indexstate->iss_NumScanKeys= numScanKeys; *** backend/optimizer/plan/createplan.c.org Thu Jul 29 12:34:11 1999 --- backend/optimizer/plan/createplan.c Wed Nov 3 10:04:41 1999 *************** *** 1040,1045 **** --- 1040,1046 ---- node->indxid = indxid; node->indxqual = indxqual; node->indxqualorig = indxqualorig; + node->indxorderdir = NoMovementScanDirection; node->scan.scanstate = (CommonScanState *) NULL; return node; *** backend/nodes/copyfuncs.c.org Wed May 26 07:41:11 1999 --- backend/nodes/copyfuncs.c Wed Nov 3 10:04:41 1999 *************** *** 251,256 **** --- 251,257 ---- newnode->indxid = listCopy(from->indxid); Node_Copy(from, newnode, indxqual); Node_Copy(from,newnode, indxqualorig); + newnode->indxorderdir = from->indxorderdir; return newnode; } *** backend/nodes/readfuncs.c.org Wed May 26 01:09:11 1999 --- backend/nodes/readfuncs.c Wed Nov 3 10:04:41 1999 *************** *** 546,551 **** --- 546,556 ---- token = lsptok(NULL, &length); /* eat :indxqualorig */ local_node->indxqualorig = nodeRead(true); /* now read it */ + token = lsptok(NULL, &length); /* eat :indxorderdir */ + token = lsptok(NULL, &length); /* get indxorderdir */ + + local_node->indxorderdir = atoi(token); + return local_node; } *** backend/nodes/outfuncs.c.org Wed May 26 07:41:14 1999 --- backend/nodes/outfuncs.c Wed Nov 3 10:04:41 1999 *************** *** 450,455 **** --- 450,456 ---- appendStringInfo(str, " :indxqualorig "); _outNode(str, node->indxqualorig); + appendStringInfo(str, " :indxorderdir %d ", node->indxorderdir); } /* *** backend/nodes/equalfuncs.c.org Thu Jul 29 12:34:11 1999 --- backend/nodes/equalfuncs.c Wed Nov 3 10:04:41 1999 *************** *** 527,532 **** --- 527,535 ---- if (a->scan.scanrelid != b->scan.scanrelid) return false; + if (a->indxorderdir != b->indxorderdir) + return false; + if (!equali(a->indxid, b->indxid)) return false; return true; *** backend/commands/explain.c.org Mon Aug 2 14:56:58 1999 --- backend/commands/explain.c Wed Nov 3 10:04:41 1999 *************** *** 200,205 **** --- 200,207 ---- switch (nodeTag(plan)) { case T_IndexScan: + if (ScanDirectionIsBackward(((IndexScan *)plan)->indxorderdir)) + appendStringInfo(str, " Backward"); appendStringInfo(str, " using "); i = 0; foreach(l, ((IndexScan *) plan)->indxid) *** include/nodes/plannodes.h.org Wed May 26 07:42:58 1999 --- include/nodes/plannodes.h Wed Nov 3 10:04:41 1999 *************** *** 175,180 **** --- 175,181 ---- List *indxid; List *indxqual; List *indxqualorig; + ScanDirection indxorderdir; IndexScanState *indxstate; } IndexScan;
Move forward all (Was Re: How to get last 10 rows in a table on a large database?)
From
Stoyan Genov
Date:
> > 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? Regards,Stoyan Genov
RE: [SQL] Move forward all (Was Re: How to get last 10 rows in a table on a large database?)
From
"Hiroshi Inoue"
Date:
> -----Original Message----- > From: owner-pgsql-sql@postgreSQL.org [mailto:owner-pgsql-sql@postgreSQL. > org]On Behalf Of Stoyan Genov > Sent: Sunday, November 07, 1999 7:25 PM > To: Tom Lane > Cc: pgsql-sql@postgreSQL.org > Subject: [SQL] Move forward all (Was Re: How to get last 10 rows in a > table on a large database?) > > > > > 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 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. Regards. Hiroshi Inoue Inoue@tpf.co.jp
Re: [SQL] Move forward all (Was Re: How to get last 10 rows in a table on a large database?)
From
Stoyan Genov
Date:
> > -----Original Message----- > > From: owner-pgsql-sql@postgreSQL.org [mailto:owner-pgsql-sql@postgreSQL. > > org]On Behalf Of Stoyan Genov > > Sent: Sunday, November 07, 1999 7:25 PM > > To: Tom Lane > > Cc: pgsql-sql@postgreSQL.org > > Subject: [SQL] Move forward all (Was Re: How to get last 10 rows in a > > table on a large database?) > > > > > > > > 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... > Regards. > > Hiroshi Inoue > Inoue@tpf.co.jp Regards, Stoyan Genov
Re: [SQL] Move forward all (Was Re: How to get last 10 rows in a table on a large database?)
From
Hiroshi Inoue
Date:
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
*** 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 ----
RE: [SQL] Move forward all (Was Re: How to get last 10 rows in a table on a large database?)
From
"Hiroshi Inoue"
Date:
> >
> > > 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 ?
>
Sorry the patch is not right for 6.5.3 .
Please apply the following patch to 6.5.3 .
Regards.
Hiroshi Inoue
Inoue@tpf.co.jp
*** access/heap/heapam.c.orig Mon Aug 2 14:56:36 1999
--- access/heap/heapam.c Tue Nov 9 12:59:48 1999
***************
*** 775,782 **** if (scan->rs_ptup.t_data == scan->rs_ctup.t_data &&
BufferIsInvalid(scan->rs_pbuf)) {
- if (BufferIsValid(scan->rs_nbuf))
- ReleaseBuffer(scan->rs_nbuf); return NULL; }
--- 775,780 ----
***************
*** 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; }
--- 831,836 ----
***************
*** 855,862 **** if (scan->rs_ctup.t_data == scan->rs_ntup.t_data &&
BufferIsInvalid(scan->rs_nbuf)) {
- if (BufferIsValid(scan->rs_pbuf))
- ReleaseBuffer(scan->rs_pbuf); HEAPDEBUG_3; /* heap_getnext returns NULL at end */
return NULL; }
--- 849,854 ----
***************
*** 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; }
--- 907,912 ----
Re: [SQL] Move forward all (Was Re: How to get last 10 rows in a table on a large database?)
From
Stoyan Genov
Date:
> > >
> > > > 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 ?
> >
>
> Sorry the patch is not right for 6.5.3 .
> Please apply the following patch to 6.5.3 .
>
> Regards.
>
> Hiroshi Inoue
> Inoue@tpf.co.jp
>
> *** access/heap/heapam.c.orig Mon Aug 2 14:56:36 1999
> --- access/heap/heapam.c Tue Nov 9 12:59:48 1999
> ***************
> *** 775,782 ****
> if (scan->rs_ptup.t_data == scan->rs_ctup.t_data &&
> BufferIsInvalid(scan->rs_pbuf))
> {
> - if (BufferIsValid(scan->rs_nbuf))
> - ReleaseBuffer(scan->rs_nbuf);
> return NULL;
> }
>
> --- 775,780 ----
> ***************
> *** 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;
> }
>
> --- 831,836 ----
> ***************
> *** 855,862 ****
> if (scan->rs_ctup.t_data == scan->rs_ntup.t_data &&
> BufferIsInvalid(scan->rs_nbuf))
> {
> - if (BufferIsValid(scan->rs_pbuf))
> - ReleaseBuffer(scan->rs_pbuf);
> HEAPDEBUG_3; /* heap_getnext returns NULL at end */
> return NULL;
> }
> --- 849,854 ----
> ***************
> *** 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;
> }
> --- 907,912 ----
>
>
>
> ************
>
I have applied the patch.
Now postgres 6.5.3 works fine (FETCH BACKWARD fetches
result after MOVE FORWARD ALL/FETCH FORWARD ALL).
Thank you very much.
Regards,
Stoyan Genov