minato_query (minato v0.18.6)
View SourceRunning SQL on a connection, in both of the protocol's two query paths.
simple/2 is the simple query protocol: one message, any number of statements,
every value in the text format, and no parameters. query/3 is the extended
protocol: Parse, Describe, Bind, Execute, Sync, with parameters sent
as parameters and results asked for in the binary format wherever minato has a
codec for the type.
Both take a connection and hand one back, because the connection carries the bytes that have arrived but not been read. A result and a connection to carry on from is the whole return value:
{ok, #{rows := [{1}]}, Conn1} = minato_query:query(Conn, ~"SELECT $1::int", [1]).Which formats are asked for
Describe says what the parameter types are and what the result columns are,
and minato_codec:preferred_format/1 turns each of those OIDs into binary for
a type minato has a codec for and text for one it does not. A column of an
extension type therefore still arrives, as its text representation, rather than
failing the query.
RowDescription from a Describe reports every column as text, because the
formats are not chosen until Bind. minato replaces those with what it asked
for before decoding, since believing the description would decode binary bytes
as text on the first integer column.
Errors leave the connection usable
A statement that fails is a normal outcome. {error, {pgsql_error, Fields}, Conn} hands the connection back because the extended protocol says a failed
statement is discarded until Sync, and this module reads on to
ReadyForQuery before returning, so the connection really is ready.
A connection returned in the error tuple is usable. One that is not usable is
not returned: {error, {socket, _}} has no connection in it, because a socket
that failed mid-message leaves the stream at an unknown offset, and there is
nothing to hand back that could be trusted.
What is not here
No transactions and no LISTEN. Both are session state rather than statements,
and both need the connection to be pinned to the caller for longer than one
query, so they belong with the pool that hands connections out.
Summary
Types
What went wrong.
Decoding options, plus how long the statement may run.
The map carried by an error({minato_query, Report}) exception.
A parsed statement on the server, with what it takes and what it returns.
Functions
cached/4 with the default decoding options.
Run a statement, parsing it once per connection and keeping it.
Close a prepared statement and free what the server holds for it.
execute/4 with the default decoding options.
Run a prepared statement.
Parse a statement under a name and keep it for the life of the connection.
query/4 with the default decoding options.
Run one statement with parameters, through the extended protocol.
simple/3 with the default decoding options.
Run one or more statements through the simple query protocol.
Types
-type conn() :: minato_conn:conn().
-type error() :: minato_conn:error().
What went wrong.
Every failure here comes from the connection layer or from the server. A parameter count that does not match raises instead of being returned, because it is a bug in the caller rather than a condition to branch on.
-type opts() :: #{timeout => timeout(), return_rows_as_maps => boolean(), column_name_as_atom => boolean(), uuid_format => string | binary, datetime_format => datetime | microseconds, numeric_format => binary | float}.
Decoding options, plus how long the statement may run.
#{timeout => timeout(),
return_rows_as_maps => boolean(),
column_name_as_atom => boolean(),
uuid_format => string | binary,
datetime_format => datetime | microseconds,
numeric_format => binary | float}Everything but timeout is passed to minato_protocol unchanged. timeout
becomes a deadline on the statement: when it expires the statement is cancelled
on the server rather than abandoned, and comes back as 57014 with the
connection still usable. It defaults to the connection's own read timeout, so a
statement that outlives that costs a statement rather than a connection. See
minato_conn:deadline/2.
The map carried by an error({minato_query, Report}) exception.
-type result() :: minato_protocol:result().
-opaque statement()
A parsed statement on the server, with what it takes and what it returns.
Belongs to the connection it was prepared on: a name is only meaningful to the session that parsed it, so a statement cannot be carried to another connection.
Functions
-spec cached(conn(), iodata(), [term()]) -> {ok, result(), conn()} | {error, error(), conn()} | {error, error()}.
cached/4 with the default decoding options.
-spec cached(conn(), iodata(), [term()], opts()) -> {ok, result(), conn()} | {error, error(), conn()} | {error, error()}.
Run a statement, parsing it once per connection and keeping it.
The same SQL on the same connection costs one round trip after the first, which
is what query/3 cannot do: it parses unnamed every time, because it has
nowhere to keep anything. A pooled caller gets a different connection each time
and would pay the parse on each of them; a connection that keeps its statements
pays it once per connection instead.
The cache is the connection's, bounded by prepared_statements in
minato_conn:opts/0, and a connection that is full runs anything new unnamed.
A workload with unbounded distinct SQL therefore gets the uncached path rather
than an unbounded pile of parsed statements in the backend.
A cached plan the server stops accepting
Changing a table under a prepared statement makes PostgreSQL refuse it with
0A000, because the result type it was planned for is not the one it would
return now. That is not an error the caller can do anything with, so it is not
returned: the statement is dropped from the cache, parsed again, and run once
more. A second 0A000 is a real failure and is returned.
Close a prepared statement and free what the server holds for it.
A connection that is handed back to a pool with a hundred one-off named statements on it is a connection leaking server memory for the rest of its life.
-spec execute(conn(), statement(), [term()]) -> {ok, result(), conn()} | {error, error(), conn()} | {error, error()}.
execute/4 with the default decoding options.
-spec execute(conn(), statement(), [term()], opts()) -> {ok, result(), conn()} | {error, error(), conn()} | {error, error()}.
Run a prepared statement.
Raises error({minato_query, t:report/0}) when the number of parameters does
not match what the statement was described as taking. That is a bug in the
caller rather than a condition to branch on: the server would refuse it too, a
round trip later and with a worse message.
The connection is put back to ready before the exception is raised, with a
Sync and a read to ReadyForQuery. Raising in the middle of an extended query
cycle would otherwise leave a connection that is only good for closing, which is
a heavy price for what is usually a typo.
-spec prepare(conn(), binary(), iodata()) -> {ok, statement(), conn()} | {error, error(), conn()} | {error, error()}.
Parse a statement under a name and keep it for the life of the connection.
The describe round trip happens here rather than on every execute/3, which is
the point: a statement run in a loop costs one round trip a time.
The name is the caller's. Reusing one replaces what it named, and close/2
gives it back.
-spec query(conn(), iodata(), [term()]) -> {ok, result(), conn()} | {error, error(), conn()} | {error, error()}.
query/4 with the default decoding options.
-spec query(conn(), iodata(), [term()], opts()) -> {ok, result(), conn()} | {error, error(), conn()} | {error, error()}.
Run one statement with parameters, through the extended protocol.
The statement is parsed unnamed, so it is replaced by the next one and nothing
accumulates on the server. A statement that will run again belongs in
prepare/3, which parses it once and keeps it.
Two round trips: Parse and Describe have to be answered before the
parameters can be encoded, because their types are the server's to decide.
That is the price of not guessing them, and prepare/3 pays it once.
-spec simple(conn(), iodata()) -> {ok, [result()], conn()} | {error, error(), conn()} | {error, error()}.
simple/3 with the default decoding options.
-spec simple(conn(), iodata(), opts()) -> {ok, [result()], conn()} | {error, error(), conn()} | {error, error()}.
Run one or more statements through the simple query protocol.
Answers with one result per statement, in order, which is why this is the only function here that returns a list. Every value comes back in the text format, because the simple query protocol has nowhere to ask for anything else.
There are no parameters. A value pasted into the SQL is an injection waiting to
be found, so anything with a value in it belongs in query/3.