Управление транзакциями

По умолчанию psql работает в режиме автофиксации: 

=> \echo :AUTOCOMMIT

on

Это приводит к тому, что любая команда, выданная без явного указания начала транзакции, сразу же фиксируется. Изменим строку и проверим результат в другом сеансе: 

=> UPDATE authors SET first_name = 'Алексей' WHERE last_name = 'Толстой';

UPDATE 1

=> SELECT * FROM authors WHERE last_name = 'Толстой';

 author_id | last_name | first_name | middle_name 
-----------+-----------+------------+-------------
         5 | Толстой   | Алексей    | Николаевич
(1 row)

Сравните: 

=> BEGIN;

BEGIN

=> UPDATE authors SET first_name = 'Лев' WHERE last_name = 'Толстой';

UPDATE 1

=> SELECT * FROM authors WHERE last_name = 'Толстой';

 author_id | last_name | first_name | middle_name 
-----------+-----------+------------+-------------
         5 | Толстой   | Алексей    | Николаевич
(1 row)


=> COMMIT;

COMMIT

=> SELECT * FROM authors WHERE last_name = 'Толстой';

 author_id | last_name | first_name | middle_name 
-----------+-----------+------------+-------------
         5 | Толстой   | Лев        | Николаевич
(1 row)

Режим без автофиксации неявно начинает транзакцию при первой выданной команде; изменения надо фиксировать самостоятельно. 

=> \set AUTOCOMMIT off





=> UPDATE authors SET first_name = 'Алексей' WHERE last_name = 'Толстой';





UPDATE 1





=> SELECT * FROM authors WHERE last_name = 'Толстой';





 author_id | last_name | first_name | middle_name 
-----------+-----------+------------+-------------
         5 | Толстой   | Лев        | Николаевич
(1 row)








=> COMMIT;





COMMIT





=> SELECT * FROM authors WHERE last_name = 'Толстой';





 author_id | last_name | first_name | middle_name 
-----------+-----------+------------+-------------
         5 | Толстой   | Алексей    | Николаевич
(1 row)







Изменения можно откатывать, не прерывая транзакцию (хотя необходимость в
этом возникает нечасто).





=> \set AUTOCOMMIT on









=> BEGIN;





BEGIN





=> SAVEPOINT sp;





SAVEPOINT





=> UPDATE authors SET first_name = 'Александр' WHERE last_name = 'Толстой';





UPDATE 1





=> SELECT * FROM authors WHERE last_name = 'Толстой';





 author_id | last_name | first_name | middle_name 
-----------+-----------+------------+-------------
         5 | Толстой   | Александр  | Николаевич
(1 row)








=> ROLLBACK TO sp;





ROLLBACK




Сейчас изменения откачены, но транзакция продолжается:





=> SELECT * FROM authors WHERE last_name = 'Толстой';





 author_id | last_name | first_name | middle_name 
-----------+-----------+------------+-------------
         5 | Толстой   | Алексей    | Николаевич
(1 row)






=> UPDATE authors SET first_name = 'Лев' WHERE last_name = 'Толстой';





UPDATE 1







=> COMMIT;





COMMIT





=> SELECT * FROM authors WHERE last_name = 'Толстой';





 author_id | last_name | first_name | middle_name 
-----------+-----------+------------+-------------
         5 | Толстой   | Лев        | Николаевич
(1 row)





Справедливость восторжествовала.






Подготовленные операторы




Оператор подготавливается командой PREPARE:





=> PREPARE s(text) AS
SELECT * FROM authors WHERE last_name = $1;





PREPARE






После этого оператор можно вызывать по имени, передавая фактические параметры:





=> EXECUTE s('Толстой');





 author_id | last_name | first_name | middle_name 
-----------+-----------+------------+-------------
         5 | Толстой   | Лев        | Николаевич
(1 row)








Все подготовленные операторы можно увидеть в представлении:





=> \x





Expanded display is on.





=> SELECT * FROM pg_prepared_statements;





-[ RECORD 1 ]---+--------------------------------------------
name            | s
statement       | PREPARE s(text) AS                         +
                | SELECT * FROM authors WHERE last_name = $1;
prepare_time    | 2017-09-01 15:51:16.099109+03
parameter_types | {text}
from_sql        | t






=> \x





Expanded display is off.






Помимо сокращения накладных расходов на повторный разбор запроса,
подготовленные операторы гарантируют невозможность внедрения
SQL-кода. Представим себе, что в приложении есть следующий код:




  $query = "SELECT first_name FROM authors WHERE last_name = '$name'";




Если значение $name получено из ненадежного источника (например, введено
пользователем на веб-форме), злоумышленник может получить несанкционированный
доступ к другим данным.




К чему приведет такое значение?




  X' UNION ALL SELECT usename FROM pg_user WHERE '' = '







=> SELECT first_name
FROM authors
WHERE last_name = 'X' UNION ALL SELECT usename FROM pg_user WHERE '' = '';





 first_name 
------------
 student
 postgres
 employee
(3 rows)





Мы получили доступ к данным другой таблицы, изменив структуру запроса.






При использовании подготовленных операторов это невозможно, так как структура
SQL-запроса фиксируется при синтаксическом разборе. Выражение всегда останется
выражением и не сможет превратиться, скажем, в имя таблицы.





=> EXECUTE s($$X' UNION ALL SELECT usename FROM pg_user WHERE '' = '$$);





 author_id | last_name | first_name | middle_name 
-----------+-----------+------------+-------------
(0 rows)







Альтернативный способ исключить внедрение SQL-кода - пользоваться
экранирующими функциями quote_ident, quote_literal, quote_nullable или format
(см. раздаточный материал по типам данных и функциям).






Курсоры




Обычная команда SELECT получает сразу все строки:





=> SELECT * FROM authors;





 author_id | last_name  | first_name | middle_name 
-----------+------------+------------+-------------
         1 | Пушкин     | Александр  | Сергеевич
         2 | Тургенев   | Иван       | Сергеевич
         3 | Стругацкий | Борис      | Натанович
         4 | Стругацкий | Аркадий    | Натанович
         6 | Свифт      | Джонатан   | 
         5 | Толстой    | Лев        | Николаевич
(6 rows)







Курсор позволяет получать данные построчно.





=> BEGIN;





BEGIN





=> DECLARE c CURSOR FOR SELECT * FROM authors;





DECLARE CURSOR





=> FETCH c;





 author_id | last_name | first_name | middle_name 
-----------+-----------+------------+-------------
         1 | Пушкин    | Александр  | Сергеевич
(1 row)






=> FETCH c;





 author_id | last_name | first_name | middle_name 
-----------+-----------+------------+-------------
         2 | Тургенев  | Иван       | Сергеевич
(1 row)







Размер выборки можно указывать:





=> FETCH 2 c;





 author_id | last_name  | first_name | middle_name 
-----------+------------+------------+-------------
         3 | Стругацкий | Борис      | Натанович
         4 | Стругацкий | Аркадий    | Натанович
(2 rows)





Размер выборки играет большое значение, когда строк очень много: обрабатывать
большой объем данных построчно очень неэффективно. Мы проверим этот факт
при выполнении практического задания.






Если в процессе чтения мы дойдем до конца таблицы, FETCH просто перестанет
возвращать строки. В обычных языках программирования всегда есть возможность
проверить это условие.





=> FETCH 2 c;





 author_id | last_name | first_name | middle_name 
-----------+-----------+------------+-------------
         6 | Свифт     | Джонатан   | 
         5 | Толстой   | Лев        | Николаевич
(2 rows)






=> FETCH 2 c;





 author_id | last_name | first_name | middle_name 
-----------+-----------+------------+-------------
(0 rows)






=> CLOSE c;





CLOSE CURSOR





=> COMMIT;





COMMIT









Конец демонстрации.