{"id":72265,"date":"2020-03-03T08:42:17","date_gmt":"2020-03-03T05:42:17","guid":{"rendered":"https:\/\/prohoster.info\/blog\/postgresql-antipatterns-menyaem-dannye-v-obhod-triggera"},"modified":"2020-03-03T16:13:51","modified_gmt":"2020-03-03T13:13:51","slug":"postgresql-antipatterns-menyaem-dannye-v-obhod-triggera","status":"publish","type":"post","link":"https:\/\/prohoster.info\/fr\/blog\/administrirovanie\/postgresql-antipatterns-menyaem-dannye-v-obhod-triggera","title":{"rendered":"PostgreSQL Antipatterns : modification des donn\u00e9es en contournant le trigger","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>T\u00f4t ou tard, beaucoup se retrouvent confront\u00e9s \u00e0 la n\u00e9cessit\u00e9 de corriger massivement des enregistrements dans une table. J'ai d\u00e9j\u00e0 <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/post\/481610\/\">expliqu\u00e9 comment le faire au mieux<\/a><\/noindex>, et comment ne pas le faire. Aujourd'hui, je vais parler du deuxi\u00e8me aspect de la mise \u00e0 jour massive \u2014 <b>le d\u00e9clenchement des triggers<\/b>.<\/p>\n<p>Par exemple, sur la table o\u00f9 vous devez apporter des modifications, il y a un trigger malveillant <code>ON UPDATE<\/code>, qui transf\u00e8re tous les changements vers certains agr\u00e9gats. Et vous devez tout mettre \u00e0 jour (initialiser un nouveau champ, par exemple) de mani\u00e8re \u00e0 ne pas toucher \u00e0 ces agr\u00e9gats.<\/p>\n<h2>D\u00e9sactivons simplement les triggers !<\/h2>\n<p><\/p>\n<pre><code class=\"sql\">BEGIN;\n  ALTER TABLE ... DISABLE TRIGGER ...;\n  UPDATE ...; -- ici pendant longtemps\n  ALTER TABLE ... ENABLE TRIGGER ...;\nCOMMIT;<\/code><\/pre>\n<p>\nEn fait, c'est tout \u2014 <b>tout est d\u00e9j\u00e0 suspendu<\/b>.<\/p>\n<p>Parce que <code>ALTER TABLE<\/code> impose <b>une<\/b>verrouillage AccessExclusive, sous lequel personne ne peut lire quoi que ce soit de la table, m\u00eame la plus simple <code>SELECT<\/code>, rien de la table ne pourra \u00eatre lu. C'est-\u00e0-dire que tant que cette transaction n'est pas termin\u00e9e, tous ceux qui souhaitent m\u00eame \u00ab juste lire \u00bb devront attendre. Et nous nous souvenons que <code>UPDATE<\/code> nous avons une tr\u00e8s looooong\u2026<br \/>\n<noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<h4>Alors d\u00e9sactivons rapidement, puis r\u00e9activons rapidement !<\/h4>\n<p><\/p>\n<pre><code class=\"sql\">BEGIN;\n  ALTER TABLE ... DISABLE TRIGGER ...;\nCOMMIT;\n\nUPDATE ...;\n\nBEGIN;\n  ALTER TABLE ... ENABLE TRIGGER ...;\nCOMMIT;<\/code><\/pre>\n<p>\nIci, la situation est d\u00e9j\u00e0 meilleure, le temps d'attente est consid\u00e9rablement r\u00e9duit. Mais deux probl\u00e8mes viennent g\u00e2cher toute la beaut\u00e9 :<\/p>\n<ul>\n<li><code>ALTER TABLE<\/code> il attend toutes les autres op\u00e9rations sur la table, y compris les longues <code>SELECT<\/code><\/li>\n<li>Tant que le trigger est d\u00e9sactiv\u00e9, <b>tout changement<\/b> dans la table, m\u00eame pas le n\u00f4tre, passera \u00ab \u00e0 c\u00f4t\u00e9 \u00bb. Et il ne parviendra pas aux agr\u00e9gats, alors qu'il le devrait. Quel d\u00e9sastre !<\/li>\n<\/ul>\n<p><\/p>\n<h2>Gestion des variables de session<\/h2>\n<p>\nAinsi, dans le pr\u00e9c\u00e9dent sc\u00e9nario, nous avons rencontr\u00e9 un point fondamental \u2014 il faut apprendre au trigger \u00e0 distinguer les \u00ab changements \u00bb dans la table de ceux qui ne le sont pas. Les \u00ab n\u00f4tres \u00bb doivent passer tels quels, et pour les \u00ab non-n\u00f4tres \u00bb, il doit se d\u00e9clencher. Pour cela, nous pouvons utiliser <noindex><a rel=\"nofollow\" href=\"https:\/\/postgrespro.ru\/docs\/postgresql\/12\/runtime-config-client\">des variables de session<\/a><\/noindex>.<\/p>\n<h4>session_replication_role<\/h4>\n<p>\nNous lisons <noindex><a rel=\"nofollow\" href=\"https:\/\/postgrespro.ru\/docs\/postgresql\/12\/sql-altertable\">manuel<\/a><\/noindex>:<\/p>\n<blockquote><p>Le m\u00e9canisme de d\u00e9clenchement des triggers est \u00e9galement influenc\u00e9 par la variable de configuration <noindex><a rel=\"nofollow\" href=\"https:\/\/postgrespro.ru\/docs\/postgresql\/12\/runtime-config-client#GUC-SESSION-REPLICATION-ROLE\">session_replication_role<\/a><\/noindex>. Les triggers activ\u00e9s sans indications suppl\u00e9mentaires (par d\u00e9faut) se d\u00e9clencheront lorsque le r\u00f4le de r\u00e9plication est \u00ab origin \u00bb (par d\u00e9faut) ou \u00ab local \u00bb. Les triggers activ\u00e9s avec <code>ENABLE REPLICA<\/code>, se d\u00e9clencheront uniquement si <b>le mode de session actuel<\/b> est \u00ab replica \u00bb, et les triggers activ\u00e9s avec <code>ENABLE ALWAYS<\/code>, se d\u00e9clencheront ind\u00e9pendamment du mode de r\u00e9plication actuel.<\/p><\/blockquote>\n<p>Je souligne particuli\u00e8rement que ce r\u00e9glage ne concerne pas tous-en-m\u00eame-temps, comme <code>ALTER TABLE<\/code>, mais seulement \u00e0 notre connexion sp\u00e9ciale distincte. En r\u00e9sum\u00e9, pour \u00e9viter que des d\u00e9clencheurs applicatifs ne s'activent :<\/p>\n<pre><code class=\"sql\">SET session_replication_role = replica; -- d\u00e9sactiver les d\u00e9clencheurs\nUPDATE ...;\nSET session_replication_role = DEFAULT; -- revenir \u00e0 l'\u00e9tat initial<\/code><\/pre>\n<p><\/p>\n<h4>Condition \u00e0 l'int\u00e9rieur du d\u00e9clencheur<\/h4>\n<p>\nMais l'option ci-dessus fonctionne pour tous les d\u00e9clencheurs \u00e0 la fois (ou il faut \"modifier\" \u00e0 l'avance les d\u00e9clencheurs que l'on ne souhaite pas d\u00e9sactiver). Et si nous devons <b>\"d\u00e9sactiver\" un d\u00e9clencheur sp\u00e9cifique<\/b>?<\/p>\n<p>Cela nous aidera \u00e0 <noindex><a rel=\"nofollow\" href=\"https:\/\/postgrespro.ru\/docs\/postgresql\/12\/runtime-config-custom\">\"variable\" de session personnalis\u00e9e<\/a><\/noindex>:<\/p>\n<blockquote><p>Les noms des param\u00e8tres d'extension sont \u00e9crits de la mani\u00e8re suivante : nom de l'extension, point, puis le nom propre du param\u00e8tre, semblable aux noms complets des objets en SQL. Par exemple : plpgsql.variable_conflict.<br \/>\nComme les param\u00e8tres non syst\u00e8me peuvent \u00eatre d\u00e9finis dans des processus ne chargeant pas le module d'extension correspondant, PostgreSQL accepte <b>des valeurs pour tous les noms \u00e0 deux composants<\/b>.<\/p><\/blockquote>\n<p>D'abord, nous modifions le d\u00e9clencheur, \u00e0 peu pr\u00e8s comme \u00e7a :<\/p>\n<pre><code class=\"sql\">BEGIN\n    -- le processus de conversion peut faire tout\n    IF current_setting('mycfg.my_table_convert_process') = 'TRUE' THEN\n        IF TG_OP IN ('INSERT', 'UPDATE') THEN\n            RETURN NEW;\n        ELSE\n            RETURN OLD;\n        END IF;\n    END IF;\n...<\/code><\/pre>\n<p>\n\u00c0 propos, cela peut \u00eatre fait \"en direct\", sans blocages, via <code>CREATE OR REPLACE<\/code> pour la fonction de d\u00e9clencheur. Et ensuite, dans la connexion sp\u00e9ciale, nous d\u00e9finissons notre variable :<\/p>\n<pre><code class=\"sql\">\nSET mycfg.my_table_convert_process = 'TRUE';\nUPDATE ...;\nSET mycfg.my_table_convert_process = ''; -- revenir \u00e0 l'\u00e9tat initial\n<\/code><\/pre>\n<p>\nConnaissez-vous d'autres m\u00e9thodes ? Partagez-les dans les commentaires.<br \/>\n<br \/>Source : <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/tensor\/blog\/489900\/\">habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u0420\u0430\u043d\u043e \u0438\u043b\u0438 \u043f\u043e\u0437\u0434\u043d\u043e \u043c\u043d\u043e\u0433\u0438\u0435 \u0441\u0442\u0430\u043b\u043a\u0438\u0432\u0430\u044e\u0442\u0441\u044f \u0441 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e\u0441\u0442\u044c\u044e \u0447\u0442\u043e-\u0442\u043e \u043c\u0430\u0441\u0441\u043e\u0432\u043e \u0438\u0441\u043f\u0440\u0430\u0432\u0438\u0442\u044c \u0432 \u0437\u0430\u043f\u0438\u0441\u044f\u0445 \u0442\u0430\u0431\u043b\u0438\u0446\u044b. \u042f \u0443\u0436\u0435 \u0440\u0430\u0441\u0441\u043a\u0430\u0437\u044b\u0432\u0430\u043b, \u043a\u0430\u043a \u044d\u0442\u043e \u0434\u0435\u043b\u0430\u0442\u044c \u043b\u0443\u0447\u0448\u0435, \u0430 \u043a\u0430\u043a \u2014 \u043b\u0443\u0447\u0448\u0435 \u043d\u0435 \u0434\u0435\u043b\u0430\u0442\u044c. \u0421\u0435\u0433\u043e\u0434\u043d\u044f \u0440\u0430\u0441\u0441\u043a\u0430\u0436\u0443 \u043e \u0432\u0442\u043e\u0440\u043e\u043c \u0430\u0441\u043f\u0435\u043a\u0442\u0435 \u043c\u0430\u0441\u0441\u043e\u0432\u043e\u0433\u043e \u043e\u0431\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u044f \u2014 \u043e \u0441\u0440\u0430\u0431\u043e\u0442\u043a\u0435 \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u043e\u0432. \u041d\u0430\u043f\u0440\u0438\u043c\u0435\u0440, \u043d\u0430 \u0442\u0430\u0431\u043b\u0438\u0446\u0435, \u0432 \u043a\u043e\u0442\u043e\u0440\u043e\u0439 \u0432\u0430\u043c \u043d\u0430\u0434\u043e \u0447\u0442\u043e-\u0442\u043e \u043f\u043e\u043f\u0440\u0430\u0432\u0438\u0442\u044c, \u0432\u0438\u0441\u0438\u0442 \u0437\u043b\u043e\u0431\u043d\u044b\u0439 \u0442\u0440\u0438\u0433\u0433\u0435\u0440 ON UPDATE, \u043f\u0435\u0440\u0435\u043d\u043e\u0441\u044f\u0449\u0438\u0439 \u0432\u0441\u0435 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u044f \u0432 [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-72265","post","type-post","status-publish","format-standard","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.2 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u0420\u0430\u043d\u043e \u0438\u043b\u0438 \u043f\u043e\u0437\u0434\u043d\u043e \u043c\u043d\u043e\u0433\u0438\u0435 \u0441\u0442\u0430\u043b\u043a\u0438\u0432\u0430\u044e\u0442\u0441\u044f \u0441 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e\u0441\u0442\u044c\u044e \u0447\u0442\u043e-\u0442\u043e \u043c\u0430\u0441\u0441\u043e\u0432\u043e \u0438\u0441\u043f\u0440\u0430\u0432\u0438\u0442\u044c \u0432 \u0437\u0430\u043f\u0438\u0441\u044f\u0445 \u0442\u0430\u0431\u043b\u0438\u0446\u044b.\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/fr\/blog\/administrirovanie\/postgresql-antipatterns-menyaem-dannye-v-obhod-triggera\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.2\" \/>\n\t\t<meta property=\"og:locale\" content=\"fr_FR\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47PostgreSQL Antipatterns: \u043c\u0435\u043d\u044f\u0435\u043c \u0434\u0430\u043d\u043d\u044b\u0435 \u0432 \u043e\u0431\u0445\u043e\u0434 \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u0430 | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u0420\u0430\u043d\u043e \u0438\u043b\u0438 \u043f\u043e\u0437\u0434\u043d\u043e \u043c\u043d\u043e\u0433\u0438\u0435 \u0441\u0442\u0430\u043b\u043a\u0438\u0432\u0430\u044e\u0442\u0441\u044f \u0441 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e\u0441\u0442\u044c\u044e \u0447\u0442\u043e-\u0442\u043e \u043c\u0430\u0441\u0441\u043e\u0432\u043e \u0438\u0441\u043f\u0440\u0430\u0432\u0438\u0442\u044c \u0432 \u0437\u0430\u043f\u0438\u0441\u044f\u0445 \u0442\u0430\u0431\u043b\u0438\u0446\u044b.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/fr\/blog\/administrirovanie\/postgresql-antipatterns-menyaem-dannye-v-obhod-triggera\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2020-03-03T05:42:17+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2020-03-03T13:13:51+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47PostgreSQL Antipatterns : modification des donn\u00e9es en contournant le d\u00e9clencheur | ProHoster","description":"T\u00f4t ou tard, beaucoup se retrouvent confront\u00e9s \u00e0 la n\u00e9cessit\u00e9 de corriger quelque chose en masse dans les enregistrements d'une table.","canonical_url":"https:\/\/prohoster.info\/fr\/blog\/administrirovanie\/postgresql-antipatterns-menyaem-dannye-v-obhod-triggera","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"fr_FR","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47PostgreSQL Antipatterns: \u043c\u0435\u043d\u044f\u0435\u043c \u0434\u0430\u043d\u043d\u044b\u0435 \u0432 \u043e\u0431\u0445\u043e\u0434 \u0442\u0440\u0438\u0433\u0433\u0435\u0440\u0430 | ProHoster","og:description":"\u0420\u0430\u043d\u043e \u0438\u043b\u0438 \u043f\u043e\u0437\u0434\u043d\u043e \u043c\u043d\u043e\u0433\u0438\u0435 \u0441\u0442\u0430\u043b\u043a\u0438\u0432\u0430\u044e\u0442\u0441\u044f \u0441 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e\u0441\u0442\u044c\u044e \u0447\u0442\u043e-\u0442\u043e \u043c\u0430\u0441\u0441\u043e\u0432\u043e \u0438\u0441\u043f\u0440\u0430\u0432\u0438\u0442\u044c \u0432 \u0437\u0430\u043f\u0438\u0441\u044f\u0445 \u0442\u0430\u0431\u043b\u0438\u0446\u044b.","og:url":"https:\/\/prohoster.info\/fr\/blog\/administrirovanie\/postgresql-antipatterns-menyaem-dannye-v-obhod-triggera","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-03-03T05:42:17+00:00","article:modified_time":"2020-03-03T13:13:51+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"72265","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 18:47:24","updated":"2022-09-27 18:26:38","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/posts\/72265","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/comments?post=72265"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/posts\/72265\/revisions"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/media?parent=72265"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/categories?post=72265"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/tags?post=72265"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}