[Maria-developers] bb-10.2-compatibility - Updatable cursor
Hello Alexander, Can you add a task for updatable cursor (for positioned update and delete). Regards, Jérôme.
Hello Alexander, Nice to hear you again. You can find description here : https://mariadb.com/kb/en/sql-99/positioned-update-statement/ https://mariadb.com/kb/en/sql-99/positioned-delete-statement/ I attached an example (positioned_update.sql). It's a useful feature to update or delete rows in a table without unique key and lock all records when cursor is open. Regards, Jérôme.
-----Message d'origine----- De : Alexander Barkov [mailto:bar@mariadb.org] Envoyé : vendredi 10 mars 2017 11:24 À : jerome brauge Cc : MariaDB Developers (maria-developers@lists.launchpad.net) Objet : Re: bb-10.2-compatibility - Updatable cursor
Hello Jerome,
can you please give examples?
Thanks!
On 03/08/2017 07:33 PM, jerome brauge wrote:
Hello Alexander,
Can you add a task for updatable cursor (for positioned update and delete).
Regards,
Jérôme.
Hello Jerome, On 03/10/2017 05:21 PM, jerome brauge wrote:
Hello Alexander, Nice to hear you again. You can find description here : https://mariadb.com/kb/en/sql-99/positioned-update-statement/ https://mariadb.com/kb/en/sql-99/positioned-delete-statement/
I attached an example (positioned_update.sql). It's a useful feature to update or delete rows in a table without unique key and lock all records when cursor is open.
I can add tasks, but I'm afraid this is something not doable in short terms. MariaDB cursors are materialized. I.e. a temporary table is created and populated during OPEN cursor, so FETCH is actually done from this temporary table rather than the original table. If the table does not have a unique key, then we have troubles with "WHERE CURRENT OF" referring the original table. Implementing "WHERE CURRENT OF" seems to need adding of non-materialized cursors first.
Regards, Jérôme.
-----Message d'origine----- De : Alexander Barkov [mailto:bar@mariadb.org] Envoyé : vendredi 10 mars 2017 11:24 À : jerome brauge Cc : MariaDB Developers (maria-developers@lists.launchpad.net) Objet : Re: bb-10.2-compatibility - Updatable cursor
Hello Jerome,
can you please give examples?
Thanks!
On 03/08/2017 07:33 PM, jerome brauge wrote:
Hello Alexander,
Can you add a task for updatable cursor (for positioned update and delete).
Regards,
Jérôme.
Hello Alexander, I feared something like that. Comment of class Select_materialize indicate : /** Select_materialize -- a mediator between a cursor query and the protocol. In case we were not able to open a non-materialzed cursor, it creates an internal temporary HEAP table, and insert all rows into it. When the table reaches max_heap_table_size, it's converted to a MyISAM table. Later this table is used to create a Materialized_cursor. */ Perhaps that it never able to open non-materialzed cursor. In any case, I suppose it is not easy to implement. In addition on Oracle, in the "for update of" clause, we can specify each table on which we want hold locks. Exemple : CURSOR fall_jobs_cur IS SELECT w.task, w.expected_hours, w.tools_required, w.do_it_yourself_flag FROM winterize w, husband_config hc WHERE year = TO_CHAR (SYSDATE, 'YYYY') FOR UPDATE OF husband_config.max_procrastination_allowed; In this example, lock are hold only on table husband_config. But, like I am a lucky man, I only have 4 procedures in 17100 that use cursors with "current of " ... So I would change them. Thanks. Jérôme.
-----Message d'origine----- De : Alexander Barkov [mailto:bar@mariadb.org] Envoyé : mardi 14 mars 2017 05:33 À : jerome brauge Cc : MariaDB Developers (maria-developers@lists.launchpad.net) Objet : Re: bb-10.2-compatibility - Updatable cursor
Hello Jerome,
On 03/10/2017 05:21 PM, jerome brauge wrote:
Hello Alexander, Nice to hear you again. You can find description here : https://mariadb.com/kb/en/sql-99/positioned-update-statement/ https://mariadb.com/kb/en/sql-99/positioned-delete-statement/
I attached an example (positioned_update.sql). It's a useful feature to update or delete rows in a table without unique key and lock all records when cursor is open.
I can add tasks, but I'm afraid this is something not doable in short terms.
MariaDB cursors are materialized. I.e. a temporary table is created and populated during OPEN cursor, so FETCH is actually done from this temporary table rather than the original table.
If the table does not have a unique key, then we have troubles with "WHERE CURRENT OF" referring the original table.
Implementing "WHERE CURRENT OF" seems to need adding of non- materialized cursors first.
Regards, Jérôme.
-----Message d'origine----- De : Alexander Barkov [mailto:bar@mariadb.org] Envoyé : vendredi 10 mars 2017 11:24 À : jerome brauge Cc : MariaDB Developers (maria-developers@lists.launchpad.net) Objet : Re: bb-10.2-compatibility - Updatable cursor
Hello Jerome,
can you please give examples?
Thanks!
On 03/08/2017 07:33 PM, jerome brauge wrote:
Hello Alexander,
Can you add a task for updatable cursor (for positioned update and delete).
Regards,
Jérôme.
Hello Jerome, On 03/14/2017 01:18 PM, jerome brauge wrote:
Hello Alexander, I feared something like that.
Comment of class Select_materialize indicate : /** Select_materialize -- a mediator between a cursor query and the protocol. In case we were not able to open a non-materialzed cursor, it creates an internal temporary HEAP table, and insert all rows into it. When the table reaches max_heap_table_size, it's converted to a MyISAM table. Later this table is used to create a Materialized_cursor. */
Perhaps that it never able to open non-materialzed cursor. In any case, I suppose it is not easy to implement.
In addition on Oracle, in the "for update of" clause, we can specify each table on which we want hold locks. Exemple : CURSOR fall_jobs_cur IS SELECT w.task, w.expected_hours, w.tools_required, w.do_it_yourself_flag FROM winterize w, husband_config hc WHERE year = TO_CHAR (SYSDATE, 'YYYY') FOR UPDATE OF husband_config.max_procrastination_allowed;
In this example, lock are hold only on table husband_config.
But, like I am a lucky man, I only have 4 procedures in 17100 that use cursors with "current of " ... So I would change them.
Thanks for your clarification! Right, in this case it's easier to fix the 4 procedures.
Thanks. Jérôme.
-----Message d'origine----- De : Alexander Barkov [mailto:bar@mariadb.org] Envoyé : mardi 14 mars 2017 05:33 À : jerome brauge Cc : MariaDB Developers (maria-developers@lists.launchpad.net) Objet : Re: bb-10.2-compatibility - Updatable cursor
Hello Jerome,
On 03/10/2017 05:21 PM, jerome brauge wrote:
Hello Alexander, Nice to hear you again. You can find description here : https://mariadb.com/kb/en/sql-99/positioned-update-statement/ https://mariadb.com/kb/en/sql-99/positioned-delete-statement/
I attached an example (positioned_update.sql). It's a useful feature to update or delete rows in a table without unique key and lock all records when cursor is open.
I can add tasks, but I'm afraid this is something not doable in short terms.
MariaDB cursors are materialized. I.e. a temporary table is created and populated during OPEN cursor, so FETCH is actually done from this temporary table rather than the original table.
If the table does not have a unique key, then we have troubles with "WHERE CURRENT OF" referring the original table.
Implementing "WHERE CURRENT OF" seems to need adding of non- materialized cursors first.
Regards, Jérôme.
-----Message d'origine----- De : Alexander Barkov [mailto:bar@mariadb.org] Envoyé : vendredi 10 mars 2017 11:24 À : jerome brauge Cc : MariaDB Developers (maria-developers@lists.launchpad.net) Objet : Re: bb-10.2-compatibility - Updatable cursor
Hello Jerome,
can you please give examples?
Thanks!
On 03/08/2017 07:33 PM, jerome brauge wrote:
Hello Alexander,
Can you add a task for updatable cursor (for positioned update and delete).
Regards,
Jérôme.
participants (2)
-
Alexander Barkov -
jerome brauge