we are trying to migrate from firebird v2.5.9 to v5.0.2 (classic, windows and linux) but noticed one critical issue with bulk updates which are stopping us from upgrading
bulk update is executed to change one field, trigger updates other field in other records of the same table, and then bulk update overwrites those new values with old ones, for fields which should not be updated by outer bulk update at all. Changes done by trigger are silently lost in firebird v5, backwards compatibility is broken (see simplified example testBulkUpdate_after_update.txt)
although when updating records individually (with multiple update statements, rather than single bulk update statement) everything works as expected, all updates done by trigger are intact. Such inconsistency when bulk update started to work differently than individual updates in firebird v5 is also bad
depending on the order in which bulk update processes individual records, and on which records trigger updates, some changes done by trigger may stay, and some may be lost (bulk updates usually don't have 'order by' clause, thus order is not guaranteed by firebird). Such randomness is also bad, our tests in house may run fine, but in production it may start working incorrectly
Both MS SQL Server and PostgreSQL work like firebird v2.5 does. Behavior of firebird v5 is unique, I was not able to find leading RDBMS with such behavior (oracle and mariaDB errors out at runtime, that allows to find places to rework, but I believe firebird can do much better than that)
we had a discussion in firebird support forum, more info if needed could be found there. I was told that this behavior change was introduced while fixing infinite insert-select issue (core92 or #417). Unfortunately I was not provided an example which particular bulk update issue it fixed. Assuming there were no issues with bulk updates in firebird v2.5, and this insert-select fix accidentally broke bulk updates, would it be possible to remove that fix from bulk updates?
otherwise, if there were some issues with bulk updates, would it be possible to review them and improve the fix so that it fixes those issues but does not break bulk update behavior described here? if that fix can not be improved, then maybe new firebird.conf option could be implemented to allow users to restore legacy behavior? (similar to ReadConsistency which we also needed to make read committed to become read committed again)
Thanks much,
Paulius (GLDS)
we are trying to migrate from firebird v2.5.9 to v5.0.2 (classic, windows and linux) but noticed one critical issue with bulk updates which are stopping us from upgrading
bulk update is executed to change one field, trigger updates other field in other records of the same table, and then bulk update overwrites those new values with old ones, for fields which should not be updated by outer bulk update at all. Changes done by trigger are silently lost in firebird v5, backwards compatibility is broken (see simplified example testBulkUpdate_after_update.txt)
although when updating records individually (with multiple update statements, rather than single bulk update statement) everything works as expected, all updates done by trigger are intact. Such inconsistency when bulk update started to work differently than individual updates in firebird v5 is also bad
depending on the order in which bulk update processes individual records, and on which records trigger updates, some changes done by trigger may stay, and some may be lost (bulk updates usually don't have 'order by' clause, thus order is not guaranteed by firebird). Such randomness is also bad, our tests in house may run fine, but in production it may start working incorrectly
Both MS SQL Server and PostgreSQL work like firebird v2.5 does. Behavior of firebird v5 is unique, I was not able to find leading RDBMS with such behavior (oracle and mariaDB errors out at runtime, that allows to find places to rework, but I believe firebird can do much better than that)
we had a discussion in firebird support forum, more info if needed could be found there. I was told that this behavior change was introduced while fixing infinite insert-select issue (core92 or #417). Unfortunately I was not provided an example which particular bulk update issue it fixed. Assuming there were no issues with bulk updates in firebird v2.5, and this insert-select fix accidentally broke bulk updates, would it be possible to remove that fix from bulk updates?
otherwise, if there were some issues with bulk updates, would it be possible to review them and improve the fix so that it fixes those issues but does not break bulk update behavior described here? if that fix can not be improved, then maybe new firebird.conf option could be implemented to allow users to restore legacy behavior? (similar to ReadConsistency which we also needed to make read committed to become read committed again)
Thanks much,
Paulius (GLDS)