Re: [PATCHES] Updateable cursors patch
> > 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: On 5/17/07, Jaime Casanova <[EMAIL PROTECTED]> wrote: On 5/17/07, FAST PostgreSQL <[EMAIL PROTECTED]> 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? maybe just send a better error message ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Updateable cursors patch
On 5/17/07, Jaime Casanova <[EMAIL PROTECTED]> wrote: On 5/17/07, FAST PostgreSQL <[EMAIL PROTECTED]> 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? 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 ---(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
Re: [PATCHES] Updateable cursors patch
2007/5/18, FAST PostgreSQL <[EMAIL PROTECTED]>: 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 Rgds, Arul Shaji Pavel Stehule wrote: > 2007/5/18, FAST PostgreSQL <[EMAIL PROTECTED]>: >> 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 Shaji >> >> >> Pavel 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 Stehule >> > >> > 2007/5/17, FAST PostgreSQL <[EMAIL PROTECTED]>: >> >> 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: >> >> > 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 <[EMAIL PROTECTED]> 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 >> >> >> >> http://www.postgresql.org/about/donate >> >> >> >> >> >> >> > >> > ---(end of >> broadcast)--- >> > TIP 3: Have you checked our extensive FAQ? >> > >> > http://www.postgresql.org/docs/faq >> > >> > >> >> >> ---(end of broadcast)--- >> TIP 1: if posting/reading through Usenet, please send an appropriate >>subscribe-nomail command to [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Updateable cursors patch
Correction Meant to say According to the standard, updateable cursors cannot be scrollable until we have full cursor update. FAST PostgreSQL wrote: 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: 2007/5/18, FAST PostgreSQL <[EMAIL PROTECTED]>: 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 Shaji Pavel 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 Stehule > > 2007/5/17, FAST PostgreSQL <[EMAIL PROTECTED]>: >> 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: >> > 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 <[EMAIL PROTECTED]> 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 >> >> http://www.postgresql.org/about/donate >> >> >> > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] Updateable cursors patch
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: 2007/5/18, FAST PostgreSQL <[EMAIL PROTECTED]>: 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 Shaji Pavel 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 Stehule > > 2007/5/17, FAST PostgreSQL <[EMAIL PROTECTED]>: >> 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: >> > 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 <[EMAIL PROTECTED]> 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 >> >> http://www.postgresql.org/about/donate >> >> >> > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] 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
Re: [PATCHES] Updateable cursors patch
2007/5/18, FAST PostgreSQL <[EMAIL PROTECTED]>: 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 Shaji Pavel 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 Stehule > > 2007/5/17, FAST PostgreSQL <[EMAIL PROTECTED]>: >> 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: >> > 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 <[EMAIL PROTECTED]> 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 >> >> http://www.postgresql.org/about/donate >> >> >> > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] 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
Re: [PATCHES] Updateable cursors patch
On 5/17/07, Jaime Casanova <[EMAIL PROTECTED]> wrote: On 5/17/07, FAST PostgreSQL <[EMAIL PROTECTED]> 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? 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 tests.sql Description: Binary data ---(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
Re: [PATCHES] Updateable cursors patch
On 5/17/07, FAST PostgreSQL <[EMAIL PROTECTED]> 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 tests.sql Description: Binary data ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Updateable cursors patch
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: 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 <[EMAIL PROTECTED]>: 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: > 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 <[EMAIL PROTECTED]> 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 http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Updateable cursors patch
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 <[EMAIL PROTECTED]>: 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: > 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 <[EMAIL PROTECTED]> 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 http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Updateable cursors patch
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 Shaji > > > FAST 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 <[EMAIL PROTECTED]> 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 > > http://www.postgresql.org/about/donate -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Updateable cursors patch
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: 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 <[EMAIL PROTECTED]> 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 *** /home/arul/ospg/pgsql/src/backend/parser/gram.y 2007-05-03 18:59:44.0 +1000 --- pgbase1705/src/backend/parser/gram.y 2007-05-17 14:41:13.0 +1000 *** *** 297,303 %type columnDef %type def_elem old_aggr_elem %type def_arg columnElem where_clause ! a_expr b_expr c_expr func_expr AexprConst indirection_el columnref in_expr having_clause func_table array_expr %type row type_list array_expr_list %type case_expr case_arg when_clause case_default --- 297,303 %type columnDef %type def_elem old_aggr_elem %type def_arg columnElem where_clause ! a_expr b_expr c_expr func_expr AexprConst indirection_el where_current_clause columnref in_expr having_clause func_table array_expr %type row type_list array_expr_list %type case_expr case_arg when_clause case_default *** *** 377,383 CLUSTER COALESCE COLLATE COLUMN COMMENT COMMIT COMMITTED CONCURRENTLY CONNECTION CONSTRAINT CONSTRAINTS CONTENT_P CONVERSION_P CONVERT COPY COST CREATE CREATEDB ! CREATEROLE CREATEUSER CROSS CSV CURRENT_DATE CURRENT_ROLE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS --- 377,383 CLUSTER COALESCE COLLATE COLUMN COMMENT COMMIT COMMITTED CONCURRENTLY CONNECTION CONSTRAINT CONSTRAINTS CONTENT_P CONVERSION_P CONVERT COPY COST CREATE CREATEDB ! CREATEROLE CREATEUSER CROSS CSV CURRENT CURRENT_DATE CURRENT_ROLE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS *** *** 5715,5721 */ DeleteStmt: DELETE_P FROM relation_expr_opt_alias ! using_clause where_clause returning_clause { DeleteStmt *n = makeNode(DeleteStmt); n->relation = $3; --- 5715,5721 */ DeleteStmt: DELETE_P FROM relation_expr_opt_alias ! using_clause where_current_clause returning_clause { DeleteStmt *n = makeNode(DeleteStmt); n->relation = $3; *** *** 5771,5777 UpdateStmt: UPDATE relation_expr_opt_alias SET set_clause_list from_clause ! where_clause returning_clause { UpdateStmt *n = makeNode(UpdateStmt); --- 5771,5777 UpdateStmt: UPDATE relation_expr_opt_alias SET set_clause_list from_clause ! where_current_clause returning_clause { UpdateStmt *n = makeNode(UpdateStmt); *** *** 5859,5864 --- 5859,5867 /* currently we always set FAST_PLAN option */ n->options = $3 | $5 | CURSOR_OPT_FAST_PLAN; n->query = $7; + if($7 && ((SelectStmt *)$7)->lockingClause) + if (((LockingClause *)((SelectStmt *)$7)->lockingClause)->forUpdate) + n->options |= CURSOR_OPT_FORUPDATE; $$ = (Node *)n; } ; *** *** 6563,6568 --- 6566,6581 ; + where_current_clause: + WHERE a_expr { $$ = $2; } + | WHERE CURRENT OF name + { + $$ = (Node *) makeString($4); + } + | /*EMPTY*/{ $$ = NULL; } + ; + + TableFuncElementList: TableFuncElement { *** *** 9120,9125 --- 9133,9139 | COLUMN | CONSTRAINT | CREATE + | CURRENT | CURRENT_DATE | CURRENT_ROLE | CURRENT_TIME *** /home/arul/ospg/pgsql/src/backend/parser/keywords.c 2007-05-03 18:59:44.0 +1000 --- pgbase1705/src/backend/parser/keywords.c 2007-05-17 14:44:05.0 +1000 *** *** 101,106 --- 101,107 {"createuser", CREATEUSER}, {"cross", CROSS}, {"csv", CSV}, + {"current", CURRENT}, {"current_date", CURRENT_DATE}, {"current_role", CURRENT_ROLE}, {"current_time", CURRENT_TIME}, *** /home/arul/ospg/pgsql/src/backend/parser/analyze.c 2007-05-03 18:59:42.0 +1000 --- pgbase1705/src/backend/parser/analyze.c 2007-05-17 16:39:20.0 +1000 *
Re: [PATCHES] Updateable cursors patch
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 <[EMAIL PROTECTED]> 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
Re: [PATCHES] Updateable cursors patch
On 4/4/07, FAST PostgreSQL <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Updateable cursors patch
On Mon, 2 Apr 2007 03:15, Simon Riggs wrote: > Cool patch. > > On Wed, 2007-04-04 at 18:36 +, 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Updateable cursors patch
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 <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Updateable cursors patch
Cool patch. On Wed, 2007-04-04 at 18:36 +, 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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate