Re: ECPG FETCH readahead

From: Boszormenyi Zoltan <zb(at)cybertec(dot)at>
To: Noah Misch <noah(at)leadboat(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Michael Meskes <meskes(at)postgresql(dot)org>, PG Hackers <pgsql-hackers(at)postgresql(dot)org>, Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>, Bruce Momjian <bruce(at)momjian(dot)us>
Subject: Re: ECPG FETCH readahead
Date: 2012-03-29 22:48:07
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

2012-03-29 19:03 keltezéssel, Noah Misch írta:
> To me, it feels too magical to make READAHEAD 1 and READAHEAD 0 differ only in
> whether ECPGFETCHSZ can override. I'm willing to go with whatever consensus
> arises on this topic, though.

I now removed this distinction because the grammar does it so
the value 0 doesn't even reach ECPGopen().

>> This part is policy that can be debated and modified accordingly,
>> it doesn't affect the internals of the caching code.
> Agreed.
>>>> +</para>
>>>> +
>>>> +<para>
>>>> +<command>UPDATE</command> or<command>DELETE</command> with the
>>>> +<literal>WHERE CURRENT OF</literal> clause, cursor readahead may or may not
>>>> + actually improve performance, as<command>MOVE</command> statement has to be
>>>> + sent to the backend before the DML statement to ensure correct cursor position
>>>> + in the backend.
>>>> +</para>
>>> This sentence seems to be missing a word near its beginning.
>> Sounds like a corner case to me, but I am not a native english speaker.
>> Which one sounds better:
>> UPDATE or DELETE with the WHERE CURRENT OF clause...
>> or
>> UPDATE or DELETE statements with the WHERE CURRENT OF clause...
>> ?
> Here is the simplest change to make the original grammatical:
> Given an UPDATE or DELETE with the WHERE CURRENT OF clause, ...
> I might write the whole thing this way:
> To execute an UPDATE or DELETE statement bearing the WHERE CURRENT OF
> clause, ECPG may first execute an implicit MOVE to synchronize the server
> cursor position with the local cursor position. This can reduce or
> eliminate the performance benefit of readahead for affected cursors.

OK, I used your text.

>>> one of the new sections about readahead should somehow reference the hazard
>>> around volatile functions.
>> Done.
> I don't see the mention in your latest patch. You do mention it for the
> sqlerrd[2] compatibility stuff.

sqlerrd[2] compatibility stuff? I mentioned it in section "ecpg-sqlca", this is the main
documentation section, not the compatibility one AFAIK. Anyway, I now reference the volatile
function hazard in the first paragraphs added to section "ecpg-cursors".

> I can understand the desire to have a way to disable it. Perhaps you have a
> bunch of old Informix code, so you enable the global option and later discover
> some expensive cursors that don't use sqlerrd[2]. It would be nice to locally
> suppress the behavior for those cursors.
> Still, we're looking at dedicated ECPG syntax, quite visible even to folks
> with no interest in Informix. We have eschewed littering our syntax with
> compatibility aids, and I like it that way. IMO, an option to the "ecpg"
> preprocessor is an acceptable level of muddle to provide this aid. A DECLARE
> CURSOR syntax extension goes too far.

I have now deleted this grammar extension.

Attached is the new core feature patch. Summary of changes:
- documentation changes according to your query
- cursor.c doesn't distinguish between 0 and 1, this is the job of the grammar.
- I saved some MOVE statements in case the cursor position in the backend
is what we want at the moment. This means READAHEAD 1 is at the same level
of performance like without caching, there are no extra implicit MOVEs.
WHERE CURRENT OF also avoids MOVEs in this case. The caching code is now
clearly faster in case when there are a lot of MOVEs without actual FETCHes.
This is only an if () condition around the previously coded MOVE block in
ecpg_cursor_execute(). It doesn't change the semantics, only omits the
MOVE in case it's not needed.

I also refreshed the second patch that drives all cursors with the new
cursor functions. Because the runtime doesn't handle 0, 1 is the default
readahead window size. Now, NO READAHEAD would mean READAHEAD 1
but ECPGFETCHSZ can also override it. This means that NO READAHEAD
makes no sense, so it's removed from the grammar and the documentation
is changed accordingly.

Best regards,
Zoltán Böszörményi

Zoltán Böszörményi
Cybertec Schönig& Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria

Attachment Content-Type Size
ecpg-cursor-readahead-9.2-git-v14.patch.gz application/x-tar 38.1 KB
ecpg-cursor-readahead-all-cursors-v2.patch.gz application/x-tar 21.3 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2012-03-29 22:56:30 Re: Speed dblink using alternate libpq tuple storage
Previous Message Robert Haas 2012-03-29 22:16:44 Re: Command Triggers patch v18