Lists: | pgsql-hackers |
---|
From: | Craig Ringer <craig(at)2ndquadrant(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Feasibility of supporting bind params for all command types |
Date: | 2014-10-06 01:32:28 |
Message-ID: | 5431F12C.3050003@2ndquadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Craig Ringer <craig(at)2ndquadrant(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Feasibility of supporting bind params for all command types |
Date: | 2014-10-06 02:13:53 |
Message-ID: | 301.1412561633@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Craig Ringer <craig(at)2ndquadrant(dot)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
From: | "Greg Sabino Mullane" <greg(at)turnstep(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Feasibility of supporting bind params for all command types |
Date: | 2014-10-06 11:12:17 |
Message-ID: | 87b663faa5b362fdd37320a176e5afe9@biglumber.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
Tom Lane said:
...
> Craig Ringer <craig(at)2ndquadrant(dot)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(at)turnstep(dot)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-----
From: | Craig Ringer <craig(at)2ndquadrant(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Feasibility of supporting bind params for all command types |
Date: | 2014-10-07 09:42:39 |
Message-ID: | 5433B58F.2090108@2ndquadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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