>> It is easily doable by having a AFTER INSERT OR UPDATE trigger or add a
>> field with CURRENT_TIMESTAMP as default. Though, of course,
>> this will only help you get the last COMMITTED change of the table.
>
>Yes and it won't work for deletes.
Well, a BEFORE DELETE trigger would.
>There is also a gotcha I have seen in the field, where people maintain a
>single table with one row per table and some
>sort of last changed field. Or even worse a SINGLE record with a last updated
>timestamp field per table.
>
>This results in a pure hotspot table (bottleneck) from a
>concurrency/throughput POV.
Sure it is possible to implement this in a way that effectively makes your
system serialized rather than concurrent, but that is avoidable by doing things
similar to what can be done for keeping counts in a table, e.g. have a table
LAST_UPDATED (ID INTEGER, TABLENAME VARCHAR(31), LAST_UPDATED TIMESTAMP), that
triggers insert into (never update) and that you regularly issue
DELETE FROM LAST_UPDATED L1
WHERE EXISTS(SELECT * FROM LAST_UPDATED L2
WHERE L1.TABLENAME = L2.TABLENAME AND
L1.LAST_UPDATED < L2.LAST_UPDATED).
Then running
SELECT TABLENAME, MAX(LAST_UPDATED)
FROM LAST_UPDATED
GROUP BY 1
would get you information about when a particular table was last updated
(excepting uncommitted changes). That would avoid the concurrency issue your
mentioning, but of course, it still affects the throughput and is not a viable
option for heavily loaded systems.
>This all would be a very nice use case for per-statement trigger instead of
>per-row trigger, which Firebird doesn't support, unfortunately.
I agree that per statement triggers could be a lot better in situations like
this, Thomas (provided DML is done in batches as opposed to having lots of
simultaneous users).
Set
