PGO

TestscodecovHex.pm

PG…Oh god not nother Postgres client in Erlang…

Why

Requirements

Erlang/OTP 26 and above.

Use

Pools defined in the pgo application’s environment will be started on boot. You can also add pools dynamically with pgo:start_pool/3.

To try pgo simply modify config/example.config by replacing the host, database, user and password values for the database you wish to connect to:

[
  {pgo, [{pools, [{default, #{pool_size => 10,
                              host => "127.0.0.1",
                              database => "test",
                              user => "test"}}]}]}
].

default is the name of the pool, size is the number of connections to create for the pool. Or you can start the pool through pgo:start_pool/2 which creates it as a child of pgo‘s simple one for one:

> application:ensure_all_started(pgo).
{ok,[backoff,opentelemetry_api,pg_types,pgo]}
> pgo:start_pool(default, #{pool_size => 5, host => "127.0.0.1", database => "test", user => "test"}).

Or start a pool as a child of your application’s supervisor:

ChildSpec = #{id => pgo_pool,
              start => {pgo_pool, start_link, [Name, PoolConfig]},
              shutdown => 1000},

Then start a shell with rebar3 shell, it will boot the applications which will start the pool automatically if it is configured through sys.config.

> pgo:query("select 1").
#{command => select, num_rows => 1, rows => [{1}]}
> pgo:transaction(fun() ->
>     pgo:query("INSERT INTO my_table(name) VALUES('Name 1')"),
>     pgo:query("INSERT INTO my_table(name) VALUES('Name 2')")
> end).
#{command => insert,num_rows => 1,rows => []}

Options

Pool configuration includes the Postgres connection information, pool configuration like size and defaults for options used at query time. 

#{host => string(),
  port => integer(),
  user => string(),
  password => string(),
  database => string(),

  %% pool specific settings
  pool_size => integer(),
  queue_target => integer(),
  queue_interval => integer(),
  idle_interval => integer(),

  %% gen_tcp socket options
  socket_options => [gen_tcp:socket_option()],

  %% defaults for options used at query time
  queue => boolean(),
  trace => boolean(),
  decode_opts => [decode_option()]}

The query time options can also be set through options passed to pgo:query/3:

decode_fun() :: fun((row(), fields()) -> row()) | undefined.

decode_option() :: return_rows_as_maps | {return_rows_as_maps, boolean()} |
                   column_name_as_atom | {column_name_as_atom, boolean()} |
                   {decode_fun, decode_fun()}.
                         
#{pool => atom(),
  trace => boolean(),
  queue => boolean(),
  decode_opts => [decode_option()]}

Query Options

Database Settings

Pool Settings

Erlang TCP Socket Settings

Telemetry and Tracing

OpenTelemetry spans can be enabled for queries and transactions by either setting the trace to true for the pool:

> pgo:start_pool(default, #{host => "127.0.0.1", 
                            database => "test", 
                            user => "test",
                            pool_size => 5,
                            trace => true}]).

Or by passing #{trace => true} in the options for a query or transaction:

> pgo:query("select 1", [], #{trace => true}).
#{command => select, num_rows => 1, rows => [{1}]}
> pgo:transaction(fun() ->
>     pgo:query("INSERT INTO my_table(name) VALUES('Name 1')"),
>     pgo:query("INSERT INTO my_table(name) VALUES('Name 2')")
> end, #{trace => true}).
#{command => insert,num_rows => 1,rows => []}

Note that since this is optional the opentelemetry application is not included as a dependency of pgo – only opentelemetry_api is included by default. So it must be included as a rebar3 dependency and runtime dependency (listed in your application’s .app.srcapplications or the list of applications for relx to include in a release).

Listen/Notify

Use pgo_notifications:start_link to create a process holding a connection to the database that you can call pgo_notifications:listen/2 with to listen on a channel for notifications. Those notifications will arrive at the calling process in the form:

{notification, Pid, Ref, Channel, Payload}

Running Tests

Pool functionality is tested with common test suites:

$ rebar3 ct

Postgres query functionality is tested with eunit, create user test and database test:

$ rebar3 eunit

Acknowledgements

Much is owed to https://github.com/semiocast/pgsql (especially for protocol step logic) and https://github.com/epgsql/epgsql/ (especially for some decoding logic).

The pool implementation is owed to James Fish’s found in db_connectionPR 108. While db_connection and postgrex as a whole were both used as inspiration as well.