Re: 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(at)hotmail(dot)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(at)2ndquadrant(dot)com> writes:

> On Fri, 2008-02-01 at 19:25 +0100, mac_man2005(at)hotmail(dot)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)
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!

--------------------------------------------------
From: "Gregory Stark" <stark(at)enterprisedb(dot)com>
Sent: Friday, February 01, 2008 10:31 PM
To: "Simon Riggs" <simon(at)2ndquadrant(dot)com>
Cc: <mac_man2005(at)hotmail(dot)it>; <pgsql-hackers(at)postgresql(dot)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
>