Hi.
I thought the question I posted would have a easy answer... :)
Hugo
Em 11/03/2014 07:41, Svein Erling Tysvær escreveu:
>> 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
