Re: Detect INSERT vs REPLACE in BEFORE INSERT trigger
Hi Alex,
May I assume that it's also not quite as trivial as doing something like this:
CONSTRAINT `valid_phone` CHECK (`phoneno` regexp '^0[1-8][0-9]{8}$') Correct. It does its own conflict detection as UNIQUE keys can't represent it.
I wasn't thinking that the CONSTRAINT needs to be UNIQUE, the above is a CHECK ... which could potentially use a stored function to perform the check ... timing could then possibly again be an issue depending on whether the function will still see the old data or not (https://mariadb.com/kb/en/constraint/#check-constraints has some info, but testing will be required). "One can use most deterministic functions in a constraint, including UDFs <https://mariadb.com/kb/en/user-defined-functions/>." Kind regards, Jaco
On vrijdag 3 mei 2024 12:27:49 CEST Jaco Kroon via discuss wrote:
May I assume that it's also not quite as trivial as doing something like this:
CONSTRAINT `valid_phone` CHECK (`phoneno` regexp '^0[1-8][0-9]{8}$')
Correct. It does its own conflict detection as UNIQUE keys can't represent it.
I wasn't thinking that the CONSTRAINT needs to be UNIQUE, the above is a CHECK ... which could potentially use a stored function to perform the check ...
IIRC with MariaDB, it is not possible to query the table within a CHECK CONSTRAINT. What the trigger should do in pseudocode: IF EXISTS (SELECT * FROM {horrifying conditions} ) THEN IF {IS_INSERT} OR {further validation for REPLACE} THEN SIGNAL SQLSTATE '45000' MESSAGE_TEXT = 'Row is invalid...'; END IF; END IF; -- Alex Hermann
Hi Alex, On 2024/05/03 12:49, Alex Hermann via discuss wrote:
On vrijdag 3 mei 2024 12:27:49 CEST Jaco Kroon via discuss wrote:
May I assume that it's also not quite as trivial as doing something like this:
CONSTRAINT `valid_phone` CHECK (`phoneno` regexp '^0[1-8][0-9]{8}$') Correct. It does its own conflict detection as UNIQUE keys can't represent it. I wasn't thinking that the CONSTRAINT needs to be UNIQUE, the above is a CHECK ... which could potentially use a stored function to perform the check ... IIRC with MariaDB, it is not possible to query the table within a CHECK CONSTRAINT. That would actually make sense.
What the trigger should do in pseudocode:
IF EXISTS (SELECT * FROM {horrifying conditions} ) THEN IF {IS_INSERT} OR {further validation for REPLACE} THEN SIGNAL SQLSTATE '45000' MESSAGE_TEXT = 'Row is invalid...'; END IF; END IF;
This is ~ what I understood. I can't find a way to determine the {IS_INSERT} above, however (from https://mariadb.com/kb/en/trigger-overview/): "If a trigger contains an error and the engine is transactional, or it is a BEFORE trigger, the trigger will not run, and will prevent the original statement from running as well. If the engine is non-transactional, and it is an AFTER trigger, the trigger will not run, but the original statement will." So under the assumption that you're using transactional tables here ... is it possible to verify AFTER INSERT? In other words, instead of testing if the statement will violate the constraints, rather check afterwards if the constraints has been violated, and if so, roll back. Depending on frequency of OK vs NOT OK updates the performance could be horrendous overall though ... but if we assume that the bulk of queries won't be problematic this should probably be OK. Probably not helpful, so sorry if I'm just causing noise, I find this an extremely interesting problem. Kind regards, Jaco
On vrijdag 3 mei 2024 13:25:10 CEST Jaco Kroon via discuss wrote:
On 2024/05/03 12:49, Alex Hermann via discuss wrote:
On vrijdag 3 mei 2024 12:27:49 CEST Jaco Kroon via discuss wrote:
This is ~ what I understood. I can't find a way to determine the {IS_INSERT} above, however (from http://url7640.wenlex.nl/ls/click?upn=u001.MdwzzSnEbdLo8Gnan-2FT5Ljt0NIym6Wi...
"If a trigger contains an error and the engine is transactional, or it is a BEFORE trigger, the trigger will not run, and will prevent the original statement from running as well. If the engine is non-transactional, and it is an AFTER trigger, the trigger will not run, but the original statement will."
So under the assumption that you're using transactional tables here ... is it possible to verify AFTER INSERT? In other words, instead of testing if the statement will violate the constraints, rather check afterwards if the constraints has been violated, and if so, roll back.
Interesting idea, I'll have to dive into that. Though my first gut feeling says it will have the same problem: Was the row inserted or replaced?
Probably not helpful, so sorry if I'm just causing noise, I find this an extremely interesting problem.
Suggestions are always welcome. What I forgot to mention is that a REPLACE may only succeed if the new data is not in cnflict with _other_ rows. I.e. either no conflict at all OR only in conflict with the row it replaces. An INSERT must fail on any conflict. Writing this down does trigger an idea which would even make INSERT .. ON DUPLCIATE KEY UPDATE .. possible, but I doubt I haven't already tried that. -- Alex Hermann
participants (2)
-
Alex Hermann -
Jaco Kroon