Re: Updateable cursors

On Wed, 2007-01-24 at 02:42 +1100, FAST PostgreSQL wrote:

> In the UPDATE or DELETE statements the ‘WHERE CURRENT OF <cursor_name>’
> clause results in the cursor name being placed in the UpdateStmt or
> DeleteStmt structure. During the processing of the functions -
> transformDeleteStmt() and transformUpdateStmt() - the cursor name is used to
> obtain a pointer to the related Portal structure

To support prepared statements we'd need to do this name lookup just
once, so that the Update/Delete stmt can record which Portal to look at
for the current tuple.

> and the tuple affected by
> the current UPDATE or DELETE statement is extracted from the Portal, where it
> has been placed as the result of a previous FETCH request. At this point all
> the information for the UPDATE or DELETE statement is available so the
> statements can be transformed into standard UPDATE or DELETE statements and
> sent for re-write/planning/execution as usual.

> 2.5 Changes to the Executor
> -------------------------------
> There are various options that have been considered for this part of the
> enhancement. These are described in the sections below.

> Option 1 MVCC Via Continuous Searching of Database
>
> The Executor is to be changed in the following ways:
> 1) When the FETCH statement is executed the id of the resulting tuple is
> extracted and passed back to the Portal structure to be saved to indicate the
> cursor is currently positioned on a tuple.
> 2) When the UPDATE or DELETE request is executed the tuple id previously
> FETCHed is held in the QueryDesc structure so that it can be compared with
> the tuple ids returned from the TidScan node processed prior to the actual
> UPDATE / DELETE node in the plan. This enables a decision to be made as to
> whether the tuple held in the cursor is visible to the UPDATE / DELETE
> request according to the rules of concurrency. The result is that, at the
> cost of repeatedly searching the database at each UPDATE / DELETE command,
> the hash table is no longer required.
> This approach has the advantage that there is no hash table held in memory or
> on disk so it will not be memory intensive but will be processing intensive.

Do you have a specific example that would cause problems? It's much
easier to give examples that might cause problems and discuss those.

AFAICS in the straightforward case the Fetch will only return rows it
can see so update/delete should have no problems, iff the update/delete
is using a same or later snapshot than the cursor. I can see potential
problems with scrollable cursors.

So I'm not sure why there's a big need for any of the 5 options, yet.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com