sql: Return column types in driver.Result

[bdarnell]

driver.Result contains a list of column names; we should extend this to include type information as well (perhaps a Datum containing a zero value of the appropriate type). This would allow for types to be determined for queries that return no results, or where all returned rows had NULL for a particular column. This also implies enforcing a rule that a column cannot contain a mix of different types (if we aren't doing this already). These changes are necessary to implement the postgres wire protocol.

[bdarnell]

The PostgreSQL protocol requires that type information for a prepared statement be available as soon as it is prepared (before placeholders are filled in). So we'll need a separate method (on sql.Executor; doesn't need to be an RPC) to type-check a query containing placeholders, returning both the result types and the types required for each placeholder

[petermattis]

We already enforce that a result column is of a particular type (or NULL). Type-checking a query before executing it and determining the types of the result columns is straightforward. But how do we determine the type required for each placeholder? For certain expressions I can see how this would make sense (e.g. in lower($1) the placeholders needs to be a string). But what about an expression like CAST($1 AS string)? In that expression, the placeholder can be any type. Can you investigate what postgres does?

[bdarnell]

Postgres fails the prepare if the type is ambiguous. You either need a type in the query ($1::int), or the driver has the option of passing parameter type information out of band (the go driver doesn't).

func TestFoo(t *testing.T) {
    db, err := sql.Open("postgres", "sslmode=disable user=postgres")
    if err != nil {
        t.Fatal(err)
    }

    _, err = db.Query("select $1", 42)
    if err != nil {
        t.Fatal(err)
    }
}

--- FAIL: TestFoo (0.00s)
    foo_test.go:35: pq: could not determine data type of parameter $1
FAIL

[petermattis]

For the $1::int case, what is the required type of the $1 placeholder? Seems like it could be any type.

[bdarnell]

The server returns that the type of $1 is int4 (i.e. int32). Strings will still work, though, because the default is to send everything in textual format to be parsed (i.e. integers are sent in base-10 ascii). Binary formats are only used for a small number of types (integers and byte arrays, and the fact that they need both binary and text formats for byte arrays kind of scares me), and only when the client and server agree on what the type is. So if you pass a string for $1::int, the server wants int4 but the client doesn't have it, and sends the string instead in the hope that the server can parse it. Eww.

[petermattis]

Ugh, that's so odd. There is nothing about the expression $1::int that is indicating $1 should be an int4. If the expression is $1::text what does the server expect? I hope it isn't text because that doesn't make sense. The reason for the cast is that $1 is probably not-text. Is there someway to avoid needing the required placeholder type? Or using some type that would be universally ok (e.g. text).

[bdarnell]

For $1::text the server asks for text. This is a mess. It manages to mostly work because text is already the lowest common denominator, but it has a lot of rough edges.

Part of this appears to be the go driver's fault. Without placeholders, 3.14::int returns 3. With placeholders from go, Query("select $1::int", 3.14) returns an error: pq: invalid input syntax for integer: "3.14". But at the psql command line, PREPARE foo AS SELECT $1::int; EXECUTE foo (3.14) returns 3. It's not clear what exactly the two clients are doing differently.

[petermattis]

I'll have time tomorrow or Wednesday to work on this issue, but I'm not clear on what to do. Type checking a query and getting the result types is straightforward. But I don't see what the semantics for placeholders should be. For example, what is the required placeholder type for abs($1)? What about $1 + 1 vs $1 + 1.1?

[bdarnell]

Empirically, postgres says float8 for abs($1) and numeric for $1+1.1 ($1+$2 is an error). It seems to be relying on text format and what amounts to an implicit cast from text to anything else. I think to make this work for our stricter type system, we need type checking to give us enough information to turn a string into an acceptable Datum. This could take the form of either a list of acceptable Datum types, or a system of aliases for all known combinations (e.g. "numeric" = (int | float)).

[bdarnell]

One more oddity: for $1 + interval '1h', the postgres server asks for an interval, and will not accept a timestamp. This isn't just the go driver; it happens in psql too:

postgres=# prepare foo as select $1 + interval '1h';
PREPARE
postgres=# execute foo (now());
ERROR:  parameter $1 of type timestamp with time zone cannot be coerced to the expected type interval
HINT:  You will need to rewrite or cast the expression.
postgres=# 

To make this work you have to specify $1::timestamp.

[petermattis]

It looks like the postgres protocol supports sending the parameter types in the Parse request, but lib/pq never does, instead only sending the parameters in the Bind request.

[bdarnell]

Right. lib/pq can't send the types in Parse in the general case (prepared statements) because the API is Prepare(string) and the types aren't known yet (this is very common across many languages' DB driver interfaces).

Sometimes it could send the types (when it's using Parse as a part of the extended query protocol for a single statement, instead of a prepared statement to be executed later), but it doesn't currently implement this optimization. This optimization would be useful for us so we should consider submitting it to lib/pq at some point, but we should proceed under the assumption that at least sometimes clients cannot or will not send type information and we have to generate the types on the server side.