MIME-Version: 1.0
References: 
 <VisenaEmail.3d.88fc3a77a99e504f.18635052f66@visena.app.internal.visena.net>
 <CAJexoSJT_uVdMGwne8wDBEsZEMA_jfPdz41S0W9sVoj+MugOUA@mail.gmail.com>
 <VisenaEmail.6a.d3040d504876c082.1863705b248@visena.app.internal.visena.net>
In-Reply-To: 
 <VisenaEmail.6a.d3040d504876c082.1863705b248@visena.app.internal.visena.net>
From: Steve Midgley <science@misuse.org>
Date: Thu, 9 Feb 2023 08:37:35 -0800
Message-ID: 
 <CAJexoS+s7FcuOTgXXsBDM+xNTUgE0+Tu1+eo54TW_SBsYsTvow@mail.gmail.com>
Subject: Re: How to ensure a log-entry is created based on state of data in
 other tables
To: Andreas Joseph Krogh <andreas@visena.com>
Cc: pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000035a45e05f44700e3"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJexoS%2Bs7FcuOTgXXsBDM%2BxNTUgE0%2BTu1%2Beo54TW_SBsYsTvow%40mail.gmail.com>
Precedence: bulk

--00000000000035a45e05f44700e3
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, Feb 9, 2023 at 8:33 AM Andreas Joseph Krogh <andreas@visena.com>
wrote:

> P=C3=A5 torsdag 09. februar 2023 kl. 16:08:16, skrev Steve Midgley <
> science@misuse.org>:
>
> [=E2=80=A6]
>>
>>
>>
> What is the time window required for "DONE" and "NOT_DONE" to be correct?
> Do they need to be atomic (meaning the time window is effectively 0)? Or
> can the system "notice" recent changes and keep track of done/not done
> after-the-fact? If your time window is > 0, it seems like recurring
> processes could be set up to track DONE / NOT_DONE?
>
> Another totally different way to think about this is to create a view tha=
t
> provides answers on DONE and NOT_DONE as computed values based on the
> underlying state of the table at the time the view is queried? That would
> seem to satisfy a time window of 0?
>
> Steve
>
> Yes, they need to be atomic. Either all are DONE and there *is* an entry
> in activity_product_log for product_id, *or* there is no entry in
> activity_product_log.
>
>
>
So, would the view table approach work? So DONE / NOT_DONE is simply
calculated at the time that view is queried? It seems atomic to me,
especially if the query to the table is made with the appropriate
concurrency flags?

--00000000000035a45e05f44700e3
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Thu, Feb 9, 2023 at 8:33 AM Andrea=
s Joseph Krogh &lt;<a href=3D"mailto:andreas@visena.com">andreas@visena.com=
</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:=
0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">=
<div>P=C3=A5 torsdag 09. februar 2023 kl. 16:08:16, skrev Steve Midgley &lt=
;<a href=3D"mailto:science@misuse.org" target=3D"_blank">science@misuse.org=
</a>&gt;:</div><blockquote><div dir=3D"ltr"><div class=3D"gmail_quote"><blo=
ckquote class=3D"gmail_quote"><div><p>[=E2=80=A6]</p><p>=C2=A0</p></div></b=
lockquote><div>What is the time window required for &quot;DONE&quot; and &q=
uot;NOT_DONE&quot; to be correct? Do they need to be atomic (meaning the ti=
me window is effectively 0)? Or can the system &quot;notice&quot; recent ch=
anges and keep track of done/not done after-the-fact? If your time window i=
s &gt; 0, it seems like recurring processes could be set up to track DONE /=
 NOT_DONE?</div><div>=C2=A0</div><div>Another totally different way to thin=
k about this is to create a view that provides answers on DONE and NOT_DONE=
 as computed values based on the underlying state of the table at the time =
the view is queried? That would seem to satisfy a time window of 0?</div><d=
iv>=C2=A0</div><div>Steve=C2=A0</div></div></div></blockquote><p>Yes, they =
need to be atomic. Either all are DONE and there <i>is</i> an entry in <cod=
e>activity_product_log</code> for <code>product_id</code>, <i>or</i> there =
is no entry in <code>activity_product_log</code>.</p><p>=C2=A0</p></blockqu=
ote><div>So, would the view table approach work? So DONE / NOT_DONE is simp=
ly calculated at the time that view is queried? It seems atomic to me, espe=
cially if the query to the table is made with the appropriate concurrency f=
lags?=C2=A0</div></div></div>

--00000000000035a45e05f44700e3--