Re: Common Table Expressions (WITH RECURSIVE) patch

2008/10/2 Greg Stark <greg(dot)stark(at)enterprisedb(dot)com>:
>
>
> On 2 Oct 2008, at 05:44 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> "Hitoshi Harada" <umi(dot)tanuki(at)gmail(dot)com> writes:
>>
>>> Hmm, I've looked over the patch. Logically window functions can access
>>> arbitrary rows that have been stored in a frame. Thus I had thought
>>> tuplestore should hold all the positions and allow arbitrary random
>>> access indicated by integer. Maybe those functionalities can be
>>> abstracted by the window function API itself. For this matter it seems
>>> that you'd better to look at my future patch.
>>
>> Well, the problem with defining it as "arbitrary" random access is that
>> there's no way for the tuplestore to throw away old data.
>
> And that there's no way to make it work if the tuplestore has spilled to
> disk.
>

In my purpose the "old data" can always be indicated by an integer row
position. So the real problem here is how you store all the row
positions for arbitrary random access closer to O(1). Yes, you can go
to certain row by fetching tuples manytimes from a marked row but it's
inefficient. I know my patch sent before is also inefficent. But
essentially the window function needs high cost.

I'll try to process my work with the patch but maybe more work on
tuplestore will be needed after we see the real problem that I don't
see now either.

Regards,

--
Hitoshi Harada