Re: Updateable cursors patch
Cool patch.
On Wed, 2007-04-04 at 18:36 +0000, FAST PostgreSQL wrote:
The planner has to be taught to treat a DELETE/UPDATE WHERE CURRENT OF
as a TidScan. Currently it follows the sequential scan route and
extracts the current tuple based on the cursor position from the
portal.
So you let the planner take a SeqScan, then override this at the top of
the executor? So if we EXPLAIN this it would say "SeqScan", but doesn't
actually do that? It works, but you're right it should do this the same
way as other plan types.
ISTM you need to add a special case in set_plain_rel_pathlist() in
optimizer/paths/allpaths.c similar to the special case for
relation_excluded_by_constraints(). In the case of an updateable cursor
there is only one path we want it to take, so a shortcut out is
appropriate, so the code would look similar-ish. Then you need to teach
the tidscan node to handle the case for an updateable cursor, i.e. a
call similar to GetCursorSlot() in TidNext() in nodeTidscan.c. That way
the rest of the executor machinery can get the slot for you.
Do we need additional code in any of the clients to handle this new
functionality correctly? ECPG etc?
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
Import Notes
Reply to msg id not found: 21558.10861175333802.fast.fujitsu.com.au@MHSReference msg id not found: 21558.10861175333802.fast.fujitsu.com.au@MHS
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
---------------------------------------------------------------------------
FAST PostgreSQL wrote:
Attached is a working updateable cursors patch. The core functionality has
been implemented and the patch also contains the regression tests and
documentation.However there are still a couple of TODOs that needs to be done. The planner
has to be taught to treat a DELETE/UPDATE WHERE CURRENT OF as a TidScan.
Currently it follows the sequential scan route and extracts the current tuple
based on the cursor position from the portal. Also, an issue that needs to
be looked at is how to treat joins with an updateable cursor. Currently it is
allowed and when it comes to delete or update, an error message is thrown
from the executor.Rgds,
Arul Shaji
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Import Notes
Reply to msg id not found: 21558.10861175333802.fast.fujitsu.com.au@MHS | Resolved by subject fallback
On Mon, 2 Apr 2007 03:15, Simon Riggs wrote:
Cool patch.
On Wed, 2007-04-04 at 18:36 +0000, FAST PostgreSQL wrote:
The planner has to be taught to treat a DELETE/UPDATE WHERE CURRENT OF
as a TidScan. Currently it follows the sequential scan route and
extracts the current tuple based on the cursor position from the
portal.So you let the planner take a SeqScan, then override this at the top of
the executor? So if we EXPLAIN this it would say "SeqScan", but doesn't
actually do that? It works, but you're right it should do this the same
way as other plan types.
Yeah, currently EXPLAIN will give the wrong details. That's not ideal of
course. We will be fixing that soon. Thanks for the code tips.
ISTM you need to add a special case in set_plain_rel_pathlist() in
optimizer/paths/allpaths.c similar to the special case for
relation_excluded_by_constraints(). In the case of an updateable cursor
there is only one path we want it to take, so a shortcut out is
appropriate, so the code would look similar-ish. Then you need to teach
the tidscan node to handle the case for an updateable cursor, i.e. a
call similar to GetCursorSlot() in TidNext() in nodeTidscan.c. That way
the rest of the executor machinery can get the slot for you.Do we need additional code in any of the clients to handle this new
functionality correctly? ECPG etc?
Psql's tab completion is one that quickly comes to mind. Along with the above
said fix, I can do this. Don't know too much about other clients....
Rgds,
Arul Shaji