{"action":"created","issue":{"url":"https://api.github.com/repos/cockroachdb/cockroach/issues/5963","repository_url":"https://api.github.com/repos/cockroachdb/cockroach","labels_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/5963/labels{/name}","comments_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/5963/comments","events_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/5963/events","html_url":"https://github.com/cockroachdb/cockroach/issues/5963","id":147156461,"number":5963,"title":"sql: support \"time-travel\" queries","user":{"login":"petermattis","id":6201335,"avatar_url":"https://avatars.githubusercontent.com/u/6201335?v=3","gravatar_id":"","url":"https://api.github.com/users/petermattis","html_url":"https://github.com/petermattis","followers_url":"https://api.github.com/users/petermattis/followers","following_url":"https://api.github.com/users/petermattis/following{/other_user}","gists_url":"https://api.github.com/users/petermattis/gists{/gist_id}","starred_url":"https://api.github.com/users/petermattis/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/petermattis/subscriptions","organizations_url":"https://api.github.com/users/petermattis/orgs","repos_url":"https://api.github.com/users/petermattis/repos","events_url":"https://api.github.com/users/petermattis/events{/privacy}","received_events_url":"https://api.github.com/users/petermattis/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":19,"created_at":"2016-04-09T19:28:49Z","updated_at":"2016-04-19T00:16:54Z","closed_at":null,"body":"The desire to perform queries on historical versions of a table has come up in discussions several times recently. We could either allow a transaction-wide timestamp to be set or add syntax for querying a table at a timestamp. The latter approach would be more flexible as we could query one table at a timestamp and join with another table at the current time. For syntax, I propose we extend the existing index-hints syntax. This might look something like:\r\n\r\n```\r\nSELECT * FROM t@{TIMESTAMP=<timestamp>}\r\n```\r\n\r\nThe KV scan operation would need to be enhanced to take a timestamp that differs from the transaction timestamp. I'm not sure what the ramifications of this are. Presumably this wouldn't affect the timestamp cache. Are there other concerns\r\n\r\nTime-travel queries would also have to be aware of schema changes. The current proposal for backfilling schema changes (#5817) would cause index data to have a different timestamp than the associated row data. So time-travel queries would need to be careful to disable usage of index depending on when it was created.\r\n\r\nWe would also likely want to enhance the zone config GC policies. Currently the only policy is time based (default 1 day). This could cause surprises because an overwritten key would have the old version deleted after a day causing the time-travel query to not accurately represent the table at the specified time. "},"comment":{"url":"https://api.github.com/repos/cockroachdb/cockroach/issues/comments/211644308","html_url":"https://github.com/cockroachdb/cockroach/issues/5963#issuecomment-211644308","issue_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/5963","id":211644308,"user":{"login":"bdarnell","id":160562,"avatar_url":"https://avatars.githubusercontent.com/u/160562?v=3","gravatar_id":"","url":"https://api.github.com/users/bdarnell","html_url":"https://github.com/bdarnell","followers_url":"https://api.github.com/users/bdarnell/followers","following_url":"https://api.github.com/users/bdarnell/following{/other_user}","gists_url":"https://api.github.com/users/bdarnell/gists{/gist_id}","starred_url":"https://api.github.com/users/bdarnell/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bdarnell/subscriptions","organizations_url":"https://api.github.com/users/bdarnell/orgs","repos_url":"https://api.github.com/users/bdarnell/repos","events_url":"https://api.github.com/users/bdarnell/events{/privacy}","received_events_url":"https://api.github.com/users/bdarnell/received_events","type":"User","site_admin":false},"created_at":"2016-04-19T00:16:54Z","updated_at":"2016-04-19T00:16:54Z","body":"I think modifying the `BEGIN` statement is going to be difficult to use in practice. For example, the Go `pq` driver always sends a hard-coded [\"BEGIN\"](https://github.com/lib/pq/blob/3cd0097429be7d611bb644ef85b42bfb102ceea4/conn.go#L513). I think it's probably better to make it a `SET` statement like priority or isolation. Or we we could implement the SQL 2011 syntax, which is apparently `AS OF SYSTEM TIME $1` placed after the table name in the `FROM` clause. "}}