maria-developers team mailing list archive

[jerome brauge <j.brauge@xxxxxxxxxxx>]

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@xxxxxxxxxxx]
> Envoyé : mardi 14 mars 2017 05:33
> À : jerome brauge
> Cc : MariaDB Developers (maria-developers@xxxxxxxxxxxxxxxxxxx)
> 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@xxxxxxxxxxx] Envoyé : vendredi 10
> >> mars 2017 11:24 À : jerome brauge Cc : MariaDB Developers
> >> (maria-developers@xxxxxxxxxxxxxxxxxxx)
> >> 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.
> >>>