catmaid / CATMAID / 6693 / 1
45%
master: 45%

LAST BUILD BRANCH: dev
DEFAULT BRANCH: master
Ran
Files 106
Run time 7s
Badge
Embed ▾
README BADGES
x

If you need to use a raster PNG badge, change the '.svg' to '.png' in the link

Markdown

Textile

RDoc

HTML

Rst
coverage: 44.948%. Remained the same
6693.1

push

travis-ci

tomka 
History tracking: make update/delete trigger more efficient

This database migration modifies the trigger setup of the history
tracking system of CATMAID. If history tracking is enabled, so far any
UPDATE and DELETE statement would execute a trigger function for every
touched row that would insert its old version into the table's history
table. This trigger function would also update the modified row's
transaction ID with a reference to the current transaction.

The latter part hast to happen for each row, because Postgres will not
allow us to modify updated rows (to store the transaction ID) in a
statement-level trigger. However, the first part, moving the old data to
the history table can happen on a per-statement basis, replacing N
INSERT statements with a single on. In larger setups this makes a
significant difference.

As an example, consider a well tuned server with most the database's
active set sitting in memory and a treenode table with ~800,000,000
treenodes in which we split off a fragment of ~9,000 nodes from a bigger
skeleton. So far the break-down of trigger behavior looked like this:.

 Trigger on_change_update_history_regular: time=953.769 calls=9215
 Trigger on_edit_treenode: time=35.184 calls=9215
 Trigger on_edit_treenode_check_review: time=19.663 calls=9215
 Trigger on_edit_treenode_update_summary_and_edges: time=2515.228 calls=1
 Trigger on_edit_treenode_update_suppressed_virtual_treenodes: time=17.984 calls=9215
 Trigger on_edit_treenode_update_treenode_connector_edges: time=19.190 calls=9215
 Execution Time: 4254.615 m

With the separation of the trigger behavior into two functions each, the
breakdown looks like this:

 Trigger on_change_update_history_regular: time=206.256 calls=1
 Trigger on_change_update_history_txid_regular: time=44.531 calls=9215
 Trigger on_edit_treenode: time=25.508 calls=9215
 Trigger on_edit_treenode_check_review: time=20.152 calls=9215
 Trigger on_edit_treenode_update_summary_and_edges: time=2529.471 c... (continued)

8613 of 19162 relevant lines covered (44.95%)

0.45 hits per line

Source Files on job 6693.1