Re: [HACKERS] Feasibility of supporting bind params for all command types
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 -- 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] Feasibility of supporting bind params for all command types
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Tom Lane said: ... Craig Ringer cr...@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 g...@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- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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(SET ROLE ?);
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
--
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] Feasibility of supporting bind params for all command types
Craig Ringer cr...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
