Триггерные процедуры (функции) SQL

База знаний по разработке в telegram t.me/lets_goto_it
Важный инструмент рефакторинга архитектуры.
Моя статья про триггерные функции - dzen.ru/media/lets_goto_it/tr...
Почитать на сайте postgresqlpro - postgrespro.ru/docs/postgresp...
--
Понравился ролик?
Ставь лайк, а можешь поддержать канал рублем - vk.cc/cu1oD4
---
Репозиторий - github.com/arturgspb/pg-course
Телеграм - t.me/lets_goto_it
Дзен - dzen.ru/lets_goto_it

[@crawner6198]

Очень полезное видео! Автор красавчик, все структурировано, понятно и голос приятный! Спасибо, помогли

[@selub1058]

Круто ❤. Спасибо что делитесь опытом.

[@user-ee8ek3xx2r]

Подписался) как ты просил 😎🖐🏻 надеюсь продолжишь 🙂

[@lets_goto_it]

🤝👌

[@kirill_ignatev]

Спасибо за это познавательное видео, было очень интересно послушать про практики!

[@uliavvvvvvvvvv]

Большое спасибо за ваш курс,за ваш труд!Вас очень интересно слушать,а главное полезно)

[@lets_goto_it]

Спасибо 😊. Готовлю новые выпуски

[@christophlebapa]

Спасибо! Полезный материал понятным языком.

[@user-ff8xv2vn7c]

Очень понятное объяснение, спасибо!

[@eb6006]

Интересная вещь! Не знал про такое! Выглядит как класная идея!

[@user-wz9vl9li7q]

Спасибо за урок!

[@user-jd4iw8fs1h]

Спасибо, очень помогло в учебе!

[@lets_goto_it]

удачи в учёбе!

[@Mars-sm]

👍👍👍

[@SlonMorze]

В MSSQL можно ограничить выполнение триггера в зависимости от контекста (context_info). Т.е. предположим два приложения выполняют одно и то же - insert. Но для первого приложения нужно выполнять триггер, а для второго - нет. В таких случаях задаётся некое значение переменной сессии (set context_info) в приложении, а в триггере проверяется select context_info(). Как такой фокус провернуть в postgres?

[@lets_goto_it]

Можно через переменные окружения как тут например postgres.cz/index.php/PostgreSQL_SQL_Tricks_II#Any_other_session_variables НО я в продакшене так никогда не делал - не доверяю я им =) Не дай бол балансировщик типа pg bouncer перенастроят и он будет как-то иначе с сессиями работать - там много нюансов начинается в параллельной работе с БД. Для варианта как у вас я делал колонку - типа initiator, чтобы хранить что последний делал или обновлял запись, но это, конечно, накладные расходы. Далее по ее значению можно внутри триггера или запускать код или выходить из триггера. Ну или вообще колонку сделать, которая бы впрямую показывала надо запускать конкретный триггер или нет - is_run_my_trigger и все. Главное в этом варианте всегда менять ее значение. Вообще кейс довольно необычный - не поделитесь зачем вам надо запускать обработку иногда? Я как раз в видео говорю, что перестал сложную логику в БД выносить как раз из-за неявности и иных неудобных моментов.

[@SlonMorze]

@@lets_goto_it Использовался самописный сервис репликации, который перегонял данные с точки продаж на основной сервер в моменты доступности сети. Так же, этот сервис принимал данные от такого же сервиса на основном сервере и обновлял данные о меню, ценах, клиентах и т.п. А теперь это все переводится на pg. Пока рассматриваю разные способы. Аналогичный сервис SymmetricDS использует переменные окружения, но мне этот вариант не нравится тоже

[@lets_goto_it]

Хорошая задача - вы, кстати, второй за последние пару месяцев, кто поднимает вопрос про кастомную репликацию БД. В кастомности, если что, я ничего плохого не вижу. Можете поподробнее рассказать - по сути это master -> slave репликация или что-то типа master-master, но для выборочных таблиц? Не уверен, что до конца понял задачу

[@SlonMorze]

@@lets_goto_it Это master-master. Триггер приводит к созданию записи в спец.таблице - буфере. Сервис репликации просматривает эту таблицу и если есть связь формирует и отправляет веб-запрос (rest-api) на другие настроенные сервера. Там сервис репликации принимает запрос и добавляет/обновляет/удаляет запись в базу приемнике. Если не ограничить выполнение триггера, то эта репликация будет бесконечной. Sql инструкции, выполняемые сервисом репликации не должны приводить к выполнению триггера

[@lets_goto_it]

Понял, спасибо. Мне кажется для pg много готового есть для вашего или почти вашего сценария. И встроенные механизмы репликации можно глянуть и, например, bucardo (но он чето не обновляется в последнее время). Если хочется оставить вашу реализацию, то я бы смотрел то, как решались те же самые проблемы в готовом софте - можно идей почерпнуть как минимум

[@user-bs8ng7qt8b]

скопировала код. Функция создалась успешно, а создание триггера зависло. В чем может быть проблема?

[@lets_goto_it]

Ирина, такое часто бывает в случаях, когда БД нагружена или открыты транзакции или идет DDL запрос. Рекомендую в момент запроса глянуть pg_stat_activity в соседдем окне. Там можно понять реально ли выполяется ваш запрос, ждет ли он чего-то и что еще выполняется. Обычно в таких ситуациях видно один и более медленных запросов к БД, которые все тормозят. Их надо или подождать или снять задачу через pg_cancel_backend, но на проде с этим надо очень аккуратно. Были случаи, когда pg_cancel_backend реально не работал и приходилось делать pg_terminate_backend (это как kill -9 в unix) и это приводило к recovery mode в БД и, соответственно, временной недоступности. Я обычно в каждой БД имеют такую view: gist.github.com/arturgspb/1749d2b1edb35e275da9d89ecc4fe7b0 Она подходит к PG 13, но скорее всего работает и на более поздних или ранних до 9.6 версии PG. Если что - погуглите аналоги для работы с pg_stat_activity

[@zosyanax]

Я как раз обычно иду лбом на переписывание всего кода сразу, подход рефакторинга с триггерами взял на вооружение)

[@lets_goto_it]

Главное не забыть их описать и постараться выпилить со временем) Чтобы сильно много они не разрасталось

[@user-xw2kv6tt7i]

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

[@lets_goto_it]

Ну да, MVCC и все дела. Я тут скорее к тому, что не стоит рассчитывать на конкретную последовательность, так как это будет зависеть от реализации в конкретной версии pg. Безопаснее всегда добавлять сортировку по нужному полю

[@user-ie3zj8rw5r]

Не понял по поводу 23:00 За остальное благодарю.

[@lets_goto_it]

А что именно надо пояснить?

[@user-ie3zj8rw5r]

@@lets_goto_it про разбор старого кода. как вы избавляетесь в массе от всего непонятного в базе данных я пошел долгим путем - описание всей базы данных, чтобы ничего не сломалось. пишу код SQL, который анализирует базу данных (ссылку давал ранее), но там нет тригеров. думаю как быть с ними. работа была проведена: 1) написан код на SQL по выгрузке данных описания 2) написан скрипт на питоне, который дополняет информацию в итог скрипта SQL (например,имеется приложения, которые обновляют справочники в базе из других систем) (факт) 3)Загружаю все в конфу где имеется описание каждой таблицы и поля. вот в поле есть поля автообновления напротив каждого поля и его описания имеется отметка autoupdate (факт) 4) если поле заполняется тригером, то напишу autofill. (планы) в планах запилить систему, которая будет дополнять комментарий к полю и таблице, которые будут рассказывать, что да как работает

[@lets_goto_it]

@@user-ie3zj8rw5r да, много всего можно получать селектом из information_schema. Я обычно не пользуюь генераторами схем данных. Обычно хватало общего описания бизнес процесса + схема основных таблиц, причем не каждого поля, а в целом зачем таблица. Поля в таблицах как правило или говорящие типа id, name, created_at, updated_by_user_id, description или что-то особенное, для особенного обычно пишутся комменты прямо к полю в БД. Триггеров я сейчас стараюсь делать очень мало - за ними действительно сложно следить и я их использую скорее для особых случаев, когда без них никак или они сильно все ускоряют или дают не переписывать тонну кода на ЯП. Далее у нас на проекте автогенерируемое api на основе БД, там тоже полно описания к таблицам и полям, включая ссылки на задачи + это все снабжается коммитами в репозиторий с указанием ключа задачи в таск трекере. Поэтому по сути всегда можно понять какая миграция и какая таблица/поле для какой задачи добавлялись и как менялись.

[@TechBusinessDev]

Не понимаю зачем это. То же самое ведь можно в коде сделать за то же время в принципе. Плюс не размазывается логика, как и говорилось в начале.

[@lets_goto_it]

Тут всё будет зависеть от количества кода и ваших знаний о нем. Если проект наследуется от другой команды, то вы не всё можете знать, к примеру. В некоторых проектах надо добавлять поддержку транзакций и это не всегда просто. Как я и говорил - ситуации бывают разные на разных проектах, не всегда хочется или вообще оправдано трогать код, иногда хочется просто рядом строить новую инфраструктуру, постепенно заменяя старую. Суть видео в том, чтобы отчасти показать, что не всегда все можно взять и поделить на черное и белое. Как правило вся работа сводится в искусству компромиссов.

[@kura3327]

это goland что ли?)

[@lets_goto_it]

Pycharm или WebStorm, точно не помню (

[@user-ps4bt3vh9q]

не прав насчет сравнениия тeхт и варчар есть разница.

[@lets_goto_it]

Вы зоркий глаз ;) Спасибо за комментарий. Да, она есть, но как по мне несущественная. Если строки у вас не будут конскими, то они не попадут в TOAST. В доке ( postgrespro.ru/docs/postgrespro/current/datatype-character ) написано "По быстродействию эти три типа практически не отличаются друг от друга, не считая большего размера хранения для типа с дополняющими пробелами и нескольких машинных операций для проверки длины при сохранении строк в столбце с ограниченной длиной. Хотя в некоторых СУБД тип character(n) работает быстрее других, в Postgres Pro это не так; на деле character(n) обычно оказывается медленнее остальных типов из-за большего размера данных и более медленной сортировки. В большинстве случаев вместо него лучше применять text или character varying."