Thread: Feasibility of supporting bind params for all command types
Hi all While looking at an unrelated issue in PgJDBC I noticed that it's difficult for users and the driver to tell in advance if a given statement will support bind parameters. PostgreSQL just treats placeholders as syntax errors for non-plannable statements at parse time. This forces users to try to guess whether a given statement can be parameterised or not, or forces drivers to guess this on behalf of users and do client-side parameter substitution. As a result, some code that worked with PgJDBC using the v2 protocol will fail with the v3 protocol, e.g. @Test public void test() throws SQLException {PGConnection pgc = (PGConnection)conn;PreparedStatement ps = conn.prepareStatement("SETROLE ?");ps.setString(1, "somebody");ps.executeUpdate(); } This works with the v2 protocol because PgJDBC does client side parameter binding unless you request sever-side prepare (via SQL-level PREPARE and EXECUTE). With the v3 protocol it always uses the extended parse/bind/execute flow, with unnamed statements. (Another case where this is quite frustrating is COPY, though PgJDBC has a wrapper API for COPY that helps cover that up.) It'd be nice not to force users to do their own escaping of literals in non-plannable statements. Before embarking on anything like this I thought I'd check and see if anyone's looked into supporting bind parameters in utility statements, or if not, if anyone has any ideas about the feasibility of adding such support. I didn't have much luck searching for discussion on the matter. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
Craig Ringer <craig@2ndquadrant.com> writes: > While looking at an unrelated issue in PgJDBC I noticed that it's > difficult for users and the driver to tell in advance if a given > statement will support bind parameters. It's not that hard ;-) ... if it ain't SELECT/INSERT/UPDATE/DELETE, it won't accept parameters. > As a result, some code that worked with PgJDBC using the v2 protocol > will fail with the v3 protocol, e.g. > @Test > public void test() throws SQLException { > PGConnection pgc = (PGConnection)conn; > PreparedStatement ps = conn.prepareStatement("SET ROLE ?"); > ps.setString(1, "somebody"); > ps.executeUpdate(); > } It's more or less accidental that that works, I think. I assume that the statement that actually gets sent to the server looks like SET ROLE 'something' which morally ought to be a syntax error: you'd expect the role name to be an identifier (possibly double-quoted). Not a singly-quoted string literal. We allow a string literal because for some weird reason the SQL standard says so, but it still feels like a type violation. > It'd be nice not to force users to do their own escaping of literals in > non-plannable statements. Before embarking on anything like this I > thought I'd check and see if anyone's looked into supporting bind > parameters in utility statements, or if not, if anyone has any ideas > about the feasibility of adding such support. I think it might be desirable but it'd be a mess, both as to the concept/definition and as to the implementation. How would a parameter placeholder substitute for an identifier --- for example, what type would be reported by "Describe"? What would you do about parameter placeholders in expressions in DDL --- for example, CREATE TABLE mytable (f1 int default ?+? ); Here, the placeholders surely don't represent identifiers, but the system is going to have a hard time figuring out what datatype they *should* represent. Carrying that example a bit further, I wonder what the chances are of doing something sane or useful with CREATE TABLE ? (? ? default ?+? ); But if you want to punt on that, I think you just greatly weakened your argument for the whole thing. On the implementation side, I'm worried about how we make sure that parameter placeholders get replaced in a DDL expression that would normally *not* get evaluated immediately, like the DEFAULT expression above. regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 Tom Lane said: ... > Craig Ringer <craig@2ndquadrant.com> writes: >> While looking at an unrelated issue in PgJDBC I noticed that it's >> difficult for users and the driver to tell in advance if a given >> statement will support bind parameters. > > It's not that hard ;-) ... if it ain't SELECT/INSERT/UPDATE/DELETE, > it won't accept parameters. Yes, it is as easy as that. That's exactly what DBD::Pg does - looks at the first word of the statement. Although you also need to add VALUES and WITH to that list. :) >> As a result, some code that worked with PgJDBC using the v2 protocol >> will fail with the v3 protocol, e.g. >> >> It'd be nice not to force users to do their own escaping of literals in >> non-plannable statements. Before embarking on anything like this I >> thought I'd check and see if anyone's looked into supporting bind >> parameters in utility statements, or if not, if anyone has any ideas >> about the feasibility of adding such support. I don't think that's a hill you want to conquer. Let that code relying on v2 behavior get rewritten, or make the driver smart enough to handle it automagically the best it can. - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201410060710 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAlQyeNIACgkQvJuQZxSWSshYewCgg/EmgTbPp5KnfUpYfga8nsee GVMAniXC+FxHFsiuT07idP8Tw70gCoBe =a20X -----END PGP SIGNATURE-----
On 10/06/2014 10:13 AM, Tom Lane wrote: > I think it might be desirable but it'd be a mess, both as to the > concept/definition and as to the implementation. Thanks Tom. The issues around ALTER etc pretty much put it in the not-worth-caring-about bucket. The issues around parameter typing alone... I think we just need to add support for client-side parameter binding of literals with a client-side flag, or by detecting statement type. So users still get to use bind parameters, but PgJDBC deals with the details. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services