{"action":"created","issue":{"url":"https://api.github.com/repos/zombodb/zombodb/issues/83","repository_url":"https://api.github.com/repos/zombodb/zombodb","labels_url":"https://api.github.com/repos/zombodb/zombodb/issues/83/labels{/name}","comments_url":"https://api.github.com/repos/zombodb/zombodb/issues/83/comments","events_url":"https://api.github.com/repos/zombodb/zombodb/issues/83/events","html_url":"https://github.com/zombodb/zombodb/issues/83","id":131127182,"number":83,"title":"Correct syntax for a shadow index on a view","user":{"login":"johnrballard","id":1420634,"avatar_url":"https://avatars.githubusercontent.com/u/1420634?v=3","gravatar_id":"","url":"https://api.github.com/users/johnrballard","html_url":"https://github.com/johnrballard","followers_url":"https://api.github.com/users/johnrballard/followers","following_url":"https://api.github.com/users/johnrballard/following{/other_user}","gists_url":"https://api.github.com/users/johnrballard/gists{/gist_id}","starred_url":"https://api.github.com/users/johnrballard/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/johnrballard/subscriptions","organizations_url":"https://api.github.com/users/johnrballard/orgs","repos_url":"https://api.github.com/users/johnrballard/repos","events_url":"https://api.github.com/users/johnrballard/events{/privacy}","received_events_url":"https://api.github.com/users/johnrballard/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2016-02-03T19:23:40Z","updated_at":"2016-02-03T21:12:42Z","closed_at":null,"body":"I create the following database:\r\n\r\n```sql\r\nCREATE EXTENSION hstore;\r\nCREATE EXTENSION zombodb;\r\n\r\nCREATE TABLE public.documents (\r\n  pk_documents SERIAL8,\r\n  doc_stuff    TEXT,\r\n  CONSTRAINT idx_documents PRIMARY KEY (pk_documents)\r\n);\r\nCREATE TABLE public.docs_usage (\r\n  pk_docs_usage SERIAL8,\r\n  fk_documents  BIGINT,\r\n  fk_library_profile BIGINT,\r\n  place_used    TEXT,\r\n  CONSTRAINT idx_docs_usage PRIMARY KEY (pk_docs_usage)\r\n);\r\nCREATE TABLE public.library_profile (\r\n  pk_library_profile SERIAL8,\r\n  library_name       TEXT,\r\n  CONSTRAINT idx_library_profile PRIMARY KEY (pk_library_profile)\r\n);\r\n\r\nCREATE INDEX es_documents ON documents USING zombodb (zdb('public.documents':: REGCLASS, ctid), zdb(documents.*)) WITH (url='http://localhost:9200/', replicas=1, shards=5);\r\nCREATE INDEX es_docs_usage ON docs_usage USING zombodb (zdb('public.docs_usage':: REGCLASS, ctid), zdb(docs_usage.*)) WITH (url='http://localhost:9200/', replicas=1, shards=5);\r\nCREATE INDEX es_library_profile ON library_profile USING zombodb (zdb('public.library_profile':: REGCLASS, ctid), zdb(library_profile.*)) WITH (url='http://localhost:9200/', replicas=1, shards=5);\r\n\r\nCREATE OR REPLACE FUNCTION zdb_to_docs_usage(table_name REGCLASS, ctid tid)\r\n  RETURNS tid AS '$libdir/plugins/zombodb', 'zdb_table_ref_and_tid' LANGUAGE C IMMUTABLE STRICT COST 1;\r\n\r\nCREATE INDEX es_idx_zdb_to_docs_usage \r\n                     ON public.documents \r\n                USING zombodb (zdb_to_docs_usage('public.documents', ctid), zdb(documents.*))\r\n                 WITH (shadow='es_documents', \r\n                        options ='docs_usage_data:(pk_documents=<docs_usage.es_docs_usage>fk_documents)'\r\n                 );\r\n\r\nCREATE OR REPLACE VIEW documents_master_view AS\r\n  SELECT documents.*,\r\n    ( SELECT json_agg(row_to_json(du.*)) AS json_agg\r\n      FROM ( SELECT docs_usage.*,\r\n               ( SELECT library_profile.library_name\r\n                 FROM library_profile\r\n                 WHERE library_profile.pk_library_profile = docs_usage.fk_library_profile) AS library_name\r\n             from docs_usage\r\n             WHERE documents.pk_documents = docs_usage.fk_documents) du) AS usage_data,\r\n\r\n    zdb_to_docs_usage('public.documents'::regclass, documents.ctid) AS zdb\r\n  FROM public.documents;\r\n{\r\n```\r\n\r\nI insert the following data\r\n```sql\r\nINSERT INTO documents (doc_stuff) VALUES ('Every good boy does fine.'), ('Sally sells sea shells down by the seashore.'), ('The quick brown fox jumps over the lazy dog.');\r\nINSERT INTO library_profile (library_name) VALUES ('GSO Public Library'), ('Library of Congress'), ('The interwebs.');\r\nINSERT INTO docs_usage (fk_documents, fk_library_profile, place_used) VALUES (1, 1, 'somewhere'), (2, 2, 'anywhere'), (3, 3, 'everywhere'), (3, 1, 'somewhere');\r\n```\r\n\r\nThis works:\r\n\r\n```sql\r\nSELECT count(*) FROM documents_master_view WHERE public.documents_master_view.zdb ==> 'somewhere';\r\n count \r\n-------\r\n     2\r\n(1 row)\r\n```\r\n\r\nThis does not work:\r\n\r\n```sql\r\nSELECT count(*) FROM documents_master_view WHERE public.documents_master_view.zdb ==> 'GSO';\r\n count \r\n-------\r\n     0\r\n(1 row)\r\n```\r\n\r\nWhy?"},"comment":{"url":"https://api.github.com/repos/zombodb/zombodb/issues/comments/179471028","html_url":"https://github.com/zombodb/zombodb/issues/83#issuecomment-179471028","issue_url":"https://api.github.com/repos/zombodb/zombodb/issues/83","id":179471028,"user":{"login":"eeeebbbbrrrr","id":5117238,"avatar_url":"https://avatars.githubusercontent.com/u/5117238?v=3","gravatar_id":"","url":"https://api.github.com/users/eeeebbbbrrrr","html_url":"https://github.com/eeeebbbbrrrr","followers_url":"https://api.github.com/users/eeeebbbbrrrr/followers","following_url":"https://api.github.com/users/eeeebbbbrrrr/following{/other_user}","gists_url":"https://api.github.com/users/eeeebbbbrrrr/gists{/gist_id}","starred_url":"https://api.github.com/users/eeeebbbbrrrr/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/eeeebbbbrrrr/subscriptions","organizations_url":"https://api.github.com/users/eeeebbbbrrrr/orgs","repos_url":"https://api.github.com/users/eeeebbbbrrrr/repos","events_url":"https://api.github.com/users/eeeebbbbrrrr/events{/privacy}","received_events_url":"https://api.github.com/users/eeeebbbbrrrr/received_events","type":"User","site_admin":false},"created_at":"2016-02-03T21:12:42Z","updated_at":"2016-02-03T21:12:42Z","body":"After fixing your example so it actually plays in to psql, a few comments.\r\n\r\nFirst, the shadow index definition needs to describe how to find the `library_profile` table.  In your example, the term `GSO` only appears in that table, and there's no path to that table through the index options.  So the index definition would need to look like this:\r\n\r\n```sql\r\nCREATE INDEX es_idx_zdb_to_docs_usage \r\n                     ON public.documents \r\n                USING zombodb (zdb_to_docs_usage('public.documents', ctid), zdb(documents.*))\r\n                 WITH (shadow='es_documents', \r\n                        options ='\r\n                                  docs_usage_data:(pk_documents=<docs_usage.es_docs_usage>fk_documents),\r\n                                  fk_library_profile=<library_profile.es_library_profile>pk_library_profile\r\n                                 '\r\n                 );\r\n```\r\n\r\nNote that the above describes a linkage to library_profile (`fk_library_profile=<library_profile.es_library_profile>pk_library_profile`).\r\n\r\nThat said, once you do that, it exposes a bug in ZDB where it still doesn't work.  Based on the index `options`, ZDB builds a graph of relationships between indexes.  Unfortunately there's a small bug where it's assuming, in this case, that the `fk_library_profile` field lives in `documents` instead of `docs_usage`.  \r\n\r\nAfter fixing that, ZDB starts to complain with `QueryRewriteException[Don't know how to resolve multiple levels of indirection]` which comes from around [line 1265 of `QueryRewriter.java`](https://github.com/zombodb/zombodb/blob/502bbb6ef46dffe33f8e4ae0118459ed02d093f7/elasticsearch/src/main/java/com/tcdi/zombodb/query_parser/QueryRewriter.java#L1265).\r\n\r\nSo I'm looking into how to actually get this implemented.  This doesn't look straightforward, so I'm not sure how long it's going to take..."}}