{"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":6,"created_at":"2016-08-01T18:11:46Z","updated_at":"2016-08-16T17:27:13Z","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/240175213","html_url":"https://github.com/citusdata/citus/issues/692#issuecomment-240175213","issue_url":"https://api.github.com/repos/citusdata/citus/issues/692","id":240175213,"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-16T17:27:13Z","updated_at":"2016-08-16T17:27:13Z","body":"having a join on partition column requirement looks very restrictive and  difficult to check when you consider all sorts of queries we want to support. "}}
{"actor":{"display_login":"mtuncer"}}