{"id":1745,"date":"2023-08-29T14:45:02","date_gmt":"2023-08-29T12:45:02","guid":{"rendered":"https:\/\/alessandromasciadri.com\/?p=1745"},"modified":"2023-08-29T14:59:15","modified_gmt":"2023-08-29T12:59:15","slug":"che-cose-log_cnt-in-una-postgres-sequence","status":"publish","type":"post","link":"https:\/\/alessandromasciadri.com\/che-cose-log_cnt-in-una-postgres-sequence\/","title":{"rendered":"Che cos&#8217;\u00e8 log_cnt in una Postgres sequence"},"content":{"rendered":"\t\t<div data-akihiro-type=\"ama-post\" data-akihiro-id=\"1745\" class=\"akihiro akihiro-1745\" data-akihiro-post-type=\"post\">\n\t\t\t\t<div class=\"akihiro-element akihiro-element-2f9b03d e-flex e-con-boxed e-con e-parent\" data-id=\"2f9b03d\" data-element_type=\"container\" data-e-type=\"container\">\n\t\t\t\t\t<div class=\"e-con-inner\">\n\t\t\t\t<div class=\"akihiro-element akihiro-element-4c7f286 akihiro-widget akihiro-widget-text-editor\" data-id=\"4c7f286\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Uno scenario interessante su cui mi \u00e8 capitato di lavorare riguarda l&#8217;utilizzo di una sequence in un database Postgres. In un applicazione containerizzata con Docker era prevista un&#8217;operazione di reboot del sistema operativo, la quale eseguiva il riavvio della macchina host su cui erano in esecuzione i container. La cosa bizzarra \u00e8 che a seguito del riavvio, la sequence veniva incrementata di un valore pari a quello indicato nel campo log_cnt della tabella della sequence.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-21743f4 akihiro-widget akihiro-widget-heading\" data-id=\"21743f4\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<h3 class=\"akihiro-heading-title akihiro-size-default\">Esempio pratico<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-3505769 akihiro-widget akihiro-widget-text-editor\" data-id=\"3505769\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Questo esempio \u00e8 preso dal caso reale che ho affrontato. Come prima cosa, quando interroghiamo la sequence per verificarne lo stato corrente utilizzando questa query<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-8b6860c akihiro-widget akihiro-widget-code-highlight\" data-id=\"8b6860c\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-sql \">\n\t\t\t\t<code readonly=\"true\" class=\"language-sql\">\n\t\t\t\t\t<xmp>SELECT * FROM sequence_name;<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-ac00f6e akihiro-widget akihiro-widget-text-editor\" data-id=\"ac00f6e\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Otteniamo un risultato simile al seguente<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-384e750 akihiro-widget akihiro-widget-code-highlight\" data-id=\"384e750\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language- \">\n\t\t\t\t<code readonly=\"true\" class=\"language-\">\n\t\t\t\t\t<xmp>last_value | log_cnt | is_called \n------------+---------+-----------\n          1 |      32 | t\n(1 row)<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-37e7a8f akihiro-widget akihiro-widget-text-editor\" data-id=\"37e7a8f\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\tNotiamo che l&#8217;ultimo valore della sequence \u00e8 1 e che il valore <code>log_cnt<\/code> \u00e8 32.\n\nLa cosa anomala si verificava al riavvio della macchina host mediante questo comando\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-e0accc9 akihiro-widget akihiro-widget-code-highlight\" data-id=\"e0accc9\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-bash \">\n\t\t\t\t<code readonly=\"true\" class=\"language-bash\">\n\t\t\t\t\t<xmp>dbus-send --system --print-reply --dest=org.freedesktop.login1 \/org\/freedesktop\/login1 \"org.freedesktop.login1.Manager.Reboot\" boolean:true'<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-6954c88 akihiro-widget akihiro-widget-text-editor\" data-id=\"6954c88\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\tUna volta ripartita la macchina host, il valore <code>last_value<\/code> era 33 ossia <code>log_cnt + last_value<\/code>.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-e1afcfa akihiro-widget akihiro-widget-code-highlight\" data-id=\"e1afcfa\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language- \">\n\t\t\t\t<code readonly=\"true\" class=\"language-\">\n\t\t\t\t\t<xmp> last_value | log_cnt | is_called \n------------+---------+-----------\n         33 |       0 | t\n(1 row)<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-def21b8 akihiro-widget akihiro-widget-text-editor\" data-id=\"def21b8\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Per maggiori approfondimenti tecnici invito a prendere visione di questa domanda su stackoverflow: <a href=\"https:\/\/stackoverflow.com\/questions\/66456952\/what-does-log-cnt-mean-in-the-postgres-sequence\">https:\/\/stackoverflow.com\/questions\/66456952\/what-does-log-cnt-mean-in-the-postgres-sequence<\/a>.<\/p><p>Per una soluzione pratica, \u00e8 sufficiente, prima del riavvio della macchina host, arrestare il container dove \u00e8 in esecuzione Postgres, affinch\u00e9 l&#8217;evento di reboot della macchina non venga interpretato come un crash di sistema e gestito quindi da Postgres con un restore della sequence al valore <code>last_value + log_cnt<\/code>.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"<p>Uno scenario interessante su cui mi \u00e8 capitato di lavorare riguarda l&#8217;utilizzo di una sequence in un database Postgres. In un applicazione containerizzata con Docker era prevista un&#8217;operazione di reboot del sistema operativo, la quale eseguiva il riavvio della macchina host su cui erano in esecuzione i container. La cosa bizzarra \u00e8 che a seguito [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[50,16],"class_list":["post-1745","post","type-post","status-publish","format-standard","hentry","category-sistemistica","tag-postgresql","tag-sql"],"_links":{"self":[{"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/posts\/1745","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/comments?post=1745"}],"version-history":[{"count":13,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/posts\/1745\/revisions"}],"predecessor-version":[{"id":1758,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/posts\/1745\/revisions\/1758"}],"wp:attachment":[{"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/media?parent=1745"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/categories?post=1745"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/tags?post=1745"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}