Hi.

I thought the question I posted would have a easy answer... :)

Hugo

 

>> 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

-- 


Atenciosamente,

Hugo Eyng