Re: Updateable cursors

Hi Simon,

Thanks for your comments.

The reason for those 5 options is to consider different means to cover the
Prepared Stmt requirement where the different stages of processing are
actually in different transactions.

Regards,
John Bartlett
Software Development Engineer
Fujitsu Australia Software Technology
14 Rodborough Road, Frenchs Forest NSW 2086
Tel: +61 2 9452 9161
Fax: +61 2 9975 2899
Email: johnb(at)fast(dot)fujitsu(dot)com(dot)au
Web site: www.fastware.com

-----Original Message-----
From: pgsql-hackers-owner(at)postgresql(dot)org
[mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Simon Riggs
Sent: Tuesday, 23 January 2007 11:12 PM
To: FAST PostgreSQL
Cc: PostgreSQL-development
Subject: Re: [HACKERS] 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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN 27 003 693 481. It is confidential to the ordinary user of the email address to which it was addressed and may contain copyright and/or legally privileged information. No one else may read, print, store, copy or forward all or any of it or its attachments. If you receive this email in error, please return to sender. Thank you.

If you do not wish to receive commercial email messages from Fujitsu Australia Software Technology Pty Ltd, please email unsubscribe(at)fast(dot)fujitsu(dot)com(dot)au