{"action":"created","issue":{"url":"https://api.github.com/repos/npgsql/npgsql/issues/447","labels_url":"https://api.github.com/repos/npgsql/npgsql/issues/447/labels{/name}","comments_url":"https://api.github.com/repos/npgsql/npgsql/issues/447/comments","events_url":"https://api.github.com/repos/npgsql/npgsql/issues/447/events","html_url":"https://github.com/npgsql/npgsql/issues/447","id":53205767,"number":447,"title":"Switch to 100% binary and extended protocol everywhere?","user":{"login":"roji","id":1862641,"avatar_url":"https://avatars.githubusercontent.com/u/1862641?v=3","gravatar_id":"","url":"https://api.github.com/users/roji","html_url":"https://github.com/roji","followers_url":"https://api.github.com/users/roji/followers","following_url":"https://api.github.com/users/roji/following{/other_user}","gists_url":"https://api.github.com/users/roji/gists{/gist_id}","starred_url":"https://api.github.com/users/roji/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/roji/subscriptions","organizations_url":"https://api.github.com/users/roji/orgs","repos_url":"https://api.github.com/users/roji/repos","events_url":"https://api.github.com/users/roji/events{/privacy}","received_events_url":"https://api.github.com/users/roji/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":{"login":"roji","id":1862641,"avatar_url":"https://avatars.githubusercontent.com/u/1862641?v=3","gravatar_id":"","url":"https://api.github.com/users/roji","html_url":"https://github.com/roji","followers_url":"https://api.github.com/users/roji/followers","following_url":"https://api.github.com/users/roji/following{/other_user}","gists_url":"https://api.github.com/users/roji/gists{/gist_id}","starred_url":"https://api.github.com/users/roji/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/roji/subscriptions","organizations_url":"https://api.github.com/users/roji/orgs","repos_url":"https://api.github.com/users/roji/repos","events_url":"https://api.github.com/users/roji/events{/privacy}","received_events_url":"https://api.github.com/users/roji/received_events","type":"User","site_admin":false},"milestone":{"url":"https://api.github.com/repos/npgsql/npgsql/milestones/3","labels_url":"https://api.github.com/repos/npgsql/npgsql/milestones/3/labels","id":620657,"number":3,"title":"3.0","description":"","creator":{"login":"franciscojunior","id":829924,"avatar_url":"https://avatars.githubusercontent.com/u/829924?v=3","gravatar_id":"","url":"https://api.github.com/users/franciscojunior","html_url":"https://github.com/franciscojunior","followers_url":"https://api.github.com/users/franciscojunior/followers","following_url":"https://api.github.com/users/franciscojunior/following{/other_user}","gists_url":"https://api.github.com/users/franciscojunior/gists{/gist_id}","starred_url":"https://api.github.com/users/franciscojunior/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/franciscojunior/subscriptions","organizations_url":"https://api.github.com/users/franciscojunior/orgs","repos_url":"https://api.github.com/users/franciscojunior/repos","events_url":"https://api.github.com/users/franciscojunior/events{/privacy}","received_events_url":"https://api.github.com/users/franciscojunior/received_events","type":"User","site_admin":false},"open_issues":40,"closed_issues":20,"state":"open","created_at":"2014-04-04T16:37:19Z","updated_at":"2015-01-02T10:01:53Z","due_on":null,"closed_at":null},"comments":22,"created_at":"2014-12-31T22:06:40Z","updated_at":"2015-01-02T17:24:42Z","closed_at":null,"body":"In #370 we discussed various aspects of switching to the extended query protocol, and also to binary encoding. @Emill and I discussed this again recently on our [gitter channel](https://gitter.im/npgsql/npgsql), and I wanted to get your opinions (@franciscojunior @jbcooley @glenebob). I'm sorry if revisiting this subject again is annoying.\r\n\r\nTo recall the situation, we can only request binary values using the extended query protocol, and we currently use extended query protocol only in prepared statements. In #370 we already discussed switching to the extended query protocol for non-prepared parameterized queries; this would remove a big chunk of complexity in client-side query rewriting, allow use of binary encoding when receiving values, etc. Once we do this we can also drop text encoding entirely for *sending* values to the database.\r\n\r\nSo after thinking about the whole thing some more, I now think that v3.0 should be a 100% binary-only, extended-query-only provider - I can see no real reasons for maintaining either text encoding or the simple protocol. The new read_refactor branch implements binary encoding for almost all PostgreSQL built-in types, including exotic types such as geometric and networking types, and the few types that haven't been binarized can be done for v3.0.\r\n\r\nNow, the main argument against going binary-only was unknown types - types defined by extensions are unknown to Npgsql so they're binary representation can't be decoded. With text encoding we can at least hand off the string to the user. To address this problem, we can allow a user to turn on a \"text-only\" flag on a command before executing it. This would make Npgsql request all results in text, and the user can access them as strings. The only drawback I can see is that one can't mix text and binary in the same query, so even simple ints have to be accessed as strings. The user has the option of either separating \"unknown-type queries\" from normal queries, or to deal with text representations of known types themselves - IMHO this is a very acceptable situation for the extremely rare unknown type problem (which will become even rarer as we implement support for more and more types).\r\n\r\nNote that as @franciscojunior suggested, the unknown type problem can be partially mitigated also by allowing external type handlers to be registered with Npgsql (although I think it's a bit early to actually allow that in 3.0).\r\n\r\nPros:\r\n* A transparent performance boost thanks to binary encoding. With the current situation, users have to be aware that binary encoding requires preparing the query, the fact that this entails two roundtrips... It's complicated and it involves users in the inner workings of both Npgsql and the PostgreSQL protocol. Making everything binary and extended-protocol makes the performance boost accessible to everyone in a transparent and easy way.\r\n* No more mucking around with locale settings which affect text representations (e.g. lc_monetary for money which causes #163, DateStyle=ISO...). Note that Npgsql users may want to work with PostgreSQL functions which depend on these settings, and Npgsql interferes with them to make text decoding possible.\r\n* No more loss of precision converting floating point values to and from text. Binary \"conversion\" is 100% accurate. (see #324).\r\n* Greatly simplifies our code base: text encoding and the simple protocol can simply be cut out, no more need to maintain two encoding paths for each type, etc. Npgsql becomes very lean and lightweight without losing any real functionality.\r\n\r\nCons:\r\n* Unknown (or text-only) types are a bit clumsy to use - they can't be easily mixed with known types (a query is either all-binary or all-text).\r\n* Multi-resultset queries aren't supported with the extended protocol. We'd have to fake them by sending multiple extended-protocol queries (but this can still be done in a single message, so only one roundtrip).\r\n* There was some mention of the extended protocol being less efficient than the simple protocol. We need to evaluate this with an actual performance test, but I also think the performance gains due to binary encoding should at the very least offset this.\r\n"},"comment":{"url":"https://api.github.com/repos/npgsql/npgsql/issues/comments/68544132","html_url":"https://github.com/npgsql/npgsql/issues/447#issuecomment-68544132","issue_url":"https://api.github.com/repos/npgsql/npgsql/issues/447","id":68544132,"user":{"login":"franciscojunior","id":829924,"avatar_url":"https://avatars.githubusercontent.com/u/829924?v=3","gravatar_id":"","url":"https://api.github.com/users/franciscojunior","html_url":"https://github.com/franciscojunior","followers_url":"https://api.github.com/users/franciscojunior/followers","following_url":"https://api.github.com/users/franciscojunior/following{/other_user}","gists_url":"https://api.github.com/users/franciscojunior/gists{/gist_id}","starred_url":"https://api.github.com/users/franciscojunior/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/franciscojunior/subscriptions","organizations_url":"https://api.github.com/users/franciscojunior/orgs","repos_url":"https://api.github.com/users/franciscojunior/repos","events_url":"https://api.github.com/users/franciscojunior/events{/privacy}","received_events_url":"https://api.github.com/users/franciscojunior/received_events","type":"User","site_admin":false},"created_at":"2015-01-02T17:24:42Z","updated_at":"2015-01-02T17:24:42Z","body":"> Well, the real question is what actually happens when the user sends the query, I guess it's less important what happens with the source code (after all we always have git if we want to roll back).\r\n\r\nWhoops, I think I didn't explain it well. What I meant was to let the non binary code available and usable through a flag in case of problems until we squash out all the problems we are getting with all binary. :) This way, the user would use the binary only encoding first and if there is any problem, she can fallback to the non binary path. Of course we also can suggest her to rollback the upgrade and use a pre-3.0 version. But I think this should be our last alternative. Also, all this would only happen if we can't fix all the bugs. :)\r\n\r\n\r\n> The problem is the PostgreSQL limitation which forces us to decide, at query sending time, how we want the result (text or binary), but we don't know anything at that point. If PostgreSQL supported a mechanism where we could declare when connecting that we always wants ints as binary, the whole problem wouldn't be there. But since we can't do that, we're pretty much stuck either going full-binary or keeping full support for text.\r\n\r\n\r\nI see. We could mark only the types we wanted as binary and the others would automatically be returned as text. I think we have good problem at hands. But as long users don't use types we don't handle, it is ok. Do we have a table of currently unsupported binary datatypes?\r\n\r\nIn order to transparently handle this binary/text mix we would need to exercise the Describe command in the portal. From the documentation, it returns the datatype of each column being returned and we could use that to change how we want the columns values to be returned. It would have a hit in the performance though. We could make this strategy available through a flag in the connection string in case the user finds problems when querying the database. What do  you think?\r\n\r\n\r\n\r\n> That's a good suggestion - if you could have a look at the text vs. binary and simple vs. extended situation that would be really interesting (if not I'll to try to look).\r\n\r\nSure, I'll have a look.\r\n\r\n\r\n> In these cases the user at least gets a string representation of the field, and they can parse it textually and do what they want. That's the most we can provide in terms of forward-compatibility I think...\r\n\r\nI think this is a good compromise. Until we get a proper handler available. Also, I think this will seldom happen  because most of the common datatypes are already covered. \r\n\r\n\r\n> The problem is that we have no way of detecting anything in advance without going through a second roundtrip: if the user sends \"SEND * FROM data\", there's nothing we can know about the results in advance...\r\n\r\nYes, I know. But as a fallback strategy, I think it could be used. \r\n\r\nWe currently have then, two strategies:\r\n1. Adapt the unknown handler to return a textual representation of the binary data returned for unknown types.\r\n2. Exercise the Describe method to return information about the unknown types and for them, ask the server to return as text. This would have a performance hit but would allow the user to keep receiving data as usual.\r\n \r\n\r\n> Of course:)\r\n> \r\n> I actually feel better now about the solutions we've been discussing here, it seems like there's no real blocker for this...\r\n\r\nGreat!\r\n\r\n\r\n\r\n> The problem with the deadlocking happens because we send all the queries before we start to read any results. An alternative approach would be to send the next query only after the previous one has completed. In other words, when the user does ExecuteQuery, we split on ; and send only the first one. Then, when the user does NextResult(), only then we send the second query and so on.\r\n\r\n+1 To this idea. \r\nBetter yet if we can abstract and simplify it to make it work the most similar to the user sending a lot of single statements queries as possible. This way we don't add too much complexity to Npgsql to handle those cases. \r\n\r\nAlthough while thinking about this idea, I think we should at least run the parse for each one of them, in case of syntactical errors. Supposed that only the third statement has a syntactical error, if we execute and return data for the first 2, it would be different from the previous model where the error would be catch before returning any row, right?\r\n\r\nWe would create a lot of named prepared statements and then we would bind and execute each one of them. I think the server buffer wouldn't be filled up only with the parsing of the queries, would it? \r\nWhat do you think?\r\n\r\nIn the jdbc case, they don't need to handle this problem because the contract of the preparedstatement interface doesn't accept multiple statements as a query string.\r\n\r\nhttp://stackoverflow.com/questions/8326958/can-you-put-multiple-statements-in-one-query-string-in-oracle-jdbc\r\n\r\nFrom the discussion, the idea of multiple statements come from the support in sqlserver. That may explain why ado.net also allows it and why they don't have something like addBatch method just like there is in jdbc to handle multiple statements queries.\r\n\r\n"}}