maria-developers team mailing list archive
Mailing list archive
Re: bb-10.2-compatibility - Updatable cursor
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.
CURSOR fall_jobs_cur IS
SELECT w.task, w.expected_hours,
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.
> -----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.