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 1pQA3S-0000om-FG for pgsql-sql@arkaria.postgresql.org; Thu, 09 Feb 2023 16:45:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pQA3R-0003Uy-CO for pgsql-sql@arkaria.postgresql.org; Thu, 09 Feb 2023 16:45:49 +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 1pQA2P-00064V-LX for pgsql-sql@lists.postgresql.org; Thu, 09 Feb 2023 16:44:45 +0000 Received: from mail-pl1-x630.google.com ([2607:f8b0:4864:20::630]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pQA2N-0005jd-EM for pgsql-sql@lists.postgresql.org; Thu, 09 Feb 2023 16:44:45 +0000 Received: by mail-pl1-x630.google.com with SMTP id u9so3432319plr.9 for ; Thu, 09 Feb 2023 08:44:43 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=8Myf8dTUWMY+U+QTqHFJutX+1dszpx1IKvihVykUdow=; b=g1Nn0AoAZlChKEsSFrUYMze5GSZHCoFniORY61LNb/D8Y6T4JRRwkZKAJRlA4Lj/kb hZh8/hRq738M6qefS85+GVS9/cgm/xmJPn32w7tupdtu1jReIbLEi79mD1eTex/kb4/l 6P1KeRdzEdU0EbXVMXaXvFnsx7zhsuOljTJWM66R4sLw5fyYqxsMSqL9DNajRZnUTYHf UrG59PftzKKMTzgA7NkgWqFmZ/bVwOYwLR/MqCjYdnGgoFyVzG/6+m0dxug7Zsftesjj hncFGpuRC7Ss4Ro038Bh9DcMKd2tH4S00nRU6cQJ5I68M/j4eBeA3aOmx9IVajaVuOIy wmBQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=8Myf8dTUWMY+U+QTqHFJutX+1dszpx1IKvihVykUdow=; b=3Rmd1VaEqGiNY9ueXqjQpxGM8X7pneKMhNLtUw3WYD/NWsVED2l4Cq4L/256UDJC77 6/ropnQJRapiFkhsw8J92dHpTlg8/QX9kdFWtso42KdelRr6xlagiCCTnssW9V6P4C5P mu591QOTx5YXrQOPpbnOA0NjWcksShv/VGytkHwSvmOrtZ2hKKyxmh4satnF2Qvlqxnf M4sEyf+WlUDMXZlImAsNa86dqmFGt0oAIe9yOQ5422yhHMXol/eW7PuJuZLRkGn8c5u1 FAlUpuqBhCf4txDLh+jUempLz3T9eUFPFvaG3zFS+agEnhufWskflNMCTgYW+cAMM5JK CLBA== X-Gm-Message-State: AO0yUKUJkh0eyCxjMEtSyLd+9yZnrcm4TYa7Zz1O0vKOIeTx5nVz7Uhr CdwCWoPQJdzAa/JnyQ+h4kEPTChaP+A= X-Google-Smtp-Source: AK7set9g4CuaO6fhK7UVtrWB75YGiZ/MNyRf5IjdknQTPOjoUSKpOEEMnHKeomYhW03sIVQKzsku0A== X-Received: by 2002:a17:903:22c8:b0:198:a49b:9f53 with SMTP id y8-20020a17090322c800b00198a49b9f53mr13135338plg.10.1675961080284; Thu, 09 Feb 2023 08:44:40 -0800 (PST) Received: from ?IPV6:2601:681:5a00:f790:2759:543d:79de:c076? ([2601:681:5a00:f790:2759:543d:79de:c076]) by smtp.gmail.com with ESMTPSA id x12-20020a17090300cc00b001966eaf7365sm1725233plc.17.2023.02.09.08.44.39 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 09 Feb 2023 08:44:39 -0800 (PST) Content-Type: multipart/alternative; boundary="------------IYAvevM6Xkk78OI896D2M33H" Message-ID: <8dc38af0-6862-42b0-aa09-6c89a3bc16a0@gmail.com> Date: Thu, 9 Feb 2023 09:44:38 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: How to ensure a log-entry is created based on state of data in other tables Content-Language: en-CA To: pgsql-sql@lists.postgresql.org References: From: Rob Sargent In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------IYAvevM6Xkk78OI896D2M33H Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 2/9/23 00:23, 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='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='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 =*'DONE' **WHERE **name *=*'A1'*; > *UPDATE **activity **SET *status =*'DONE' **WHERE **name *=*'A2'*; > *UPDATE **activity **SET *status =*'DONE' **WHERE **name *=*'A3'*; > *UPDATE **activity **SET *status =*'DONE' **WHERE **name *=*'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 ‘DONE’ but > then gets toggled ‘NOT_DONE’ and ‘DONE’ “very fast”. > > Is there a better way, using some extra tables to do bookkeeping can > the have constraints ensuring this business-requirement? > Is there any ordering or workflow associated? Does the product (or perhaps product type) define number of activities (or list of "activity.name"s).  Is there no "activity type"? Can a product be "DONE" more than once?  If not perhaps the product table should have the status field? --------------IYAvevM6Xkk78OI896D2M33H Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 2/9/23 00:23, 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='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='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 = 'DONE' WHERE name = 'A1';
UPDATE activity SET status = 'DONE' WHERE name = 'A2';
UPDATE activity SET status = 'DONE' WHERE name = 'A3';
UPDATE activity SET status = 'DONE' WHERE name = '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 ‘DONE’ but then gets toggled ‘NOT_DONE’ and ‘DONE’ “very fast”.

 

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


Is there any ordering or workflow associated? 
Does the product (or perhaps product type) define number of activities (or list of "activity.name"s).  Is there no "activity type"?
Can a product be "DONE" more than once?  If not perhaps the product table should have the status field?

--------------IYAvevM6Xkk78OI896D2M33H--