Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pn3fe-0001Jp-Ap for pgsql-general@arkaria.postgresql.org; Thu, 13 Apr 2023 20:35:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pn3fc-0004za-4X for pgsql-general@arkaria.postgresql.org; Thu, 13 Apr 2023 20:35:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pn3fb-0004zR-Pf for pgsql-general@lists.postgresql.org; Thu, 13 Apr 2023 20:35:51 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pn3fY-002iHP-H5 for pgsql-general@lists.postgresql.org; Thu, 13 Apr 2023 20:35:51 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 33DKZkjW1380650; Thu, 13 Apr 2023 16:35:46 -0400 From: Tom Lane To: Bryn Llewellyn cc: pgsql-general list Subject: Re: Why does "fetch last from cur" always cause error 55000 for non-scrollable cursor? In-reply-to: <3DDA11AC-718C-4B1A-BC98-7FC25DED0259@yugabyte.com> References: <3DDA11AC-718C-4B1A-BC98-7FC25DED0259@yugabyte.com> Comments: In-reply-to Bryn Llewellyn message dated "Thu, 13 Apr 2023 12:52:56 -0700" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <1380648.1681418146.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Thu, 13 Apr 2023 16:35:46 -0400 Message-ID: <1380649.1681418146@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Bryn Llewellyn writes: > But why does "fetch last" fail here: > -- Test Two > start transaction; > declare cur no scroll cursor without hold for > select g.val as k, g.val*100 as v > from generate_series(1, 10) as g(val) > order by g.val; > fetch first from cur; > fetch last from cur; > rollback; FETCH LAST is implemented as "run forward to the end, then back up one". You could imagine adding more infrastructure to allow doing it without backup, but it'd be complicated (not least because the cursor's ending position would be subtly wrong). > Finally, I tried the same tests in PG 11.19. There, in Test One, the sec= ond "fetch first" succeeds (and gets the right result). But it has to scro= ll backwards to do this. I'm guessing that the Version 11 behavior was reg= arded as a bug=E2=80=94and was fixed. When did the behavior change here? Probably here: https://git.postgresql.org/gitweb/?p=3Dpostgresql.git&a=3Dcommitdiff&h=3Dc= 1b7a6c27 regards, tom lane