Re: ECPG FETCH readahead

On Tue, Mar 6, 2012 at 6:06 AM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> On Tue, Mar 06, 2012 at 07:07:41AM +0100, Boszormenyi Zoltan wrote:
>> 2012-03-05 19:56 keltez?ssel, Noah Misch ?rta:
>> >> Or how about a new feature in the backend, so ECPG can do
>> >>     UPDATE/DELETE ... WHERE OFFSET N OF cursor
>> >> and the offset of computed from the actual cursor position and the position known
>> >> by the application? This way an app can do readahead and do work on rows collected
>> >> by the cursor with WHERE CURRENT OF which gets converted to WHERE OFFSET OF
>> >> behind the scenes.
>> > That's a neat idea, but I would expect obstacles threatening our ability to
>> > use it automatically for readahead.  You would have to make the cursor a
>> > SCROLL cursor.  We'll often pass a negative offset, making the operation fail
>> > if the cursor query used FOR UPDATE.  Volatile functions in the query will get
>> > more calls.  That's assuming the operation will map internally to something
>> > like MOVE N; UPDATE ... WHERE CURRENT OF; MOVE -N.  You might come up with
>> > innovations to mitigate those obstacles, but those innovations would probably
>> > also apply to MOVE/FETCH.  In any event, this would constitute a substantive
>> > patch in its own right.
>>
>> I was thinking along the lines of a Portal keeping the ItemPointerData
>> for each tuple in the last FETCH statement. The WHERE OFFSET N OF cursor
>> would treat the offset value relative to the tuple order returned by FETCH.
>> So, OFFSET 0 OF == CURRENT OF and other values of N are negative.
>> This way, it doesn't matter if the cursor is SCROLL, NO SCROLL or have
>>  the default behaviour with "SCROLL in some cases". Then ECPGopen()
>> doesn't have to play games with the DECLARE statement. Only ECPGfetch()
>> needs to play with MOVE statements, passing different offsets to the backend,
>> not what the application passed.
>
> That broad approach sounds promising.  The main other consideration that comes
> to mind is a plan to limit resource usage for a cursor that reads, say, 1B
> rows.  However, I think attempting to implement this now will significantly
> decrease the chance of getting the core patch features committed now.
>
>> > One way out of trouble here is to make WHERE CURRENT OF imply READHEAD
>> > 1/READHEAD 0 (incidentally, perhaps those two should be synonyms) on the
>> > affected cursor.  If the cursor has some other readahead quantity declared
>> > explicitly, throw an error during preprocessing.
>>
>> I played with this idea a while ago, from a different point of view.
>> If the ECPG code had the DECLARE mycur, DML ... WHERE CURRENT OF mycur
>> and OPEN mycur in exactly this order, i.e. WHERE CURRENT OF appears in
>> a standalone function between DECLARE and the first OPEN for the cursor,
>> then ECPG disabled readahead automatically for that cursor and for that
>> cursor only. But this requires effort on the user of ECPG and can be very
>> fragile. Code cleanup with reordering functions can break previously
>> working code.
>
> Don't the same challenges apply to accurately reporting an error when the user
> specifies WHERE CURRENT OF for a readahead cursor?

I think we need either an updated version of this patch that's ready
for commit real soon now, or we need to postpone it to 9.3.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company