From: Noah Misch <noah@leadboat.com>
To: Boszormenyi Zoltan <zb@cybertec.at>
Cc: Michael Meskes <meskes@postgresql.org>
Cc: PG Hackers <pgsql-hackers@postgresql.org>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
Cc: Bruce Momjian <bruce@momjian.us>
Subject: Re: ECPG FETCH readahead
Date: Tue, 6 Mar 2012 06:06:58 -0500
Message-ID: <20120306110658.GC15988@tornado.leadboat.com> (raw)
In-Reply-To: <4F55A9AD.6050600@cybertec.at>
References: <4C231F9E.6000102@enterprisedb.com>
<20100624121922.GD24137@feivel.credativ.lan>
<AANLkTinij9ULnS54zKsdAJnhvuj-07-8-T-D=Nwk6f14@mail.gmail.com>
<4CB6D3C8.3020709@cybertec.at>
<4EC41434.7010603@cybertec.at>
<4EFC36EF.1060308@cybertec.at>
<20120302164105.GD23100@tornado.leadboat.com>
<4F538B4C.1030605@cybertec.at>
<20120305185632.GE13348@tornado.leadboat.com>
<4F55A9AD.6050600@cybertec.at>
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: noah@leadboat.com, zb@cybertec.at, meskes@postgresql.org, robertmhaas@gmail.com, heikki.linnakangas@enterprisedb.com, bruce@momjian.us
Subject: Re: ECPG FETCH readahead
In-Reply-To: <20120306110658.GC15988@tornado.leadboat.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox