{"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\/et\/blog\/administrirovanie\/postgresql-antipatterns-menyaem-dannye-v-obhod-triggera","title":{"rendered":"PostgreSQL Antipatterns: muudame andmeid l\u00e4bi triggeeritud","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>Varsti v\u00f5i hiljem seisavad paljud silmitsi vajadusega teha massilisi muudatusi tabeli sissekannetes. Ma olen juba <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/post\/481610\/\">r\u00e4\u00e4kinud, kuidas seda paremini teha<\/a><\/noindex>, ja kuidas \u2014 parem mitte teha. T\u00e4na r\u00e4\u00e4gin teise aspekti kohta massilisest uuendamisest \u2014 <b>triggertest<\/b>.<\/p>\n<p>N\u00e4iteks, tabelil, kus peate midagi parandama, on kurjakuulutav trigger <code>ON UPDATE<\/code>, mis viib k\u00f5ik muudatused mingitesse agregaatidesse. Ja teil on vaja k\u00f5ik uuendada (uue v\u00e4 field initsialiseerida), nii ettevaatlikult, et need agregaadid ei puutuks.<\/p>\n<h2>L\u00fclitame lihtsalt triggerid v\u00e4lja!<\/h2>\n<p><\/p>\n<pre><code class=\"sql\">BEGIN;\n  ALTER TABLE ... DISABLE TRIGGER ...;\n  UPDATE ...; -- siin kaua-kaua\n  ALTER TABLE ... ENABLE TRIGGER ...;\nCOMMIT;<\/code><\/pre>\n<p>\nP\u00f5him\u00f5tteliselt, siin ongi k\u00f5ik \u2014 <b>k\u00f5ik on juba kinni<\/b>.<\/p>\n<p>Sest <code>ALTER TABLE<\/code> kehtestab <b>AccessExclusive<\/b>-lukustuse, mille all keegi, kes paralleelselt teostab, isegi lihtne <code>SELECT<\/code>, ei suuda tabelist midagi lugeda. See t\u00e4hendab, et seni, kuni see tehing ei l\u00f5pe, ootavad k\u00f5ik soovijad isegi \"lihtsalt lugeda\". Ja me m\u00e4letame, et <code>UPDATE<\/code> meil on p\u00f6\u00f6\u00f6\u00f6\u00f6rd.<br \/>\n<noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<h4>L\u00fclitame siis kiiresti v\u00e4lja, seej\u00e4rel kiiresti sisse!<\/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>\nSiin on olukord juba parem, ooteaeg on oluliselt l\u00fchem. Kuid kaks probleemi rikuvad kogu ilu:<\/p>\n<ul>\n<li><code>ALTER TABLE<\/code> ise ootab k\u00f5iki teisi toiminguid tabelis, sealhulgas pikki <code>SELECT<\/code><\/li>\n<li>Kuni trigger on v\u00e4lja l\u00fclitatud, <b>\"halveneb\" iga muudatus<\/b> tabelis, isegi mitte meie oma. Ja agregaatidesse see kuidagi ei satu, kuigi peaks. H\u00e4da!<\/li>\n<\/ul>\n<p><\/p>\n<h2>Sessioonimuutujate haldamine<\/h2>\n<p>\nNii et eelmise variandi puhul sattusime p\u00f5him\u00f5ttelisele punktile \u2014 peame kuidagi \u00f5petama triggerit eristama \"meie\" muudatused tabelis \"mitte meie\" omadest. \"Meie\" peate k\u00fcljele j\u00e4tma, kuid \"mitte meie\" puhul peab see toimima. Selleks v\u00f5ime kasutada <noindex><a rel=\"nofollow\" href=\"https:\/\/postgrespro.ru\/docs\/postgresql\/12\/runtime-config-client\">sessioonimuutujat<\/a><\/noindex>.<\/p>\n<h4>session_replication_role<\/h4>\n<p>\nLugesime <noindex><a rel=\"nofollow\" href=\"https:\/\/postgrespro.ru\/docs\/postgresql\/12\/sql-altertable\">administraatori<\/a><\/noindex>:<\/p>\n<blockquote><p>Triggerite k\u00e4ivitamise mehanismile m\u00f5jub ka konfiguratsioonimuutuja <noindex><a rel=\"nofollow\" href=\"https:\/\/postgrespro.ru\/docs\/postgresql\/12\/runtime-config-client#GUC-SESSION-REPLICATION-ROLE\">session_replication_role<\/a><\/noindex>. Ilma t\u00e4iendavate juhisteta (vaikimisi) aktiveeritud triggerid toimivad, kui replikatsioonire\u017eiim on \u2014 \"origin\" (vaikimisi) v\u00f5i \"local\". Triggerid, mis aktiveeritakse m\u00e4\u00e4ramisega <code>ENABLE REPLICA<\/code>, toimivad ainult siis, kui <b>aktuaalne seansire\u017eiim<\/b> \u2014 \"replica\", ja triggerid, mis aktiveeritakse m\u00e4\u00e4ramisega <code>ENABLE ALWAYS<\/code>, toimivad olenemata aktuaalsest replikatsioonire\u017eiimist.<\/p><\/blockquote>\n<p>Eriti r\u00f5hutan, et seade ei kehti k\u00f5igi jaoks korraga, nagu <code>ALTER TABLE<\/code>, vaid meie eraldi spetsiaalset \u00fchendust. Kokkuv\u00f5tteks, et mingeid rakenduse triggereid ei aktiveeruks:<\/p>\n<pre><code class=\"sql\">SET session_replication_role = replica; -- v\u00e4lja l\u00fclitatud triggerid\nUPDATE ...;\nSET session_replication_role = DEFAULT; -- tagastatud algsesse olekusse<\/code><\/pre>\n<p><\/p>\n<h4>Tingimus triggeri sees<\/h4>\n<p>\nKuid \u00fclaltoodud variant t\u00f6\u00f6tab k\u00f5igi triggerite puhul korraga (v\u00f5i tuleb eelnevalt \"muuta\" need triggerid, mida ei soovita v\u00e4lja l\u00fclitada). Ja kui me peame <b>\"v\u00e4lja l\u00fclitama\" \u00fche konkreetse triggeri<\/b>?<\/p>\n<p>Sellega aitab meid <noindex><a rel=\"nofollow\" href=\"https:\/\/postgrespro.ru\/docs\/postgresql\/12\/runtime-config-custom\">\"kasutaja\" sessioonimuutuja<\/a><\/noindex>:<\/p>\n<blockquote><p>Laiendite parameetrite nimed kirjutatakse j\u00e4rgmiselt: laiendi nimi, punkt ja seej\u00e4rel parameetri nimi, sarnaselt objektide t\u00e4ielikele nimedele SQL-is. N\u00e4iteks: plpgsql.variable_conflict.<br \/>\nKuna mitte-s\u00fcsteemsed parameetrid v\u00f5ivad olla seatud protsessides, mis ei lae vastavat laiendimoodulit, aktsepteerib PostgreSQL <b>v\u00e4\u00e4rtused mis tahes kahe komponendiga nimede jaoks<\/b>.<\/p><\/blockquote>\n<p>Esiteks viime triggeri t\u00f6\u00f6tluse l\u00e4bi, umbes nii:<\/p>\n<pre><code class=\"sql\">BEGIN\n    -- konverteerimisprotsessis v\u00f5ib teha k\u00f5ike\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>\nMuide, seda saab teha \"realses ajas\", ilma lukustusteta, l\u00e4bi <code>CREATE OR REPLACE<\/code> -iga triggeri funktsiooni. Ja siis seadistame eraldi \u00fchenduses \"oma\" muutuja:<\/p>\n<pre><code class=\"sql\">\nSET mycfg.my_table_convert_process = 'TRUE';\nUPDATE ...;\nSET mycfg.my_table_convert_process = ''; -- tagastatud algsesse olekusse\n<\/code><\/pre>\n<p>\nKas tead muid viise? Jaga oma m\u00f5tteid kommentaarides.<br \/>\n<br \/>Allikas: <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.1.1 - 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\/et\/blog\/administrirovanie\/postgresql-antipatterns-menyaem-dannye-v-obhod-triggera\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.1.1\" \/>\n\t\t<meta property=\"og:locale\" content=\"et_EE\" \/>\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\/et\/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: muudame andmeid triggeritest m\u00f6\u00f6da | ProHoster","description":"Varem v\u00f5i hiljem seisavad paljud silmitsi vajadusega massiliselt parandada tabeli kirjeid.","canonical_url":"https:\/\/prohoster.info\/et\/blog\/administrirovanie\/postgresql-antipatterns-menyaem-dannye-v-obhod-triggera","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"et_EE","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\/et\/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\/et\/wp-json\/wp\/v2\/posts\/72265","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/comments?post=72265"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts\/72265\/revisions"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media?parent=72265"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/categories?post=72265"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/tags?post=72265"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}