Current section
18 Versions
Jump to
Current section
18 Versions
Compare versions
13
files changed
+203
additions
-160
deletions
| @@ -79,7 +79,9 @@ connect(Opts) -> {ok, Connection :: epgsql:connection()} | {error, Reason :: epg | |
| 79 79 | |
| 80 80 | connect(Host, Username, Password, Opts) -> {ok, C} | {error, Reason}. |
| 81 81 | ``` |
| 82 | + |
| 82 83 | example: |
| 84 | + |
| 83 85 | ```erlang |
| 84 86 | {ok, C} = epgsql:connect("localhost", "username", "psss", #{ |
| 85 87 | database => "test_db", |
| @@ -146,7 +148,9 @@ Asynchronous connect example (applies to **epgsqli** too): | |
| 146 148 | %% @doc runs simple `SqlQuery' via given `Connection' |
| 147 149 | squery(Connection, SqlQuery) -> ... |
| 148 150 | ``` |
| 151 | + |
| 149 152 | examples: |
| 153 | + |
| 150 154 | ```erlang |
| 151 155 | epgsql:squery(C, "insert into account (name) values ('alice'), ('bob')"). |
| 152 156 | > {ok,2} |
| @@ -237,6 +241,7 @@ end. | |
| 237 241 | {ok, Count, Columns, Rows} = epgsql:equery(C, "insert ... returning ...", [Parameters]). |
| 238 242 | {error, Error} = epgsql:equery(C, "invalid SQL", [Parameters]). |
| 239 243 | ``` |
| 244 | + |
| 240 245 | `Parameters` - optional list of values to be bound to `$1`, `$2`, `$3`, etc. |
| 241 246 | |
| 242 247 | The extended query protocol combines parse, bind, and execute using |
| @@ -276,17 +281,20 @@ end. | |
| 276 281 | squery including final `{C, Ref, done}`. |
| 277 282 | |
| 278 283 | ### Prepared Query |
| 284 | + |
| 279 285 | ```erlang |
| 280 286 | {ok, Columns, Rows} = epgsql:prepared_query(C, StatementName, [Parameters]). |
| 281 287 | {ok, Count} = epgsql:prepared_query(C, StatementName, [Parameters]). |
| 282 288 | {ok, Count, Columns, Rows} = epgsql:prepared_query(C, StatementName, [Parameters]). |
| 283 289 | {error, Error} = epgsql:prepared_query(C, "non_existent_query", [Parameters]). |
| 284 290 | ``` |
| 291 | + |
| 285 292 | `Parameters` - optional list of values to be bound to `$1`, `$2`, `$3`, etc. |
| 286 293 | `StatementName` - name of query given with ```erlang epgsql:parse(C, StatementName, "select ...", []).``` |
| 287 294 | |
| 288 295 | With prepared query one can parse a query giving it a name with `epgsql:parse` on start and reuse the name |
| 289 296 | for all further queries with different parameters. |
| 297 | + |
| 290 298 | ```erlang |
| 291 299 | epgsql:parse(C, "inc", "select $1+1", []). |
| 292 300 | epgsql:prepared_query(C, "inc", [4]). |
| @@ -322,11 +330,13 @@ squery including final `{C, Ref, done}`. | |
| 322 330 | For valid type names see `pgsql_types.erl`. |
| 323 331 | |
| 324 332 | `epgsqla:parse/2` sends `{C, Ref, {ok, Statement} | {error, Reason}}`. |
| 333 | + |
| 325 334 | `epgsqli:parse/2` sends: |
| 326 | - - `{C, Ref, {types, Types}}` |
| 327 | - - `{C, Ref, {columns, Columns}}` |
| 328 | - - `{C, Ref, no_data}` if statement will not return rows |
| 329 | - - `{C, Ref, {error, Reason}}` |
| 335 | + |
| 336 | + - `{C, Ref, {types, Types}}` |
| 337 | + - `{C, Ref, {columns, Columns}}` |
| 338 | + - `{C, Ref, no_data}` if statement will not return rows |
| 339 | + - `{C, Ref, {error, Reason}}` |
| 330 340 | |
| 331 341 | ```erlang |
| 332 342 | ok = epgsql:bind(C, Statement, [PortalName], ParameterValues). |
| @@ -351,6 +361,7 @@ both `epgsqla:bind/3` and `epgsqli:bind/3` send `{C, Ref, ok | {error, Reason}}` | |
| 351 361 | return value of `epgsql:execute/3`. |
| 352 362 | |
| 353 363 | `epgsqli:execute/3` sends |
| 364 | + |
| 354 365 | - `{C, Ref, {data, Row}}` |
| 355 366 | - `{C, Ref, {error, Reason}}` |
| 356 367 | - `{C, Ref, suspended}` partial result was sent, more rows are available |
| @@ -367,7 +378,6 @@ All epgsql functions return `{error, Error}` when an error occurs. | |
| 367 378 | |
| 368 379 | `epgsqla`/`epgsqli` modules' `close` and `sync` functions send `{C, Ref, ok}`. |
| 369 380 | |
| 370 | - |
| 371 381 | ### Batch execution |
| 372 382 | |
| 373 383 | Batch execution is `bind` + `execute` for several prepared statements. |
| @@ -389,7 +399,9 @@ example: | |
| 389 399 | ``` |
| 390 400 | |
| 391 401 | `epgsqla:execute_batch/3` sends `{C, Ref, Results}` |
| 402 | + |
| 392 403 | `epgsqli:execute_batch/3` sends |
| 404 | + |
| 393 405 | - `{C, Ref, {data, Row}}` |
| 394 406 | - `{C, Ref, {error, Reason}}` |
| 395 407 | - `{C, Ref, {complete, {_Type, Count}}}` |
| @@ -422,7 +434,7 @@ PG type | Representation | |
| 422 434 | point | `{10.2, 100.12}` |
| 423 435 | int4range | `[1,5)` |
| 424 436 | hstore | `{[ {binary(), binary() \| null} ]}` |
| 425 | - json/jsonb | `<<"{ \"key\": [ 1, 1.0, true, \"string\" ] }">>` |
| 437 | + json/jsonb | `<<"{ \"key\": [ 1, 1.0, true, \"string\" ] }">>` (see below for codec details) |
| 426 438 | uuid | `<<"123e4567-e89b-12d3-a456-426655440000">>` |
| 427 439 | inet | `inet:ip_address()` |
| 428 440 | cidr | `{ip_address(), Mask :: 0..32}` |
| @@ -432,14 +444,31 @@ PG type | Representation | |
| 432 444 | tstzrange | `{{Hour, Minute, Second.Microsecond}, {Hour, Minute, Second.Microsecond}}` |
| 433 445 | daterange | `{{Year, Month, Day}, {Year, Month, Day}}` |
| 434 446 | |
| 435 | - `timestamp` and `timestamptz` parameters can take `erlang:now()` format: `{MegaSeconds, Seconds, MicroSeconds}` |
| 447 | + `timestamp` and `timestamptz` parameters can take `erlang:now()` format: `{MegaSeconds, Seconds, MicroSeconds}` |
| 436 448 | |
| 437 | - `int4range` is a range type for ints that obeys inclusive/exclusive semantics, |
| 438 | - bracket and parentheses respectively. Additionally, infinities are represented by the atoms `minus_infinity` |
| 439 | - and `plus_infinity` |
| 449 | + `int4range` is a range type for ints that obeys inclusive/exclusive semantics, |
| 450 | + bracket and parentheses respectively. Additionally, infinities are represented by the atoms `minus_infinity` |
| 451 | + and `plus_infinity` |
| 440 452 | |
| 441 | - `tsrange`, `tstzrange`, `daterange` are range types for `timestamp`, `timestamptz` and `date` |
| 442 | - respectively. They can return `empty` atom as the result from a database if bounds are equal |
| 453 | + `tsrange`, `tstzrange`, `daterange` are range types for `timestamp`, `timestamptz` and `date` |
| 454 | + respectively. They can return `empty` atom as the result from a database if bounds are equal |
| 455 | + |
| 456 | + `json` and `jsonb` types can optionally use a custom JSON encoding/decoding module to accept |
| 457 | + and return erlang-formatted JSON. The module must implement the callbacks in `epgsql_codec_json`, |
| 458 | + which most popular open source JSON parsers will already, and you can specify it in the codec |
| 459 | + configuration like this: |
| 460 | + |
| 461 | + ```erlang |
| 462 | + {epgsql_codec_json, JsonMod} |
| 463 | + |
| 464 | + % With options |
| 465 | + {epgsql_codec_json, JsonMod, EncodeOpts, DecodeOpts} |
| 466 | + |
| 467 | + % Real world example using jiffy to return a map on decode |
| 468 | + {epgsql_codec_json, {jiffy, [], [return_maps]}} |
| 469 | + ``` |
| 470 | + |
| 471 | + Note that the decoded terms will be message-passed to the receiving process (i.e. copied), which may exhibit a performance hit if decoding large terms very frequently. |
| 443 472 | |
| 444 473 | ## Errors |
| 445 474 | |
| @@ -482,6 +511,7 @@ Message formats: | |
| 482 511 | ```erlang |
| 483 512 | {epgsql, Connection, {notification, Channel, Pid, Payload}} |
| 484 513 | ``` |
| 514 | + |
| 485 515 | - `Connection` - connection the notification occurred on |
| 486 516 | - `Channel` - channel the notification occurred on |
| 487 517 | - `Pid` - database session pid that sent notification |
| @@ -490,6 +520,7 @@ Message formats: | |
| 490 520 | ```erlang |
| 491 521 | {epgsql, Connection, {notice, Error}} |
| 492 522 | ``` |
| 523 | + |
| 493 524 | - `Connection` - connection the notice occurred on |
| 494 525 | - `Error` - an `#error{}` record, see `epgsql.hrl` |
| 495 526 | |
| @@ -510,7 +541,9 @@ Executes a function in a PostgreSQL transaction. It executes `BEGIN` prior to ex | |
| 510 541 | `ROLLBACK` if the function raises an exception and `COMMIT` if the function returns without an error. |
| 511 542 | If it is successful, it returns the result of the function. The failure case may differ, depending on |
| 512 543 | the options passed. |
| 544 | + |
| 513 545 | Options (proplist or map): |
| 546 | + |
| 514 547 | - `reraise` (default `true`): when set to true, the original exception will be re-thrown after rollback, |
| 515 548 | otherwise `{rollback, ErrorReason}` will be returned |
| 516 549 | - `ensure_committed` (default `false`): even when the callback returns without exception, |
| @@ -522,7 +555,6 @@ Options (proplist or map): | |
| 522 555 | appending them to `"BEGIN "` string. Eg `{begin_opts, "ISOLATION LEVEL SERIALIZABLE"}`. |
| 523 556 | Beware of SQL injection! The value of `begin_opts` is not escaped! |
| 524 557 | |
| 525 | - |
| 526 558 | ### Command status |
| 527 559 | |
| 528 560 | `epgsql{a,i}:get_cmd_status(C) -> undefined | atom() | {atom(), integer()}` |
| @@ -571,10 +603,10 @@ See [pluggable_types.md](pluggable_types.md) | |
| 571 603 | epgsql is a community driven effort - we welcome contributions! |
| 572 604 | Here's how to create a patch that's easy to integrate: |
| 573 605 | |
| 574 | - * Create a new branch for the proposed fix. |
| 575 | - * Make sure it includes a test and documentation, if appropriate. |
| 576 | - * Open a pull request against the `devel` branch of epgsql. |
| 577 | - * Passing build in travis |
| 606 | + - Create a new branch for the proposed fix. |
| 607 | + - Make sure it includes a test and documentation, if appropriate. |
| 608 | + - Open a pull request against the `devel` branch of epgsql. |
| 609 | + - Passing build in travis |
| 578 610 | |
| 579 611 | ## Test Setup |
| 580 612 | |
| @@ -582,11 +614,10 @@ In order to run the epgsql tests, you will need to install local | |
| 582 614 | Postgres database. |
| 583 615 | |
| 584 616 | NOTE: you will need the postgis and hstore extensions to run these |
| 585 | - tests! On Ubuntu, you can install them with a command like this: |
| 617 | + tests! On Ubuntu, you can install them with a command like this: |
| 586 618 | |
| 587 | - 1. apt-get install postgresql-9.3-postgis-2.1 postgresql-contrib |
| 588 | - |
| 589 | - 2. `make test` # Runs the tests |
| 619 | + 1. `apt-get install postgresql-9.3-postgis-2.1 postgresql-contrib` |
| 620 | + 1. `make test` # Runs the tests |
| 590 621 | |
| 591 622 | NOTE 2: It's possible to run tests on exact postgres version by changing $PATH like |
| @@ -1,8 +1,5 @@ | |
| 1 | - {<<"name">>,<<"epgsql">>}. |
| 2 | - {<<"version">>,<<"4.2.1">>}. |
| 3 | - {<<"requirements">>,#{}}. |
| 4 1 | {<<"app">>,<<"epgsql">>}. |
| 5 | - {<<"precompiled">>,false}. |
| 2 | + {<<"build_tools">>,[<<"rebar3">>]}. |
| 6 3 | {<<"description">>,<<"PostgreSQL Client">>}. |
| 7 4 | {<<"files">>, |
| 8 5 | [<<"src/epgsql.app.src">>,<<"LICENSE">>,<<"README.md">>, |
| @@ -46,4 +43,6 @@ | |
| 46 43 | <<"src/ewkb.erl">>]}. |
| 47 44 | {<<"licenses">>,[<<"BSD">>]}. |
| 48 45 | {<<"links">>,[{<<"Github">>,<<"https://github.com/epgsql/epgsql">>}]}. |
| 49 | - {<<"build_tools">>,[<<"rebar3">>]}. |
| 46 | + {<<"name">>,<<"epgsql">>}. |
| 47 | + {<<"requirements">>,[]}. |
| 48 | + {<<"version">>,<<"4.3.0">>}. |
| @@ -1,4 +1,4 @@ | |
| 1 | - {erl_opts, [{platform_define, "^17", 'SLOW_MAPS'}]}. % Erlang 17 |
| 1 | + %% -*- mode: erlang -*- |
| 2 2 | |
| 3 3 | {eunit_opts, [verbose]}. |
| 4 4 | |
| @@ -8,7 +8,7 @@ | |
| 8 8 | {profiles, [ |
| 9 9 | {test, [ |
| 10 10 | {deps, [ |
| 11 | - {erlexec, {git, "https://github.com/saleyn/erlexec.git", {ref, "576fb5d"}}} |
| 11 | + {erlexec, "1.10.0"} |
| 12 12 | ]} |
| 13 13 | ]}, |
| 14 14 | {lint, [ |
| @@ -45,39 +45,47 @@ | |
| 45 45 | init(#{host := _, username := _} = Opts) -> |
| 46 46 | #connect{opts = Opts}. |
| 47 47 | |
| 48 | - execute(PgSock, #connect{opts = Opts, stage = connect} = State) -> |
| 49 | - #{host := Host, |
| 50 | - username := Username} = Opts, |
| 48 | + execute(PgSock, #connect{opts = #{host := Host} = Opts, stage = connect} = State) -> |
| 51 49 | Timeout = maps:get(timeout, Opts, 5000), |
| 52 50 | Port = maps:get(port, Opts, 5432), |
| 53 51 | SockOpts = [{active, false}, {packet, raw}, binary, {nodelay, true}, {keepalive, true}], |
| 54 52 | case gen_tcp:connect(Host, Port, SockOpts, Timeout) of |
| 55 53 | {ok, Sock} -> |
| 54 | + client_handshake(Sock, PgSock, State); |
| 55 | + {error, Reason} = Error -> |
| 56 | + {stop, Reason, Error, PgSock} |
| 57 | + end; |
| 58 | + execute(PgSock, #connect{stage = auth, auth_send = {PacketId, Data}} = St) -> |
| 59 | + epgsql_sock:send(PgSock, PacketId, Data), |
| 60 | + {ok, PgSock, St#connect{auth_send = undefined}}. |
| 56 61 | |
| 57 | - %% Increase the buffer size. Following the recommendation in the inet man page: |
| 58 | - %% |
| 59 | - %% It is recommended to have val(buffer) >= |
| 60 | - %% max(val(sndbuf),val(recbuf)). |
| 62 | + client_handshake(Sock, PgSock, #connect{opts = #{username := Username} = Opts} = State) -> |
| 63 | + %% Increase the buffer size. Following the recommendation in the inet man page: |
| 64 | + %% |
| 65 | + %% It is recommended to have val(buffer) >= |
| 66 | + %% max(val(sndbuf),val(recbuf)). |
| 61 67 | |
| 62 | - {ok, [{recbuf, RecBufSize}, {sndbuf, SndBufSize}]} = |
| 63 | - inet:getopts(Sock, [recbuf, sndbuf]), |
| 64 | - inet:setopts(Sock, [{buffer, max(RecBufSize, SndBufSize)}]), |
| 65 | - |
| 66 | - PgSock1 = maybe_ssl(Sock, maps:get(ssl, Opts, false), Opts, PgSock), |
| 68 | + {ok, [{recbuf, RecBufSize}, {sndbuf, SndBufSize}]} = |
| 69 | + inet:getopts(Sock, [recbuf, sndbuf]), |
| 70 | + inet:setopts(Sock, [{buffer, max(RecBufSize, SndBufSize)}]), |
| 67 71 | |
| 72 | + case maybe_ssl(Sock, maps:get(ssl, Opts, false), Opts, PgSock) of |
| 73 | + {error, Reason} -> |
| 74 | + {stop, Reason, {error, Reason}, PgSock}; |
| 75 | + PgSock1 -> |
| 68 76 | Opts2 = ["user", 0, Username, 0], |
| 69 77 | Opts3 = case maps:find(database, Opts) of |
| 70 | - error -> Opts2; |
| 71 | - {ok, Database} -> [Opts2 | ["database", 0, Database, 0]] |
| 72 | - end, |
| 78 | + error -> Opts2; |
| 79 | + {ok, Database} -> [Opts2 | ["database", 0, Database, 0]] |
| 80 | + end, |
| 73 81 | |
| 74 82 | {Opts4, PgSock2} = |
| 75 83 | case Opts of |
| 76 84 | #{replication := Replication} -> |
| 77 85 | {[Opts3 | ["replication", 0, Replication, 0]], |
| 78 86 | epgsql_sock:init_replication_state(PgSock1)}; |
| 79 | - _ -> {Opts3, PgSock1} |
| 80 | - end, |
| 87 | + _ -> {Opts3, PgSock1} |
| 88 | + end, |
| 81 89 | |
| 82 90 | epgsql_sock:send(PgSock2, [<<196608:?int32>>, Opts4, 0]), |
| 83 91 | PgSock3 = case Opts of |
| @@ -85,13 +93,8 @@ execute(PgSock, #connect{opts = Opts, stage = connect} = State) -> | |
| 85 93 | epgsql_sock:set_attr(async, Async, PgSock2); |
| 86 94 | _ -> PgSock2 |
| 87 95 | end, |
| 88 | - {ok, PgSock3, State#connect{stage = maybe_auth}}; |
| 89 | - {error, Reason} = Error -> |
| 90 | - {stop, Reason, Error, PgSock} |
| 91 | - end; |
| 92 | - execute(PgSock, #connect{stage = auth, auth_send = {PacketId, Data}} = St) -> |
| 93 | - epgsql_sock:send(PgSock, PacketId, Data), |
| 94 | - {ok, PgSock, St#connect{auth_send = undefined}}. |
| 96 | + {ok, PgSock3, State#connect{stage = maybe_auth}} |
| 97 | + end. |
| 95 98 | |
| 96 99 | |
| 97 100 | %% @doc Replace `password' in Opts map with obfuscated one |
| @@ -127,14 +130,15 @@ maybe_ssl(S, Flag, Opts, PgSock) -> | |
| 127 130 | {ok, S2} -> |
| 128 131 | epgsql_sock:set_net_socket(ssl, S2, PgSock); |
| 129 132 | {error, Reason} -> |
| 130 | - exit({ssl_negotiation_failed, Reason}) |
| 133 | + Err = {ssl_negotiation_failed, Reason}, |
| 134 | + {error, Err} |
| 131 135 | end; |
| 132 136 | $N -> |
| 133 137 | case Flag of |
| 134 138 | true -> |
| 135 139 | epgsql_sock:set_net_socket(gen_tcp, S, PgSock); |
| 136 140 | required -> |
| 137 | - exit(ssl_not_available) |
| 141 | + {error, ssl_not_available} |
| 138 142 | end |
| 139 143 | end. |
| 140 144 | |
| @@ -245,13 +249,16 @@ handle_message(?READY_FOR_QUERY, _, Sock, _State) -> | |
| 245 249 | |
| 246 250 | |
| 247 251 | %% ErrorResponse |
| 248 | - handle_message(?ERROR, Err, Sock, #connect{stage = Stage} = _State) when Stage == auth; |
| 249 | - Stage == maybe_auth -> |
| 250 | - Why = case Err#error.code of |
| 251 | - <<"28000">> -> invalid_authorization_specification; |
| 252 | - <<"28P01">> -> invalid_password; |
| 253 | - Any -> Any |
| 254 | - end, |
| 252 | + handle_message(?ERROR, #error{code = Code} = Err, Sock, #connect{stage = Stage} = _State) -> |
| 253 | + IsAuthStage = (Stage == auth) orelse (Stage == maybe_auth), |
| 254 | + Why = case Code of |
| 255 | + <<"28000">> when IsAuthStage -> |
| 256 | + invalid_authorization_specification; |
| 257 | + <<"28P01">> when IsAuthStage -> |
| 258 | + invalid_password; |
| 259 | + _ -> |
| 260 | + Err |
| 261 | + end, |
| 255 262 | {stop, normal, {error, Why}, Sock}; |
| 256 263 | handle_message(_, _, _, _) -> |
| 257 264 | unknown. |
| @@ -16,18 +16,20 @@ | |
| 16 16 | callback, |
| 17 17 | cb_state, |
| 18 18 | wal_pos, |
| 19 | - plugin_opts}). |
| 19 | + plugin_opts, |
| 20 | + opts}). |
| 20 21 | |
| 21 | - init({ReplicationSlot, Callback, CbInitState, WALPosition, PluginOpts}) -> |
| 22 | + init({ReplicationSlot, Callback, CbInitState, WALPosition, PluginOpts, Opts}) -> |
| 22 23 | #start_repl{slot = ReplicationSlot, |
| 23 24 | callback = Callback, |
| 24 25 | cb_state = CbInitState, |
| 25 26 | wal_pos = WALPosition, |
| 26 | - plugin_opts = PluginOpts}. |
| 27 | + plugin_opts = PluginOpts, |
| 28 | + opts = Opts}. |
| 27 29 | |
| 28 30 | execute(Sock, #start_repl{slot = ReplicationSlot, callback = Callback, |
| 29 31 | cb_state = CbInitState, wal_pos = WALPosition, |
| 30 | - plugin_opts = PluginOpts} = St) -> |
| 32 | + plugin_opts = PluginOpts, opts = Opts} = St) -> |
| 31 33 | %% Connection should be started with 'replication' option. Then |
| 32 34 | %% 'replication_state' will be initialized |
| 33 35 | Repl = #repl{} = epgsql_sock:get_replication_state(Sock), |
| @@ -46,9 +48,10 @@ execute(Sock, #start_repl{slot = ReplicationSlot, callback = Callback, | |
| 46 48 | |
| 47 49 | Hex = [H || H <- WALPosition, H =/= $/], |
| 48 50 | {ok, [LSN], _} = io_lib:fread("~16u", Hex), |
| 49 | - |
| 51 | + AlignLsn = maps:get(align_lsn, Opts, false), |
| 50 52 | Repl3 = Repl2#repl{last_flushed_lsn = LSN, |
| 51 | - last_applied_lsn = LSN}, |
| 53 | + last_applied_lsn = LSN, |
| 54 | + align_lsn = AlignLsn}, |
| 52 55 | Sock2 = epgsql_sock:set_attr(replication_state, Repl3, Sock), |
| 53 56 | %% handler = on_replication}, |
Loading more files…