Hello Helmut,

> Milan, do I understand right that by "natural" update, you mean an
> update on all records of a table without restriction?
>

No, Milan was refering to my stored procedure suggestion.

In particular, the logic

select id
from tablea
order by id desc

He points out correctly, that if Firebird used the primary key index
with this query, it would slow it down, so Firebird would probably
choose a natural plan to get the ids (ie. read them off the disk in
whatever order they are physically stored).

> In fact, the example in my initial post (UPDATE MY_TABLE SET
> IX=IX+1) is a somewhat simplified presentation of our actual
> setting. In reality, the table holds arrays of records for a set of
> objects. Each object may contain any number of array items; the
> order of the array items of an object is relevant. The primary key
> is formed by the object key and the array index. We must be able to
> shift index ranges for deleting/inserting array items.
>

This is how I would do it. Feel free to comment or pick it to pieces
or modify it as appropriate.

MyArrayTable
(
ID (PK), <- Generated with no real world significance.
Object_Key,
ObjectArrayIndex,
OtherColumns
);

Create a non unique index on (Object_Key, ObjectArrayIndex). You
would expect this to have a selectivity of pretty close to 1 if not
exactly 1.

If I understand correctly, you are using a combination of Object_Key,
ObjectArrayIndex (which you call IX) as your primary key.

If your table looks as above, the update would suceed because there
is no database record requirement for ObjectArrayIndex to be unique.

UPDATE MyArrayTable
SET ObjectArrayIndex = ObjectArrayIndex + 1
WHERE Object_Key = 123456789
AND ObjectArrayIndex >= 27;

INSERT INTO MyArrayTable (...)
VALUES (GEN_ID(Gen_MyArrayTableID,1), 123456789, 27, .....);

Of course you might then have an issue with the insert statement in
MySQL, so you could instead implement the PK lookup in a before
insert trigger.

if (NEW.ID is NULL) then NEW.ID = etc.

In my (admittedly limited) multi-database support experience, it is
very difficult to find a single methodology that works well across
multiple different DBMS, particularily across a MGA and a non MGA
DBMS as you have here. FB will slow down MySQL in places, and MySQL
will force you to do things sequentially that FB allows to happen
simultaneously because of readers and writers blocking each other in
MySQL.

It is often best to let each DBMS fly in their areas of strength.

> Would you under these circumstances still prefer the double-update
> to the SP approach? (In our special case, there's another problem
> with stored procedures: our solution should work with
> Firebird/Interbase as well as with MySQL (via dbExpress). But we
> might consider a two-way solution, if there's a good reason for
> doing so.)

If not for the MySQL compatibility, I would use a SP to do it. You
can consider the suggested schema changes here if they are possible
and worthwhile, but if it is not too much effort, the SP will have
good performance if you create a descending index on the ID field.

Adam