Re: [HACKERS] Proper query implementation for Postgresql driver
On Tue, Sep 30, 2014 at 1:20 AM, Craig Ringer wrote: > Frankly, I suggest dropping "simple" entirely and using only the > parse/bind/describe/execute flow in the v3 protocol. The last time I checked, that was significantly slower. http://www.postgresql.org/message-id/ca+tgmoyjkfnmrtmhodwhnoj1jwcgzs_h1r70ercecrwjm65...@mail.gmail.com -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proper query implementation for Postgresql driver
On Tue, Sep 30, 2014 at 11:06 AM, Tom Lane wrote: > Shay Rojansky writes: > > Thanks for the suggestion to look at PgJDBC, I'll do that. > > BTW, libpqtypes (http://libpqtypes.esilo.com) might be worth > studying as well. I've not used it myself, but it claims to > offer datatype-extensible processing of binary formats. > > regards, tom lane > > > -- > > It does offer that, and is indeed a good idea in the current context.
Re: [HACKERS] Proper query implementation for Postgresql driver
Shay Rojansky writes: > Thanks for the suggestion to look at PgJDBC, I'll do that. BTW, libpqtypes (http://libpqtypes.esilo.com) might be worth studying as well. I've not used it myself, but it claims to offer datatype-extensible processing of binary formats. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proper query implementation for Postgresql driver
Thanks for the answer Craig. The remaining point of confusion is not really about simple vs. extended, it's about text vs. binary. Sending parse/bind/describe/execute messages back-to-back means the results have to be all text or all binary. So the question is: are we supposed to transfer all types to and from the backend in binary? If so, that raises some difficulties (see my previous message) which I wanted to get your opinion on. Thanks for the suggestion to look at PgJDBC, I'll do that. Shay On Tue, Sep 30, 2014 at 7:20 AM, Craig Ringer wrote: > On 09/28/2014 05:53 PM, Shay Rojansky wrote: > > Hi. > > > > I'm a core developer on npgsql (the Postgresql .NET provider), we're > > hard at work on a major 3.0 version. I have a fundamental question > > that's been bugging me for a while and would like to get your opinion on > it. > > > > Npgsql currently supports three basic query modes: simple, client-side > > parameters and prepared. The first two use the Postgresql simple query > > flow (client-side parameters means the user specifies parameters > > programmatically, just like with prepared queries, but the actual > > substitution work is done client-side). Prepared uses the Postgresql > > extended query flow. > > Frankly, I suggest dropping "simple" entirely and using only the > parse/bind/describe/execute flow in the v3 protocol. > > You can use this for server-side parameter binding *without* storing a > prepared statement by using unnamed statements. > > Client-side parameter binding remains useful if you want to support > parameterisation where the PostgreSQL server its self does not, e.g. in > DDL. If you don't care about that, you could reasonably just drop client > side parameter support entirely. > > > I would, in theory, love to switch the entire thing to binary and > > thereby avoid all textual parsing once and for all. If I understand > > correctly, this means all queries must be implemented as extended > > queries, with numerous extra client-server roundtrips - which are a bit > > hard to stomach. > > What round-trips? > > You can and should send parse/bind/describe/execute messages > back-to-back without waiting for a server response. Just Sync and wait > for server response at the end. > > You can even send a parse then a stream of bind/describe/execute > messages for batch execution of a prepared statement against a list of > params, then a single Sync at the end. > > > Can someone please let me know what the recommended/best practice here > > would be? > > You might want to check out what PgJDBC does; it's fairly sane in this > area. > > -- > Craig Ringer http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >
Re: [HACKERS] Proper query implementation for Postgresql driver
On 09/28/2014 05:53 PM, Shay Rojansky wrote: > Hi. > > I'm a core developer on npgsql (the Postgresql .NET provider), we're > hard at work on a major 3.0 version. I have a fundamental question > that's been bugging me for a while and would like to get your opinion on it. > > Npgsql currently supports three basic query modes: simple, client-side > parameters and prepared. The first two use the Postgresql simple query > flow (client-side parameters means the user specifies parameters > programmatically, just like with prepared queries, but the actual > substitution work is done client-side). Prepared uses the Postgresql > extended query flow. Frankly, I suggest dropping "simple" entirely and using only the parse/bind/describe/execute flow in the v3 protocol. You can use this for server-side parameter binding *without* storing a prepared statement by using unnamed statements. Client-side parameter binding remains useful if you want to support parameterisation where the PostgreSQL server its self does not, e.g. in DDL. If you don't care about that, you could reasonably just drop client side parameter support entirely. > I would, in theory, love to switch the entire thing to binary and > thereby avoid all textual parsing once and for all. If I understand > correctly, this means all queries must be implemented as extended > queries, with numerous extra client-server roundtrips - which are a bit > hard to stomach. What round-trips? You can and should send parse/bind/describe/execute messages back-to-back without waiting for a server response. Just Sync and wait for server response at the end. You can even send a parse then a stream of bind/describe/execute messages for batch execution of a prepared statement against a list of params, then a single Sync at the end. > Can someone please let me know what the recommended/best practice here > would be? You might want to check out what PgJDBC does; it's fairly sane in this area. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proper query implementation for Postgresql driver
Thanks guys, that makes perfect sense to me... Am Sonntag, 28. September 2014 schrieb Tom Lane : > Marko Tiikkaja > writes: > > On 9/28/14, 11:53 AM, Shay Rojansky wrote: > >> [ complaint about multiple round trips in extended protocol ] > > > You don't have to do multiple round-trips for that; you can just send > > all the messages in one go. See how e.g. libpq does it in > PQexecParams(). > > Right. The key thing to understand is that after an error, the server > skips messages until it sees a Sync. So you can send out Parse, Bind, > Execute, Sync in one packet and not have to worry that the server will > attempt to execute a query that failed parsing or whatever. > > regards, tom lane >
Re: [HACKERS] Proper query implementation for Postgresql driver
Marko Tiikkaja writes: > On 9/28/14, 11:53 AM, Shay Rojansky wrote: >> [ complaint about multiple round trips in extended protocol ] > You don't have to do multiple round-trips for that; you can just send > all the messages in one go. See how e.g. libpq does it in PQexecParams(). Right. The key thing to understand is that after an error, the server skips messages until it sees a Sync. So you can send out Parse, Bind, Execute, Sync in one packet and not have to worry that the server will attempt to execute a query that failed parsing or whatever. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proper query implementation for Postgresql driver
On 9/28/14, 11:53 AM, Shay Rojansky wrote: I would, in theory, love to switch the entire thing to binary and thereby avoid all textual parsing once and for all. If I understand correctly, this means all queries must be implemented as extended queries, with numerous extra client-server roundtrips - which are a bit hard to stomach. Section 49.1.2 of the manual also states that the unnamed prepared statement and portal are optimized for the case of executing a query only once, hinting that this is the proper way to do things - but this optimization still cannot not eliminate the extra roundtrips mentioned above (PREPARE, BIND, EXECUTE). You don't have to do multiple round-trips for that; you can just send all the messages in one go. See how e.g. libpq does it in PQexecParams(). .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
