{"action":"created","issue":{"url":"https://api.github.com/repos/pipelinedb/pipelinedb/issues/1774","repository_url":"https://api.github.com/repos/pipelinedb/pipelinedb","labels_url":"https://api.github.com/repos/pipelinedb/pipelinedb/issues/1774/labels{/name}","comments_url":"https://api.github.com/repos/pipelinedb/pipelinedb/issues/1774/comments","events_url":"https://api.github.com/repos/pipelinedb/pipelinedb/issues/1774/events","html_url":"https://github.com/pipelinedb/pipelinedb/issues/1774","id":215087823,"number":1774,"title":"PipelineDB <-> PostgreSQL interface","user":{"login":"derekjn","id":384666,"avatar_url":"https://avatars2.githubusercontent.com/u/384666?v=3","gravatar_id":"","url":"https://api.github.com/users/derekjn","html_url":"https://github.com/derekjn","followers_url":"https://api.github.com/users/derekjn/followers","following_url":"https://api.github.com/users/derekjn/following{/other_user}","gists_url":"https://api.github.com/users/derekjn/gists{/gist_id}","starred_url":"https://api.github.com/users/derekjn/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/derekjn/subscriptions","organizations_url":"https://api.github.com/users/derekjn/orgs","repos_url":"https://api.github.com/users/derekjn/repos","events_url":"https://api.github.com/users/derekjn/events{/privacy}","received_events_url":"https://api.github.com/users/derekjn/received_events","type":"User","site_admin":false},"labels":[{"id":474093110,"url":"https://api.github.com/repos/pipelinedb/pipelinedb/labels/extension%20refactor","name":"extension refactor","color":"06f98c","default":false},{"id":300447438,"url":"https://api.github.com/repos/pipelinedb/pipelinedb/labels/question","name":"question","color":"0ff0c3","default":true}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":{"url":"https://api.github.com/repos/pipelinedb/pipelinedb/milestones/48","html_url":"https://github.com/pipelinedb/pipelinedb/milestone/48","labels_url":"https://api.github.com/repos/pipelinedb/pipelinedb/milestones/48/labels","id":2065064,"number":48,"title":"1.0.0","description":"","creator":{"login":"usmanm","id":853039,"avatar_url":"https://avatars3.githubusercontent.com/u/853039?v=3","gravatar_id":"","url":"https://api.github.com/users/usmanm","html_url":"https://github.com/usmanm","followers_url":"https://api.github.com/users/usmanm/followers","following_url":"https://api.github.com/users/usmanm/following{/other_user}","gists_url":"https://api.github.com/users/usmanm/gists{/gist_id}","starred_url":"https://api.github.com/users/usmanm/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/usmanm/subscriptions","organizations_url":"https://api.github.com/users/usmanm/orgs","repos_url":"https://api.github.com/users/usmanm/repos","events_url":"https://api.github.com/users/usmanm/events{/privacy}","received_events_url":"https://api.github.com/users/usmanm/received_events","type":"User","site_admin":false},"open_issues":5,"closed_issues":0,"state":"open","created_at":"2016-10-12T14:29:44Z","updated_at":"2017-03-18T17:23:43Z","due_on":"2017-07-07T07:00:00Z","closed_at":null},"comments":17,"created_at":"2017-03-17T18:34:33Z","updated_at":"2017-04-02T06:47:52Z","closed_at":null,"body":"All of the internal refactoring work for #1596 is fairly straightforward and well defined. The most important thing in my mind that needs to be figured out is what exactly we want the PipelineDB <-> PostgreSQL interface to look and feel like at a high level. That is, how do users interact with PipelineDB's abstractions now that we're going to only be using PostgreSQL's interface/syntax?\r\n\r\nI see a few different approaches we can take here. In no particular order,\r\n\r\n## 0) Keep PipelineDB syntax\r\n\r\nContinuous views, transforms, and streams are fairly unique and simply do not map very directly to any existing PG objects. So it's nice to have a small bit of syntax to represent these objects, and helps new users become familiar with PipelineDB's abstractions. It also makes documentation more distinctive and precise.\r\n\r\nHowever, PG does not support syntax extension so this approach would require working around that. Fundamentally we'd probably need a very thin shim process in front of PostgreSQL that detects and handles PipelineDB DDL statements.\r\n\r\nIf it's a `CREATE CONTINUOUS VIEW`, `CREATE CONTINUOUS TRANSFORM`, or `CREATE STREAM`, statement, we'd transpile the query into a valid PG query using function calls. Otherwise, the statement would just be quickly passed off to PG.\r\n\r\nObviously this isn't ideal, but I do see quite a bit of value in keeping our syntax for our primitives. This approach would also incur minimal migration friction.\r\n\r\n## 1) Map PipelineDB objects to PostgreSQL objects\r\n\r\nStreams are already foreign tables internally, so if we simply removed `CREATE STREAM` syntax and required that streams be created with the foreign table interface, we'd be able to to detect when queries are reading from a stream. That would allow us to leverage existing syntax to create continuous views and transforms.\r\n\r\nFor example, you could create a stream like so:\r\n\r\n```sql\r\nCREATE FOREIGN TABLE stream (x integer) SERVER pipelinedb_stream;\r\n```\r\n\r\nAnd perhaps use `CREATE MATERIALIZED VIEW` syntax to create a continuous view: \r\n\r\n```sql\r\nCREATE MATERIALIZED VIEW continuous_view AS SELECT count(*) FROM stream;\r\n```\r\n\r\nSo we'd just detect the stream in the `FROM` clause, and create a continuous view accordingly. Similarly, for transforms we could use a `VIEW` over a stream:\r\n\r\n```sql\r\nCREATE VIEW continuous_transform AS SELECT transform(x) FROM stream;\r\n```\r\n\r\nThese objects map relatively intuitively to PipelineDB objects. The downside is that their behavior is quite a bit different: The \"materialized view\" in this case would be automatically updating, whereas actual materialized views are updated via `REFRESH`. And the \"view\" would be writing rows out to another stream (its output stream), which is kind of weird for a view.\r\n\r\n## 2) Use functions for everything\r\n\r\nWe could just encapsulate all PipelineDB DDL functionality in function calls. This is what **approach 0)** would do internally. For example,\r\n\r\n```sql\r\n-- CREATE STREAM\r\nSELECT pipelinedb.create_stream('stream_name', '(x integer, y integer)');\r\n\r\n-- CREATE CONTINUOUS VIEW\r\nSELECT pipelinedb.create_continuous_view('cv_name',\r\n  'SELECT x, count(*) FROM stream GROUP BY x');\r\n\r\n-- CREATE CONTINUOUS TRANSFORM\r\nSELECT pipelinedb.create_continuous_transform('xform_name',\r\n  'SELECT transform(x) FROM stream');\r\n```\r\n\r\nThis is probably the most straightforward approach, but does feel a bit lacking. It feels weird to be writing SQL within strings, rather than being able to leverage things like syntax highlighting.\r\n\r\n--------\r\n\r\nThere may be fundamentally better approaches, or perhaps the best one is some combination of the above. We'd love to get some user feedback here and have a discussion about what the best design looks like. Getting this interface just right as we become an extension is the most important part of the remaining work.\r\n\r\n\r\n\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/pipelinedb/pipelinedb/issues/comments/290968382","html_url":"https://github.com/pipelinedb/pipelinedb/issues/1774#issuecomment-290968382","issue_url":"https://api.github.com/repos/pipelinedb/pipelinedb/issues/1774","id":290968382,"user":{"login":"jberkus","id":115146,"avatar_url":"https://avatars3.githubusercontent.com/u/115146?v=3","gravatar_id":"","url":"https://api.github.com/users/jberkus","html_url":"https://github.com/jberkus","followers_url":"https://api.github.com/users/jberkus/followers","following_url":"https://api.github.com/users/jberkus/following{/other_user}","gists_url":"https://api.github.com/users/jberkus/gists{/gist_id}","starred_url":"https://api.github.com/users/jberkus/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jberkus/subscriptions","organizations_url":"https://api.github.com/users/jberkus/orgs","repos_url":"https://api.github.com/users/jberkus/repos","events_url":"https://api.github.com/users/jberkus/events{/privacy}","received_events_url":"https://api.github.com/users/jberkus/received_events","type":"User","site_admin":false},"created_at":"2017-04-02T06:47:52Z","updated_at":"2017-04-02T06:47:52Z","body":"There has been no public work on streaming core Postgres since TelegraphCQ went proprietary, AFAIK.\r\n\r\nAnd not everything needs to be a core Postgres feature.  If anything, the extensions interfaces are possibly PostgreSQL's \"killer feature\"."}}
{"actor":{"display_login":"jberkus"}}