maria-developers team mailing list archive

[Alexander Barkov <bar@xxxxxxxxxxx>]

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@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.
>>>>>