Re: Feasibility of supporting bind params for all command types

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