-
Notifications
You must be signed in to change notification settings - Fork 1.4k
/
postgres.ex
284 lines (226 loc) · 9.12 KB
/
postgres.ex
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
defmodule Ecto.Adapters.Postgres do
@moduledoc """
Adapter module for PostgreSQL.
It uses `postgrex` for communicating to the database
and a connection pool, such as `poolboy`.
## Features
* Full query support (including joins, preloads and associations)
* Support for transactions
* Support for data migrations
* Support for ecto.create and ecto.drop operations
* Support for transactional tests via `Ecto.Adapters.SQL`
## Options
Postgres options split in different categories described
below. All options should be given via the repository
configuration. These options are also passed to the module
specified in the `:pool` option, so check that module's
documentation for more options.
### Compile time options
Those options should be set in the config file and require
recompilation in order to make an effect.
* `:adapter` - The adapter name, in this case, `Ecto.Adapters.Postgres`
### Connection options
* `:pool` - The connection pool module, defaults to `DBConnection.Poolboy`
* `:pool_timeout` - The default timeout to use on pool calls, defaults to `5000`
* `:timeout` - The default timeout to use on queries, defaults to `15000`
* `:hostname` - Server hostname
* `:port` - Server port (default: 5432)
* `:username` - Username
* `:password` - User password
* `:ssl` - Set to true if ssl should be used (default: false)
* `:ssl_opts` - A list of ssl options, see Erlang's `ssl` docs
* `:parameters` - Keyword list of connection parameters
* `:connect_timeout` - The timeout for establishing new connections (default: 5000)
* `:socket_options` - Specifies socket configuration
The `:socket_options` are particularly useful when configuring the size
of both send and receive buffers. For example, when Ecto starts with a
pool of 20 connections, the memory usage may quickly grow from 20MB to
50MB based on the operating system default values for TCP buffers. It is
advised to stick with the operating system defaults but they can be
tweaked if desired:
socket_options: [recbuf: 8192, sndbuf: 8192]
We also recommend developers to consult the
[Postgrex documentation](https://hexdocs.pm/postgrex/Postgrex.html#start_link/1)
for a complete listing of all supported options.
### Storage options
* `:encoding` - the database encoding (default: "UTF8")
* `:template` - the template to create the database from
* `:lc_collate` - the collation order
* `:lc_ctype` - the character classification
* `:dump_path` - where to place dumped structures
### After connect callback
If you want to execute a callback as soon as connection is established
to the database, you can use the `:after_connect` configuration. For
example, in your repository configuration you can add:
after_connect: {Postgrex, :query!, ["SET search_path TO global_prefix", []]}
You can also specify your own module that will receive the Postgrex
connection as argument.
## Extensions
Both PostgreSQL and its adapter for Elixir, Postgrex, support an
extension system. If you want to use custom extensions for Postgrex
alongside Ecto, you must define a type module with your extensions.
Create a new file anywhere in your application with the following:
Postgrex.Types.define(MyApp.PostgresTypes,
[MyExtension.Foo, MyExtensionBar] ++ Ecto.Adapters.Postgres.extensions(),
json: Poison)
Once your type module is defined, you can configure the repository to use it:
config :my_app, MyApp.Repo, types: MyApp.PostgresTypes
"""
# Inherit all behaviour from Ecto.Adapters.SQL
use Ecto.Adapters.SQL, :postgrex
# And provide a custom storage implementation
@behaviour Ecto.Adapter.Storage
@behaviour Ecto.Adapter.Structure
@doc """
All Ecto extensions for Postgrex.
"""
def extensions do
[Ecto.Adapters.Postgres.Date, Ecto.Adapters.Postgres.Time,
Ecto.Adapters.Postgres.Timestamp, Ecto.Adapters.Postgres.TimestampTZ]
end
# Support arrays in place of IN
@doc false
def dumpers({:embed, _} = type, _), do: [&Ecto.Adapters.SQL.dump_embed(type, &1)]
def dumpers({:in, sub}, {:in, sub}), do: [{:array, sub}]
def dumpers(:binary_id, type), do: [type, Ecto.UUID]
def dumpers(_, type), do: [type]
## Storage API
@doc false
def storage_up(opts) do
database = Keyword.fetch!(opts, :database) || raise ":database is nil in repository configuration"
encoding = opts[:encoding] || "UTF8"
opts = Keyword.put(opts, :database, "postgres")
command =
~s(CREATE DATABASE "#{database}" ENCODING '#{encoding}')
|> concat_if(opts[:template], &"TEMPLATE=#{&1}")
|> concat_if(opts[:lc_ctype], &"LC_CTYPE='#{&1}'")
|> concat_if(opts[:lc_collate], &"LC_COLLATE='#{&1}'")
case run_query(command, opts) do
{:ok, _} ->
:ok
{:error, %{postgres: %{code: :duplicate_database}}} ->
{:error, :already_up}
{:error, error} ->
{:error, Exception.message(error)}
end
end
defp concat_if(content, nil, _fun), do: content
defp concat_if(content, value, fun), do: content <> " " <> fun.(value)
@doc false
def storage_down(opts) do
database = Keyword.fetch!(opts, :database) || raise ":database is nil in repository configuration"
command = "DROP DATABASE \"#{database}\""
opts = Keyword.put(opts, :database, "postgres")
case run_query(command, opts) do
{:ok, _} ->
:ok
{:error, %{postgres: %{code: :invalid_catalog_name}}} ->
{:error, :already_down}
{:error, error} ->
{:error, Exception.message(error)}
end
end
@doc false
def supports_ddl_transaction? do
true
end
@doc false
def structure_dump(default, config) do
table = config[:migration_source] || "schema_migrations"
with {:ok, versions} <- select_versions(table, config),
{:ok, path} <- pg_dump(default, config),
do: append_versions(table, versions, path)
end
defp select_versions(table, config) do
case run_query(~s[SELECT version FROM public."#{table}" ORDER BY version], config) do
{:ok, %{rows: rows}} -> {:ok, Enum.map(rows, &hd/1)}
{:error, %{postgres: %{code: :undefined_table}}} -> {:ok, []}
{:error, _} = error -> error
end
end
defp pg_dump(default, config) do
path = config[:dump_path] || Path.join(default, "structure.sql")
File.mkdir_p!(Path.dirname(path))
case run_with_cmd("pg_dump", config, ["--file", path, "--schema-only", "--no-acl",
"--no-owner", config[:database]]) do
{_output, 0} ->
{:ok, path}
{output, _} ->
{:error, output}
end
end
defp append_versions(_table, [], path) do
{:ok, path}
end
defp append_versions(table, versions, path) do
sql =
~s[INSERT INTO public."#{table}" (version) VALUES ] <>
Enum.map_join(versions, ", ", &"(#{&1})") <> ~s[;\n\n]
File.open!(path, [:append], fn file ->
IO.write(file, sql)
end)
{:ok, path}
end
@doc false
def structure_load(default, config) do
path = config[:dump_path] || Path.join(default, "structure.sql")
case run_with_cmd("psql", config, ["--quiet", "--file", path, config[:database]]) do
{_output, 0} -> {:ok, path}
{output, _} -> {:error, output}
end
end
## Helpers
defp run_query(sql, opts) do
{:ok, _} = Application.ensure_all_started(:postgrex)
opts =
opts
|> Keyword.drop([:name, :log])
|> Keyword.put(:pool, DBConnection.Connection)
|> Keyword.put(:backoff_type, :stop)
{:ok, pid} = Task.Supervisor.start_link
task = Task.Supervisor.async_nolink(pid, fn ->
{:ok, conn} = Postgrex.start_link(opts)
value = Ecto.Adapters.Postgres.Connection.execute(conn, sql, [], opts)
GenServer.stop(conn)
value
end)
timeout = Keyword.get(opts, :timeout, 15_000)
case Task.yield(task, timeout) || Task.shutdown(task) do
{:ok, {:ok, result}} ->
{:ok, result}
{:ok, {:error, error}} ->
{:error, error}
{:exit, {%{__struct__: struct} = error, _}}
when struct in [Postgrex.Error, DBConnection.Error] ->
{:error, error}
{:exit, reason} ->
{:error, RuntimeError.exception(Exception.format_exit(reason))}
nil ->
{:error, RuntimeError.exception("command timed out")}
end
end
defp run_with_cmd(cmd, opts, opt_args) do
unless System.find_executable(cmd) do
raise "could not find executable `#{cmd}` in path, " <>
"please guarantee it is available before running ecto commands"
end
env =
[{"PGCONNECT_TIMEOUT", "10"}]
env =
if password = opts[:password] do
[{"PGPASSWORD", password}|env]
else
env
end
args =
[]
args =
if username = opts[:username], do: ["-U", username|args], else: args
args =
if port = opts[:port], do: ["-p", to_string(port)|args], else: args
host = opts[:hostname] || System.get_env("PGHOST") || "localhost"
args = ["--host", host|args]
args = args ++ opt_args
System.cmd(cmd, args, env: env, stderr_to_stdout: true)
end
end