При работе с функцией Postgres где нужно делать транзакцию и как правильно?
в Postges внутри функции нельзя открывать транзакции и коммитить/откатывать?
Да, функция выполняется строго в транзакции и не может управлять внешней транзакцией.
Процедура управлять транзакцией может. Поддержка процедур ( create procedure и вызов через call , соответственно) была добавлена в postgresql 11.
в транзакцию надо помещать вызов этой самой функции из кода PHP?
На усмотрение автора кода.
Вызов хранимой функции в любом случае будет внутри транзакции, явно выполненной приложением либо неявно открытой только на этот sql запрос с вызовом функции. PostgreSQL строго транзакционная база, у вас нет опции «запустить запрос вне транзакции», запрос будет в транзакции всегда, даже если вы об этом явно не просили.
Когда в PHP открываю и закрываю транзакцию, то по сути драйвер БД «говорит» самой БД эту команду (BEGIN) исполнить, так?
Автономные транзакции в PostgreSQL
Начнем с определения того, что такое автономные транзакции в принципе:
Автономные транзакции позволяют создавать новые подтранзакции (subtransaction), которые могут сохранять или отменять изменения вне зависимости от родительской транзакции. Подробнее тут.
Реализованы они в СУБД Oracle, и являются очень гибким и удобным средством. Самым популярным (но далеко не единственным) использованием автономных транзакций является логирование. Рассмотрим несложный пример. Представьте себе следующую ситуацию — в вашей БД реализована длинная и сложная хранимая процедура, например ежемесячный биллинг. Важные события вы наверняка хотели бы видеть в таблице логов. Вы, конечно, можете просто писать в нее безо всяких ухищрений. Но есть один серьезный недостаток — если в процедуре происходит необработанная ошибка — транзакция откатывается вместе со всеми записями в таблицу логов. Тут на помощь и приходят автономные транзакции. Они выполняются независимо от родительской транзакции, и вы можете увидеть записи в таблице логов еще до того, как закончилось выполнение логгируемой процедуры биллинга.
Перейдем же к теме топика. Несмотря на претенциозный заголовок, в PostgreSQL автономных транзакций не существует. Но есть способ, с помощью которого мы можем получить схожий результат.
dblink
Во-первых, нам понадобится contrib-модуль dblink. Этот модуль позволяет с помощью своих функций обращаться к другому экземпляру PostgreSQL. Подробное описание выходит за рамки топика. От себя скажу, что dblink — один из нескольких полезнейших contrib-модулей, которые я всегда устанавливаю независимо от задач, решаемых БД.
Пример
Покажу описываемый прием на примере из начала топика. Реализуем логирование на «автономных» транзакциях.
Создаем таблицу логов:
CREATE TABLE » public «.»logs» (
«log_id» BIGSERIAL,
«source» TEXT NOT NULL ,
» level » TEXT NOT NULL ,
«message» TEXT NOT NULL ,
«time_added» TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL ,
CONSTRAINT «logs_pkey» PRIMARY KEY («log_id»)
) WITH OIDS;
* This source code was highlighted with Source Code Highlighter .
Создадим функцию для логгирования:
CREATE OR REPLACE FUNCTION » public «.»log»( IN p_source text, IN p_level text, IN p_message text)
RETURNS void AS
$$
DECLARE
INSERT_SQL text := ‘INSERT INTO public.logs (source, level, message) VALUES (‘ ‘[SOURCE]’ ‘,’ ‘[LEVEL]’ ‘,’ ‘[MESSAGE]’ ‘)’ ;
BEGIN
IF p_level != ‘ERROR’ AND p_level != ‘WARNING’
AND p_level != ‘INFO’ AND p_level != ‘DEBUG’ THEN
RAISE EXCEPTION ‘Log level should be one of the following types: ERROR, WARNING, INFO, DEBUG’ ;
END IF ;
INSERT_SQL := replace(INSERT_SQL, ‘[SOURCE]’ , p_source);
INSERT_SQL := replace(INSERT_SQL, ‘[LEVEL]’ , p_level);
INSERT_SQL := replace(INSERT_SQL, ‘[MESSAGE]’ , p_message);
PERFORM dblink_exec( ‘dbname=’ || current_database(), INSERT_SQL);
END ;
$$ LANGUAGE ‘plpgsql’ ;
* This source code was highlighted with Source Code Highlighter .
Теперь в любой момент мы можем вызвать функцию «log» и запись тут же появится в таблице логов независимо от статуса транзакции, в во время которой мы ее вызвали.
PERFORM » public «.»log»( ‘monthly billing’ , ‘INFO’ , ‘Starting monthly billing’ );
* This source code was highlighted with Source Code Highlighter .
Как видите, весь прием состоит в том, что мы записываем в таблицу логов с помощью функции «dblink_exec». Это означает, что создается новые соединение, сессия и транзакция, в контексте которых и происходит запись.
Замечания
- К сожалению, этот финт не может полностью заменить автономных транзакций Oracle. Проблемой является производительность — я бы не советовал использовать этот прием налево и направо, подумайте — где это действительно нужно.
- Подобный метод применим не только к PostgreSQL. Каждая известная мне СУБД располагает функциями, подобными «dblink_exec»
- Как грамотно заметили в комментариях — использование dblink_connect для создания persistent соединения, и использование этого соединения в дальнейшем, ощутимо ускорит работу.
- postgresql
- автономные транзакции
Postgresql как запустить что то вне транзакции
END — зафиксировать текущую транзакцию
Синтаксис
END [ WORK | TRANSACTION ]
Описание
END фиксирует текущую транзакцию. Все изменения, произведённые этой транзакцией, становятся видимыми для других и гарантированно сохранятся в случае сбоя. Эта команда является расширением PostgreSQL и равнозначна команде COMMIT .
Параметры
WORK
TRANSACTION
Необязательные ключевые слова, не оказывают никакого влияния.
Замечания
Для прерывания транзакции используйте ROLLBACK .
При попытке выполнить END вне транзакции ничего не произойдёт, но будет выдано предупреждение.
Примеры
Следующая команда фиксирует текущую транзакцию и сохраняет все изменения:
Совместимость
END является расширением PostgreSQL и выполняет ту же функцию, что и оператор COMMIT , описанный в стандарте SQL.
См. также
| Пред. | Наверх | След. |
| DROP VIEW | Начало | EXECUTE |
Thread: SELECT FOR UPDATE без транзакции
> Привет
> Вы в принципе не можете сделать select вне транзакции. Если вы делаете
> запрос вне транзакции — этот запрос будет обёрнут в отдельную транзакцию
> автоматически.
ну я примерно так и думал, просто решил уточнить 🙂
> Не представляю зачем в вашем примере мог быть нужен s1 вообще — но да,
> так брать блокировку возможно.
Я немного неудачно пример записал.
в моём случае второй запрос еще должен сделать insert в t2 на самом
деле.
то есть полеый алгоритм такой:
1. ставим лок на запись t1
2. записываем новую запись t2
3. аггрегатором вычисляем по t2 значение
4. модифицируем запись в t1 на которую поставили лок
(условно говоря первая таблица — таблица со значениями неких
счетчиков, вторая таблица — таблица с логом операций над счетчиками)
В целом у меня подобная система работает без блокировок, но на одном
процессе (конкурентность отстуствует). Сейчас хочу запустить второй
параллельный процесс. Он будет очень редко пересекаться по ID записей,
однако поскольку пересечения возможны — задумался над локами.
. »`. Dmitry E. Oboukhov
: :’ :
`. `~’ GPG key: 4096R/08EEA756 2014-08-30
`- 71ED ACFC 6801 0DD9 1AD1 9B86 8D1F 969A 08EE A756