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 1pQ8XY-00054c-3T for pgsql-sql@arkaria.postgresql.org; Thu, 09 Feb 2023 15:08:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pQ8XW-0004Ff-Fd for pgsql-sql@arkaria.postgresql.org; Thu, 09 Feb 2023 15:08:46 +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 1pQ8XW-0004FW-1R for pgsql-sql@lists.postgresql.org; Thu, 09 Feb 2023 15:08:46 +0000 Received: from premium22-2.web-hosting.com ([68.65.122.104]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pQ8XR-0004lT-EQ for pgsql-sql@lists.postgresql.org; Thu, 09 Feb 2023 15:08:45 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=misuse.org; s=default; h=Content-Type:Cc:To:Subject:Message-ID:Date:From:In-Reply-To: References:MIME-Version:Sender:Reply-To:Content-Transfer-Encoding:Content-ID: Content-Description:Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc :Resent-Message-ID:List-Id:List-Help:List-Unsubscribe:List-Subscribe: List-Post:List-Owner:List-Archive; bh=uarEbQsPI8L89gVzl8XES+/XlxBGYTk96IsBAKLcPyQ=; b=rQsP91SiO1dfHv9hJaop8acw3o PYERrQP0loANaohUkzMUpAFe5dve8Yrb8x7MF4aGWvCMmRNsCUeLAkOem5Hfs5FjootDZwOxYLNfp M+7KeZIP9CBXHgD09xsVepJqoymnYARQ8hjPAwWaDR9ubwrScBPpvEtZRx46FKOeuyr+CuBYUkojI TAWOd3mxQQn052XsuBfV5RzOFC3rHHVJuFc7skj84Dz2hCGNWQD9rlx7iHMHsZsKSWsWLqIIHVBa5 3IoA+nvx/yDks1qDL5TfS9GiRATGR6M2RMgiqubrzeX/LwXWJJP/V66SgO7MeyQbVnifgos4VHXRP eQ8xgaXQ==; Received: from mail-yw1-f176.google.com ([209.85.128.176]:33723) by premium22.web-hosting.com with esmtpsa (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.95) (envelope-from ) id 1pQ8XE-004y0B-UE for pgsql-sql@lists.postgresql.org; Thu, 09 Feb 2023 10:08:33 -0500 Received: by mail-yw1-f176.google.com with SMTP id 00721157ae682-4a263c4ddbaso29504657b3.0 for ; Thu, 09 Feb 2023 07:08:28 -0800 (PST) X-Gm-Message-State: AO0yUKWBoXL6rmJU7zMuCi4dAd4DPNks2mLmlrDOatChgEZywyysecGq gDuagwwW6iEFHQWcerlyeWRPw71AKQGioe23mis= X-Google-Smtp-Source: AK7set9u6mH7JPEn/IYAE+qruiyQ79HLblqxV5UuRUYLSWjwlj3AG6elrXoXNu6Qd+Q7P8WD8WJYjChqC08ETOg8quM= X-Received: by 2002:a81:8804:0:b0:527:bdc3:c0fb with SMTP id y4-20020a818804000000b00527bdc3c0fbmr1542802ywf.344.1675955307864; Thu, 09 Feb 2023 07:08:27 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Steve Midgley Date: Thu, 9 Feb 2023 07:08:16 -0800 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: How to ensure a log-entry is created based on state of data in other tables To: Andreas Joseph Krogh Cc: pgsql-sql@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000c8eb7e05f445c01e" X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - premium22.web-hosting.com X-AntiAbuse: Original Domain - lists.postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - misuse.org X-Get-Message-Sender-Via: premium22.web-hosting.com: authenticated_id: science@misuse.org X-Authenticated-Sender: premium22.web-hosting.com: science@misuse.org X-Source: X-Source-Args: X-Source-Dir: X-From-Rewrite: unmodified, already matched List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c8eb7e05f445c01e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Feb 8, 2023 at 11:24 PM Andreas Joseph Krogh wrote: > Hi. > > > > I'm not sure what the best subject is for this email, but here are the > requirements: When all activities for a given product has status=3D'DONE' > then an entry in activity_product_log should be created. This should be > guaranteed somehow. If an activity for which there exists an entry in > activity_product_log is set to status=3D'NOT_DONE', then the entry in > activity_product_log should be deleted. > > > > Here's an example-schema: > > *DROP TABLE IF EXISTS **activity_product_log*; > *DROP TABLE IF EXISTS **activity*; > *DROP TABLE IF EXISTS **product*; > > *CREATE TABLE **product* > ( > id *INTEGER primary key*, > *name **varchar not null unique* > ); > > *CREATE TABLE **activity* > ( > id *serial primary key*, > *name **varchar not null unique*, > product_id *integer not null references **product*(id), > status *varchar NOT NULL*, > *CHECK *(status *IN *(*'NOT_DONE'*, *'DONE'*)) > ); > > *CREATE TABLE **activity_product_log* > ( > id *serial primary key*, > product_id *integer not null references **product*(id), > created *TIMESTAMP NOT NULL* > ); > > *INSERT INTO **product*(id, *name*) *VALUES*(1, *'Horse'*), (2, *'Fish'*)= ; > > *INSERT INTO **activity*(*name*, product_id, status) > *VALUES *(*'A1'*, 1, *'NOT_DONE'*), (*'A2'*, 1, *'NOT_DONE'*), (*'A3'*, 1= , *'NOT_DONE'*), (*'A4'*, 1, *'NOT_DONE'*) > , (*'A5'*, 2, *'NOT_DONE'*), (*'A6'*, 2, *'NOT_DONE'*) > ; > > *UPDATE **activity **SET *status =3D *'DONE' **WHERE **name *=3D *'A1'*; > *UPDATE **activity **SET *status =3D *'DONE' **WHERE **name *=3D *'A2'*; > *UPDATE **activity **SET *status =3D *'DONE' **WHERE **name *=3D *'A3'*; > *UPDATE **activity **SET *status =3D *'DONE' **WHERE **name *=3D *'A4'*; = *-- Here an entry in activity_log should be created* > > I'm thinking about using constraint-triggers for this but will that > actually *guarantee* the requirements? I'm worried about the last part of > the requirement, if all activities for a product are =E2=80=98DONE=E2=80= =99 but then gets > toggled =E2=80=98NOT_DONE=E2=80=99 and =E2=80=98DONE=E2=80=99 =E2=80=9Cve= ry fast=E2=80=9D. > > > > Is there a better way, using some extra tables to do bookkeeping can the > have constraints ensuring this business-requirement? > > > 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 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? Steve --000000000000c8eb7e05f445c01e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Wed, Feb 8, 2023 at 11:24 PM Andre= as Joseph Krogh <andreas@visena.co= m> wrote:

Hi.

=C2=A0

I'm not sure what the best subject is f= or this email, but here are the requirements: When all activities for a giv= en product has status=3D'DONE' then an entry in activity_prod= uct_log should be created. This should be guaranteed somehow. If an = activity for which there exists an entry in activity_product_log is set to status=3D'NOT_DONE', then the entry in activity_= product_log should be deleted.

=C2=A0

Here's an exam= ple-schema:

DROP TABLE IF EXISTS activity_product_log;
DROP TABLE IF EXISTS activity;
DROP=
 TABLE IF EXISTS =
product;

CREATE TABLE product
(    id INTEGER primary key,
    name <=
/strong>varc=
har not null unique
);

CREATE TABLE activity
=
(
    id serial primary key,
    name=
 va=
rchar not null unique,
    product_id integer not null references product(id),
    status varchar NOT NULL,
    CHECK =
(status IN ('NOT_DONE'<=
/strong>, &=
#39;DONE'))
);

CREATE TABLE activity_product_log<=
/span>
(
    id serial primary key,
    pr=
oduct_id int=
eger not null references product(id),
    created <=
span>TIMESTAMP NOT NULL
);

INSERT INTO product<=
/span>(id, name) =
VALUES(1, 'Horse'), (2, 'Fish');

INSERT INTO <=
/strong>acti=
vity(name, product_id, st=
atus)
VAL=
UES ('A1', 1, =
'NOT_DONE'), ('A2', 1, 'NOT_DONE'), ('A3',=
 1, 'NOT_DONE'), ('A4', 1, 'NOT_DONE')
     , (<=
strong>'A5', 2, 'NOT_DONE'), ('A6', 2, 'NOT_DONE')
;
UPDATE activity SET status =3D 'DONE' WHERE <=
/strong>n=
ame =3D 'A1';
UPDATE activity SET status =3D 'DONE' WHERE name =3D=
 'A2';
UPDATE activi=
ty =
SET stat=
us =3D '=
;DONE' =
WHERE =
name =3D 'A3';
UPDATE activity SET status =3D 'DONE' WHERE =
name =3D 'A4&#=
39;; -- Here an entry in activity_log should be created
=

I'm thinking about using constraint-triggers for this bu= t will that actually guarantee the requirements? I'm worried abo= ut the last part of the requirement, if all activities for a product are = =E2=80=98DONE=E2=80=99 but then gets toggled =E2=80=98NOT_DONE=E2=80=99 and= =E2=80=98DONE=E2=80=99 =E2=80=9Cvery fast=E2=80=9D.

=C2=A0

Is = there a better way, using some extra tables to do bookkeeping can the have = constraints ensuring this business-requirement?

=C2=A0

What is the time window required for "DONE" and &quo= t;NOT_DONE" to be correct? Do they need to be atomic (meaning the time= window is effectively 0)? Or can the system "notice" recent chan= ges 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 / N= OT_DONE?

Another totally different way to think ab= out 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?
<= br>
Steve=C2=A0
--000000000000c8eb7e05f445c01e--