{"action":"created","comment":{"url":"https://api.github.com/repos/thoughtbot/scenic/pulls/comments/87752193","pull_request_review_id":8350723,"id":87752193,"diff_hunk":"@@ -0,0 +1,67 @@\n+module Scenic\n+  module Adapters\n+    class Postgres\n+      class RefreshDependencies\n+        def self.call(name, adapter, connection)\n+          new(name, adapter, connection).call\n+        end\n+\n+        def initialize(name, adapter, connection)\n+          @name = name\n+          @adapter = adapter\n+          @connection = connection\n+        end\n+\n+        def call\n+          dependencies.each do |dependency|\n+            adapter.refresh_materialized_view(dependency)\n+          end\n+        end\n+\n+        private\n+\n+        attr_reader :name, :adapter, :connection\n+\n+        DEPENDENCY_SQL = <<~SQL.freeze\n+          SELECT r_ns.nspname || '.' || cl_r.relname AS materialized_view,\n+          array_agg(d_ns.nspname || '.' || cl_d.relname) AS depends_on\n+          FROM pg_rewrite AS r\n+          JOIN pg_class AS cl_r ON r.ev_class=cl_r.oid\n+          JOIN pg_depend AS d ON r.oid=d.objid\n+          JOIN pg_class AS cl_d ON d.refobjid=cl_d.oid\n+          JOIN pg_namespace AS r_ns ON r_ns.oid = cl_r.relnamespace\n+          JOIN pg_namespace AS d_ns ON d_ns.oid = cl_d.relnamespace\n+          WHERE cl_d.relkind = 'm'\n+          AND cl_r.relkind = 'm'\n+          AND cl_d.relname != cl_r.relname\n+          GROUP BY cl_r.relname, r_ns.nspname\n+          ORDER BY cl_r.relname;\n+        SQL\n+\n+        def dependencies\n+          dependency_rows = connection.select_rows(DEPENDENCY_SQL)\n+          dependency_hash = parse_to_hash(dependency_rows)\n+          sorted_arr = tsort(dependency_hash)\n+          idx = sorted_arr.find_index { |dep| dep.include?(name.to_s) }\n+          sorted_arr[0...idx]\n+        end\n+\n+        def parse_to_hash(dependency_rows)\n+          dependency_rows.each_with_object({}) do |row, hash|\n+            formatted_dependencies = row.last.tr('{}', '').split(',')","path":"lib/scenic/adapters/postgres/refresh_dependencies.rb","position":51,"original_position":51,"commit_id":"9871c846a87c5183bd97337427ee393f63e8a18b","original_commit_id":"9871c846a87c5183bd97337427ee393f63e8a18b","user":{"login":"houndci-bot","id":6697940,"avatar_url":"https://avatars.githubusercontent.com/u/6697940?v=3","gravatar_id":"","url":"https://api.github.com/users/houndci-bot","html_url":"https://github.com/houndci-bot","followers_url":"https://api.github.com/users/houndci-bot/followers","following_url":"https://api.github.com/users/houndci-bot/following{/other_user}","gists_url":"https://api.github.com/users/houndci-bot/gists{/gist_id}","starred_url":"https://api.github.com/users/houndci-bot/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/houndci-bot/subscriptions","organizations_url":"https://api.github.com/users/houndci-bot/orgs","repos_url":"https://api.github.com/users/houndci-bot/repos","events_url":"https://api.github.com/users/houndci-bot/events{/privacy}","received_events_url":"https://api.github.com/users/houndci-bot/received_events","type":"User","site_admin":false},"body":"Prefer double-quoted strings unless you need single quotes to avoid extra backslashes for escaping.","created_at":"2016-11-14T08:03:28Z","updated_at":"2016-11-14T08:03:28Z","html_url":"https://github.com/thoughtbot/scenic/pull/174#discussion_r87752193","pull_request_url":"https://api.github.com/repos/thoughtbot/scenic/pulls/174","_links":{"self":{"href":"https://api.github.com/repos/thoughtbot/scenic/pulls/comments/87752193"},"html":{"href":"https://github.com/thoughtbot/scenic/pull/174#discussion_r87752193"},"pull_request":{"href":"https://api.github.com/repos/thoughtbot/scenic/pulls/174"}}},"pull_request":{"url":"https://api.github.com/repos/thoughtbot/scenic/pulls/174","id":93522616,"html_url":"https://github.com/thoughtbot/scenic/pull/174","diff_url":"https://github.com/thoughtbot/scenic/pull/174.diff","patch_url":"https://github.com/thoughtbot/scenic/pull/174.patch","issue_url":"https://api.github.com/repos/thoughtbot/scenic/issues/174","number":174,"state":"open","locked":false,"title":"Add refreshing materialized views with dependencies","user":{"login":"devonestes","id":8422484,"avatar_url":"https://avatars.githubusercontent.com/u/8422484?v=3","gravatar_id":"","url":"https://api.github.com/users/devonestes","html_url":"https://github.com/devonestes","followers_url":"https://api.github.com/users/devonestes/followers","following_url":"https://api.github.com/users/devonestes/following{/other_user}","gists_url":"https://api.github.com/users/devonestes/gists{/gist_id}","starred_url":"https://api.github.com/users/devonestes/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/devonestes/subscriptions","organizations_url":"https://api.github.com/users/devonestes/orgs","repos_url":"https://api.github.com/users/devonestes/repos","events_url":"https://api.github.com/users/devonestes/events{/privacy}","received_events_url":"https://api.github.com/users/devonestes/received_events","type":"User","site_admin":false},"body":"We had issues with refreshing complex chains of materialized views that\r\ndepended on one another. For example, we had the following type of thing\r\nhappening.\r\n\r\n(imagine all the relations below are materialized views)\r\n\r\nA depends on B\r\nB depends on C and D\r\nC depends on D\r\nD depends only on non-materialized views\r\n\r\nNow if you wanted to refresh A, and you wanted it to be as fresh as possible,\r\nfirst you would need to refresh the materialized views on which it depends,\r\nfollowed by A itself. This became frequent enough, and changed with enough\r\nfrequency, to make keeping refresh methods for each individual materialized\r\nview unreasonable.\r\n\r\nI realized we could use the information kept in `pg_depend` for this purpose,\r\nand also thought that I'm most likely not the first person to have this\r\nproblem, so I'm contributing it back here. Also, I can see further improvements\r\nto the migrations being built off of this usage of the dependency graph stored\r\nin Postgres, so I thought it might help there, too!\r\n\r\nThere's more refactoring that can be done here, but I wanted to get this\r\nworking (but imperfect) version up to get feedback on design, style, etc.\r\n\r\nAlso, how much, and where, would you like this documented?\r\n\r\ncc: @calebthompson","created_at":"2016-11-14T08:03:19Z","updated_at":"2016-11-14T08:03:28Z","closed_at":null,"merged_at":null,"merge_commit_sha":"f678e9d82362ec7c7e1739781c470782bf0f8a49","assignee":null,"assignees":[],"milestone":null,"commits_url":"https://api.github.com/repos/thoughtbot/scenic/pulls/174/commits","review_comments_url":"https://api.github.com/repos/thoughtbot/scenic/pulls/174/comments","review_comment_url":"https://api.github.com/repos/thoughtbot/scenic/pulls/comments{/number}","comments_url":"https://api.github.com/repos/thoughtbot/scenic/issues/174/comments","statuses_url":"https://api.github.com/repos/thoughtbot/scenic/statuses/9871c846a87c5183bd97337427ee393f63e8a18b","head":{"label":"devonestes:add-refresh-dependencies","ref":"add-refresh-dependencies","sha":"9871c846a87c5183bd97337427ee393f63e8a18b","user":{"login":"devonestes","id":8422484,"avatar_url":"https://avatars.githubusercontent.com/u/8422484?v=3","gravatar_id":"","url":"https://api.github.com/users/devonestes","html_url":"https://github.com/devonestes","followers_url":"https://api.github.com/users/devonestes/followers","following_url":"https://api.github.com/users/devonestes/following{/other_user}","gists_url":"https://api.github.com/users/devonestes/gists{/gist_id}","starred_url":"https://api.github.com/users/devonestes/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/devonestes/subscriptions","organizations_url":"https://api.github.com/users/devonestes/orgs","repos_url":"https://api.github.com/users/devonestes/repos","events_url":"https://api.github.com/users/devonestes/events{/privacy}","received_events_url":"https://api.github.com/users/devonestes/received_events","type":"User","site_admin":false},"repo":{"id":47918407,"name":"scenic","full_name":"devonestes/scenic","owner":{"login":"devonestes","id":8422484,"avatar_url":"https://avatars.githubusercontent.com/u/8422484?v=3","gravatar_id":"","url":"https://api.github.com/users/devonestes","html_url":"https://github.com/devonestes","followers_url":"https://api.github.com/users/devonestes/followers","following_url":"https://api.github.com/users/devonestes/following{/other_user}","gists_url":"https://api.github.com/users/devonestes/gists{/gist_id}","starred_url":"https://api.github.com/users/devonestes/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/devonestes/subscriptions","organizations_url":"https://api.github.com/users/devonestes/orgs","repos_url":"https://api.github.com/users/devonestes/repos","events_url":"https://api.github.com/users/devonestes/events{/privacy}","received_events_url":"https://api.github.com/users/devonestes/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/devonestes/scenic","description":"A fresh view for Rails","fork":true,"url":"https://api.github.com/repos/devonestes/scenic","forks_url":"https://api.github.com/repos/devonestes/scenic/forks","keys_url":"https://api.github.com/repos/devonestes/scenic/keys{/key_id}","collaborators_url":"https://api.github.com/repos/devonestes/scenic/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/devonestes/scenic/teams","hooks_url":"https://api.github.com/repos/devonestes/scenic/hooks","issue_events_url":"https://api.github.com/repos/devonestes/scenic/issues/events{/number}","events_url":"https://api.github.com/repos/devonestes/scenic/events","assignees_url":"https://api.github.com/repos/devonestes/scenic/assignees{/user}","branches_url":"https://api.github.com/repos/devonestes/scenic/branches{/branch}","tags_url":"https://api.github.com/repos/devonestes/scenic/tags","blobs_url":"https://api.github.com/repos/devonestes/scenic/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/devonestes/scenic/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/devonestes/scenic/git/refs{/sha}","trees_url":"https://api.github.com/repos/devonestes/scenic/git/trees{/sha}","statuses_url":"https://api.github.com/repos/devonestes/scenic/statuses/{sha}","languages_url":"https://api.github.com/repos/devonestes/scenic/languages","stargazers_url":"https://api.github.com/repos/devonestes/scenic/stargazers","contributors_url":"https://api.github.com/repos/devonestes/scenic/contributors","subscribers_url":"https://api.github.com/repos/devonestes/scenic/subscribers","subscription_url":"https://api.github.com/repos/devonestes/scenic/subscription","commits_url":"https://api.github.com/repos/devonestes/scenic/commits{/sha}","git_commits_url":"https://api.github.com/repos/devonestes/scenic/git/commits{/sha}","comments_url":"https://api.github.com/repos/devonestes/scenic/comments{/number}","issue_comment_url":"https://api.github.com/repos/devonestes/scenic/issues/comments{/number}","contents_url":"https://api.github.com/repos/devonestes/scenic/contents/{+path}","compare_url":"https://api.github.com/repos/devonestes/scenic/compare/{base}...{head}","merges_url":"https://api.github.com/repos/devonestes/scenic/merges","archive_url":"https://api.github.com/repos/devonestes/scenic/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/devonestes/scenic/downloads","issues_url":"https://api.github.com/repos/devonestes/scenic/issues{/number}","pulls_url":"https://api.github.com/repos/devonestes/scenic/pulls{/number}","milestones_url":"https://api.github.com/repos/devonestes/scenic/milestones{/number}","notifications_url":"https://api.github.com/repos/devonestes/scenic/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/devonestes/scenic/labels{/name}","releases_url":"https://api.github.com/repos/devonestes/scenic/releases{/id}","deployments_url":"https://api.github.com/repos/devonestes/scenic/deployments","created_at":"2015-12-13T12:31:38Z","updated_at":"2016-01-11T16:14:34Z","pushed_at":"2016-11-14T07:58:23Z","git_url":"git://github.com/devonestes/scenic.git","ssh_url":"git@github.com:devonestes/scenic.git","clone_url":"https://github.com/devonestes/scenic.git","svn_url":"https://github.com/devonestes/scenic","homepage":"","size":185,"stargazers_count":0,"watchers_count":0,"language":"Ruby","has_issues":false,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":0,"forks":0,"open_issues":0,"watchers":0,"default_branch":"master"}},"base":{"label":"thoughtbot:master","ref":"master","sha":"a2dc1ae253600d97aa6cb3c0e28687d00721b5d3","user":{"login":"thoughtbot","id":6183,"avatar_url":"https://avatars.githubusercontent.com/u/6183?v=3","gravatar_id":"","url":"https://api.github.com/users/thoughtbot","html_url":"https://github.com/thoughtbot","followers_url":"https://api.github.com/users/thoughtbot/followers","following_url":"https://api.github.com/users/thoughtbot/following{/other_user}","gists_url":"https://api.github.com/users/thoughtbot/gists{/gist_id}","starred_url":"https://api.github.com/users/thoughtbot/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/thoughtbot/subscriptions","organizations_url":"https://api.github.com/users/thoughtbot/orgs","repos_url":"https://api.github.com/users/thoughtbot/repos","events_url":"https://api.github.com/users/thoughtbot/events{/privacy}","received_events_url":"https://api.github.com/users/thoughtbot/received_events","type":"Organization","site_admin":false},"repo":{"id":19283674,"name":"scenic","full_name":"thoughtbot/scenic","owner":{"login":"thoughtbot","id":6183,"avatar_url":"https://avatars.githubusercontent.com/u/6183?v=3","gravatar_id":"","url":"https://api.github.com/users/thoughtbot","html_url":"https://github.com/thoughtbot","followers_url":"https://api.github.com/users/thoughtbot/followers","following_url":"https://api.github.com/users/thoughtbot/following{/other_user}","gists_url":"https://api.github.com/users/thoughtbot/gists{/gist_id}","starred_url":"https://api.github.com/users/thoughtbot/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/thoughtbot/subscriptions","organizations_url":"https://api.github.com/users/thoughtbot/orgs","repos_url":"https://api.github.com/users/thoughtbot/repos","events_url":"https://api.github.com/users/thoughtbot/events{/privacy}","received_events_url":"https://api.github.com/users/thoughtbot/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/thoughtbot/scenic","description":"Versioned database views for Rails","fork":false,"url":"https://api.github.com/repos/thoughtbot/scenic","forks_url":"https://api.github.com/repos/thoughtbot/scenic/forks","keys_url":"https://api.github.com/repos/thoughtbot/scenic/keys{/key_id}","collaborators_url":"https://api.github.com/repos/thoughtbot/scenic/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/thoughtbot/scenic/teams","hooks_url":"https://api.github.com/repos/thoughtbot/scenic/hooks","issue_events_url":"https://api.github.com/repos/thoughtbot/scenic/issues/events{/number}","events_url":"https://api.github.com/repos/thoughtbot/scenic/events","assignees_url":"https://api.github.com/repos/thoughtbot/scenic/assignees{/user}","branches_url":"https://api.github.com/repos/thoughtbot/scenic/branches{/branch}","tags_url":"https://api.github.com/repos/thoughtbot/scenic/tags","blobs_url":"https://api.github.com/repos/thoughtbot/scenic/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/thoughtbot/scenic/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/thoughtbot/scenic/git/refs{/sha}","trees_url":"https://api.github.com/repos/thoughtbot/scenic/git/trees{/sha}","statuses_url":"https://api.github.com/repos/thoughtbot/scenic/statuses/{sha}","languages_url":"https://api.github.com/repos/thoughtbot/scenic/languages","stargazers_url":"https://api.github.com/repos/thoughtbot/scenic/stargazers","contributors_url":"https://api.github.com/repos/thoughtbot/scenic/contributors","subscribers_url":"https://api.github.com/repos/thoughtbot/scenic/subscribers","subscription_url":"https://api.github.com/repos/thoughtbot/scenic/subscription","commits_url":"https://api.github.com/repos/thoughtbot/scenic/commits{/sha}","git_commits_url":"https://api.github.com/repos/thoughtbot/scenic/git/commits{/sha}","comments_url":"https://api.github.com/repos/thoughtbot/scenic/comments{/number}","issue_comment_url":"https://api.github.com/repos/thoughtbot/scenic/issues/comments{/number}","contents_url":"https://api.github.com/repos/thoughtbot/scenic/contents/{+path}","compare_url":"https://api.github.com/repos/thoughtbot/scenic/compare/{base}...{head}","merges_url":"https://api.github.com/repos/thoughtbot/scenic/merges","archive_url":"https://api.github.com/repos/thoughtbot/scenic/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/thoughtbot/scenic/downloads","issues_url":"https://api.github.com/repos/thoughtbot/scenic/issues{/number}","pulls_url":"https://api.github.com/repos/thoughtbot/scenic/pulls{/number}","milestones_url":"https://api.github.com/repos/thoughtbot/scenic/milestones{/number}","notifications_url":"https://api.github.com/repos/thoughtbot/scenic/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/thoughtbot/scenic/labels{/name}","releases_url":"https://api.github.com/repos/thoughtbot/scenic/releases{/id}","deployments_url":"https://api.github.com/repos/thoughtbot/scenic/deployments","created_at":"2014-04-29T16:36:17Z","updated_at":"2016-11-14T02:53:24Z","pushed_at":"2016-11-14T08:03:19Z","git_url":"git://github.com/thoughtbot/scenic.git","ssh_url":"git@github.com:thoughtbot/scenic.git","clone_url":"https://github.com/thoughtbot/scenic.git","svn_url":"https://github.com/thoughtbot/scenic","homepage":"","size":307,"stargazers_count":1124,"watchers_count":1124,"language":"Ruby","has_issues":true,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":61,"mirror_url":null,"open_issues_count":7,"forks":61,"open_issues":7,"watchers":1124,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/thoughtbot/scenic/pulls/174"},"html":{"href":"https://github.com/thoughtbot/scenic/pull/174"},"issue":{"href":"https://api.github.com/repos/thoughtbot/scenic/issues/174"},"comments":{"href":"https://api.github.com/repos/thoughtbot/scenic/issues/174/comments"},"review_comments":{"href":"https://api.github.com/repos/thoughtbot/scenic/pulls/174/comments"},"review_comment":{"href":"https://api.github.com/repos/thoughtbot/scenic/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/thoughtbot/scenic/pulls/174/commits"},"statuses":{"href":"https://api.github.com/repos/thoughtbot/scenic/statuses/9871c846a87c5183bd97337427ee393f63e8a18b"}}}}
{"actor":{"display_login":"houndci-bot"}}