epgsql_SUITE.erl 46 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230
  1. -module(epgsql_SUITE).
  2. -include_lib("eunit/include/eunit.hrl").
  3. -include_lib("common_test/include/ct.hrl").
  4. -include_lib("public_key/include/public_key.hrl").
  5. -include("epgsql_tests.hrl").
  6. -include("epgsql.hrl").
  7. -export([
  8. init_per_suite/1,
  9. init_per_group/2,
  10. all/0,
  11. groups/0,
  12. end_per_group/2,
  13. end_per_suite/1
  14. ]).
  15. -compile(export_all).
  16. modules() ->
  17. [
  18. epgsql,
  19. epgsql_cast,
  20. epgsql_incremental
  21. ].
  22. init_per_suite(Config) ->
  23. Config.
  24. all() ->
  25. [{group, M} || M <- modules()].
  26. -ifdef(have_maps).
  27. -define(MAPS_TESTS, [
  28. connect_map
  29. ]).
  30. -else.
  31. -define(MAPS_TESTS, []).
  32. -endif.
  33. groups() ->
  34. Groups = [
  35. {connect, [parrallel], [
  36. connect,
  37. connect_to_db,
  38. connect_as,
  39. connect_with_cleartext,
  40. connect_with_md5,
  41. connect_with_scram,
  42. connect_with_invalid_user,
  43. connect_with_invalid_password,
  44. connect_with_ssl,
  45. connect_with_client_cert,
  46. connect_to_closed_port
  47. | ?MAPS_TESTS
  48. ]},
  49. {types, [parallel], [
  50. numeric_type,
  51. character_type,
  52. uuid_type,
  53. point_type,
  54. geometry_type,
  55. uuid_select,
  56. date_time_type,
  57. json_type,
  58. misc_type,
  59. hstore_type,
  60. net_type,
  61. array_type,
  62. record_type,
  63. range_type,
  64. range8_type,
  65. custom_types
  66. ]},
  67. {generic, [parallel], [
  68. with_transaction
  69. ]}
  70. ],
  71. Tests = [
  72. {group, connect},
  73. {group, types},
  74. prepared_query,
  75. select,
  76. insert,
  77. update,
  78. delete,
  79. create_and_drop_table,
  80. cursor,
  81. multiple_result,
  82. execute_batch,
  83. batch_error,
  84. single_batch,
  85. extended_select,
  86. extended_sync_ok,
  87. extended_sync_error,
  88. returning_from_insert,
  89. returning_from_update,
  90. returning_from_delete,
  91. parse,
  92. parse_column_format,
  93. parse_error,
  94. parse_and_close,
  95. bind,
  96. bind_parameter_format,
  97. bind_error,
  98. bind_and_close,
  99. execute_error,
  100. describe,
  101. describe_with_param,
  102. describe_named,
  103. describe_error,
  104. portal,
  105. returning,
  106. multiple_statement,
  107. multiple_portal,
  108. execute_function,
  109. parameter_get,
  110. parameter_set,
  111. text_format,
  112. query_timeout,
  113. execute_timeout,
  114. connection_closed,
  115. connection_closed_by_server,
  116. active_connection_closed,
  117. warning_notice,
  118. listen_notify,
  119. listen_notify_payload,
  120. set_notice_receiver,
  121. get_cmd_status
  122. ],
  123. Groups ++ [case Module of
  124. epgsql ->
  125. {Module, [], [{group, generic} | Tests]};
  126. _ ->
  127. {Module, [], Tests}
  128. end || Module <- modules()].
  129. end_per_suite(_Config) ->
  130. ok.
  131. init_per_group(GroupName, Config) ->
  132. case lists:member(GroupName, modules()) of
  133. true -> [{module, GroupName}|Config];
  134. false -> Config
  135. end.
  136. end_per_group(_GroupName, _Config) ->
  137. ok.
  138. -define(UUID1,
  139. <<163,189,240,40,149,151,17,227,141,6,112,24,139,130,16,73>>).
  140. -define(UUID2,
  141. <<183,55,22,52,149,151,17,227,187,167,112,24,139,130,16,73>>).
  142. -define(UUID3,
  143. <<198,188,155,66,149,151,17,227,138,98,112,24,139,130,16,73>>).
  144. -define(TIMEOUT_ERROR, {error, #error{
  145. severity = error,
  146. code = <<"57014">>,
  147. codename = query_canceled,
  148. message = <<"canceling statement due to statement timeout">>,
  149. extra = [{file, <<"postgres.c">>},
  150. {line, _},
  151. {routine, _} | _]
  152. }}).
  153. %% From uuid.erl in http://gitorious.org/avtobiff/erlang-uuid
  154. uuid_to_bin_string(<<U0:32, U1:16, U2:16, U3:16, U4:48>>) ->
  155. iolist_to_binary(io_lib:format(
  156. "~8.16.0b-~4.16.0b-~4.16.0b-~4.16.0b-~12.16.0b",
  157. [U0, U1, U2, U3, U4])).
  158. connect(Config) ->
  159. epgsql_ct:connect_only(Config, []).
  160. connect_to_db(Connect) ->
  161. epgsql_ct:connect_only(Connect, [{database, "epgsql_test_db1"}]).
  162. connect_as(Config) ->
  163. epgsql_ct:connect_only(Config, ["epgsql_test", [{database, "epgsql_test_db1"}]]).
  164. connect_with_cleartext(Config) ->
  165. epgsql_ct:connect_only(Config, [
  166. "epgsql_test_cleartext",
  167. "epgsql_test_cleartext",
  168. [{database, "epgsql_test_db1"}]
  169. ]).
  170. connect_with_md5(Config) ->
  171. epgsql_ct:connect_only(Config, [
  172. "epgsql_test_md5",
  173. "epgsql_test_md5",
  174. [{database, "epgsql_test_db1"}]
  175. ]).
  176. connect_with_scram(Config) ->
  177. PgConf = ?config(pg_config, Config),
  178. Ver = ?config(version, PgConf),
  179. (Ver >= [10, 0])
  180. andalso
  181. epgsql_ct:connect_only(
  182. Config,
  183. [
  184. "epgsql_test_scram",
  185. "epgsql_test_scram",
  186. [{database, "epgsql_test_db1"}]
  187. ]).
  188. connect_with_invalid_user(Config) ->
  189. {Host, Port} = epgsql_ct:connection_data(Config),
  190. Module = ?config(module, Config),
  191. {error, Why} = Module:connect(
  192. Host,
  193. "epgsql_test_invalid",
  194. "epgsql_test_invalid",
  195. [{port, Port}, {database, "epgsql_test_db1"}]),
  196. case Why of
  197. invalid_authorization_specification -> ok; % =< 8.4
  198. invalid_password -> ok % >= 9.0
  199. end.
  200. connect_with_invalid_password(Config) ->
  201. {Host, Port} = epgsql_ct:connection_data(Config),
  202. Module = ?config(module, Config),
  203. {error, Why} = Module:connect(
  204. Host,
  205. "epgsql_test_md5",
  206. "epgsql_test_invalid",
  207. [{port, Port}, {database, "epgsql_test_db1"}]),
  208. case Why of
  209. invalid_authorization_specification -> ok; % =< 8.4
  210. invalid_password -> ok % >= 9.0
  211. end.
  212. connect_with_ssl(Config) ->
  213. Module = ?config(module, Config),
  214. epgsql_ct:with_connection(Config,
  215. fun(C) ->
  216. {ok, _Cols, [{true}]} = Module:equery(C, "select ssl_is_used()")
  217. end,
  218. "epgsql_test",
  219. [{ssl, true}]).
  220. connect_with_client_cert(Config) ->
  221. Module = ?config(module, Config),
  222. Dir = filename:join(code:lib_dir(epgsql), ?TEST_DATA_DIR),
  223. File = fun(Name) -> filename:join(Dir, Name) end,
  224. {ok, Pem} = file:read_file(File("epgsql.crt")),
  225. [{'Certificate', Der, not_encrypted}] = public_key:pem_decode(Pem),
  226. Cert = public_key:pkix_decode_cert(Der, plain),
  227. #'TBSCertificate'{serialNumber = Serial} = Cert#'Certificate'.tbsCertificate,
  228. Serial2 = list_to_binary(integer_to_list(Serial)),
  229. epgsql_ct:with_connection(Config,
  230. fun(C) ->
  231. {ok, _, [{true}]} = Module:equery(C, "select ssl_is_used()"),
  232. {ok, _, [{Serial2}]} = Module:equery(C, "select ssl_client_serial()")
  233. end,
  234. "epgsql_test_cert",
  235. [{ssl, true}, {ssl_opts, [{keyfile, File("epgsql.key")},
  236. {certfile, File("epgsql.crt")}]}]).
  237. -ifdef(have_maps).
  238. connect_map(Config) ->
  239. {Host, Port} = epgsql_ct:connection_data(Config),
  240. Module = ?config(module, Config),
  241. Opts = #{
  242. host => Host,
  243. port => Port,
  244. database => "epgsql_test_db1",
  245. username => "epgsql_test_md5",
  246. password => "epgsql_test_md5"
  247. },
  248. {ok, C} = Module:connect(Opts),
  249. Module:close(C),
  250. epgsql_ct:flush(),
  251. ok.
  252. -endif.
  253. connect_to_closed_port(Config) ->
  254. {Host, Port} = epgsql_ct:connection_data(Config),
  255. Module = ?config(module, Config),
  256. Trap = process_flag(trap_exit, true),
  257. ?assertEqual({error, econnrefused},
  258. Module:connect(
  259. Host,
  260. "epgsql_test",
  261. "epgsql_test",
  262. [{port, Port + 1}, {database, "epgsql_test_db1"}])),
  263. ?assertMatch({'EXIT', _, econnrefused}, receive Stop -> Stop end),
  264. process_flag(trap_exit, Trap).
  265. prepared_query(Config) ->
  266. Module = ?config(module, Config),
  267. epgsql_ct:with_connection(Config, fun(C) ->
  268. {ok, _} = Module:parse(C, "inc", "select $1+1", []),
  269. {ok, Cols, [{5}]} = Module:prepared_query(C, "inc", [4]),
  270. {ok, Cols, [{2}]} = Module:prepared_query(C, "inc", [1]),
  271. {ok, Cols, [{23}]} = Module:prepared_query(C, "inc", [22]),
  272. {error, _} = Module:prepared_query(C, "non_existent_query", [4])
  273. end).
  274. select(Config) ->
  275. Module = ?config(module, Config),
  276. epgsql_ct:with_connection(Config, fun(C) ->
  277. {ok, Cols, Rows} = Module:squery(C, "select * from test_table1"),
  278. [
  279. #column{name = <<"id">>, type = int4, size = 4},
  280. #column{name = <<"value">>, type = text, size = -1}
  281. ] = Cols,
  282. [{<<"1">>, <<"one">>}, {<<"2">>, <<"two">>}] = Rows
  283. end).
  284. insert(Config) ->
  285. Module = ?config(module, Config),
  286. epgsql_ct:with_rollback(Config, fun(C) ->
  287. {ok, 1} = Module:squery(C, "insert into test_table1 (id, value) values (3, 'three')")
  288. end).
  289. update(Config) ->
  290. Module = ?config(module, Config),
  291. epgsql_ct:with_rollback(Config, fun(C) ->
  292. {ok, 1} = Module:squery(C, "insert into test_table1 (id, value) values (3, 'three')"),
  293. {ok, 1} = Module:squery(C, "insert into test_table1 (id, value) values (4, 'four')"),
  294. {ok, 2} = Module:squery(C, "update test_table1 set value = 'foo' where id > 2"),
  295. {ok, _, [{<<"2">>}]} = Module:squery(C, "select count(*) from test_table1 where value = 'foo'")
  296. end).
  297. delete(Config) ->
  298. Module = ?config(module, Config),
  299. epgsql_ct:with_rollback(Config, fun(C) ->
  300. {ok, 1} = Module:squery(C, "insert into test_table1 (id, value) values (3, 'three')"),
  301. {ok, 1} = Module:squery(C, "insert into test_table1 (id, value) values (4, 'four')"),
  302. {ok, 2} = Module:squery(C, "delete from test_table1 where id > 2"),
  303. {ok, _, [{<<"2">>}]} = Module:squery(C, "select count(*) from test_table1")
  304. end).
  305. create_and_drop_table(Config) ->
  306. Module = ?config(module, Config),
  307. epgsql_ct:with_rollback(Config, fun(C) ->
  308. {ok, [], []} = Module:squery(C, "create table test_table3 (id int4)"),
  309. {ok, [#column{type = int4}], []} = Module:squery(C, "select * from test_table3"),
  310. {ok, [], []} = Module:squery(C, "drop table test_table3")
  311. end).
  312. cursor(Config) ->
  313. Module = ?config(module, Config),
  314. epgsql_ct:with_connection(Config, fun(C) ->
  315. {ok, [], []} = Module:squery(C, "begin"),
  316. {ok, [], []} = Module:squery(C, "declare c cursor for select id from test_table1"),
  317. {ok, 2} = Module:squery(C, "move forward 2 from c"),
  318. {ok, 1} = Module:squery(C, "move backward 1 from c"),
  319. {ok, 1, _Cols, [{<<"2">>}]} = Module:squery(C, "fetch next from c"),
  320. {ok, [], []} = Module:squery(C, "close c")
  321. end).
  322. multiple_result(Config) ->
  323. Module = ?config(module, Config),
  324. epgsql_ct:with_connection(Config, fun(C) ->
  325. [{ok, _, [{<<"1">>}]}, {ok, _, [{<<"2">>}]}] = Module:squery(C, "select 1; select 2"),
  326. [{ok, _, [{<<"1">>}]}, {error, #error{}}] = Module:squery(C, "select 1; select foo;")
  327. end).
  328. execute_batch(Config) ->
  329. Module = ?config(module, Config),
  330. epgsql_ct:with_connection(Config, fun(C) ->
  331. {ok, S1} = Module:parse(C, "one", "select $1", [int4]),
  332. {ok, S2} = Module:parse(C, "two", "select $1 + $2", [int4, int4]),
  333. [{ok, [{1}]}, {ok, [{3}]}] =
  334. Module:execute_batch(C, [{S1, [1]}, {S2, [1, 2]}])
  335. end).
  336. batch_error(Config) ->
  337. Module = ?config(module, Config),
  338. epgsql_ct:with_rollback(Config, fun(C) ->
  339. {ok, S} = Module:parse(C, "insert into test_table1(id, value) values($1, $2)"),
  340. [{ok, 1}, {error, _}] =
  341. Module:execute_batch(
  342. C,
  343. [{S, [3, "batch_error 3"]},
  344. {S, [2, "batch_error 2"]}, % duplicate key error
  345. {S, [5, "batch_error 5"]} % won't be executed
  346. ])
  347. end).
  348. single_batch(Config) ->
  349. Module = ?config(module, Config),
  350. epgsql_ct:with_connection(Config, fun(C) ->
  351. {ok, S1} = Module:parse(C, "one", "select $1", [int4]),
  352. [{ok, [{1}]}] = Module:execute_batch(C, [{S1, [1]}])
  353. end).
  354. extended_select(Config) ->
  355. Module = ?config(module, Config),
  356. epgsql_ct:with_connection(Config, fun(C) ->
  357. {ok, Cols, Rows} = Module:equery(C, "select * from test_table1", []),
  358. [#column{name = <<"id">>, type = int4, size = 4},
  359. #column{name = <<"value">>, type = text, size = -1}] = Cols,
  360. [{1, <<"one">>}, {2, <<"two">>}] = Rows
  361. end).
  362. extended_sync_ok(Config) ->
  363. Module = ?config(module, Config),
  364. epgsql_ct:with_connection(Config, fun(C) ->
  365. {ok, _Cols, [{<<"one">>}]} = Module:equery(C, "select value from test_table1 where id = $1", [1]),
  366. {ok, _Cols, [{<<"two">>}]} = Module:equery(C, "select value from test_table1 where id = $1", [2])
  367. end).
  368. extended_sync_error(Config) ->
  369. Module = ?config(module, Config),
  370. epgsql_ct:with_connection(Config, fun(C) ->
  371. {error, #error{}} = Module:equery(C, "select _alue from test_table1 where id = $1", [1]),
  372. {ok, _Cols, [{<<"one">>}]} = Module:equery(C, "select value from test_table1 where id = $1", [1])
  373. end).
  374. returning_from_insert(Config) ->
  375. Module = ?config(module, Config),
  376. epgsql_ct:with_rollback(Config, fun(C) ->
  377. {ok, 1, _Cols, [{3}]} = Module:equery(C, "insert into test_table1 (id) values (3) returning id")
  378. end).
  379. returning_from_update(Config) ->
  380. Module = ?config(module, Config),
  381. epgsql_ct:with_rollback(Config, fun(C) ->
  382. {ok, 2, _Cols, [{1}, {2}]} = Module:equery(C, "update test_table1 set value = 'hi' returning id"),
  383. ?assertMatch({ok, 0, [#column{}], []},
  384. Module:equery(C, "update test_table1 set value = 'hi' where false returning id")),
  385. ?assertMatch([{ok, 2, [#column{}], [{<<"1">>}, {<<"2">>}]},
  386. {ok, 0, [#column{}], []}],
  387. Module:squery(C,
  388. "update test_table1 set value = 'hi2' returning id; "
  389. "update test_table1 set value = 'hi' where false returning id"))
  390. end).
  391. returning_from_delete(Config) ->
  392. Module = ?config(module, Config),
  393. epgsql_ct:with_rollback(Config, fun(C) ->
  394. {ok, 2, _Cols, [{1}, {2}]} = Module:equery(C, "delete from test_table1 returning id"),
  395. ?assertMatch({ok, 0, [#column{}], []},
  396. Module:equery(C, "delete from test_table1 returning id"))
  397. end).
  398. parse(Config) ->
  399. Module = ?config(module, Config),
  400. epgsql_ct:with_connection(Config, fun(C) ->
  401. {ok, S} = Module:parse(C, "select * from test_table1"),
  402. [#column{name = <<"id">>}, #column{name = <<"value">>}] = S#statement.columns,
  403. ok = Module:close(C, S),
  404. ok = Module:sync(C)
  405. end).
  406. parse_column_format(Config) ->
  407. Module = ?config(module, Config),
  408. epgsql_ct:with_connection(Config, fun(C) ->
  409. {ok, S} = Module:parse(C, "select 1::int4, false::bool, 2.0::float4"),
  410. [#column{type = int4},
  411. #column{type = bool},
  412. #column{type = float4}] = S#statement.columns,
  413. ok = Module:bind(C, S, []),
  414. {ok, [{1, false, 2.0}]} = Module:execute(C, S, 0),
  415. ok = Module:close(C, S),
  416. ok = Module:sync(C)
  417. end).
  418. parse_error(Config) ->
  419. Module = ?config(module, Config),
  420. epgsql_ct:with_connection(Config, fun(C) ->
  421. {error, #error{
  422. extra = [{file, _}, {line, _}, {position, <<"8">>}, {routine, _} | _]
  423. }} = Module:parse(C, "select _ from test_table1"),
  424. {ok, S} = Module:parse(C, "select * from test_table1"),
  425. [#column{name = <<"id">>}, #column{name = <<"value">>}] = S#statement.columns,
  426. ok = Module:close(C, S),
  427. ok = Module:sync(C)
  428. end).
  429. parse_and_close(Config) ->
  430. Module = ?config(module, Config),
  431. epgsql_ct:with_connection(Config, fun(C) ->
  432. Parse = fun() -> Module:parse(C, "test", "select * from test_table1", []) end,
  433. {ok, S} = Parse(),
  434. {error, #error{code = <<"42P05">>, codename = duplicate_prepared_statement}} = Parse(),
  435. Module:close(C, S),
  436. {ok, S} = Parse(),
  437. ok = Module:sync(C)
  438. end).
  439. bind(Config) ->
  440. Module = ?config(module, Config),
  441. epgsql_ct:with_connection(Config, fun(C) ->
  442. {ok, S} = Module:parse(C, "select value from test_table1 where id = $1"),
  443. ok = Module:bind(C, S, [1]),
  444. ok = Module:close(C, S),
  445. ok = Module:sync(C)
  446. end).
  447. bind_parameter_format(Config) ->
  448. Module = ?config(module, Config),
  449. epgsql_ct:with_connection(Config, fun(C) ->
  450. {ok, S} = Module:parse(C, "select $1, $2, $3", [int2, text, bool]),
  451. [int2, text, bool] = S#statement.types,
  452. ok = Module:bind(C, S, [1, "hi", true]),
  453. {ok, [{1, <<"hi">>, true}]} = Module:execute(C, S, 0),
  454. ok = Module:close(C, S),
  455. ok = Module:sync(C)
  456. end).
  457. bind_error(Config) ->
  458. Module = ?config(module, Config),
  459. epgsql_ct:with_connection(Config, fun(C) ->
  460. {ok, S} = Module:parse(C, "select $1::char"),
  461. {error, #error{}} = Module:bind(C, S, [0]),
  462. ok = Module:bind(C, S, [$A]),
  463. ok = Module:close(C, S),
  464. ok = Module:sync(C)
  465. end).
  466. bind_and_close(Config) ->
  467. Module = ?config(module, Config),
  468. epgsql_ct:with_connection(Config, fun(C) ->
  469. {ok, S} = Module:parse(C, "select * from test_table1"),
  470. ok = Module:bind(C, S, "one", []),
  471. {error, #error{code = <<"42P03">>, codename = duplicate_cursor}} = Module:bind(C, S, "one", []),
  472. ok = Module:close(C, portal, "one"),
  473. ok = Module:bind(C, S, "one", []),
  474. ok = Module:sync(C)
  475. end).
  476. execute_error(Config) ->
  477. Module = ?config(module, Config),
  478. epgsql_ct:with_connection(Config, fun(C) ->
  479. {ok, S} = Module:parse(C, "insert into test_table1 (id, value) values ($1, $2)"),
  480. ok = Module:bind(C, S, [1, <<"foo">>]),
  481. {error, #error{
  482. code = <<"23505">>, codename = unique_violation,
  483. extra = [
  484. {constraint_name, <<"test_table1_pkey">>},
  485. {detail, _},
  486. {file, _},
  487. {line, _},
  488. {routine, _},
  489. {schema_name, <<"public">>} | _%,
  490. %{table_name, <<"test_table1">>}
  491. ]
  492. }} = Module:execute(C, S, 0),
  493. {error, sync_required} = Module:bind(C, S, [3, <<"quux">>]),
  494. ok = Module:sync(C),
  495. ok = Module:bind(C, S, [3, <<"quux">>]),
  496. {ok, _} = Module:execute(C, S, 0),
  497. {ok, 1} = Module:squery(C, "delete from test_table1 where id = 3")
  498. end).
  499. describe(Config) ->
  500. Module = ?config(module, Config),
  501. epgsql_ct:with_connection(Config, fun(C) ->
  502. {ok, S} = Module:parse(C, "select * from test_table1"),
  503. [#column{name = <<"id">>}, #column{name = <<"value">>}] = S#statement.columns,
  504. {ok, S} = Module:describe(C, S),
  505. ok = Module:close(C, S),
  506. ok = Module:sync(C)
  507. end).
  508. describe_with_param(Config) ->
  509. Module = ?config(module, Config),
  510. epgsql_ct:with_connection(Config, fun(C) ->
  511. {ok, S} = Module:parse(C, "select id from test_table1 where id = $1"),
  512. ?assertEqual([int4], S#statement.types),
  513. ?assertMatch([#column{name = <<"id">>}], S#statement.columns),
  514. {ok, S} = Module:describe(C, S),
  515. ok = Module:close(C, S),
  516. ok = Module:sync(C)
  517. end).
  518. describe_named(Config) ->
  519. Module = ?config(module, Config),
  520. epgsql_ct:with_connection(Config, fun(C) ->
  521. {ok, S} = Module:parse(C, "name", "select * from test_table1", []),
  522. [#column{name = <<"id">>}, #column{name = <<"value">>}] = S#statement.columns,
  523. {ok, S} = Module:describe(C, S),
  524. ok = Module:close(C, S),
  525. ok = Module:sync(C)
  526. end).
  527. describe_error(Config) ->
  528. Module = ?config(module, Config),
  529. epgsql_ct:with_connection(Config, fun(C) ->
  530. {error, #error{}} = Module:describe(C, statement, ""),
  531. {ok, S} = Module:parse(C, "select * from test_table1"),
  532. {ok, S} = Module:describe(C, statement, ""),
  533. ok = Module:sync(C)
  534. end).
  535. portal(Config) ->
  536. Module = ?config(module, Config),
  537. epgsql_ct:with_connection(Config, fun(C) ->
  538. {ok, S} = Module:parse(C, "select value from test_table1"),
  539. ok = Module:bind(C, S, []),
  540. {partial, [{<<"one">>}]} = Module:execute(C, S, 1),
  541. {partial, [{<<"two">>}]} = Module:execute(C, S, 1),
  542. {ok, []} = Module:execute(C, S,1),
  543. ok = Module:close(C, S),
  544. ok = Module:sync(C)
  545. end).
  546. returning(Config) ->
  547. Module = ?config(module, Config),
  548. epgsql_ct:with_rollback(Config, fun(C) ->
  549. {ok, S} = Module:parse(C, "update test_table1 set value = $1 returning id"),
  550. ok = Module:bind(C, S, ["foo"]),
  551. {ok, 2, [{1}, {2}]} = Module:execute(C, S),
  552. ok = Module:sync(C)
  553. end).
  554. multiple_statement(Config) ->
  555. Module = ?config(module, Config),
  556. epgsql_ct:with_connection(Config, fun(C) ->
  557. {ok, S1} = Module:parse(C, "one", "select value from test_table1 where id = 1", []),
  558. ok = Module:bind(C, S1, []),
  559. {partial, [{<<"one">>}]} = Module:execute(C, S1, 1),
  560. {ok, S2} = Module:parse(C, "two", "select value from test_table1 where id = 2", []),
  561. ok = Module:bind(C, S2, []),
  562. {partial, [{<<"two">>}]} = Module:execute(C, S2, 1),
  563. {ok, []} = Module:execute(C, S1, 1),
  564. {ok, []} = Module:execute(C, S2, 1),
  565. ok = Module:close(C, S1),
  566. ok = Module:close(C, S2),
  567. ok = Module:sync(C)
  568. end).
  569. multiple_portal(Config) ->
  570. Module = ?config(module, Config),
  571. epgsql_ct:with_connection(Config, fun(C) ->
  572. {ok, S} = Module:parse(C, "select value from test_table1 where id = $1"),
  573. ok = Module:bind(C, S, "one", [1]),
  574. ok = Module:bind(C, S, "two", [2]),
  575. {ok, [{<<"one">>}]} = Module:execute(C, S, "one", 0),
  576. {ok, [{<<"two">>}]} = Module:execute(C, S, "two", 0),
  577. ok = Module:close(C, S),
  578. ok = Module:sync(C)
  579. end).
  580. execute_function(Config) ->
  581. Module = ?config(module, Config),
  582. epgsql_ct:with_rollback(Config, fun(C) ->
  583. {ok, _Cols1, [{3}]} = Module:equery(C, "select insert_test1(3, 'three')"),
  584. {ok, _Cols2, [{<<>>}]} = Module:equery(C, "select do_nothing()")
  585. end).
  586. parameter_get(Config) ->
  587. Module = ?config(module, Config),
  588. epgsql_ct:with_connection(Config, fun(C) ->
  589. {ok, <<"off">>} = Module:get_parameter(C, "is_superuser")
  590. end).
  591. parameter_set(Config) ->
  592. Module = ?config(module, Config),
  593. epgsql_ct:with_connection(Config, fun(C) ->
  594. {ok, [], []} = Module:squery(C, "set DateStyle = 'ISO, MDY'"),
  595. {ok, <<"ISO, MDY">>} = Module:get_parameter(C, "DateStyle"),
  596. {ok, _Cols, [{<<"2000-01-02">>}]} = Module:squery(C, "select '2000-01-02'::date"),
  597. {ok, [], []} = Module:squery(C, "set DateStyle = 'German'"),
  598. {ok, <<"German, DMY">>} = Module:get_parameter(C, "DateStyle"),
  599. {ok, _Cols, [{<<"02.01.2000">>}]} = Module:squery(C, "select '2000-01-02'::date")
  600. end).
  601. numeric_type(Config) ->
  602. check_type(Config, int2, "1", 1, [0, 256, -32768, +32767]),
  603. check_type(Config, int4, "1", 1, [0, 512, -2147483648, +2147483647]),
  604. check_type(Config, int8, "1", 1, [0, 1024, -9223372036854775808, +9223372036854775807]),
  605. check_type(Config, float4, "1.0", 1.0, [0.0, 1.23456, -1.23456]),
  606. check_type(Config, float4, "'-Infinity'", minus_infinity, [minus_infinity, plus_infinity, nan]),
  607. check_type(Config, float8, "1.0", 1.0, [0.0, 1.23456789012345, -1.23456789012345]),
  608. check_type(Config, float8, "'nan'", nan, [minus_infinity, plus_infinity, nan]).
  609. character_type(Config) ->
  610. Alpha = unicode:characters_to_binary([16#03B1]),
  611. Ka = unicode:characters_to_binary([16#304B]),
  612. One = unicode:characters_to_binary([16#10D360]),
  613. check_type(Config, bpchar, "'A'", $A, [1, $1, 16#7F, Alpha, Ka, One], "c_char"),
  614. check_type(Config, text, "'hi'", <<"hi">>, [<<"">>, <<"hi">>]),
  615. check_type(Config, varchar, "'hi'", <<"hi">>, [<<"">>, <<"hi">>]),
  616. epgsql_ct:with_connection(
  617. Config,
  618. fun(C) ->
  619. Module = ?config(module, Config),
  620. %% IOlists
  621. ?assertMatch({ok, _, [{<<1087/utf8, 1088/utf8, 1080/utf8,
  622. 1074/utf8, 1077/utf8, 1090/utf8>>}]},
  623. Module:equery(C, "SELECT $1::text", [[1087,1088,1080,1074,1077,1090]])),
  624. %% Deprecated casts
  625. ?assertMatch({ok, _, [{<<"my_atom">>}]},
  626. Module:equery(C, "SELECT $1::varchar", [my_atom])),
  627. ?assertMatch({ok, _, [{<<"12345">>}]},
  628. Module:equery(C, "SELECT $1::varchar", [12345])),
  629. FloatBin = erlang:float_to_binary(1.2345),
  630. ?assertMatch({ok, _, [{FloatBin}]},
  631. Module:equery(C, "SELECT $1::varchar", [1.2345])),
  632. %% String bpchar
  633. ?assertMatch({ok, _, [{<<"hello world">>}]},
  634. Module:equery(C, "SELECT $1::bpchar", ["hello world"]))
  635. end).
  636. uuid_type(Config) ->
  637. check_type(Config, uuid,
  638. io_lib:format("'~s'", [uuid_to_bin_string(?UUID1)]),
  639. uuid_to_bin_string(?UUID1), []).
  640. point_type(Config) ->
  641. check_type(Config, point, "'(23.15, 100)'", {23.15, 100.0}, []).
  642. geometry_type(Config) ->
  643. check_type(Config, geometry, "'COMPOUNDCURVE(CIRCULARSTRING(0 0,1 1,1 0),(1 0,0 1))'",
  644. {compound_curve,'2d', [
  645. {circular_string,'2d', [
  646. {point,'2d',0.0,0.0,undefined,undefined},
  647. {point,'2d',1.0,1.0,undefined,undefined},
  648. {point,'2d',1.0,0.0,undefined,undefined}
  649. ]},
  650. {line_string,'2d', [
  651. {point,'2d',1.0,0.0,undefined,undefined},
  652. {point,'2d',0.0,1.0,undefined,undefined}
  653. ]}
  654. ]}, []).
  655. uuid_select(Config) ->
  656. Module = ?config(module, Config),
  657. epgsql_ct:with_rollback(Config, fun(C) ->
  658. U1 = uuid_to_bin_string(?UUID1),
  659. U2 = uuid_to_bin_string(?UUID2),
  660. U3 = uuid_to_bin_string(?UUID3),
  661. {ok, 1} =
  662. Module:equery(C, "insert into test_table2 (c_varchar, c_uuid) values ('UUID1', $1)",
  663. [U1]),
  664. {ok, 1} =
  665. Module:equery(C, "insert into test_table2 (c_varchar, c_uuid) values ('UUID2', $1)",
  666. [U2]),
  667. {ok, 1} =
  668. Module:equery(C, "insert into test_table2 (c_varchar, c_uuid) values ('UUID3', $1)",
  669. [U3]),
  670. Res = Module:equery(C, "select c_varchar, c_uuid from test_table2 where c_uuid = any($1)",
  671. [[U1, U2]]),
  672. ?assertMatch(
  673. {ok,[#column{name = <<"c_varchar">>, type = varchar},
  674. #column{name = <<"c_uuid">>, type = uuid}],
  675. [{<<"UUID1">>, U1},
  676. {<<"UUID2">>, U2}]}, Res)
  677. end).
  678. date_time_type(Config) ->
  679. Module = ?config(module, Config),
  680. epgsql_ct:with_connection(Config, fun(C) ->
  681. case Module:get_parameter(C, "integer_datetimes") of
  682. {ok, <<"on">>} -> MaxTsDate = 294276;
  683. {ok, <<"off">>} -> MaxTsDate = 5874897
  684. end,
  685. check_type(Config, date, "'2008-01-02'", {2008,1,2}, [{-4712,1,1}, {5874897,1,1}]),
  686. check_type(Config, time, "'00:01:02'", {0,1,2.0}, [{0,0,0.0}, {24,0,0.0}]),
  687. check_type(Config, timetz, "'00:01:02-01'", {{0,1,2.0},1*60*60},
  688. [{{0,0,0.0},0}, {{24,0,0.0},-13*60*60}]),
  689. check_type(Config, timestamp, "'2008-01-02 03:04:05'", {{2008,1,2},{3,4,5.0}},
  690. [{{-4712,1,1},{0,0,0.0}}, {{MaxTsDate,12,31}, {23,59,59.0}}, {1322,334285,440966}]),
  691. check_type(Config, timestamptz, "'2011-01-02 03:04:05+3'", {{2011, 1, 2}, {0, 4, 5.0}}, [{1324,875970,286983}]),
  692. check_type(Config, interval, "'1 hour 2 minutes 3.1 seconds'", {{1,2,3.1},0,0},
  693. [{{0,0,0.0},0,-178000000 * 12}, {{0,0,0.0},0,178000000 * 12}])
  694. end).
  695. json_type(Config) ->
  696. check_type(Config, json, "'{}'", <<"{}">>,
  697. [<<"{}">>, <<"[]">>, <<"1">>, <<"1.0">>, <<"true">>, <<"\"string\"">>, <<"{\"key\": []}">>]),
  698. check_type(Config, jsonb, "'{}'", <<"{}">>,
  699. [<<"{}">>, <<"[]">>, <<"1">>, <<"1.0">>, <<"true">>, <<"\"string\"">>, <<"{\"key\": []}">>]).
  700. misc_type(Config) ->
  701. check_type(Config, bool, "true", true, [true, false]),
  702. check_type(Config, bytea, "E'\001\002'", <<1,2>>, [<<>>, <<0,128,255>>]).
  703. hstore_type(Config) ->
  704. Values = [
  705. {[]},
  706. {[{null, null}]},
  707. {[{null, undefined}]},
  708. {[{1, null}]},
  709. {[{1.0, null}]},
  710. {[{1, undefined}]},
  711. {[{1.0, undefined}]},
  712. {[{<<"a">>, <<"c">>}, {<<"c">>, <<"d">>}]},
  713. {[{<<"a">>, <<"c">>}, {<<"c">>, null}]},
  714. {[{<<"a">>, <<"c">>}, {<<"c">>, undefined}]}
  715. ],
  716. check_type(Config, hstore, "''", {[]}, []),
  717. check_type(Config, hstore,
  718. "'a => 1, b => 2.0, c => null'",
  719. {[{<<"a">>, <<"1">>}, {<<"b">>, <<"2.0">>}, {<<"c">>, null}]}, Values).
  720. net_type(Config) ->
  721. check_type(Config, cidr, "'127.0.0.1/32'", {{127,0,0,1}, 32}, [{{127,0,0,1}, 32}, {{0,0,0,0,0,0,0,1}, 128}]),
  722. check_type(Config, inet, "'127.0.0.1'", {127,0,0,1}, [{127,0,0,1}, {0,0,0,0,0,0,0,1}]),
  723. %% macaddr8 available only on PG>=10
  724. check_type(Config, macaddr,
  725. "'FF:FF:FF:FF:FF:FF'", {255, 255, 255, 255, 255, 255},
  726. [{255, 255, 255, 255, 255, 255},
  727. {6, 0, 0, 0, 0, 0}]).
  728. array_type(Config) ->
  729. Module = ?config(module, Config),
  730. epgsql_ct:with_connection(Config, fun(C) ->
  731. {ok, _, [{[1, 2]}]} = Module:equery(C, "select ($1::int[])[1:2]", [[1, 2, 3]]),
  732. {ok, _, [{[{1, <<"one">>}, {2, <<"two">>}]}]} =
  733. Module:equery(C, "select Array(select (id, value) from test_table1)", []),
  734. Select = fun(Type, A) ->
  735. Query = "select $1::" ++ atom_to_list(Type) ++ "[]",
  736. {ok, _Cols, [{A2}]} = Module:equery(C, Query, [A]),
  737. case lists:all(fun({V, V2}) -> compare(Type, V, V2) end, lists:zip(A, A2)) of
  738. true -> ok;
  739. false -> ?assertMatch(A, A2)
  740. end
  741. end,
  742. Select(int2, []),
  743. Select(int2, [1, 2, 3, 4]),
  744. Select(int2, [[1], [2], [3], [4]]),
  745. Select(int2, [[[[[[1, 2]]]]]]),
  746. Select(bool, [true]),
  747. Select(char, [$a, $b, $c]),
  748. Select(int4, [[1, 2]]),
  749. Select(int8, [[[[1, 2]], [[3, 4]]]]),
  750. Select(text, [<<"one">>, <<"two>">>]),
  751. Select(varchar, [<<"one">>, <<"two>">>]),
  752. Select(float4, [0.0, 1.0, 0.123]),
  753. Select(float8, [0.0, 1.0, 0.123]),
  754. Select(date, [{2008,1,2}, {2008,1,3}]),
  755. Select(time, [{0,1,2.0}, {0,1,3.0}]),
  756. Select(timetz, [{{0,1,2.0},1*60*60}, {{0,1,3.0},1*60*60}]),
  757. Select(timestamp, [{{2008,1,2},{3,4,5.0}}, {{2008,1,2},{3,4,6.0}}]),
  758. Select(timestamptz, [{{2008,1,2},{3,4,5.0}}, {{2008,1,2},{3,4,6.0}}]),
  759. Select(interval, [{{1,2,3.1},0,0}, {{1,2,3.2},0,0}]),
  760. Select(hstore, [{[{null, null}, {a, 1}, {1, 2}, {b, undefined}]}]),
  761. Select(hstore, [[{[{null, null}, {a, 1}, {1, 2}, {b, undefined}]}, {[]}], [{[{a, 1}]}, {[{null, 2}]}]]),
  762. Select(cidr, [{{127,0,0,1}, 32}, {{0,0,0,0,0,0,0,1}, 128}]),
  763. Select(inet, [{127,0,0,1}, {0,0,0,0,0,0,0,1}]),
  764. Select(json, [<<"{}">>, <<"[]">>, <<"1">>, <<"1.0">>, <<"true">>, <<"\"string\"">>, <<"{\"key\": []}">>]),
  765. Select(jsonb, [<<"{}">>, <<"[]">>, <<"1">>, <<"1.0">>, <<"true">>, <<"\"string\"">>, <<"{\"key\": []}">>])
  766. end).
  767. record_type(Config) ->
  768. Module = ?config(module, Config),
  769. epgsql_ct:with_connection(Config, fun(C) ->
  770. Select = fun(Sql, Expected) ->
  771. {ok, _Columns, [Row]} = Module:equery(C, Sql, []),
  772. ?assertMatch(Expected, Row)
  773. end,
  774. %% Simple record
  775. Select("select (1,2)", {{1, 2}}),
  776. %% Record inside other record
  777. Select("select (1, (select (2,3)))", {{1, {2, 3}}}),
  778. %% Array inside record
  779. Select("select (1, '{2,3}'::int[])", {{1, [2, 3]}}),
  780. %% Array of records inside record
  781. Select("select (0, ARRAY(select (id, value) from test_table1))", {{0,[{1,<<"one">>},{2,<<"two">>}]}})
  782. end).
  783. custom_types(Config) ->
  784. Module = ?config(module, Config),
  785. epgsql_ct:with_connection(Config, fun(C) ->
  786. Module:squery(C, "drop table if exists t_foo;"),
  787. Module:squery(C, "drop type if exists my_type;"),
  788. {ok, [], []} = Module:squery(C, "create type my_type as enum('foo', 'bar');"),
  789. {ok, [my_type]} = epgsql:update_type_cache(C, [{epgsql_codec_test_enum, [foo, bar]}]),
  790. {ok, [], []} = Module:squery(C, "create table t_foo (col my_type);"),
  791. {ok, S} = Module:parse(C, "insert_foo", "insert into t_foo values ($1)", [my_type]),
  792. ok = Module:bind(C, S, [bar]),
  793. {ok, 1} = Module:execute(C, S),
  794. ?assertMatch({ok, _, [{bar}]}, Module:equery(C, "SELECT col FROM t_foo"))
  795. end).
  796. text_format(Config) ->
  797. Module = ?config(module, Config),
  798. epgsql_ct:with_connection(Config, fun(C) ->
  799. Select = fun(Type, V) ->
  800. V2 = list_to_binary(V),
  801. Query = "select $1::" ++ Type,
  802. ?assertMatch({ok, _Cols, [{V2}]}, Module:equery(C, Query, [V])),
  803. ?assertMatch({ok, _Cols, [{V2}]}, Module:equery(C, Query, [V2]))
  804. end,
  805. Select("numeric", "123456")
  806. end).
  807. query_timeout(Config) ->
  808. Module = ?config(module, Config),
  809. epgsql_ct:with_connection(Config, fun(C) ->
  810. {ok, _, _} = Module:squery(C, "SET statement_timeout = 500"),
  811. ?assertMatch(?TIMEOUT_ERROR, Module:squery(C, "SELECT pg_sleep(1)")),
  812. ?assertMatch(?TIMEOUT_ERROR, Module:equery(C, "SELECT pg_sleep(2)")),
  813. {ok, _Cols, [{1}]} = Module:equery(C, "SELECT 1")
  814. end, []).
  815. execute_timeout(Config) ->
  816. Module = ?config(module, Config),
  817. epgsql_ct:with_connection(Config, fun(C) ->
  818. {ok, _, _} = Module:squery(C, "SET statement_timeout = 500"),
  819. {ok, S} = Module:parse(C, "select pg_sleep($1)"),
  820. ok = Module:bind(C, S, [2]),
  821. ?assertMatch(?TIMEOUT_ERROR, Module:execute(C, S, 0)),
  822. ok = Module:sync(C),
  823. ok = Module:bind(C, S, [0]),
  824. {ok, [{<<>>}]} = Module:execute(C, S, 0),
  825. ok = Module:close(C, S),
  826. ok = Module:sync(C)
  827. end, []).
  828. connection_closed(Config) ->
  829. {Host, Port} = epgsql_ct:connection_data(Config),
  830. Module = ?config(module, Config),
  831. P = self(),
  832. spawn_link(fun() ->
  833. process_flag(trap_exit, true),
  834. {ok, C} = Module:connect(Host, "epgsql_test",[
  835. {port, Port},
  836. {database, "epgsql_test_db1"}
  837. ]),
  838. P ! {connected, C},
  839. receive
  840. Any -> P ! Any
  841. end
  842. end),
  843. receive
  844. {connected, C} ->
  845. timer:sleep(100),
  846. Module:close(C),
  847. {'EXIT', C, _} = receive R -> R end
  848. end,
  849. epgsql_ct:flush().
  850. connection_closed_by_server(Config) ->
  851. Module = ?config(module, Config),
  852. epgsql_ct:with_connection(Config, fun(C1) ->
  853. P = self(),
  854. spawn_link(fun() ->
  855. process_flag(trap_exit, true),
  856. epgsql_ct:with_connection(Config, fun(C2) ->
  857. {ok, _, [{Pid}]} = Module:equery(C2, "select pg_backend_pid()"),
  858. % emulate of disconnection
  859. {ok, _, [{true}]} = Module:equery(C1,
  860. "select pg_terminate_backend($1)", [Pid]),
  861. receive
  862. {'EXIT', C2, {shutdown, #error{code = <<"57P01">>}}} ->
  863. P ! ok;
  864. Other ->
  865. ?debugFmt("Unexpected msg: ~p~n", [Other]),
  866. P ! error
  867. end
  868. end)
  869. end),
  870. receive ok -> ok end
  871. end).
  872. active_connection_closed(Config) ->
  873. {Host, Port} = epgsql_ct:connection_data(Config),
  874. Module = ?config(module, Config),
  875. P = self(),
  876. F = fun() ->
  877. process_flag(trap_exit, true),
  878. {ok, C} = Module:connect(Host, [
  879. {database, "epgsql_test_db1"},
  880. {port, Port}
  881. ]),
  882. P ! {connected, C},
  883. R = Module:squery(C, "select pg_sleep(10)"),
  884. P ! R
  885. end,
  886. spawn_link(F),
  887. receive
  888. {connected, C} ->
  889. timer:sleep(100),
  890. Module:close(C),
  891. {error, closed} = receive R -> R end
  892. end,
  893. epgsql_ct:flush().
  894. warning_notice(Config) ->
  895. Module = ?config(module, Config),
  896. epgsql_ct:with_connection(Config, fun(C) ->
  897. Q = "create function pg_temp.raise() returns void as $$
  898. begin
  899. raise warning 'oops';
  900. end;
  901. $$ language plpgsql;
  902. select pg_temp.raise()",
  903. [{ok, _, _}, _] = Module:squery(C, Q),
  904. receive
  905. {epgsql, C, {notice, #error{message = <<"oops">>, extra = Extra}}} ->
  906. ?assertMatch([{file, _},{line, _},{routine, _} | _], Extra),
  907. ok
  908. after
  909. 100 -> erlang:error(didnt_receive_notice)
  910. end
  911. end, [{async, self()}]).
  912. listen_notify(Config) ->
  913. Module = ?config(module, Config),
  914. epgsql_ct:with_connection(Config, fun(C) ->
  915. {ok, [], []} = Module:squery(C, "listen epgsql_test"),
  916. {ok, _, [{Pid}]} = Module:equery(C, "select pg_backend_pid()"),
  917. {ok, [], []} = Module:squery(C, "notify epgsql_test"),
  918. receive
  919. {epgsql, C, {notification, <<"epgsql_test">>, Pid, <<>>}} -> ok
  920. after
  921. 100 -> erlang:error(didnt_receive_notification)
  922. end
  923. end, [{async, self()}]).
  924. listen_notify_payload(Config) ->
  925. Module = ?config(module, Config),
  926. epgsql_ct:with_min_version(Config, [9, 0], fun(C) ->
  927. {ok, [], []} = Module:squery(C, "listen epgsql_test"),
  928. {ok, _, [{Pid}]} = Module:equery(C, "select pg_backend_pid()"),
  929. {ok, [], []} = Module:squery(C, "notify epgsql_test, 'test!'"),
  930. receive
  931. {epgsql, C, {notification, <<"epgsql_test">>, Pid, <<"test!">>}} -> ok
  932. after
  933. 100 -> erlang:error(didnt_receive_notification)
  934. end
  935. end, [{async, self()}]).
  936. set_notice_receiver(Config) ->
  937. Module = ?config(module, Config),
  938. epgsql_ct:with_min_version(Config, [9, 0], fun(C) ->
  939. {ok, [], []} = Module:squery(C, "listen epgsql_test"),
  940. {ok, _, [{Pid}]} = Module:equery(C, "select pg_backend_pid()"),
  941. EnsureNoNotification = fun(Payload) ->
  942. {ok, [], []} = Module:squery(C, ["notify epgsql_test, '", Payload, "'"]),
  943. receive
  944. {epgsql, _, _} -> erlang:error(got_unexpected_notification)
  945. after
  946. 10 -> ok
  947. end
  948. end,
  949. EnsureNotification = fun(Payload) ->
  950. {ok, [], []} = Module:squery(C, ["notify epgsql_test, '", Payload, "'"]),
  951. receive
  952. {epgsql, C, {notification, <<"epgsql_test">>, Pid, Payload}} -> ok
  953. after
  954. 100 -> erlang:error(didnt_receive_notification)
  955. end
  956. end,
  957. Self = self(),
  958. EnsureNoNotification(<<"test1">>),
  959. % Set pid()
  960. {ok, undefined} = Module:set_notice_receiver(C, Self),
  961. EnsureNotification(<<"test2">>),
  962. %% test PL/PgSQL NOTICE
  963. {ok, [], []} = Module:squery(C, ["DO $$ BEGIN RAISE WARNING 'test notice'; END $$;"]),
  964. receive
  965. {epgsql, C, {notice, #error{severity = warning,
  966. code = <<"01000">>,
  967. message = <<"test notice">>,
  968. extra = _}}} -> ok
  969. after
  970. 100 -> erlang:error(didnt_receive_notice)
  971. end,
  972. % set registered pid
  973. Receiver = pg_notification_receiver,
  974. register(Receiver, Self),
  975. {ok, Self} = Module:set_notice_receiver(C, Receiver),
  976. EnsureNotification(<<"test3">>),
  977. % make registered name invalid
  978. unregister(Receiver),
  979. EnsureNoNotification(<<"test4">>),
  980. % disable
  981. {ok, Receiver} = Module:set_notice_receiver(C, undefined),
  982. EnsureNoNotification(<<"test5">>)
  983. end, []).
  984. get_cmd_status(Config) ->
  985. Module = ?config(module, Config),
  986. epgsql_ct:with_connection(Config, fun(C) ->
  987. {ok, [], []} = Module:squery(C, "BEGIN"),
  988. ?assertEqual({ok, 'begin'}, Module:get_cmd_status(C)),
  989. {ok, [], []} = epgsql:equery(C, "CREATE TEMPORARY TABLE cmd_status_t(col INTEGER)"),
  990. ?assertEqual({ok, 'create'}, Module:get_cmd_status(C)),
  991. %% Some commands have number of affected rows in status
  992. {ok, N} = Module:squery(C, "INSERT INTO cmd_status_t (col) VALUES (1), (2)"),
  993. ?assertEqual({ok, {'insert', N}}, Module:get_cmd_status(C)),
  994. {ok, 1} = Module:squery(C, "UPDATE cmd_status_t SET col=3 WHERE col=1"),
  995. ?assertEqual({ok, {'update', 1}}, Module:get_cmd_status(C)),
  996. %% Failed queries have no status
  997. {error, _} = Module:squery(C, "DELETE FROM cmd_status_t WHERE not_col=2"),
  998. ?assertEqual({ok, undefined}, Module:get_cmd_status(C)),
  999. %% if COMMIT failed, status will be 'rollback'
  1000. {ok, [], []} = Module:squery(C, "COMMIT"),
  1001. ?assertEqual({ok, 'rollback'}, Module:get_cmd_status(C)),
  1002. %% Only last command's status returned
  1003. [_, _, _] = Module:squery(C, "BEGIN; SELECT 1; COMMIT"),
  1004. ?assertEqual({ok, 'commit'}, Module:get_cmd_status(C))
  1005. end).
  1006. range_type(Config) ->
  1007. epgsql_ct:with_min_version(Config, [9, 2], fun(_C) ->
  1008. check_type(Config, int4range, "int4range(10, 20)", {10, 20}, [
  1009. {1, 58}, {-1, 12}, {-985521, 5412687}, {minus_infinity, 0},
  1010. {984655, plus_infinity}, {minus_infinity, plus_infinity}
  1011. ])
  1012. end, []).
  1013. range8_type(Config) ->
  1014. epgsql_ct:with_min_version(Config, [9, 2], fun(_C) ->
  1015. check_type(Config, int8range, "int8range(10, 20)", {10, 20}, [
  1016. {1, 58}, {-1, 12}, {-9223372036854775808, 5412687},
  1017. {minus_infinity, 9223372036854775807},
  1018. {984655, plus_infinity}, {minus_infinity, plus_infinity}
  1019. ])
  1020. end, []).
  1021. with_transaction(Config) ->
  1022. Module = ?config(module, Config),
  1023. epgsql_ct:with_connection(
  1024. Config,
  1025. fun(C) ->
  1026. %% Success case
  1027. ?assertEqual(
  1028. success, Module:with_transaction(C, fun(_) -> success end)),
  1029. ?assertEqual(
  1030. success, Module:with_transaction(C, fun(_) -> success end,
  1031. [{ensure_committed, true}])),
  1032. %% begin_opts
  1033. ?assertMatch(
  1034. [{ok, _, [{<<"serializable">>}]},
  1035. {ok, _, [{<<"on">>}]}],
  1036. Module:with_transaction(
  1037. C, fun(C1) ->
  1038. Module:squery(C1, ("SHOW transaction_isolation; "
  1039. "SHOW transaction_read_only"))
  1040. end,
  1041. [{begin_opts, "READ ONLY ISOLATION LEVEL SERIALIZABLE"}])),
  1042. %% ensure_committed failure
  1043. ?assertError(
  1044. {ensure_committed_failed, rollback},
  1045. Module:with_transaction(
  1046. C, fun(C1) ->
  1047. {error, _} = Module:squery(C1, "SELECT col FROM _nowhere_"),
  1048. ok
  1049. end,
  1050. [{ensure_committed, true}])),
  1051. %% reraise
  1052. ?assertEqual(
  1053. {rollback, my_err},
  1054. Module:with_transaction(
  1055. C, fun(_) -> error(my_err) end,
  1056. [{reraise, false}])),
  1057. ?assertError(
  1058. my_err,
  1059. Module:with_transaction(
  1060. C, fun(_) -> error(my_err) end, []))
  1061. end, []).
  1062. %% =============================================================================
  1063. %% Internal functions
  1064. %% ============================================================================
  1065. check_type(Config, Type, In, Out, Values) ->
  1066. Column = "c_" ++ atom_to_list(Type),
  1067. check_type(Config, Type, In, Out, Values, Column).
  1068. check_type(Config, Type, In, Out, Values, Column) ->
  1069. Module = ?config(module, Config),
  1070. epgsql_ct:with_connection(Config, fun(C) ->
  1071. Select = io_lib:format("select ~s::~w", [In, Type]),
  1072. Res = Module:equery(C, Select),
  1073. ?assertMatch({ok, [#column{type = Type}], [{Out}]}, Res),
  1074. Sql = io_lib:format("insert into test_table2 (~s) values ($1) returning ~s", [Column, Column]),
  1075. {ok, #statement{columns = [#column{type = Type}]} = S} = Module:parse(C, Sql),
  1076. Insert = fun(V) ->
  1077. ok = Module:bind(C, S, [V]),
  1078. {ok, 1, [{V2}]} = Module:execute(C, S),
  1079. case compare(Type, V, V2) of
  1080. true -> ok;
  1081. false ->
  1082. error({write_read_compare_failed,
  1083. iolist_to_binary(
  1084. io_lib:format("~p =/= ~p~n", [V, V2]))})
  1085. end,
  1086. ok = Module:sync(C)
  1087. end,
  1088. lists:foreach(Insert, [null, undefined | Values])
  1089. end).
  1090. compare(_Type, null, null) -> true;
  1091. compare(_Type, undefined, null) -> true;
  1092. compare(float4, V1, V2) when is_float(V1) -> abs(V2 - V1) < 0.000001;
  1093. compare(float8, V1, V2) when is_float(V1) -> abs(V2 - V1) < 0.000000000000001;
  1094. compare(hstore, {V1}, V2) -> compare(hstore, V1, V2);
  1095. compare(hstore, V1, {V2}) -> compare(hstore, V1, V2);
  1096. compare(hstore, V1, V2) ->
  1097. orddict:from_list(format_hstore(V1)) =:= orddict:from_list(format_hstore(V2));
  1098. compare(Type, V1 = {_, _, MS}, {D2, {H2, M2, S2}}) when Type == timestamp;
  1099. Type == timestamptz ->
  1100. {D1, {H1, M1, S1}} = calendar:now_to_universal_time(V1),
  1101. ({D1, H1, M1} =:= {D2, H2, M2}) and (abs(S1 + MS/1000000 - S2) < 0.000000000000001);
  1102. compare(_Type, V1, V2) -> V1 =:= V2.
  1103. format_hstore({Hstore}) -> Hstore;
  1104. format_hstore(Hstore) ->
  1105. [{format_hstore_key(Key), format_hstore_value(Value)} || {Key, Value} <- Hstore].
  1106. format_hstore_key(Key) -> format_hstore_string(Key).
  1107. format_hstore_value(null) -> null;
  1108. format_hstore_value(undefined) -> null;
  1109. format_hstore_value(Value) -> format_hstore_string(Value).
  1110. format_hstore_string(Num) when is_number(Num) -> iolist_to_binary(io_lib:format("~w", [Num]));
  1111. format_hstore_string(Str) -> iolist_to_binary(io_lib:format("~s", [Str])).