GitHub - erleans/pgo: Erlang Postgres client and connection pool (original) (raw)

PGO

PG...Oh god not nother Postgres client in Erlang...

Why

• No message passing. Clients checkout the socket and use it directly.
• Binary protocol with input oids cached.
• Simple and direct. Tries to limit runtime options as much as possible.
• Instrumented with Telemetry and OpenCensus
• Mix apps currently too hard to use in a Rebar3 project.

Requirements

Erlang/OTP 21.3 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

• pool (default: default): Name of the pool to use for checking out a connection to the database.
• return_rows_as_maps (default: false): When true each row is returned as a map of column name to value instead of a list of values.
• column_name_as_atom (default: false): If true converts each column name in the result to an atom.
• decode_fun (default: undefined): Optional function for performing transformations on each row in a result. It must be a 2-arity function returning a list or map for the row and takes the row (as a list or map) and a list of #row_description_field{} records.
• queue (default: true): Whether to wait for a connection from the pool if none are available.
• trace (default: false): pgo is instrumented with OpenCensus and when this option is true a span will be created (if sampled).

Database Settings

• host (default: 127.0.0.1): Database server hostname.
• port (default: 5432): Port the server is listening on.
• user: Username to connect to database as.
• password: Password for the user.
• database: Name of database to use.
• ssl (default: false): Whether to use SSL or not.
• ssl_options: List of SSL options to use if ssl is true. See the Erlang SSL connect options.
• connection_parameters (default: []): List of 2-tuples, where key and value must be binary strings. You can include any Postgres connection parameter here, such as {<<"application_name">>, <<"myappname">>} and {<<"timezone">>, <<"GMT">>}.

Pool Settings

• pool_size (default: 1): Number of connections to keep open with the database
• queue_target (default: 50) and queue_interval (default: 1000): Checking out connections is handled through a queue. If it takes longer than queue_target to get out of the queue for longer than queue_interval then the queue_target will be doubled and checkouts will start to be dropped if that target is surpassed.
• idle_interval (default: 1000): The database is pinged every idle_interval when the connection is idle.

Erlang TCP Socket Settings

• socket_options (default []): Addition options to pass to gen_tcp:connect such as inet6 for IPv6 support.

Telemetry and Tracing

A Telemetry event [pgo, query] can be attached to for receiving the time a query takes as well as other metadata for each query.

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

pgo:start_pool(default, #{host => "127.0.0.1", database => "test", user => "test", pool_size => 5, trace_default => 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 opencensus application is not included as a dependency of pgo. So it must be included as a rebar3 dependency and runtime dependency (listed in your application's .app.src applications or the list of applications for relx to include in a release).

Running Tests

Pool functionality is tested with common test suites:

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

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_connection PR 108. While db_connection and postgrex as a whole were both used as inspiration as well.