userver: userver/storages/postgres/transaction.hpp Source File
All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Modules Pages Concepts
transaction.hpp
Go to the documentation of this file.
1#pragma once
2
3/// @file userver/storages/postgres/transaction.hpp
4/// @brief Transactions
5
6#include <memory>
7#include <string>
8
9#include <userver/storages/postgres/detail/connection_ptr.hpp>
10#include <userver/storages/postgres/detail/query_parameters.hpp>
11#include <userver/storages/postgres/detail/time_types.hpp>
12#include <userver/storages/postgres/options.hpp>
13#include <userver/storages/postgres/parameter_store.hpp>
14#include <userver/storages/postgres/portal.hpp>
15#include <userver/storages/postgres/postgres_fwd.hpp>
16#include <userver/storages/postgres/query.hpp>
17#include <userver/storages/postgres/result_set.hpp>
18
19USERVER_NAMESPACE_BEGIN
20
21namespace storages::postgres {
22
23/// @page pg_transactions uPg: Transactions
24///
25/// All queries that are run on a PostgreSQL cluster are executed inside
26/// a transaction, even if a single-query interface is used.
27///
28/// A uPg transaction can be started using all isolation levels and modes
29/// supported by PostgreSQL server as specified in documentation here
30/// https://www.postgresql.org/docs/current/static/sql-set-transaction.html.
31/// When starting a transaction, the options are specified using
32/// TransactionOptions structure.
33///
34/// For convenience and improvement of readability there are constants
35/// defined: Transaction::RW, Transaction::RO and Transaction::Deferrable.
36///
37/// @see TransactionOptions
38///
39/// Transaction object ensures that a transaction started in a PostgreSQL
40/// connection will be either committed or rolled back and the connection
41/// will returned back to a connection pool.
42///
43/// @todo Code snippet with transaction starting and committing
44///
45/// Next: @ref pg_run_queries
46///
47/// See also: @ref pg_process_results
48///
49/// ----------
50///
51/// @htmlonly <div class="bottom-nav"> @endhtmlonly
52/// ⇦ @ref pg_driver | @ref pg_run_queries ⇨
53/// @htmlonly </div> @endhtmlonly
54
55/// @page pg_run_queries uPg: Running queries
56///
57/// All queries are executed through a transaction object, event when being
58/// executed through singe-query interface, so here only executing queries
59/// with transaction will be covered. Single-query interface is basically
60/// the same except for additional options.
61///
62/// uPg provides means to execute text queries only. There is no query
63/// generation, but can be used by other tools to execute SQL queries.
64///
65/// @warning A query must contain a single query, multiple statements delimited
66/// by ';' are not supported.
67///
68/// All queries are parsed and prepared during the first invocation and are
69/// executed as prepared statements afterwards.
70///
71/// Any query execution can throw an exception. Please see @ref pg_errors for
72/// more information on possible errors.
73///
74/// @par Queries without parameters
75///
76/// Executing a query without any parameters is rather straightforward.
77/// @code
78/// auto trx = cluster->Begin(/* transaction options */);
79/// auto res = trx.Execute("select foo, bar from foobar");
80/// trx.Commit();
81/// @endcode
82///
83/// The cluster also provides interface for single queries
84/// @code
85/// #include <service/sql_queries.hpp>
86///
87/// auto res = cluster->Execute(/* transaction options */, sql::kMyQuery);
88/// @endcode
89///
90/// You may store SQL queries in separate `.sql` files and access them via
91/// sql_queries.hpp include header. See @ref scripts/docs/en/userver/sql_files.md
92/// for more information.
93///
94/// @par Queries with parameters
95///
96/// uPg supports SQL dollar notation for parameter placeholders. The statement
97/// is prepared at first execution and then only arguments for a query is sent
98/// to the server.
99///
100/// A parameter can be of any type that is supported by the driver.
101/// See @ref scripts/docs/en/userver/pg_types.md for more information.
102///
103/// @code
104/// auto trx = cluster->Begin(/* transaction options */);
105/// auto res = trx.Execute(
106/// "select foo, bar from foobar where foo > $1 and bar = $2", 42, "baz");
107/// trx.Commit();
108/// @endcode
109///
110/// @note You may write a query in `.sql` file and generate a header file with Query from it.
111/// See @ref scripts/docs/en/userver/sql_files.md for more information.
112/// @see Transaction
113/// @see ResultSet
114///
115/// ----------
116///
117/// @htmlonly <div class="bottom-nav"> @endhtmlonly
118/// ⇦ @ref pg_transactions | @ref pg_process_results ⇨
119/// @htmlonly </div> @endhtmlonly
120
121// clang-format off
122/// @brief PostgreSQL transaction.
123///
124/// RAII wrapper for running transactions on PostgreSQL connections. Should be
125/// retrieved by calling storages::postgres::Cluster::Begin().
126///
127/// Non-copyable.
128///
129/// If the transaction is not explicitly finished (either committed or rolled back)
130/// it will roll itself back in the destructor.
131///
132/// @par Usage synopsis
133/// @code
134/// auto trx = someCluster.Begin(/* transaction options */);
135/// auto res = trx.Execute("select col1, col2 from schema.table");
136/// DoSomething(res);
137/// res = trx.Execute("update schema.table set col1 = $1 where col2 = $2", v1, v2);
138/// // If in the above lines an exception is thrown, then the transaction is
139/// // rolled back in the destructor of trx.
140/// trx.Commit();
141/// @endcode
142// clang-format on
143
145public:
146 //@{
147 /** @name Shortcut transaction options constants */
148 /// Read-write read committed transaction
149 static constexpr TransactionOptions RW{};
150 /// Read-only read committed transaction
151 static constexpr TransactionOptions RO{TransactionOptions::kReadOnly};
152 /// Read-only serializable deferrable transaction
154 //@}
155
156 static constexpr std::size_t kDefaultRowsInChunk = 1024;
157
158 /// @cond
159 explicit Transaction(
160 detail::ConnectionPtr&& conn,
161 const TransactionOptions& = RW,
162 OptionalCommandControl trx_cmd_ctl = {},
163 detail::SteadyClock::time_point trx_start_time = detail::SteadyClock::now()
164 );
165
166 void SetName(std::string name);
167 /// @endcond
168
169 Transaction(Transaction&&) noexcept;
170 Transaction& operator=(Transaction&&) noexcept;
171
172 Transaction(const Transaction&) = delete;
173 Transaction& operator=(const Transaction&) = delete;
174
175 ~Transaction();
176 /// @name Query execution
177 /// @{
178 /// Execute statement with arbitrary parameters.
179 ///
180 /// Suspends coroutine for execution.
181 ///
182 /// @note You may write a query in `.sql` file and generate a header file with Query from it.
183 /// See @ref scripts/docs/en/userver/sql_files.md for more information.
184 ///
185 /// @snippet storages/postgres/tests/landing_test.cpp TransacExec
186 template <typename... Args>
187 ResultSet Execute(const Query& query, const Args&... args) {
188 return Execute(OptionalCommandControl{}, query, args...);
189 }
190
191 /// Execute statement with arbitrary parameters and per-statement command
192 /// control.
193 ///
194 /// Suspends coroutine for execution.
195 ///
196 /// @note You may write a query in `.sql` file and generate a header file with Query from it.
197 /// See @ref scripts/docs/en/userver/sql_files.md for more information.
198 ///
199 /// @warning Do NOT create a query string manually by embedding arguments!
200 /// It leads to vulnerabilities and bad performance. Either pass arguments
201 /// separately, or use storages::postgres::ParameterScope.
202 template <typename... Args>
203 ResultSet Execute(OptionalCommandControl statement_cmd_ctl, const Query& query, const Args&... args) {
204 detail::StaticQueryParameters<sizeof...(args)> params;
205 params.Write(GetConnectionUserTypes(), args...);
206 return DoExecute(query, detail::QueryParameters{params}, statement_cmd_ctl);
207 }
208
209 /// Execute statement with stored parameters.
210 ///
211 /// Suspends coroutine for execution.
212 ///
213 /// @note You may write a query in `.sql` file and generate a header file with Query from it.
214 /// See @ref scripts/docs/en/userver/sql_files.md for more information.
215 ///
216 /// @warning Do NOT create a query string manually by embedding arguments!
217 /// It leads to vulnerabilities and bad performance. Either pass arguments
218 /// separately, or use storages::postgres::ParameterScope.
219 ResultSet Execute(const Query& query, const ParameterStore& store) {
220 return Execute(OptionalCommandControl{}, query, store);
221 }
222
223 /// Execute statement with stored parameters and per-statement command
224 /// control.
225 ///
226 /// Suspends coroutine for execution.
227 ///
228 /// @note You may write a query in `.sql` file and generate a header file with Query from it.
229 /// See @ref scripts/docs/en/userver/sql_files.md for more information.
230 ///
231 /// @warning Do NOT create a query string manually by embedding arguments!
232 /// It leads to vulnerabilities and bad performance. Either pass arguments
233 /// separately, or use storages::postgres::ParameterScope.
234 ResultSet Execute(OptionalCommandControl statement_cmd_ctl, const Query& query, const ParameterStore& store);
235
236 /// Execute statement that uses an array of arguments splitting that array in
237 /// chunks and executing the statement with a chunk of arguments.
238 ///
239 /// Useful for statements that unnest their arguments to avoid the need to
240 /// increase timeouts due to data amount growth.
241 ///
242 /// @note You may write a query in `.sql` file and generate a header file with Query from it.
243 /// See @ref scripts/docs/en/userver/sql_files.md for more information.
244 template <typename Container>
245 void ExecuteBulk(const Query& query, const Container& args, std::size_t chunk_rows = kDefaultRowsInChunk);
246
247 /// Execute statement that uses an array of arguments splitting that array in
248 /// chunks and executing the statement with a chunk of arguments.
249 ///
250 /// Useful for statements that unnest their arguments to avoid the need to
251 /// increase timeouts due to data amount growth.
252 ///
253 /// @note You may write a query in `.sql` file and generate a header file with Query from it.
254 /// See @ref scripts/docs/en/userver/sql_files.md for more information.
255 template <typename Container>
256 void ExecuteBulk(
257 OptionalCommandControl statement_cmd_ctl,
258 const Query& query,
259 const Container& args,
260 std::size_t chunk_rows = kDefaultRowsInChunk
261 );
262
263 /// Execute statement that uses an array of arguments transforming that array
264 /// into N arrays of corresponding fields and executing the statement
265 /// with a chunk of each of these arrays values.
266 /// Basically, a column-wise ExecuteBulk.
267 ///
268 /// Useful for statements that unnest their arguments to avoid the need to
269 /// increase timeouts due to data amount growth, but providing an explicit
270 /// mapping from `Container::value_type` to PG type is infeasible for some
271 /// reason (otherwise, use ExecuteBulk).
272 ///
273 /// @note You may write a query in `.sql` file and generate a header file with Query from it.
274 /// See @ref scripts/docs/en/userver/sql_files.md for more information.
275 ///
276 /// @snippet storages/postgres/tests/arrays_pgtest.cpp ExecuteDecomposeBulk
277 template <typename Container>
278 void ExecuteDecomposeBulk(const Query& query, const Container& args, std::size_t chunk_rows = kDefaultRowsInChunk);
279
280 /// Execute statement that uses an array of arguments transforming that array
281 /// into N arrays of corresponding fields and executing the statement
282 /// with a chunk of each of these arrays values.
283 /// Basically, a column-wise ExecuteBulk.
284 ///
285 /// Useful for statements that unnest their arguments to avoid the need to
286 /// increase timeouts due to data amount growth, but providing an explicit
287 /// mapping from `Container::value_type` to PG type is infeasible for some
288 /// reason (otherwise, use ExecuteBulk).
289 ///
290 /// @note You may write a query in `.sql` file and generate a header file with Query from it.
291 /// See @ref scripts/docs/en/userver/sql_files.md for more information.
292 ///
293 /// @snippet storages/postgres/tests/arrays_pgtest.cpp ExecuteDecomposeBulk
294 template <typename Container>
296 OptionalCommandControl statement_cmd_ctl,
297 const Query& query,
298 const Container& args,
299 std::size_t chunk_rows = kDefaultRowsInChunk
300 );
301
302 /// @brief Create a portal for fetching results of a statement with arbitrary
303 /// parameters.
304 ///
305 /// @note You may write a query in `.sql` file and generate a header file with Query from it.
306 /// See @ref scripts/docs/en/userver/sql_files.md for more information.
307 template <typename... Args>
308 Portal MakePortal(const Query& query, const Args&... args) {
309 return MakePortal(OptionalCommandControl{}, query, args...);
310 }
311
312 /// @brief Create a portal for fetching results of a statement with arbitrary
313 /// parameters and per-statement command control.
314 ///
315 /// @note You may write a query in `.sql` file and generate a header file with Query from it.
316 /// See @ref scripts/docs/en/userver/sql_files.md for more information.
317 template <typename... Args>
318 Portal MakePortal(OptionalCommandControl statement_cmd_ctl, const Query& query, const Args&... args) {
319 detail::StaticQueryParameters<sizeof...(args)> params;
320 params.Write(GetConnectionUserTypes(), args...);
321 return MakePortal(PortalName{}, query, detail::QueryParameters{params}, statement_cmd_ctl);
322 }
323
324 /// @brief Create a portal for fetching results of a statement with stored
325 /// parameters.
326 ///
327 /// @note You may write a query in `.sql` file and generate a header file with Query from it.
328 /// See @ref scripts/docs/en/userver/sql_files.md for more information.
329 Portal MakePortal(const Query& query, const ParameterStore& store) {
330 return MakePortal(OptionalCommandControl{}, query, store);
331 }
332
333 /// @brief Create a portal for fetching results of a statement with stored parameters
334 /// and per-statement command control.
335 ///
336 /// @note You may write a query in `.sql` file and generate a header file with Query from it.
337 /// See @ref scripts/docs/en/userver/sql_files.md for more information.
338 Portal MakePortal(OptionalCommandControl statement_cmd_ctl, const Query& query, const ParameterStore& store);
339
340 /// Set a connection parameter
341 /// https://www.postgresql.org/docs/current/sql-set.html
342 /// The parameter is set for this transaction only
343 void SetParameter(const std::string& param_name, const std::string& value);
344 //@}
345
346 /// Commit the transaction
347 /// Suspends coroutine until command complete.
348 /// After Commit or Rollback is called, the transaction is not usable any
349 /// more.
350 void Commit();
351 /// Rollback the transaction
352 /// Suspends coroutine until command complete.
353 /// After Commit or Rollback is called, the transaction is not usable any
354 /// more.
355 void Rollback();
356
357 /// Used in tests
358 OptionalCommandControl GetConnTransactionCommandControlDebug() const;
359 TimeoutDuration GetConnStatementTimeoutDebug() const;
360
361private:
363 DoExecute(const Query& query, const detail::QueryParameters& params, OptionalCommandControl statement_cmd_ctl);
364 Portal MakePortal(
365 const PortalName&,
366 const Query& query,
367 const detail::QueryParameters& params,
368 OptionalCommandControl statement_cmd_ctl
369 );
370
371 const UserTypes& GetConnectionUserTypes() const;
372
373 std::string name_;
374 detail::ConnectionPtr conn_;
375};
376
377template <typename Container>
378void Transaction::ExecuteBulk(const Query& query, const Container& args, std::size_t chunk_rows) {
379 auto split = io::SplitContainer(args, chunk_rows);
380 for (auto&& chunk : split) {
381 Execute(query, chunk);
382 }
383}
384
385template <typename Container>
387 OptionalCommandControl statement_cmd_ctl,
388 const Query& query,
389 const Container& args,
390 std::size_t chunk_rows
391) {
392 auto split = io::SplitContainer(args, chunk_rows);
393 for (auto&& chunk : split) {
394 Execute(statement_cmd_ctl, query, chunk);
395 }
396}
397
398template <typename Container>
399void Transaction::ExecuteDecomposeBulk(const Query& query, const Container& args, std::size_t chunk_rows) {
400 io::SplitContainerByColumns(args, chunk_rows).Perform([&query, this](const auto&... args) {
401 this->Execute(query, args...);
402 });
403}
404
405template <typename Container>
407 OptionalCommandControl statement_cmd_ctl,
408 const Query& query,
409 const Container& args,
410 std::size_t chunk_rows
411) {
412 io::SplitContainerByColumns(args, chunk_rows).Perform([&query, &statement_cmd_ctl, this](const auto&... args) {
413 this->Execute(statement_cmd_ctl, query, args...);
414 });
415}
416
417} // namespace storages::postgres
418
419USERVER_NAMESPACE_END