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 1pQ1Hs-0001u2-K0 for pgsql-sql@arkaria.postgresql.org; Thu, 09 Feb 2023 07:24:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pQ1Hr-000390-CQ for pgsql-sql@arkaria.postgresql.org; Thu, 09 Feb 2023 07:24:07 +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 1pQ1Hq-00038r-Vj for pgsql-sql@lists.postgresql.org; Thu, 09 Feb 2023 07:24:07 +0000 Received: from outbound.visena.net ([46.226.12.34]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pQ1Hk-0000Sf-Ue for pgsql-sql@lists.postgresql.org; Thu, 09 Feb 2023 07:24:06 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=visena.com; s=20141101.wh; h=Content-Type:MIME-Version:Subject:Message-ID:To:From:Date: Sender:Reply-To:Cc:Content-Transfer-Encoding:Content-ID:Content-Description: Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc:Resent-Message-ID: In-Reply-To:References:List-Id:List-Help:List-Unsubscribe:List-Subscribe: List-Post:List-Owner:List-Archive; bh=5DAjPC8VgCIN6tvlQQsvuSqNxb+ZpYHvU2h6z9rw/YE=; b=cpicqT2wKwiru3VZPxiqmL54fQ 6384sUMBxZuUBaIwx+DqUCKzBGSgfw/5SHnxIxJdP0I6w3wCg/uIGvN3vzg7Xa6y7xwpw4/YL4+It loo525GobGPVZZ/ZJwGQjbejOMKFcP25fsHMf3vWOmBqztck0EjzEP/TzjC8uODo8Ju8=; Received: from batch01.services.internal.visena.net ([10.3.0.103]) by outbound.visena.net with utf8esmtp (Exim 4.93) (envelope-from ) id 1pQ1Hk-005e7V-1v for pgsql-sql@lists.postgresql.org; Thu, 09 Feb 2023 08:24:00 +0100 Date: Thu, 9 Feb 2023 08:23:59 +0100 (CET) From: Andreas Joseph Krogh To: pgsql-sql@lists.postgresql.org Message-ID: Subject: How to ensure a log-entry is created based on state of data in other tables MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_Part_608663_86086516.1675927439963" X-Mailer: Visena Mail 3.2.302 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_608663_86086516.1675927439963 Content-Type: multipart/related; boundary="----=_Part_608664_1566272424.1675927439963" ------=_Part_608664_1566272424.1675927439963 Content-Type: multipart/alternative; boundary="----=_Part_608665_1823113761.1675927439985" ------=_Part_608665_1823113761.1675927439985 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi. I'm not sure what the best subject is for this email, but here are the=20 requirements: When all activities for a given product has status=3D'DONE' t= hen an=20 entry inactivity_product_log should be created. This should be guaranteed= =20 somehow. If an activity for which there exists an entry inactivity_product_= log=20 is set to status=3D'NOT_DONE', then the entry inactivity_product_log should= be=20 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=20 activity_log should be created I'm thinking about using constraint-triggers for this but will that actuall= y=20 guarantee the requirements? I'm worried about the last part of the requirem= ent,=20 if all activities for a product are =E2=80=98DONE=E2=80=99 but then gets to= ggled =E2=80=98NOT_DONE=E2=80=99 and=20 =E2=80=98DONE=E2=80=99 =E2=80=9Cvery fast=E2=80=9D. Is there a better way, using some extra tables to do bookkeeping can the ha= ve=20 constraints ensuring this business-requirement? -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas@visena.com www.visena.com ------=_Part_608665_1823113761.1675927439985 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable

Hi.

=C2=A0

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'DON= E' then an entry in activity_product_log should be created. Th= is should be guaranteed somehow. If an activity for which there exists an e= ntry in activity_product_log is set to status=3D'NOT_DONE', th= en the entry in activity_product_log should be deleted.

=C2=A0

Here's an exampl= e-schema:

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

CREATE TABLE=
 produc=
t
(
    i=
d INTEGER primar=
y key,
    <=
strong>name varchar not null unique
);

CREATE TABLE activity(
    id serial primary key=
,
    name =
varchar not null unique=
,
    produc=
t_id integer not=
 null references product(id),
    status varchar NOT NULL,
    =
CHECK (status <=
/span>IN ('NOT_DONE'<=
/span>, 'DONE'=
))
);

=
CREATE TABLE activity_product_log
(
    id <=
span>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');

INSE=
RT INTO activity(name, produc=
t_id, status)
VALUES =
('A1', 1, 'NOT_DONE'), ('A2', 1, 'NOT_DONE'), ('A3', 1, 'NOT_DONE'), (=
'A4', 1=
, 'NOT_DONE')
     , ('A5', 2, 'NOT_DONE'<=
/span>), ('A6'=
, 2, 'NOT_DONE')<=
br>;

UPDATE activity =
SET status =3D 'DONE' WHERE name =3D <=
span style=3D"color:#067d17;">'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 <=
span>'DONE' <=
span>WHERE name =3D =
'A4'; <=
i>-- Here an entry in activity_log should be created

I'm thinking about using const= raint-triggers for this but will that actually guarantee the require= ments? I'm worried about the last part of the requirement, if all activitie= s for a product are =E2=80=98DONE=E2=80=99 but then gets toggled =E2=80=98N= OT_DONE=E2=80=99 and =E2=80=98DONE=E2=80=99 =E2=80=9Cvery fast=E2=80=9D.

=C2=A0

Is there a b= etter way, using some extra tables to do bookkeeping can the have constrain= ts ensuring this business-requirement?

=C2=A0<= /p>

=
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47= 909 56 963
3D""
=
------=_Part_608665_1823113761.1675927439985-- ------=_Part_608664_1566272424.1675927439963 Content-Type: image/png Content-Transfer-Encoding: base64 Content-Disposition: inline Content-ID: iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAABHNCSVQICAgIfAhkiAAABzBJREFU aEPtmNFxHDcMhmVP3i1VECpvnjzkVIHWFfhcgVcVRKrAUgWRK/C6Al8H3lTgy0PGbzFdQc4VJP/H ADs43q6kROeJNbOYgQACIAgCWJKng4MZ5gxUGXh0U0b+SIuF9G+EWXj2Q15vbrE/NPtk9uub7Gfd t5mBx1NhqSEo8HshjbEUtlO2yIM9tsx5dZP9rPt2MzDZFAqZE4LGcFjdsg3saQaHt7fY70X949On aS+OZidDBkavD33157L4xaw2os+Mp/DAC10l2XhOCeStj0W5ajrJL8WfCi80Xgf9vVlrhg9yRONe /f7xI2vNsIcM7JwU9o6oGyJrLT8JOA1aX9sKP4wl94bAniukEbo/n7YPmuTETzIab4Y9ZWCrKexd 8M58lxPCvvD6aihfvexbEQoPYO8NQRO0JnddGO6FJYaVsBde7cXj7KRk4LsqDxQzCYeGsJNgaXZe +JXkyGgWINq3Gp+bHNILz+wEYs5qH1eJrouNrpDXtk4O6x1IvtD40GWyJYYdkF0jIbYZlN16xygI gl9smbMDsmFdfAKb23xGB8H/mv3tOJfArs1kukk79DEPdQ5u0g1vCvvqKXIW8mZYW+F3Tg4r8HvZ kQCCLydK8EFMQCe5N8RgL9mRG0SqQFuNvdE6beSs0qPDBuCdg0+gvClso8SbTO4ki7mQzQqBrcMH QPwReg2wW8umEe/+L8T/LEzBGF9nXjzZ46s+ITHvhegW8LL39xm6App7KYL/GE+vcYnFbJiP/0aY hdiCnRC7jaj7eiK2ETInC5PRF6LYkSN0+HabF75WuT6syCyI0YkVGGMvUC33AmfZTDUEj8u6IVhu EhRUJ2U2g6UlugyNb03Hl9obHwlxJbcRzcYjO4QPjVfGFTQav4vrmp7cpMp2qbHnBxWJbisbho2Q XI6C1sIHDUFhH4Hij4UbIev66eANeiwbkA+LBsO36zAHzoXU7AhbqLAXEiOYTXcSdO8VS9L4wN8U BIYhBd6oSUgYMijOkecRuTdQa/YiBXhbXFcnCnI2SrfeBG9NydrLYMhGHa5qB9pQIxlzAE6Okjzx JO5afGe6kmhBicWKQNKuTZ5EW+MjQY8v/9rQ0bhJSJyNGUe/JH1t8h1iMbdSPAvxHYin6YmN9YBX QmTYZZNh14vHhhhal5vtcIrJjmvszPRJdExH3MWHvykoBEc9CoCGWJisOLOGoCORs1FvIMbYA8z3 kwM59oemy6LlWrLxFLmWgiQAfEGd8S+NssbK+EhyGLxUkhhiy717waBqHOJYSEacwBejkFNhjJNj v/gANOdQxPfciP/JVBCK2cOIcg3RRJ+CPrLPNVhhN6F38VKMF3XLlIJrjU5C8gMFxvKDvOcPc4rV NjCn7KM0BV+161V8viSCuJZ8SITGJGEh7IUUlxOFMYUH1kJOCN4Wh+I5pqCuK01k40kSNtnKiKIl UfxAgW5sU5JlS05rtt5YFJF1SWoqHv6BRgQcA4/bdb9WRjmMk3jyUMAbIoyJq9e4cVmgzKt9j5gN J/aYDhk+hhjExwaPcz5PObA5Zd9bvz5UzCTZuZDidu5AchpiKSwPR+TV1bCWqBQ9nCjJ5neivC82 Nr4LeS2j1gw5LUqwBuhGQQU5UwHeSslXkwIynz2U2A2yKDgG7OffwLA3TpGRpk0Tzpj3ZEJXi/GR a6GN0e0NtppChePdcAz1FTS+FN8Kpxoiykk+J8fC5tenjbu9kSqpHLu9jBrhUuhNwVGbpyZrzrl0 HPVD8SXj5EOOD/eDC64VjvYBZLuUbIVAfBN1t/C/SU+cAGtdGo+fVnzycUX5wmn6eCKPmWYJG2E/ ppTsuRCbvUD9fwquksG5GqLVKhzDQ3GrEyLKSXhsiK3T5j9EyxffCFOYi2wUlPyFFDQAhehESDjQ GoX0ho0oj8RPou6TxHJdcT0NTcWkO0AnG7+uXsnHqcas/72wvWF+mSf7N/Watp9kTXoluzeS0fB9 9CcZ/hvhSZTfh99pispZOXL9KrGrARkNUMu9ITbScZWs7xOYNt9pwxSZtYDsX/GE3xTkrXgwAsXm fud08FiTeC+m2/o7ppo+PTS/NBK5ARpDG5YHr++DpmVfreYdiX8mnp+DzPEG9WbqJON0JBenZofs sxBA1gj5NXGvfJu/Qh7HwQh/VDUEyUxCit5hH94QfKkEdu+GwK8BX0hvCF+D67xhjmXQCXMwJCZ+ olK0A1EKRCE4snsh42w8Mv/Zhxw9iD7Cjo7CyQC/KyF6oBfShL4PYgG+CHsYKyZxvxZSZBAgjhIz YDz+AbfD37GtbaoSKzgGt+lKfI/GZtay6vG4VXTpPsh+IcQhOk9I7WYeP5AM3HZ9+DaSGIp9HIuu huC4pCGGx+YD2fcc5tfIAA0h/Et4/jX8zz4fWAasIf4UbR9Y6HO4d8jAnd4U0Y8ageuCB+c+H5R3 CHU2mTMwZ+B/y8DfSMBLLOYXVuEAAAAASUVORK5CYII= ------=_Part_608664_1566272424.1675927439963-- ------=_Part_608663_86086516.1675927439963--