|Re: [firebird-support] Table information
I thought the question I posted would have a easy answer... :)
>> 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).
SELECT TABLENAME, MAX(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).
-- Atenciosamente, Hugo Eyng