Current section

18 Versions

Jump to

Compare versions

47 files changed
+875 additions
-325 deletions
  @@ -74,6 +74,7 @@ connect(Opts) -> {ok, Connection :: epgsql:connection()} | {error, Reason :: epg
74 74 timeout => timeout(), % socket connect timeout, default: 5000 ms
75 75 async => pid() | atom(), % process to receive LISTEN/NOTIFY msgs
76 76 codecs => [{epgsql_codec:codec_mod(), any()}]}
77 + nulls => [any(), ...], % NULL terms
77 78 replication => Replication :: string()} % Pass "database" to connect in replication mode
78 79 | list().
79 80
  @@ -96,14 +97,20 @@ Only `host` and `username` are mandatory, but most likely you would need `databa
96 97 - `password` - DB user password. It might be provided as string / binary or as a fun that returns
97 98 string / binary. Internally, plain password is wrapped to anonymous fun before it is sent to connection
98 99 process, so, if `connect` command crashes, plain password will not appear in crash logs.
99 - - `{timeout, TimeoutMs}` parameter will trigger an `{error, timeout}` result when the
100 - socket fails to connect within `TimeoutMs` milliseconds.
100 + - `timeout` parameter will trigger an `{error, timeout}` result when the
101 + socket fails to connect within provided milliseconds.
101 102 - `ssl` if set to `true`, perform an attempt to connect in ssl mode, but continue unencrypted
102 103 if encryption isn't supported by server. if set to `required` connection will fail if encryption
103 104 is not available.
104 105 - `ssl_opts` will be passed as is to `ssl:connect/3`
105 106 - `async` see [Server notifications](#server-notifications)
106 107 - `codecs` see [Pluggable datatype codecs](#pluggable-datatype-codecs)
108 + - `nulls` terms which will be used to represent SQL `NULL`. If any of those has been encountered in
109 + placeholder parameters (`$1`, `$2` etc values), it will be interpreted as `NULL`.
110 + 1st element of the list will be used to represent NULLs received from the server. It's not recommended
111 + to use `"string"`s or lists. Try to keep this list short for performance!
112 + Default is `[null, undefined]`, i.e. encode `null` or `undefined` in parameters as `NULL`
113 + and decode `NULL`s as atom `null`.
107 114 - `replication` see [Streaming replication protocol](#streaming-replication-protocol)
108 115
109 116 Options may be passed as proplist or as map with the same key names.
  @@ -126,21 +133,15 @@ Asynchronous connect example (applies to **epgsqli** too):
126 133 ### Simple Query
127 134
128 135 ```erlang
129 - -type query() :: string() | iodata().
130 - -type squery_row() :: {binary()}.
136 + -include_lib("epgsql/include/epgsql.hrl").
131 137
132 - -record(column, {
133 - name :: binary(),
134 - type :: epgsql_type(),
135 - size :: -1 | pos_integer(),
136 - modifier :: -1 | pos_integer(),
137 - format :: integer()
138 - }).
138 + -type query() :: string() | iodata().
139 + -type squery_row() :: tuple() % tuple of binary().
139 140
140 141 -type ok_reply(RowType) ::
141 - {ok, ColumnsDescription :: [#column{}], RowsValues :: [RowType]} | % select
142 + {ok, ColumnsDescription :: [epgsql:column()], RowsValues :: [RowType]} | % select
142 143 {ok, Count :: non_neg_integer()} | % update/insert/delete
143 - {ok, Count :: non_neg_integer(), ColumnsDescription :: [#column{}], RowsValues :: [RowType]}. % update/insert/delete + returning
144 + {ok, Count :: non_neg_integer(), ColumnsDescription :: [epgsql:column()], RowsValues :: [RowType]}. % update/insert/delete + returning
144 145 -type error_reply() :: {error, query_error()}.
145 146 -type reply(RowType) :: ok_reply() | error_reply().
146 147
  @@ -159,7 +160,7 @@ epgsql:squery(C, "insert into account (name) values ('alice'), ('bob')").
159 160 ```erlang
160 161 epgsql:squery(C, "select * from account").
161 162 > {ok,
162 - [{column,<<"id">>,int4,4,-1,0},{column,<<"name">>,text,-1,-1,0}],
163 + [#column{name = <<"id">>, type = int4, …},#column{name = <<"name">>, type = text, …}],
163 164 [{<<"1">>,<<"alice">>},{<<"2">>,<<"bob">>}]
164 165 }
165 166 ```
  @@ -170,13 +171,12 @@ epgsql:squery(C,
170 171 " values ('joe'), (null)"
171 172 " returning *").
172 173 > {ok,2,
173 - [{column,<<"id">>,int4,4,-1,0}, {column,<<"name">>,text,-1,-1,0}],
174 + [#column{name = <<"id">>, type = int4, …}, #column{name = <<"name">>, type = text, …}],
174 175 [{<<"3">>,<<"joe">>},{<<"4">>,null}]
175 176 }
176 177 ```
177 178
178 179 ```erlang
179 - -include_lib("epgsql/include/epgsql.hrl").
180 180 epgsql:squery(C, "SELECT * FROM _nowhere_").
181 181 > {error,
182 182 #error{severity = error,code = <<"42P01">>,
  @@ -253,7 +253,7 @@ an error occurs, all statements result in `{error, #error{}}`.
253 253 ```erlang
254 254 epgsql:equery(C, "select id from account where name = $1", ["alice"]),
255 255 > {ok,
256 - [{column,<<"id">>,int4,4,-1,1}],
256 + [#column{name = <<"id">>, type = int4, …}],
257 257 [{1}]
258 258 }
259 259 ```
  @@ -283,25 +283,26 @@ squery including final `{C, Ref, done}`.
283 283 ### Prepared Query
284 284
285 285 ```erlang
286 - {ok, Columns, Rows} = epgsql:prepared_query(C, StatementName, [Parameters]).
287 - {ok, Count} = epgsql:prepared_query(C, StatementName, [Parameters]).
288 - {ok, Count, Columns, Rows} = epgsql:prepared_query(C, StatementName, [Parameters]).
286 + {ok, Columns, Rows} = epgsql:prepared_query(C, Statement :: #statement{} | string(), [Parameters]).
287 + {ok, Count} = epgsql:prepared_query(C, Statement, [Parameters]).
288 + {ok, Count, Columns, Rows} = epgsql:prepared_query(C, Statement, [Parameters]).
289 289 {error, Error} = epgsql:prepared_query(C, "non_existent_query", [Parameters]).
290 290 ```
291 291
292 - `Parameters` - optional list of values to be bound to `$1`, `$2`, `$3`, etc.
293 - `StatementName` - name of query given with ```erlang epgsql:parse(C, StatementName, "select ...", []).```
292 + - `Parameters` - optional list of values to be bound to `$1`, `$2`, `$3`, etc.
293 + - `Statement` - name of query given with ```erlang epgsql:parse(C, StatementName, "select ...", []).```
294 + (can be empty string) or `#statement{}` record returned by `epgsql:parse`.
294 295
295 296 With prepared query one can parse a query giving it a name with `epgsql:parse` on start and reuse the name
296 297 for all further queries with different parameters.
297 298
298 299 ```erlang
299 - epgsql:parse(C, "inc", "select $1+1", []).
300 - epgsql:prepared_query(C, "inc", [4]).
301 - epgsql:prepared_query(C, "inc", [1]).
300 + {ok, Stmt} = epgsql:parse(C, "inc", "select $1+1", []).
301 + epgsql:prepared_query(C, Stmt, [4]).
302 + epgsql:prepared_query(C, Stmt, [1]).
302 303 ```
303 304
304 - Asynchronous API `epgsqla:prepared_query/3` requires you to parse statement beforehand
305 + Asynchronous API `epgsqla:prepared_query/3` requires you to always parse statement beforehand
305 306
306 307 ```erlang
307 308 #statement{types = Types} = Statement,
  @@ -384,23 +385,54 @@ Batch execution is `bind` + `execute` for several prepared statements.
384 385 It uses unnamed portals and `MaxRows = 0`.
385 386
386 387 ```erlang
387 - Results = epgsql:execute_batch(C, Batch).
388 + Results = epgsql:execute_batch(C, BatchStmt :: [{statement(), [bind_param()]}]).
389 + {Columns, Results} = epgsql:execute_batch(C, statement() | sql_query(), Batch :: [ [bind_param()] ]).
388 390 ```
389 391
390 - - `Batch` - list of {Statement, ParameterValues}
391 - - `Results` - list of {ok, Count} or {ok, Count, Rows}
392 + - `BatchStmt` - list of `{Statement, ParameterValues}`, each item has it's own `#statement{}`
393 + - `Batch` - list of `ParameterValues`, each item executes the same common `#statement{}` or SQL query
394 + - `Columns` - list of `#column{}` descriptions of `Results` columns
395 + - `Results` - list of `{ok, Count}` or `{ok, Count, Rows}`
396 +
397 + There are 2 versions:
398 +
399 + `execute_batch/2` - each item in a batch has it's own named statement (but it's allowed to have duplicates)
392 400
393 401 example:
394 402
395 403 ```erlang
396 - {ok, S1} = epgsql:parse(C, "one", "select $1", [int4]),
397 - {ok, S2} = epgsql:parse(C, "two", "select $1 + $2", [int4, int4]),
404 + {ok, S1} = epgsql:parse(C, "one", "select $1::integer", []),
405 + {ok, S2} = epgsql:parse(C, "two", "select $1::integer + $2::integer", []),
398 406 [{ok, [{1}]}, {ok, [{3}]}] = epgsql:execute_batch(C, [{S1, [1]}, {S2, [1, 2]}]).
407 + ok = epgsql:close(C, "one").
408 + ok = epgsql:close(C, "two").
399 409 ```
400 410
401 - `epgsqla:execute_batch/3` sends `{C, Ref, Results}`
411 + `execute_batch/3` - each item in a batch executed with the same common SQL query or `#statement{}`.
412 + It's allowed to use unnamed statement.
402 413
403 - `epgsqli:execute_batch/3` sends
414 + example (the most efficient way to make batch inserts with epgsql):
415 +
416 + ```erlang
417 + {ok, Stmt} = epgsql:parse(C, "my_insert", "INSERT INTO account (name, age) VALUES ($1, $2) RETURNING id", []).
418 + {[#column{name = <<"id">>}], [{ok, [{1}]}, {ok, [{2}]}, {ok, [{3}]}]} =
419 + epgsql:execute_batch(C, Stmt, [ ["Joe", 35], ["Paul", 26], ["Mary", 24] ]).
420 + ok = epgsql:close(C, "my_insert").
421 + ```
422 +
423 + equivalent:
424 +
425 + ```erlang
426 + epgsql:execute_batch(C, "INSERT INTO account (name, age) VALUES ($1, $2) RETURNING id",
427 + [ ["Joe", 35], ["Paul", 26], ["Mary", 24] ]).
428 + ```
429 +
430 + In case one of the batch items causes an error, the result returned for this particular
431 + item will be `{error, #error{}}` and no more results will be produced.
432 +
433 + `epgsqla:execute_batch/{2,3}` sends `{C, Ref, Results}`
434 +
435 + `epgsqli:execute_batch/{2,3}` sends
404 436
405 437 - `{C, Ref, {data, Row}}`
406 438 - `{C, Ref, {error, Reason}}`
  @@ -408,9 +440,45 @@ example:
408 440 - `{C, Ref, {complete, _Type}}`
409 441 - `{C, Ref, done}` - execution of all queries from Batch has finished
410 442
443 + ### Query cancellation
444 +
445 + ```erlang
446 + epgsql:cancel(connection()) -> ok.
447 + ```
448 +
449 + PostgreSQL protocol supports [cancellation](https://www.postgresql.org/docs/current/protocol-flow.html#id-1.10.5.7.9)
450 + of currently executing command. `cancel/1` sends a cancellation request via the
451 + new temporary TCP connection asynchronously, it doesn't await for the command to
452 + be cancelled. Instead, client should expect to get
453 + `{error, #error{code = <<"57014">>, codename = query_canceled}}` back from
454 + the command that was cancelled. However, normal response can still be received as well.
455 + While it's not so straightforward to use with synchronous `epgsql` API, it plays
456 + quite nicely with asynchronous `epgsqla` API. For example, that's how a query with
457 + soft timeout can be implemented:
458 +
459 + ```erlang
460 + squery(C, SQL, Timeout) ->
461 + Ref = epgsqla:squery(C, SQL),
462 + receive
463 + {C, Ref, Result} -> Result
464 + after Timeout ->
465 + ok = epgsql:cancel(C),
466 + % We can still receive {ok, …} as well as
467 + % {error, #error{codename = query_canceled}} or any other error
468 + receive
469 + {C, Ref, Result} -> Result
470 + end
471 + end.
472 + ```
473 +
474 + This API should be used with extreme care when pipelining is in use: it only cancels
475 + currently executing command, all the subsequent pipelined commands will continue
476 + their normal execution. And it's not always easy to see which command exactly is
477 + executing when we are issuing the cancellation request.
478 +
411 479 ## Data Representation
412 480
413 - Data representation may be configured using [pluggable datatype codecs](pluggable_types.md),
481 + Data representation may be configured using [pluggable datatype codecs](doc/pluggable_types.md),
414 482 so following is just default mapping:
415 483
416 484 PG type | Representation
  @@ -433,8 +501,8 @@ PG type | Representation
433 501 record | `{int2, time, text, ...}` (decode only)
434 502 point | `{10.2, 100.12}`
435 503 int4range | `[1,5)`
436 - hstore | `{[ {binary(), binary() \| null} ]}`
437 - json/jsonb | `<<"{ \"key\": [ 1, 1.0, true, \"string\" ] }">>` (see below for codec details)
504 + hstore | `{[ {binary(), binary() \| null} ]}` (configurable)
505 + json/jsonb | `<<"{ \"key\": [ 1, 1.0, true, \"string\" ] }">>` (configurable)
438 506 uuid | `<<"123e4567-e89b-12d3-a456-426655440000">>`
439 507 inet | `inet:ip_address()`
440 508 cidr | `{ip_address(), Mask :: 0..32}`
  @@ -444,6 +512,8 @@ PG type | Representation
444 512 tstzrange | `{{Hour, Minute, Second.Microsecond}, {Hour, Minute, Second.Microsecond}}`
445 513 daterange | `{{Year, Month, Day}, {Year, Month, Day}}`
446 514
515 + `null` can be configured. See `nulls` `connect/1` option.
516 +
447 517 `timestamp` and `timestamptz` parameters can take `erlang:now()` format: `{MegaSeconds, Seconds, MicroSeconds}`
448 518
449 519 `int4range` is a range type for ints that obeys inclusive/exclusive semantics,
  @@ -453,6 +523,12 @@ and `plus_infinity`
453 523 `tsrange`, `tstzrange`, `daterange` are range types for `timestamp`, `timestamptz` and `date`
454 524 respectively. They can return `empty` atom as the result from a database if bounds are equal
455 525
526 + `hstore` type can take map or jiffy-style objects as input. Output can be tuned by
527 + providing `return :: map | jiffy | proplist` option to choose the format to which
528 + hstore should be decoded. `nulls :: [atom(), ...]` option can be used to select the
529 + terms which should be interpreted as SQL `NULL` - semantics is the same as
530 + for `connect/1` `nulls` option.
531 +
456 532 `json` and `jsonb` types can optionally use a custom JSON encoding/decoding module to accept
457 533 and return erlang-formatted JSON. The module must implement the callbacks in `epgsql_codec_json`,
458 534 which most popular open source JSON parsers will already, and you can specify it in the codec
  @@ -584,15 +660,15 @@ Parameter's value may change during connection's lifetime.
584 660
585 661 ## Streaming replication protocol
586 662
587 - See [streaming.md](streaming.md).
663 + See [streaming.md](doc/streaming.md).
588 664
589 665 ## Pluggable commands
590 666
591 - See [pluggable_commands.md](pluggable_commands.md)
667 + See [pluggable_commands.md](doc/pluggable_commands.md)
592 668
593 669 ## Pluggable datatype codecs
594 670
595 - See [pluggable_types.md](pluggable_types.md)
671 + See [pluggable_types.md](doc/pluggable_types.md)
596 672
597 673 ## Mailing list
  @@ -2,10 +2,9 @@
2 2 {<<"build_tools">>,[<<"rebar3">>]}.
3 3 {<<"description">>,<<"PostgreSQL Client">>}.
4 4 {<<"files">>,
5 - [<<"src/epgsql.app.src">>,<<"LICENSE">>,<<"README.md">>,
6 - <<"include/epgsql.hrl">>,<<"include/epgsql_geometry.hrl">>,
7 - <<"include/protocol.hrl">>,<<"rebar.config">>,<<"rebar.lock">>,
8 - <<"src/commands/epgsql_cmd_batch.erl">>,
5 + [<<"LICENSE">>,<<"README.md">>,<<"include/epgsql.hrl">>,
6 + <<"include/epgsql_geometry.hrl">>,<<"include/protocol.hrl">>,
7 + <<"rebar.config">>,<<"rebar.lock">>,<<"src/commands/epgsql_cmd_batch.erl">>,
9 8 <<"src/commands/epgsql_cmd_bind.erl">>,
10 9 <<"src/commands/epgsql_cmd_close.erl">>,
11 10 <<"src/commands/epgsql_cmd_connect.erl">>,
  @@ -33,8 +32,8 @@
33 32 <<"src/datatypes/epgsql_codec_postgis.erl">>,
34 33 <<"src/datatypes/epgsql_codec_text.erl">>,
35 34 <<"src/datatypes/epgsql_codec_timerange.erl">>,
36 - <<"src/datatypes/epgsql_codec_uuid.erl">>,<<"src/epgsql.erl">>,
37 - <<"src/epgsql_binary.erl">>,<<"src/epgsql_codec.erl">>,
35 + <<"src/datatypes/epgsql_codec_uuid.erl">>,<<"src/epgsql.app.src">>,
36 + <<"src/epgsql.erl">>,<<"src/epgsql_binary.erl">>,<<"src/epgsql_codec.erl">>,
38 37 <<"src/epgsql_command.erl">>,<<"src/epgsql_errcodes.erl">>,
39 38 <<"src/epgsql_fdatetime.erl">>,<<"src/epgsql_idatetime.erl">>,
40 39 <<"src/epgsql_oid_db.erl">>,<<"src/epgsql_replication.hrl">>,
  @@ -45,4 +44,4 @@
45 44 {<<"links">>,[{<<"Github">>,<<"https://github.com/epgsql/epgsql">>}]}.
46 45 {<<"name">>,<<"epgsql">>}.
47 46 {<<"requirements">>,[]}.
48 - {<<"version">>,<<"4.3.0">>}.
47 + {<<"version">>,<<"4.4.0">>}.
  @@ -1,10 +1,26 @@
1 + %% See https://www.postgresql.org/docs/current/protocol-message-formats.html
2 + %% Description of `RowDescription' packet
1 3 -record(column, {
4 + %% field name
2 5 name :: binary(),
6 + %% name of the field data type
3 7 type :: epgsql:epgsql_type(),
4 - oid :: integer(),
8 + %% OID of the field's data type
9 + oid :: non_neg_integer(),
10 + %% data type size (see pg_type.typlen). negative values denote variable-width types
5 11 size :: -1 | pos_integer(),
12 + %% type modifier (see pg_attribute.atttypmod). meaning of the modifier is type-specific
6 13 modifier :: -1 | pos_integer(),
7 - format :: integer()
14 + %% format code being used for the field during server->client transmission.
15 + %% Currently will be zero (text) or one (binary).
16 + format :: integer(),
17 + %% If the field can be identified as a column of a specific table, the OID of the table; otherwise zero.
18 + %% SELECT relname FROM pg_catalog.pg_class WHERE oid=<table_oid>
19 + table_oid :: non_neg_integer(),
20 + %% If table_oid is not zero, the attribute number of the column; otherwise zero.
21 + %% SELECT attname FROM pg_catalog.pg_attribute
22 + %% WHERE attrelid=<table_oid> AND attnum=<table_attr_number>
23 + table_attr_number :: pos_integer()
8 24 }).
9 25
10 26 -record(statement, {
  @@ -14,10 +30,12 @@
14 30 parameter_info :: [epgsql_oid_db:oid_entry()]
15 31 }).
16 32
33 +
34 + %% See https://www.postgresql.org/docs/current/protocol-error-fields.html
17 35 -record(error, {
18 36 % see client_min_messages config option
19 37 severity :: debug | log | info | notice | warning | error | fatal | panic,
20 - code :: binary(),
38 + code :: binary(), % See https://www.postgresql.org/docs/current/errcodes-appendix.html
21 39 codename :: atom(),
22 40 message :: binary(),
23 41 extra :: [{severity | detail | hint | position | internal_position | internal_query
  @@ -43,6 +43,7 @@
43 43 -define(READY_FOR_QUERY, $Z).
44 44 -define(COPY_BOTH_RESPONSE, $W).
45 45 -define(COPY_DATA, $d).
46 + -define(TERMINATE, $X).
46 47
47 48 % CopyData replication messages
48 49 -define(X_LOG_DATA, $w).
  @@ -1,9 +1,8 @@
1 1 %% -*- mode: erlang -*-
2 2
3 - {eunit_opts, [verbose]}.
4 -
5 3 {cover_enabled, true}.
6 - {cover_print_enabled, true}.
4 +
5 + {edoc_opts, [{preprocess, true}]}.
7 6
8 7 {profiles, [
9 8 {test, [
  @@ -27,7 +26,8 @@
27 26 ruleset => erl_files,
28 27 rules =>
29 28 [{elvis_style, line_length, #{limit => 120}},
30 - {elvis_style, god_modules, #{limit => 40}},
29 + {elvis_style, god_modules, #{limit => 41}},
30 + {elvis_style, dont_repeat_yourself, #{min_complexity => 11}},
31 31 {elvis_style, state_record_and_type, disable} % epgsql_sock
32 32 ]}
33 33 ]
Loading more files…