Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pSk9j-0001WC-7w for pgsql-general@arkaria.postgresql.org; Thu, 16 Feb 2023 19:42:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pSk9i-0005OC-3V for pgsql-general@arkaria.postgresql.org; Thu, 16 Feb 2023 19:42:58 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pSk9h-0005O2-N2 for pgsql-general@lists.postgresql.org; Thu, 16 Feb 2023 19:42:57 +0000 Received: from mail-pj1-x1030.google.com ([2607:f8b0:4864:20::1030]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pSk9f-0004aV-PA for pgsql-general@postgresql.org; Thu, 16 Feb 2023 19:42:57 +0000 Received: by mail-pj1-x1030.google.com with SMTP id d2so2939698pjd.5 for ; Thu, 16 Feb 2023 11:42:55 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=to:in-reply-to:references:message-id:date:subject:mime-version:from :content-transfer-encoding:from:to:cc:subject:date:message-id :reply-to; bh=uZA806yXE0vzeSg0GmxSIXdH/4CK4jzEroArvhwvjNc=; b=Fc8CkMA0mbFtbMcF4pxJcOjdq4hwSomzygQKNVOdTWjzoORdRlyy6JL7rW2O9by7B7 ETsTrgP/Ga68G1HSc6A1bTUwlucCizEn/eb1LADSgUA54jYY5+EvIdOYyk4RkuSjsxyS 9Asm+ezhwSgUNEEQhzhuiIFolzOOAkaiRCOAixmSwGrBUYA59uO4E6cp2+5YV8jwHeI0 rqhgNkiqSKUknEczeS6ao8EdemAUOBtfXN1L6mvlrmMoiN2c9I7EgU24tlI/Qngunfi6 m2CjnGRf+grZNdZNs1VjqnPGk5EqhYwXd+eR40w9UC1tN71DJ6iaQ0R7hQTQyVgkuC/L fRzg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=to:in-reply-to:references:message-id:date:subject:mime-version:from :content-transfer-encoding:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=uZA806yXE0vzeSg0GmxSIXdH/4CK4jzEroArvhwvjNc=; b=I+QAc7koJkIxPN61f+Int5IY4aA2uvJPYBnEBuBpMeUEvp1ewQih8C5Ch2FLSLjGVJ /lr3DL0DK58Iodd8JIkgXRqBY2s3AMTMSKQWRNFFriGhsZ29ffzoLUM02KHNYdsdN018 aBQEoQSWhrW9TMgWcmodfzfpYzpGegvouAtlg+5YmUg8xc01OFGW4QkdNKZiE5UWU3jf /6YFJGwZueQS7tR8mGlFXE4YW208GtwKgnDr1plVhKTvpK0ZWFB89JLIjSpqCVNfNGXF 1hVWKzOXaXoXSGFYRtgopfHFWVcL6hfXcZ+28yQLpbTzLex+VeB8vz8DgUnKv7B1SPis /ozQ== X-Gm-Message-State: AO0yUKWjODw5veyVms1xuJqZOy1I8pjbCs0gaiG1puSnm4Lt1eOdFuok FOdhLdNnkNnUopcLIyPR6NffzdhUXkw= X-Google-Smtp-Source: AK7set8uf9cTEQzypaas/MOnfQF4eRnD2dgBVolIXm0qGwGzQnagMonw7RuzJV+6JMuIH0crrvZwXQ== X-Received: by 2002:a17:903:18e:b0:196:d05:bac7 with SMTP id z14-20020a170903018e00b001960d05bac7mr8842480plg.58.1676576573486; Thu, 16 Feb 2023 11:42:53 -0800 (PST) Received: from smtpclient.apple (180-150-36-87.b49624.bne.nbn.aussiebb.net. [180.150.36.87]) by smtp.gmail.com with ESMTPSA id h8-20020a170902748800b0019a7c890c61sm1666604pll.252.2023.02.16.11.42.52 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 16 Feb 2023 11:42:52 -0800 (PST) Content-Type: multipart/alternative; boundary=Apple-Mail-EB706833-61E2-4841-9DDC-8418E730146A Content-Transfer-Encoding: 7bit From: David Wheeler Mime-Version: 1.0 (1.0) Subject: Re: How to avoid Trigger ping/pong / infinite loop Date: Fri, 17 Feb 2023 06:42:39 +1100 Message-Id: <87FC4042-E7F1-4A53-AE60-1C84B5276713@gmail.com> References: In-Reply-To: To: pgsql-general@postgresql.org X-Mailer: iPhone Mail (20B101) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail-EB706833-61E2-4841-9DDC-8418E730146A Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable >> Are there techniques for situations like this? Just have two triggers, one for each column, and ensure that if your trigger= doesn=E2=80=99t change the value then it doesn=E2=80=99t do an update on th= e other column. Each time you do update both triggers will run but only one w= ill make a change, so that will break the cycle For insert case ensure the triggers do nothing if their source col is null --Apple-Mail-EB706833-61E2-4841-9DDC-8418E730146A Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Are there techniques for situatio= ns like this?
Just have two triggers, one for each column, and ensure that if your= trigger doesn=E2=80=99t change the value then it doesn=E2=80=99t do an upda= te on the other column. Each time you do update both triggers will run but o= nly one will make a change, so that will break the cycle

For in= sert case ensure the triggers do nothing if their source col is null
= --Apple-Mail-EB706833-61E2-4841-9DDC-8418E730146A--