Updateable cursors patch
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
Attachments:
updateablecursor.patchtext/x-diff; charset=iso-8859-15; name=updateablecursor.patchDownload+520-154
On 4/4/07, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au> wrote:
Attached is a working updateable cursors patch. The core functionality has
been implemented and the patch also contains the regression tests and
documentation.
this one doesn't apply cleanly to HEAD because of the changes in
http://archives.postgresql.org/pgsql-committers/2007-04/msg00447.php
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
Right. I will send an updated patch against the CVS head in the next
couple of days.
Jaime Casanova wrote:
Show quoted text
On 4/4/07, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au> wrote:
Attached is a working updateable cursors patch. The core functionality
has
been implemented and the patch also contains the regression tests and
documentation.this one doesn't apply cleanly to HEAD because of the changes in
http://archives.postgresql.org/pgsql-committers/2007-04/msg00447.php
Attached is an updated version of the updateable cursors patch against
the latest cvs head.
Most of the changes in the patch are to make it sync with the changes in
CVS recently, for DECLARE CURSOR and EXPLAIN, as mentioned by Jaime in
his mail below.
Rgds,
Arul Shaji
FAST PostgreSQL wrote:
Show quoted text
Right. I will send an updated patch against the CVS head in the next
couple of days.Jaime Casanova wrote:
On 4/4/07, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au> wrote:
Attached is a working updateable cursors patch. The core
functionality has
been implemented and the patch also contains the regression tests and
documentation.this one doesn't apply cleanly to HEAD because of the changes in
http://archives.postgresql.org/pgsql-committers/2007-04/msg00447.php---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Attachments:
updateablecursor.patchtext/x-patch; name=updateablecursor.patchDownload+625-104
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 an updated version of the updateable cursors patch against
the latest cvs head.Most of the changes in the patch are to make it sync with the changes in
CVS recently, for DECLARE CURSOR and EXPLAIN, as mentioned by Jaime in
his mail below.Rgds,
Arul ShajiFAST PostgreSQL wrote:
Right. I will send an updated patch against the CVS head in the next
couple of days.Jaime Casanova wrote:
On 4/4/07, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au> wrote:
Attached is a working updateable cursors patch. The core
functionality has
been implemented and the patch also contains the regression tests and
documentation.this one doesn't apply cleanly to HEAD because of the changes in
http://archives.postgresql.org/pgsql-committers/2007-04/msg00447.php---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
--
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. +
Hello
I am not sure, but your solution will faill on scrollable cursors
(it's similar to holdable cursors). I miss part about limits in
documentation. Propably updatable cursors aren't supported by plpgsql
(and it's point to ToDo).
Regards
Pavel Stehule
2007/5/17, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au>:
Show quoted text
Attached is an updated version of the updateable cursors patch against
the latest cvs head.Most of the changes in the patch are to make it sync with the changes in
CVS recently, for DECLARE CURSOR and EXPLAIN, as mentioned by Jaime in
his mail below.Rgds,
Arul ShajiFAST PostgreSQL wrote:
Right. I will send an updated patch against the CVS head in the next
couple of days.Jaime Casanova wrote:
On 4/4/07, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au> wrote:
Attached is a working updateable cursors patch. The core
functionality has
been implemented and the patch also contains the regression tests and
documentation.this one doesn't apply cleanly to HEAD because of the changes in
http://archives.postgresql.org/pgsql-committers/2007-04/msg00447.php---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
No. It works with scrollable cursors. It will work for cursors/selects
which does not put the results in some store, such as WITH hold/group
by/order by etc.... But most of these restrictions apply for normal
'Select for update' anyway. (With the order by clause, the
implementation is as per the sql standards.)
I can update the documentation once the initial review is done and what
I have done gets atleast a pass mark :-)
Rgds,
Arul Shaji
Pavel Stehule wrote:
Show quoted text
Hello
I am not sure, but your solution will faill on scrollable cursors
(it's similar to holdable cursors). I miss part about limits in
documentation. Propably updatable cursors aren't supported by plpgsql
(and it's point to ToDo).Regards
Pavel Stehule2007/5/17, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au>:
Attached is an updated version of the updateable cursors patch against
the latest cvs head.Most of the changes in the patch are to make it sync with the changes in
CVS recently, for DECLARE CURSOR and EXPLAIN, as mentioned by Jaime in
his mail below.Rgds,
Arul ShajiFAST PostgreSQL wrote:
Right. I will send an updated patch against the CVS head in the next
couple of days.Jaime Casanova wrote:
On 4/4/07, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au> wrote:
Attached is a working updateable cursors patch. The core
functionality has
been implemented and the patch also contains the regression tests and
documentation.this one doesn't apply cleanly to HEAD because of the changes in
http://archives.postgresql.org/pgsql-committers/2007-04/msg00447.php---------------------------(end of
broadcast)---------------------------
TIP 6: explain analyze is your friend
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
On 5/17/07, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au> wrote:
No. It works with scrollable cursors. It will work for cursors/selects
which does not put the results in some store, such as WITH hold/group
by/order by etc.... But most of these restrictions apply for normal
'Select for update' anyway. (With the order by clause, the
implementation is as per the sql standards.)
your patch doesn't work with updatable views because they don't have
ctid columns....
ERROR: column "ctid" does not exist
STATEMENT: update vfoo set des_cta = des_cta || ' - prueba' where
current of foo;
ERROR: current transaction is aborted, commands ignored until end of
transaction block
is this sane behavior? to accept create cursors for update on views
and then failing to update "where current of" and rollback the entire
transaction?
comments?
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
Attachments:
On 5/17/07, Jaime Casanova <systemguards@gmail.com> wrote:
On 5/17/07, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au> wrote:
No. It works with scrollable cursors. It will work for cursors/selects
which does not put the results in some store, such as WITH hold/group
by/order by etc.... But most of these restrictions apply for normal
'Select for update' anyway. (With the order by clause, the
implementation is as per the sql standards.)your patch doesn't work with updatable views because they don't have
ctid columns....ERROR: column "ctid" does not exist
STATEMENT: update vfoo set des_cta = des_cta || ' - prueba' where
current of foo;
ERROR: current transaction is aborted, commands ignored until end of
transaction blockis this sane behavior? to accept create cursors for update on views
and then failing to update "where current of" and rollback the entire
transaction?comments?
sorry, reattaching the test script
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
Attachments:
2007/5/18, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au>:
No. It works with scrollable cursors. It will work for cursors/selects
which does not put the results in some store, such as WITH hold/group
by/order by etc.... But most of these restrictions apply for normal
'Select for update' anyway. (With the order by clause, the
implementation is as per the sql standards.)
some scrollable cursors are materialised. It depends on query :-(.
Simple query without join can works.
regards
Pavel
Show quoted text
I can update the documentation once the initial review is done and what
I have done gets atleast a pass mark :-)Rgds,
Arul ShajiPavel Stehule wrote:
Hello
I am not sure, but your solution will faill on scrollable cursors
(it's similar to holdable cursors). I miss part about limits in
documentation. Propably updatable cursors aren't supported by plpgsql
(and it's point to ToDo).Regards
Pavel Stehule2007/5/17, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au>:
Attached is an updated version of the updateable cursors patch against
the latest cvs head.Most of the changes in the patch are to make it sync with the changes in
CVS recently, for DECLARE CURSOR and EXPLAIN, as mentioned by Jaime in
his mail below.Rgds,
Arul ShajiFAST PostgreSQL wrote:
Right. I will send an updated patch against the CVS head in the next
couple of days.Jaime Casanova wrote:
On 4/4/07, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au> wrote:
Attached is a working updateable cursors patch. The core
functionality has
been implemented and the patch also contains the regression tests and
documentation.this one doesn't apply cleanly to HEAD because of the changes in
http://archives.postgresql.org/pgsql-committers/2007-04/msg00447.php---------------------------(end of
broadcast)---------------------------
TIP 6: explain analyze is your friend
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Right. The current implementation allows only simple queries. Joins are
disallowed. According to the standard, updateable cursors cannot be
scrollable. So maybe I should put an explicit check during cursor
creation disallowing scrollable updateable cursors.
Rgds,
Arul Shaji
Pavel Stehule wrote:
Show quoted text
2007/5/18, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au>:
No. It works with scrollable cursors. It will work for cursors/selects
which does not put the results in some store, such as WITH hold/group
by/order by etc.... But most of these restrictions apply for normal
'Select for update' anyway. (With the order by clause, the
implementation is as per the sql standards.)some scrollable cursors are materialised. It depends on query :-(.
Simple query without join can works.regards
Pavel
I can update the documentation once the initial review is done and what
I have done gets atleast a pass mark :-)Rgds,
Arul ShajiPavel Stehule wrote:
Hello
I am not sure, but your solution will faill on scrollable cursors
(it's similar to holdable cursors). I miss part about limits in
documentation. Propably updatable cursors aren't supported by plpgsql
(and it's point to ToDo).Regards
Pavel Stehule2007/5/17, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au>:
Attached is an updated version of the updateable cursors patch against
the latest cvs head.Most of the changes in the patch are to make it sync with the
changes in
CVS recently, for DECLARE CURSOR and EXPLAIN, as mentioned by Jaime in
his mail below.Rgds,
Arul ShajiFAST PostgreSQL wrote:
Right. I will send an updated patch against the CVS head in the next
couple of days.Jaime Casanova wrote:
On 4/4/07, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au> wrote:
Attached is a working updateable cursors patch. The core
functionality has
been implemented and the patch also contains the regressiontests and
documentation.
this one doesn't apply cleanly to HEAD because of the changes in
http://archives.postgresql.org/pgsql-committers/2007-04/msg00447.php
---------------------------(end of
broadcast)---------------------------
TIP 6: explain analyze is your friend
---------------------------(end of
broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
---------------------------(end of
broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Correction.... Meant to say
According to the standard, updateable cursors cannot be scrollable until
we have full cursor update.
FAST PostgreSQL wrote:
Show quoted text
Right. The current implementation allows only simple queries. Joins are
disallowed. According to the standard, updateable cursors cannot be
scrollable. So maybe I should put an explicit check during cursor
creation disallowing scrollable updateable cursors.Rgds,
Arul ShajiPavel Stehule wrote:
2007/5/18, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au>:
No. It works with scrollable cursors. It will work for cursors/selects
which does not put the results in some store, such as WITH hold/group
by/order by etc.... But most of these restrictions apply for normal
'Select for update' anyway. (With the order by clause, the
implementation is as per the sql standards.)some scrollable cursors are materialised. It depends on query :-(.
Simple query without join can works.regards
Pavel
I can update the documentation once the initial review is done and what
I have done gets atleast a pass mark :-)Rgds,
Arul ShajiPavel Stehule wrote:
Hello
I am not sure, but your solution will faill on scrollable cursors
(it's similar to holdable cursors). I miss part about limits in
documentation. Propably updatable cursors aren't supported by plpgsql
(and it's point to ToDo).Regards
Pavel Stehule2007/5/17, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au>:
Attached is an updated version of the updateable cursors patch
against
the latest cvs head.
Most of the changes in the patch are to make it sync with the
changes in
CVS recently, for DECLARE CURSOR and EXPLAIN, as mentioned by
Jaime in
his mail below.
Rgds,
Arul ShajiFAST PostgreSQL wrote:
Right. I will send an updated patch against the CVS head in the
next
couple of days.
Jaime Casanova wrote:
On 4/4/07, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au> wrote:
Attached is a working updateable cursors patch. The core
functionality has
been implemented and the patch also contains the regressiontests and
documentation.
this one doesn't apply cleanly to HEAD because of the changes in
http://archives.postgresql.org/pgsql-committers/2007-04/msg00447.php
---------------------------(end of
broadcast)---------------------------
TIP 6: explain analyze is your friend
---------------------------(end of
broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
---------------------------(end of
broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
2007/5/18, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au>:
Right. The current implementation allows only simple queries. Joins are
disallowed. According to the standard, updateable cursors cannot be
scrollable. So maybe I should put an explicit check during cursor
creation disallowing scrollable updateable cursors.
I am for it. It good protection before strange bugs
Pavel
Show quoted text
Rgds,
Arul ShajiPavel Stehule wrote:
2007/5/18, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au>:
No. It works with scrollable cursors. It will work for cursors/selects
which does not put the results in some store, such as WITH hold/group
by/order by etc.... But most of these restrictions apply for normal
'Select for update' anyway. (With the order by clause, the
implementation is as per the sql standards.)some scrollable cursors are materialised. It depends on query :-(.
Simple query without join can works.regards
Pavel
I can update the documentation once the initial review is done and what
I have done gets atleast a pass mark :-)Rgds,
Arul ShajiPavel Stehule wrote:
Hello
I am not sure, but your solution will faill on scrollable cursors
(it's similar to holdable cursors). I miss part about limits in
documentation. Propably updatable cursors aren't supported by plpgsql
(and it's point to ToDo).Regards
Pavel Stehule2007/5/17, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au>:
Attached is an updated version of the updateable cursors patch against
the latest cvs head.Most of the changes in the patch are to make it sync with the
changes in
CVS recently, for DECLARE CURSOR and EXPLAIN, as mentioned by Jaime in
his mail below.Rgds,
Arul ShajiFAST PostgreSQL wrote:
Right. I will send an updated patch against the CVS head in the next
couple of days.Jaime Casanova wrote:
On 4/4/07, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au> wrote:
Attached is a working updateable cursors patch. The core
functionality has
been implemented and the patch also contains the regressiontests and
documentation.
this one doesn't apply cleanly to HEAD because of the changes in
http://archives.postgresql.org/pgsql-committers/2007-04/msg00447.php
---------------------------(end of
broadcast)---------------------------
TIP 6: explain analyze is your friend
---------------------------(end of
broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
---------------------------(end of
broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
On 5/17/07, Jaime Casanova <systemguards@gmail.com> wrote:
On 5/17/07, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au> wrote:
No. It works with scrollable cursors. It will work for cursors/selects
which does not put the results in some store, such as WITH hold/group
by/order by etc.... But most of these restrictions apply for normal
'Select for update' anyway. (With the order by clause, the
implementation is as per the sql standards.)your patch doesn't work with updatable views because they don't have
ctid columns....ERROR: column "ctid" does not exist
STATEMENT: update vfoo set des_cta = des_cta || ' - prueba' where
current of foo;
ERROR: current transaction is aborted, commands ignored until end of
transaction blockis this sane behavior? to accept create cursors for update on views
and then failing to update "where current of" and rollback the entire
transaction?comments?
maybe just send a better error message
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
maybe just send a better error message
I do the transformation of a where current of clause into where clause
at the analyze stage itself(which is the right place to do. I think).
Hence this error. If I can postpone this transformation until after
re-write then the usual error will be thrown if there are no rules. It
is easily doable. But I want to confirm if this will break any other
part. I am looking into it now.
Rgds,
Arul Shaji
Jaime Casanova wrote:
Show quoted text
On 5/17/07, Jaime Casanova <systemguards@gmail.com> wrote:
On 5/17/07, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au> wrote:
No. It works with scrollable cursors. It will work for cursors/selects
which does not put the results in some store, such as WITH hold/group
by/order by etc.... But most of these restrictions apply for normal
'Select for update' anyway. (With the order by clause, the
implementation is as per the sql standards.)your patch doesn't work with updatable views because they don't have
ctid columns....ERROR: column "ctid" does not exist
STATEMENT: update vfoo set des_cta = des_cta || ' - prueba' where
current of foo;
ERROR: current transaction is aborted, commands ignored until end of
transaction blockis this sane behavior? to accept create cursors for update on views
and then failing to update "where current of" and rollback the entire
transaction?comments?
maybe just send a better error message