{"action":"closed","number":1370,"pull_request":{"url":"https://api.github.com/repos/catmaid/CATMAID/pulls/1370","id":80983602,"html_url":"https://github.com/catmaid/CATMAID/pull/1370","diff_url":"https://github.com/catmaid/CATMAID/pull/1370.diff","patch_url":"https://github.com/catmaid/CATMAID/pull/1370.patch","issue_url":"https://api.github.com/repos/catmaid/CATMAID/issues/1370","number":1370,"state":"closed","locked":false,"title":"Store original transaction IDs in history tables","user":{"login":"tomka","id":112226,"avatar_url":"https://avatars.githubusercontent.com/u/112226?v=3","gravatar_id":"","url":"https://api.github.com/users/tomka","html_url":"https://github.com/tomka","followers_url":"https://api.github.com/users/tomka/followers","following_url":"https://api.github.com/users/tomka/following{/other_user}","gists_url":"https://api.github.com/users/tomka/gists{/gist_id}","starred_url":"https://api.github.com/users/tomka/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tomka/subscriptions","organizations_url":"https://api.github.com/users/tomka/orgs","repos_url":"https://api.github.com/users/tomka/repos","events_url":"https://api.github.com/users/tomka/events{/privacy}","received_events_url":"https://api.github.com/users/tomka/received_events","type":"User","site_admin":false},"body":"For documentation purposes and potential discussion I don't merge this change directly into `dev`. This branch updates history tracking to store the correct transaction ID in CATMAID's history tables. This ID is needed to correctly relate transaction log entries to rows in either the live tables or its history table. In current `dev`, however, history tables store the ID of the transaction that created the history entry and *not* the ID of the original transaction that created or updated the live table that made the history row valid (and part of the live table) before. With this branch the original transaction ID is stored for each history entry.\r\n\r\nTo implement this, some major changes had to be made. Although Postgres stores the creating/updating transaction ID for each row already in a `xid`  [system column](https://www.postgresql.org/docs/9.5/static/ddl-system-columns.html), we can't rely on it to stay valid for the lifetime of the row. At some point [transaction ID wraparound](https://www.postgresql.org/docs/9.5/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND) kicks in and old `xid` get overridden with a frozen transaction ID and the original information is lost. The original information would be needed once the live table entry is moved to history though.\r\n\r\nTherefore, CATMAID has to track the original transaction IDs itself in order to make the transaction log useful. Similar to time tracking, live tables can either provide their own transaction ID column or it is tracked in a separate table. It is currently only allowed to either provide both time and txid columns or none. If none are provided, a tracking table keeps track of both, which is created and managed by the history tracking system.\r\n\r\nAs the benchmark below shows, using separate tracking tables for *all* versioned tables is a huge performance hit (think of all the joins and index updates needed during each update). Therefore nearly all CATMAID tables now have a new column: `txid`. It stores the original transaction ID of each row as `bigint`, because this is what [txid_current()](https://www.postgresql.org/docs/9.5/static/functions-info.html#FUNCTIONS-TXID-SNAPSHOT) returns. We use this function opposed to the momentary valid `xmin` because opposed to `xmin` it is unique and won't expire (besides the 4 byte current transaction ID, it also includes a 4 byte epoch counter). This should also imply that we don't need to always look at a transaction ID and a timestamp together, we now should be able to just use this 8 byte txid. This improves robustness, because some tables allow modification of their edition time and now we don't need to rely on this timestamp anymore for lookup.\r\n\r\nSince the last CATMAID release is only a few days old and existing history tracking isn't in production use yet (as far as we know), this branch implements these changes by completely removing the present history tracking system and re-creating the new version from scratch. (while also making some variable namings more clear, etc.).\r\n\r\nHere are some benchmarks of these changes:\r\n\r\n![catmaid-txid-tracking-all-commits](https://cloud.githubusercontent.com/assets/112226/17594894/d3fe9042-5fb8-11e6-8d09-3b1be1f5e32f.png)\r\n\r\nAnd in more detail: no history, current history and new version:\r\n\r\n![catmaid-txid-tracking-detail](https://cloud.githubusercontent.com/assets/112226/17594906/e3637e58-5fb8-11e6-9b25-2a3074093c54.png)\r\n\r\nAs can be seen, this is also quite a performance gain for history tracking as a whole. This is somewhat surprising, but I suppose the removing the cast from `xid` to `text` to `bigint` had quite an impact.","created_at":"2016-08-11T15:58:01Z","updated_at":"2016-08-11T17:19:05Z","closed_at":"2016-08-11T17:19:05Z","merged_at":"2016-08-11T17:19:05Z","merge_commit_sha":"486dbdc49bbd1af45291ece466ce3c6014328e59","assignee":null,"assignees":[],"milestone":null,"commits_url":"https://api.github.com/repos/catmaid/CATMAID/pulls/1370/commits","review_comments_url":"https://api.github.com/repos/catmaid/CATMAID/pulls/1370/comments","review_comment_url":"https://api.github.com/repos/catmaid/CATMAID/pulls/comments{/number}","comments_url":"https://api.github.com/repos/catmaid/CATMAID/issues/1370/comments","statuses_url":"https://api.github.com/repos/catmaid/CATMAID/statuses/6953be7763d2e89cbbf93cfce588c55bc75395d2","head":{"label":"catmaid:features/history-tables-store-original-txid","ref":"features/history-tables-store-original-txid","sha":"6953be7763d2e89cbbf93cfce588c55bc75395d2","user":{"login":"catmaid","id":10773867,"avatar_url":"https://avatars.githubusercontent.com/u/10773867?v=3","gravatar_id":"","url":"https://api.github.com/users/catmaid","html_url":"https://github.com/catmaid","followers_url":"https://api.github.com/users/catmaid/followers","following_url":"https://api.github.com/users/catmaid/following{/other_user}","gists_url":"https://api.github.com/users/catmaid/gists{/gist_id}","starred_url":"https://api.github.com/users/catmaid/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/catmaid/subscriptions","organizations_url":"https://api.github.com/users/catmaid/orgs","repos_url":"https://api.github.com/users/catmaid/repos","events_url":"https://api.github.com/users/catmaid/events{/privacy}","received_events_url":"https://api.github.com/users/catmaid/received_events","type":"Organization","site_admin":false},"repo":{"id":1299359,"name":"CATMAID","full_name":"catmaid/CATMAID","owner":{"login":"catmaid","id":10773867,"avatar_url":"https://avatars.githubusercontent.com/u/10773867?v=3","gravatar_id":"","url":"https://api.github.com/users/catmaid","html_url":"https://github.com/catmaid","followers_url":"https://api.github.com/users/catmaid/followers","following_url":"https://api.github.com/users/catmaid/following{/other_user}","gists_url":"https://api.github.com/users/catmaid/gists{/gist_id}","starred_url":"https://api.github.com/users/catmaid/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/catmaid/subscriptions","organizations_url":"https://api.github.com/users/catmaid/orgs","repos_url":"https://api.github.com/users/catmaid/repos","events_url":"https://api.github.com/users/catmaid/events{/privacy}","received_events_url":"https://api.github.com/users/catmaid/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/catmaid/CATMAID","description":"Collaborative Annotation Toolkit for Massive Amounts of Image Data","fork":false,"url":"https://api.github.com/repos/catmaid/CATMAID","forks_url":"https://api.github.com/repos/catmaid/CATMAID/forks","keys_url":"https://api.github.com/repos/catmaid/CATMAID/keys{/key_id}","collaborators_url":"https://api.github.com/repos/catmaid/CATMAID/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/catmaid/CATMAID/teams","hooks_url":"https://api.github.com/repos/catmaid/CATMAID/hooks","issue_events_url":"https://api.github.com/repos/catmaid/CATMAID/issues/events{/number}","events_url":"https://api.github.com/repos/catmaid/CATMAID/events","assignees_url":"https://api.github.com/repos/catmaid/CATMAID/assignees{/user}","branches_url":"https://api.github.com/repos/catmaid/CATMAID/branches{/branch}","tags_url":"https://api.github.com/repos/catmaid/CATMAID/tags","blobs_url":"https://api.github.com/repos/catmaid/CATMAID/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/catmaid/CATMAID/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/catmaid/CATMAID/git/refs{/sha}","trees_url":"https://api.github.com/repos/catmaid/CATMAID/git/trees{/sha}","statuses_url":"https://api.github.com/repos/catmaid/CATMAID/statuses/{sha}","languages_url":"https://api.github.com/repos/catmaid/CATMAID/languages","stargazers_url":"https://api.github.com/repos/catmaid/CATMAID/stargazers","contributors_url":"https://api.github.com/repos/catmaid/CATMAID/contributors","subscribers_url":"https://api.github.com/repos/catmaid/CATMAID/subscribers","subscription_url":"https://api.github.com/repos/catmaid/CATMAID/subscription","commits_url":"https://api.github.com/repos/catmaid/CATMAID/commits{/sha}","git_commits_url":"https://api.github.com/repos/catmaid/CATMAID/git/commits{/sha}","comments_url":"https://api.github.com/repos/catmaid/CATMAID/comments{/number}","issue_comment_url":"https://api.github.com/repos/catmaid/CATMAID/issues/comments{/number}","contents_url":"https://api.github.com/repos/catmaid/CATMAID/contents/{+path}","compare_url":"https://api.github.com/repos/catmaid/CATMAID/compare/{base}...{head}","merges_url":"https://api.github.com/repos/catmaid/CATMAID/merges","archive_url":"https://api.github.com/repos/catmaid/CATMAID/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/catmaid/CATMAID/downloads","issues_url":"https://api.github.com/repos/catmaid/CATMAID/issues{/number}","pulls_url":"https://api.github.com/repos/catmaid/CATMAID/pulls{/number}","milestones_url":"https://api.github.com/repos/catmaid/CATMAID/milestones{/number}","notifications_url":"https://api.github.com/repos/catmaid/CATMAID/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/catmaid/CATMAID/labels{/name}","releases_url":"https://api.github.com/repos/catmaid/CATMAID/releases{/id}","deployments_url":"https://api.github.com/repos/catmaid/CATMAID/deployments","created_at":"2011-01-27T15:29:03Z","updated_at":"2016-08-09T13:31:10Z","pushed_at":"2016-08-11T17:19:06Z","git_url":"git://github.com/catmaid/CATMAID.git","ssh_url":"git@github.com:catmaid/CATMAID.git","clone_url":"https://github.com/catmaid/CATMAID.git","svn_url":"https://github.com/catmaid/CATMAID","homepage":"http://catmaid.org","size":39259,"stargazers_count":60,"watchers_count":60,"language":"JavaScript","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":true,"forks_count":28,"mirror_url":null,"open_issues_count":187,"forks":28,"open_issues":187,"watchers":60,"default_branch":"master"}},"base":{"label":"catmaid:dev","ref":"dev","sha":"d2eebba27ee91cd3f295eab89764a83855859328","user":{"login":"catmaid","id":10773867,"avatar_url":"https://avatars.githubusercontent.com/u/10773867?v=3","gravatar_id":"","url":"https://api.github.com/users/catmaid","html_url":"https://github.com/catmaid","followers_url":"https://api.github.com/users/catmaid/followers","following_url":"https://api.github.com/users/catmaid/following{/other_user}","gists_url":"https://api.github.com/users/catmaid/gists{/gist_id}","starred_url":"https://api.github.com/users/catmaid/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/catmaid/subscriptions","organizations_url":"https://api.github.com/users/catmaid/orgs","repos_url":"https://api.github.com/users/catmaid/repos","events_url":"https://api.github.com/users/catmaid/events{/privacy}","received_events_url":"https://api.github.com/users/catmaid/received_events","type":"Organization","site_admin":false},"repo":{"id":1299359,"name":"CATMAID","full_name":"catmaid/CATMAID","owner":{"login":"catmaid","id":10773867,"avatar_url":"https://avatars.githubusercontent.com/u/10773867?v=3","gravatar_id":"","url":"https://api.github.com/users/catmaid","html_url":"https://github.com/catmaid","followers_url":"https://api.github.com/users/catmaid/followers","following_url":"https://api.github.com/users/catmaid/following{/other_user}","gists_url":"https://api.github.com/users/catmaid/gists{/gist_id}","starred_url":"https://api.github.com/users/catmaid/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/catmaid/subscriptions","organizations_url":"https://api.github.com/users/catmaid/orgs","repos_url":"https://api.github.com/users/catmaid/repos","events_url":"https://api.github.com/users/catmaid/events{/privacy}","received_events_url":"https://api.github.com/users/catmaid/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/catmaid/CATMAID","description":"Collaborative Annotation Toolkit for Massive Amounts of Image Data","fork":false,"url":"https://api.github.com/repos/catmaid/CATMAID","forks_url":"https://api.github.com/repos/catmaid/CATMAID/forks","keys_url":"https://api.github.com/repos/catmaid/CATMAID/keys{/key_id}","collaborators_url":"https://api.github.com/repos/catmaid/CATMAID/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/catmaid/CATMAID/teams","hooks_url":"https://api.github.com/repos/catmaid/CATMAID/hooks","issue_events_url":"https://api.github.com/repos/catmaid/CATMAID/issues/events{/number}","events_url":"https://api.github.com/repos/catmaid/CATMAID/events","assignees_url":"https://api.github.com/repos/catmaid/CATMAID/assignees{/user}","branches_url":"https://api.github.com/repos/catmaid/CATMAID/branches{/branch}","tags_url":"https://api.github.com/repos/catmaid/CATMAID/tags","blobs_url":"https://api.github.com/repos/catmaid/CATMAID/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/catmaid/CATMAID/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/catmaid/CATMAID/git/refs{/sha}","trees_url":"https://api.github.com/repos/catmaid/CATMAID/git/trees{/sha}","statuses_url":"https://api.github.com/repos/catmaid/CATMAID/statuses/{sha}","languages_url":"https://api.github.com/repos/catmaid/CATMAID/languages","stargazers_url":"https://api.github.com/repos/catmaid/CATMAID/stargazers","contributors_url":"https://api.github.com/repos/catmaid/CATMAID/contributors","subscribers_url":"https://api.github.com/repos/catmaid/CATMAID/subscribers","subscription_url":"https://api.github.com/repos/catmaid/CATMAID/subscription","commits_url":"https://api.github.com/repos/catmaid/CATMAID/commits{/sha}","git_commits_url":"https://api.github.com/repos/catmaid/CATMAID/git/commits{/sha}","comments_url":"https://api.github.com/repos/catmaid/CATMAID/comments{/number}","issue_comment_url":"https://api.github.com/repos/catmaid/CATMAID/issues/comments{/number}","contents_url":"https://api.github.com/repos/catmaid/CATMAID/contents/{+path}","compare_url":"https://api.github.com/repos/catmaid/CATMAID/compare/{base}...{head}","merges_url":"https://api.github.com/repos/catmaid/CATMAID/merges","archive_url":"https://api.github.com/repos/catmaid/CATMAID/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/catmaid/CATMAID/downloads","issues_url":"https://api.github.com/repos/catmaid/CATMAID/issues{/number}","pulls_url":"https://api.github.com/repos/catmaid/CATMAID/pulls{/number}","milestones_url":"https://api.github.com/repos/catmaid/CATMAID/milestones{/number}","notifications_url":"https://api.github.com/repos/catmaid/CATMAID/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/catmaid/CATMAID/labels{/name}","releases_url":"https://api.github.com/repos/catmaid/CATMAID/releases{/id}","deployments_url":"https://api.github.com/repos/catmaid/CATMAID/deployments","created_at":"2011-01-27T15:29:03Z","updated_at":"2016-08-09T13:31:10Z","pushed_at":"2016-08-11T17:19:06Z","git_url":"git://github.com/catmaid/CATMAID.git","ssh_url":"git@github.com:catmaid/CATMAID.git","clone_url":"https://github.com/catmaid/CATMAID.git","svn_url":"https://github.com/catmaid/CATMAID","homepage":"http://catmaid.org","size":39259,"stargazers_count":60,"watchers_count":60,"language":"JavaScript","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":true,"forks_count":28,"mirror_url":null,"open_issues_count":187,"forks":28,"open_issues":187,"watchers":60,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/catmaid/CATMAID/pulls/1370"},"html":{"href":"https://github.com/catmaid/CATMAID/pull/1370"},"issue":{"href":"https://api.github.com/repos/catmaid/CATMAID/issues/1370"},"comments":{"href":"https://api.github.com/repos/catmaid/CATMAID/issues/1370/comments"},"review_comments":{"href":"https://api.github.com/repos/catmaid/CATMAID/pulls/1370/comments"},"review_comment":{"href":"https://api.github.com/repos/catmaid/CATMAID/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/catmaid/CATMAID/pulls/1370/commits"},"statuses":{"href":"https://api.github.com/repos/catmaid/CATMAID/statuses/6953be7763d2e89cbbf93cfce588c55bc75395d2"}},"merged":true,"mergeable":null,"mergeable_state":"unknown","merged_by":{"login":"tomka","id":112226,"avatar_url":"https://avatars.githubusercontent.com/u/112226?v=3","gravatar_id":"","url":"https://api.github.com/users/tomka","html_url":"https://github.com/tomka","followers_url":"https://api.github.com/users/tomka/followers","following_url":"https://api.github.com/users/tomka/following{/other_user}","gists_url":"https://api.github.com/users/tomka/gists{/gist_id}","starred_url":"https://api.github.com/users/tomka/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tomka/subscriptions","organizations_url":"https://api.github.com/users/tomka/orgs","repos_url":"https://api.github.com/users/tomka/repos","events_url":"https://api.github.com/users/tomka/events{/privacy}","received_events_url":"https://api.github.com/users/tomka/received_events","type":"User","site_admin":false},"comments":3,"review_comments":0,"commits":5,"additions":2408,"deletions":143,"changed_files":4}}
{"actor":{"display_login":"tomka"}}