{"action":"created","issue":{"url":"https://api.github.com/repos/citusdata/citus/issues/692","repository_url":"https://api.github.com/repos/citusdata/citus","labels_url":"https://api.github.com/repos/citusdata/citus/issues/692/labels{/name}","comments_url":"https://api.github.com/repos/citusdata/citus/issues/692/comments","events_url":"https://api.github.com/repos/citusdata/citus/issues/692/events","html_url":"https://github.com/citusdata/citus/issues/692","id":168706696,"number":692,"title":"Evaluate restricting router planner to a particular database model","user":{"login":"marcocitus","id":7127240,"avatar_url":"https://avatars.githubusercontent.com/u/7127240?v=3","gravatar_id":"","url":"https://api.github.com/users/marcocitus","html_url":"https://github.com/marcocitus","followers_url":"https://api.github.com/users/marcocitus/followers","following_url":"https://api.github.com/users/marcocitus/following{/other_user}","gists_url":"https://api.github.com/users/marcocitus/gists{/gist_id}","starred_url":"https://api.github.com/users/marcocitus/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/marcocitus/subscriptions","organizations_url":"https://api.github.com/users/marcocitus/orgs","repos_url":"https://api.github.com/users/marcocitus/repos","events_url":"https://api.github.com/users/marcocitus/events{/privacy}","received_events_url":"https://api.github.com/users/marcocitus/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":7,"created_at":"2016-08-01T18:11:46Z","updated_at":"2016-08-16T18:37:22Z","closed_at":null,"body":"Currently the router planner can plan practically anything that can be executed by changing table names into shard names provided that the shards are in the same physical location. This is very powerful, but has significant usability implications.\r\n\r\nFirst-time users often start with 2 worker nodes and a replication factor of 2. In this case, the router planner can run practically any SQL query that hits a single shard in each table. When users start expanding their cluster and moving shards or changing filter values, this may no longer be the case, potentially causing and outage or forcing the user to redesign their application.\r\n\r\nEvaluate restricting the router planner to a particular data model by default such that it only permits queries on shards that are guaranteed to be plannable given the database model (e.g. ensure that all tables in the query are co-located and have the same distribution column filter). This would prevent queries from breaking when the physical layout of shards or the specific values of filters change. We can keep the current, unrestricted behaviour as an option, as we do with subquery pushdown.\r\n\r\nAn alternative approach could be to add some logic to the workers to always allow router queries. For example, if each shard that is not physically located on a worker had a postgres_fdw instead, then we can effectively remove the restriction that shards need to be on the same worker. This would have more general benefits, such as supporting broadcast joins, but comes with its own usability and performance implications.\r\n\r\nSome examples of confusing behaviour:\r\n\r\nThe following query requires task-tracker when changing one value because we're joining tables that are not always co-located:\r\n\r\n    postgres=# SELECT * FROM review JOIN customer USING (customer_id) WHERE product_id = 1 AND customer_id = 1;\r\n     customer_id | product_id | score | name \r\n    -------------+------------+-------+------\r\n    (0 rows)\r\n\r\n    postgres=# SELECT * FROM review JOIN customer USING (customer_id) WHERE product_id = 1 AND customer_id = 2;\r\n    ERROR:  cannot use real time executor with repartition jobs\r\n    HINT:  Set citus.task_executor_type to \"task-tracker\".\r\n\r\nUnion is simultaneously supported and \"unsupported\" (we should revise our error messages):\r\n\r\n    postgres=# SELECT * FROM customer WHERE customer_id = 1 UNION SELECT * FROM customer WHERE customer_id = 5;\r\n     customer_id | name \r\n    -------------+------\r\n    (0 rows)\r\n\r\n    postgres=# SELECT * FROM customer WHERE customer_id = 1 UNION SELECT * FROM customer WHERE customer_id = 4;\r\n    ERROR:  cannot perform distributed planning on this query\r\n    DETAIL:  Union, Intersect, or Except are currently unsupported\r\n\r\nRunning shard rebalancer might cause the same query to suddenly become \"unsupported\":\r\n\r\n    postgres=# SELECT * FROM customer WHERE customer_id = 1 UNION SELECT * FROM customer WHERE customer_id = 7;\r\n     customer_id | name\r\n    -------------+------\r\n    (0 rows)\r\n    postgres=# SELECT rebalance_table_shards('customer');\r\n    NOTICE:  Moving shard 102032 from localhost:9701 to localhost:9700 ...\r\n     rebalance_table_shards \r\n    ------------------------\r\n     \r\n    (1 row)\r\n    \r\n    postgres=# SELECT * FROM customer WHERE customer_id = 1 UNION SELECT * FROM customer WHERE customer_id = 7;\r\n    ERROR:  cannot perform distributed planning on this query\r\n    DETAIL:  Union, Intersect, or Except are currently unsupported\r\n"},"comment":{"url":"https://api.github.com/repos/citusdata/citus/issues/comments/240196453","html_url":"https://github.com/citusdata/citus/issues/692#issuecomment-240196453","issue_url":"https://api.github.com/repos/citusdata/citus/issues/692","id":240196453,"user":{"login":"mtuncer","id":9413470,"avatar_url":"https://avatars.githubusercontent.com/u/9413470?v=3","gravatar_id":"","url":"https://api.github.com/users/mtuncer","html_url":"https://github.com/mtuncer","followers_url":"https://api.github.com/users/mtuncer/followers","following_url":"https://api.github.com/users/mtuncer/following{/other_user}","gists_url":"https://api.github.com/users/mtuncer/gists{/gist_id}","starred_url":"https://api.github.com/users/mtuncer/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mtuncer/subscriptions","organizations_url":"https://api.github.com/users/mtuncer/orgs","repos_url":"https://api.github.com/users/mtuncer/repos","events_url":"https://api.github.com/users/mtuncer/events{/privacy}","received_events_url":"https://api.github.com/users/mtuncer/received_events","type":"User","site_admin":false},"created_at":"2016-08-16T18:37:22Z","updated_at":"2016-08-16T18:37:22Z","body":"We discussed this with @ozgune , @anarazel and @marcocitus  here are the notes\r\n\r\n@mtuncer stated there are 2 outstanding issues with usability. \r\n1 - user runs a query successfully, then changes something in the system and query is not supported anymore. One example is the shard rebalancer changing shard locations.\r\n\r\n2 - user runs a query, makes a slight change in the filter, query is not supported anymore and the error message does not provide any help to suggest what has happened.  @metdos `s last comment describes this.\r\n\r\n@anarazel said he is for better error/warning messages instead of changing(reducing) query coverage. \r\n\r\n@marcocitus suggested an alternative to run a heuristics to determine if this query could still be  supported despite an environmental change. \r\n\r\nWe can use co-location property of pruned out shards to determine if we can reliably support this query. If not we can display a warning message something meaning you got lucky this time, this query might not be supported next time.\r\n\r\nNext Steps:\r\n- outline heuristics algoritms\r\n- list heuristics coverage, state what is/not covered.\r\n- provide high level design and time estimates.\r\n\r\n@samay-sharma , @begriffs , @metdos please feel free to join the discussion here. We will hold another session when all parties are present.\r\n "}}
{"actor":{"display_login":"mtuncer"}}