Обсуждение: Backward reading
PostgreSQL allows "backward reading" tuples writing the tuple's length after and before the tuple proper, in case a 'randomAccess' is requested.
Is there any example of backward reading tuples into PostgreSQL code?
Thanks.
On Fri, 2008-02-01 at 19:25 +0100, mac_man2005@hotmail.it wrote: > PostgreSQL allows "backward reading" tuples writing the tuple's length > after and before the tuple proper, in case a 'randomAccess' is > requested. > > Is there any example of backward reading tuples into PostgreSQL code? Don't think so, but we don't always use randomAccess anyway. Sounds like we might be able to drop the length at the end of each tuple in those cases... -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
"Simon Riggs" <simon@2ndquadrant.com> writes: > On Fri, 2008-02-01 at 19:25 +0100, mac_man2005@hotmail.it wrote: >> PostgreSQL allows "backward reading" tuples writing the tuple's length >> after and before the tuple proper, in case a 'randomAccess' is >> requested. >> >> Is there any example of backward reading tuples into PostgreSQL code? > > Don't think so, but we don't always use randomAccess anyway. Sounds like > we might be able to drop the length at the end of each tuple in those > cases... We already do. We only generate the "frozen" tape when we think it might be necessary. I think the easiest (possibly only?) way to trigger this case is to run the query in a cursor like: postgres=# set enable_indexscan = off; SET postgres=# explain select * from h order by i; QUERY PLAN ----------------------------------------------------------------Sort (cost=61772.22..62022.20 rows=99994 width=512) SortKey: i -> Seq Scan on h (cost=0.00..7666.94 rows=99994 width=512) (3 rows) postgres=# begin; BEGIN postgres=# declare c cursor for select * from h order by i; DECLARE CURSOR postgres=# fetch 5 from c;i | r ---+------1 | 103522 | 150343 | 919044 | 890585 | 87001 (5 rows) postgres=# fetch backward 5 from c;i | r ---+------4 | 890583 | 919042 | 150341 | 10352 (4 rows) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!
-------------------------------------------------- From: "Gregory Stark" <stark@enterprisedb.com> Sent: Friday, February 01, 2008 10:31 PM To: "Simon Riggs" <simon@2ndquadrant.com> Cc: <mac_man2005@hotmail.it>; <pgsql-hackers@postgresql.org> Subject: Re: [HACKERS] Backward reading >>> Is there any example of backward reading tuples into PostgreSQL code? >> >> Don't think so, but we don't always use randomAccess anyway. Sounds like >> we might be able to drop the length at the end of each tuple in those >> cases... > > We already do. We only generate the "frozen" tape when we think it might > be > necessary. Thanks for your reply. I need to read tuples backward in order to rearrange runs on tapes in a different way than what Postres does now. Has that of "frozen tape" something to do with it? Regards, Manolo. > I think the easiest (possibly only?) way to trigger this case is to run > the > query in a cursor like: > > postgres=# set enable_indexscan = off; > SET > > postgres=# explain select * from h order by i; > QUERY PLAN > ---------------------------------------------------------------- > Sort (cost=61772.22..62022.20 rows=99994 width=512) > Sort Key: i > -> Seq Scan on h (cost=0.00..7666.94 rows=99994 width=512) > (3 rows) > > postgres=# begin; > BEGIN > > postgres=# declare c cursor for select * from h order by i; > DECLARE CURSOR > postgres=# fetch 5 from c; > i | r > ---+------ > 1 | 10352 > 2 | 15034 > 3 | 91904 > 4 | 89058 > 5 | 87001 > (5 rows) > > postgres=# fetch backward 5 from c; > i | r > ---+------ > 4 | 89058 > 3 | 91904 > 2 | 15034 > 1 | 10352 > (4 rows) > > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > Ask me about EnterpriseDB's RemoteDBA services! > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >