From thiemo@gelassene-pferde.biz Fri May 15 23:21:55 2026 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfMi1-001pR9-7l for pgsql-general@arkaria.postgresql.org; Tue, 04 Feb 2025 17:27:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tfMhy-006FX2-KX for pgsql-general@arkaria.postgresql.org; Tue, 04 Feb 2025 17:27:34 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfMhy-006FWW-9s for pgsql-general@lists.postgresql.org; Tue, 04 Feb 2025 17:27:34 +0000 Received: from sm-r-002-dus.org-dns.com ([89.107.70.11]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tfMhv-003Gtn-22 for pgsql-general@lists.postgresql.org; Tue, 04 Feb 2025 17:27:33 +0000 Received: from smarthost-dus.org-dns.com (localhost [127.0.0.1]) by smarthost-dus.org-dns.com (Postfix) with ESMTP id EF51BA08B5 for ; Tue, 4 Feb 2025 18:27:30 +0100 (CET) Received: by smarthost-dus.org-dns.com (Postfix, from userid 1001) id E2EE9A1384; Tue, 4 Feb 2025 18:27:30 +0100 (CET) X-Spam-Status: No, score=-0.8 required=5.0 tests=AWL,BAYES_00,HTML_MESSAGE, KAM_INFOUSMEBIZ,RCVD_IN_VALIDITY_RPBL_BLOCKED, RCVD_IN_VALIDITY_SAFE_BLOCKED,SPF_HELO_PASS,SPF_PASS autolearn=no autolearn_force=no version=3.4.6 Received: from ha01s018.org-dns.com (ha01s018.org-dns.com [62.108.32.138]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by smarthost-dus.org-dns.com (Postfix) with ESMTPS id B569AA08B5 for ; Tue, 4 Feb 2025 18:27:30 +0100 (CET) Authentication-Results: ha01s018.org-dns.com; spf=pass (sender IP is 146.185.68.202) smtp.mailfrom=thiemo@gelassene-pferde.biz smtp.helo=dummy.faircode.eu Received-SPF: pass (ha01s018.org-dns.com: connection is authenticated) Date: Tue, 4 Feb 2025 18:27:28 +0100 (GMT+01:00) From: Thiemo Kellner To: pgsql-general@lists.postgresql.org Message-ID: In-Reply-To: References: <4e7a338-c7d3-e944-20bd-a6e346e175c7@appl-ecosys.com> Subject: Re: Lookup tables MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2_173169591.1738690048366" X-Correlation-ID: X-PPP-Message-ID: <173869005052.1776424.17685034195650385047@ha01s018.org-dns.com> X-PPP-Vhost: gelassene-pferde.biz X-POWERED-BY: wint.global - AV:CLEAN SPAM:OK List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_2_173169591.1738690048366 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Unless the lookup table is actually a check constraint one can use to populate dropdown boxes in an interface. Cheers Thiemo ------=_Part_2_173169591.1738690048366 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit Unless the lookup table is actually a check constraint one can use to populate dropdown boxes in an interface.

Cheers

Thiemo
------=_Part_2_173169591.1738690048366-- From michal@kleczek.org Fri May 15 23:22:57 2026 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfMlS-001pub-QR for pgsql-general@arkaria.postgresql.org; Tue, 04 Feb 2025 17:31:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tfMlR-006Ils-AG for pgsql-general@arkaria.postgresql.org; Tue, 04 Feb 2025 17:31:09 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfMlQ-006Ilj-Vs for pgsql-general@lists.postgresql.org; Tue, 04 Feb 2025 17:31:08 +0000 Received: from mail-ej1-x62e.google.com ([2a00:1450:4864:20::62e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tfMlN-003Gvg-1j for pgsql-general@lists.postgresql.org; Tue, 04 Feb 2025 17:31:08 +0000 Received: by mail-ej1-x62e.google.com with SMTP id a640c23a62f3a-ab7515df1faso149004466b.2 for ; Tue, 04 Feb 2025 09:31:05 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=kleczek.org; s=google; t=1738690264; x=1739295064; darn=lists.postgresql.org; h=to:in-reply-to:cc:references:message-id:date:subject:mime-version :from:content-transfer-encoding:from:to:cc:subject:date:message-id :reply-to; bh=Lzo0r0VsOjrk4hKJH1MqGmm7r+kBZJpbGiqT7lEZmxc=; b=biOb+dczf5SB/uclpO/A2IuTpD5mCXLG5tnCw8eLQyj/KX3awASKwEbpPMlgaQL6Z0 N6wXhOaKWt5BSVmanVeMyfwI4CYzLS8HVvQwnZKfxbKgkImxoAWWrq0nM/xUKlOePBgS +dPCbZhSRSBSkEjPpF85mDttyaV/kNjznZ4gE= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1738690264; x=1739295064; h=to:in-reply-to:cc: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=Lzo0r0VsOjrk4hKJH1MqGmm7r+kBZJpbGiqT7lEZmxc=; b=lYmoOyrR/3W/Xvw3bMPdyxHeBypIzA0SeT0EMGiI0U0gh/PEX8gT71CaUyeLwIUXEO qCjJmfvedLEKNmARh1NRxbEb80GjoR6QaTK5P78qLaOR6TGDhYR1aPX99xVoYW5pelLB 0yXLK5vl+bwvIOuNwbgS+RiDeLJUXqRgNxqnum7XdFlds0BCyh2i0Y3JA4I6kb3aQjpP twMvaxsO3wC2lEVTQ/60IRWJPjEc0dHit5oON7ZeVIyLUbNdhwtKC8qVnz0HwKi87Wda J6vJGcDn8j4h7xTWVZ329DBRpqGNML2JRrY69kUXkuspciT98T7mpvt+oR1NFROw8S1G yCJQ== X-Gm-Message-State: AOJu0YyPEkbAuQmNVwjH+W7Ec7/yxQ/SswL6xqLa2tU3ZJeR9lNcTKpa gVgYl74BWLJ39RF7EPkoiI4ziFRQfuXKJ57jut8axaR8MKAWFF58olkmgaI8jSvINvnrNqodDNe mCS8= X-Gm-Gg: ASbGnctZEuz/1OX5TeuD405J9/fEULPgRgBB4JLAkmdkfcM2Vayb0i3AEVd5MVezyHz 0DPOiQ8bsFx4qmL2vdchF1BnnR/A/tszWX0w7+xrAHx16W1N5kusPxMv8Lj2J8yhJfOOrx3bfir X+0LySrjdm2E3bvLMrOq98ONqkY1iUikDLIJG3WNWk82JkFDOSUX8uS92n7SNri6UMA3eedozYq FEXeTy2xB6F6hSWgB3gspEVGQasYcjOALtTYJhp9SmHMpC2Q9iUBWnhVIsrYRLlhjNQa69pzJWd sKYxMFdszHA7PtD+eRcjGkeDXxh1pTcIMkiUBLgHE68= X-Google-Smtp-Source: AGHT+IEshBbCWhvkTmPsIBxVhkKc5FWzAG28QyJUUWxQesEgK/b84+9sFkEEeDOpRbuOXFXsen9t5A== X-Received: by 2002:a17:906:60d7:b0:ab6:d2d2:4fd8 with SMTP id a640c23a62f3a-ab6d2d2527fmr2270381866b.57.1738690264448; Tue, 04 Feb 2025 09:31:04 -0800 (PST) Received: from smtpclient.apple (b15.kleczek.org. [185.20.172.182]) by smtp.gmail.com with ESMTPSA id a640c23a62f3a-ab6e49ffe2csm969732666b.97.2025.02.04.09.31.03 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 04 Feb 2025 09:31:04 -0800 (PST) Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable From: =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= Mime-Version: 1.0 (1.0) Subject: Re: Lookup tables Date: Tue, 4 Feb 2025 18:30:53 +0100 Message-Id: <628D4022-5365-4D00-810B-10349A90874A@kleczek.org> References: Cc: pgsql-general@lists.postgresql.org In-Reply-To: To: Thiemo Kellner X-Mailer: iPhone Mail (22C161) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 4 Feb 2025, at 18:27, Thiemo Kellner wrot= e: >=20 > =EF=BB=BF Unless the lookup table is actually a check constraint one can u= se to populate dropdown boxes in an interface. That is even worse because it ceases being transactional and users might sel= ect something different than what they see on the screen. =E2=80=94 Micha=C5=82=20= From Karsten.Hilbert@gmx.net Fri May 15 23:23:22 2026 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfN6G-001sWQ-4K for pgsql-general@arkaria.postgresql.org; Tue, 04 Feb 2025 17:52:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tfN6F-006ZrA-87 for pgsql-general@arkaria.postgresql.org; Tue, 04 Feb 2025 17:52:39 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfN6E-006Zr2-U9 for pgsql-general@lists.postgresql.org; Tue, 04 Feb 2025 17:52:38 +0000 Received: from mout.gmx.net ([212.227.17.22]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tfN6C-003AFq-29 for pgsql-general@lists.postgresql.org; Tue, 04 Feb 2025 17:52:38 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmx.net; s=s31663417; t=1738691554; x=1739296354; i=karsten.hilbert@gmx.net; bh=uLqSXUN+m+LWhdaFdcGpxLFsipftj2eHqS1/6EOKHUQ=; h=X-UI-Sender-Class:Date:From:To:Subject:Message-ID:References: MIME-Version:Content-Type:Content-Transfer-Encoding:In-Reply-To: cc:content-transfer-encoding:content-type:date:from:message-id: mime-version:reply-to:subject:to; b=G94B+qUukB4JF0BzxzRmbocEz9HrRDul2pW18ww/68rNOXHSJmEZ+e6lHBGJPpGc Pf9hnrMehc/jAk75f5ZYlHLByJdurxyb+s90PNSLm0xIHITcK2VOMq/LyHpc54ujv xYjgVu0R10RohwWaReGrXxs3UDq586OjfZAaTJEJs/n4KohffmexZg/xyPIYq5FIr Wnm8OqgmIm+SUsnjcKVykq2Lz1M7J/HFKb8qpIDakLVmfS4DS2l31W3H+Vu0j4H7Z 3WQy34aA/PsF+jU3v6df2xHqFStv77M4429RX1iAldB96wd1Cj41DqaaVNcmPDmP6 v/XCulwEB9mqHnkUIg== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from hermes ([84.144.215.252]) by mail.gmx.net (mrgmx104 [212.227.17.168]) with ESMTPSA (Nemesis) id 1M5wPb-1tlgUr3Anj-00H5a5 for ; Tue, 04 Feb 2025 18:52:34 +0100 Received: from ncq by hermes with local (Exim 4.96) (envelope-from ) id 1tfN6A-0002kX-1L for pgsql-general@lists.postgresql.org; Tue, 04 Feb 2025 18:52:34 +0100 Date: Tue, 4 Feb 2025 18:52:34 +0100 From: Karsten Hilbert To: pgsql-general@lists.postgresql.org Subject: Re: Lookup tables Message-ID: References: <628D4022-5365-4D00-810B-10349A90874A@kleczek.org> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable In-Reply-To: <628D4022-5365-4D00-810B-10349A90874A@kleczek.org> Ma_X_il-Followup-to: d Re_X_turn-receipt-to: Karsten.Hilbert@gmx.net Di_X_sposition-Notification-To: Karsten.Hilbert@gmx.net X-Confi_X_rm-Reading-To: Karsten.Hilbert@gmx.net X-Pri_X_ority: 2 (High) Sender: X-Provags-ID: V03:K1:9KUVR4g36tnBOO364OIRQ1vBPqnkxDqpR3Nh5Zosjom1LIsO42d Sk2joxPuIvfkwIZ8HZCrepasxSHLLPY0J9mpuyi/HsMkxxebveihKcJ7juHEGAnddYeg5mZ xl2xDxBUkDD0Esdtcw8s62F9HTVfbPJYeDx3YdUosPH3KfKcKtE9aLhn7ihF58JoW6q/Aro Xt8pPpw7I1y6bzuXi5prw== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:AcaUltZrS5o=;yVFDR0J+T/7GJf3ZRy+4RqqmvsD KHCe7FkuAdae9pvqJ0c3zqMY13PiXuofFMofnuFCAkb75gwLWSAoD9D9B1nxjY49TPApaqp0l gIYhGbXPdEhirWXT4v30inMLcWYPz59IrJzOjY6HrHx6gSnzE3Ap0CSNu8J06KECUXIBfp+Pb M0PLGnRfcY1SbKd/7lxXyhI9hWFMJzPuWjJzJertdXBCCWU0PUZOMCk5yxOiJcWOoKybZlRyy d1E0HGMGhdHiE8B0TRHOI1vbKF/sSj32LIjPAULlL/g5W3WrUpV3/DNnaYBR6YL2S4JTL6WuN Z9/D226p9UYqEXQui8r4UCEAy8fk0bhrjKOC7KwOP5qir/ijUKEqe+tLdd9EJtdGRpas7IbDz uGS2VmZcBlcDkgC5TN+AAds4PjVVGYoQMTlSkVqNiQKKhywfWti5kr5wmkASjEWWac7oqE3RQ AuzzxdgAG7X9Mo4scROX4oLoOwP8zi/JaYZzXXJLNfur/YVys4M4Uh8KK0X4dQaOcuiW3WDKf 9u1e3R/Ub7fuChgC8WDsxjo0Dsaz9dm7UAOj0gddqzLWlBc7ql8D6w3fmGh3vjM4RSozdOMNM W4W5hDBY6ayJmL6ITJEbWs8KZjRS4VAqGgm3knkwqR7lYu/j++keA3Ip0eaqTV8yTmjwFPfPL Ed8jY/6C8z8T990WpxT3ETakGNLSDngarWMDn8FBR/Ec+Y7sXr8tHGwNsyPiQSTPTsNE7i0sL fT7jbIOPQTr8/bgwVyOZvRwYY/Ef3EpcWNh5rAfEoDcxdVlwHMqst2hbJP7hXoB8sErxL9Ym0 4b32uH6ClDdGEVy5sWaEkYluksHqzYoc+N2Ytr+woEuTQjQK6EKXBQr0x8/deTN1wwNDtnP44 KX91Bs4o7WBiBizPO7zRjUJEjUCQa4rHiA4QjFhPNsut2aHAeebG9lC9FWv7nYV5Fv2XbjGJV QVTDanrmTuIz2pX4gAVnj5AKqj9JNI2UbwaE4E/fdiYYr1IKZcfbYyPd4VsRsf6KTcFG9lFlk QSZLuaEi/RzhRH2fui4rib56XDeiGHNO2S8Zw9QhuLFVvhuByAyWFCg/iRsHcLOWhB/098BPm pTXUnXFIzH7ca2UVaUAJuEAwVryt0VxB5fB3il6jc+eaa1ExtBt7QI6tnSne9y6aRJbJrX4/4 VXYhIWfLrSCV+v2GrVuuNvGsxOMNn/3yEspc1yfSOEejsZQDLH8G768vamzK2WwvdL3/VGpwJ JUODmlF54pKE0Gbmd1qUXYS/2FLshtGdYPBZx/SPPEARuVHE5Wg3HJeEbTzfZJdYmnFGfNGfI ny9aCxZaYGTgvndOah1dW09Rg== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Am Tue, Feb 04, 2025 at 06:30:53PM +0100 schrieb Micha=C5=82 K=C5=82eczek: > > On 4 Feb 2025, at 18:27, Thiemo Kellner = wrote: > > > > =EF=BB=BF Unless the lookup table is actually a check constraint one c= an use to populate dropdown boxes in an interface. > > That is even worse because it ceases being transactional and users might= select something different than what they see on the screen. I never thought of that, thanks. Karsten =2D- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B From david.g.johnston@gmail.com Fri May 15 23:23:13 2026 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfNXe-001wZu-Fj for pgsql-general@arkaria.postgresql.org; Tue, 04 Feb 2025 18:20:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tfNXd-006ydL-Di for pgsql-general@arkaria.postgresql.org; Tue, 04 Feb 2025 18:20:57 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfNXd-006yd9-3R for pgsql-general@lists.postgresql.org; Tue, 04 Feb 2025 18:20:57 +0000 Received: from mail-ot1-x32e.google.com ([2607:f8b0:4864:20::32e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tfNXa-003AU5-2I for pgsql-general@lists.postgresql.org; Tue, 04 Feb 2025 18:20:56 +0000 Received: by mail-ot1-x32e.google.com with SMTP id 46e09a7af769-724f4d99bc7so1346665a34.1 for ; Tue, 04 Feb 2025 10:20:55 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1738693254; x=1739298054; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=8480h8JrtnD6FEEcFvDq4KQRqs0DxS0DLbdAtN6Tzrs=; b=jPnRL/nvE5d+85A00iO7BjXDRlJ9gM9jM6MnNff0pMZJA/Lp9Hew0rG9yEuGAxfRA5 cVf50JL4FnAaDrdvvsfV+yu29x3wmjUJzf002/Jyd//irkiKWwsJhuFmAsdIJ5J2jPFa O/q8X9AvSjfGsukUWnlbcc0nvIBsBQmSc+fG2503o94MHAU/m93EiQ5whLPfwwWqle22 ew2UPra2q5CFQKByw5tCSgwiqYH/cPA808AN4luuA2elY4k+E4wRJRfS/QtZPJczQPZd QguxC7uAK6JOxJWNxKYg/yBAvxNOdaRfGxwmwLBsMBi16McXodS/XF1wb+4DyU1ifHg8 8GNg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1738693254; x=1739298054; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=8480h8JrtnD6FEEcFvDq4KQRqs0DxS0DLbdAtN6Tzrs=; b=TSkb3byb5C0MpzueQE3gD7o03zdw+BI++Rl4h2Ktumm4CTJcebqPlhZqkX2t6OCE2V JJ+GMA2FQZCw116Im2LNZJ71y5HBun4t0g0E+85xH/s/yJfwOBaCI0CVKzGrfhqdUf1f 6ZLm11hdQDp+ugfnbeMcdm5JyxdIrEdNOQDs+GPxX1qhBjAkpA505tndCg5CSURPP9G7 wFZigZFn8mZLxjTyjB0jvQQxhodyB/XYMtUQzTKmSOA3HKrKt8749x/3XqITfEl+P+aw IV10r9uS1Jt9OpfksRd9/vAapPT3Ay2Czq0Fb5bhXcUimbAS7m+SrhHey9EdzxTy9Wa5 cjpQ== X-Forwarded-Encrypted: i=1; AJvYcCXiJ5tj6m7Yn/QGnI6bgLrNmMscfReWISut7AuEA6wEHw7efywNtUojggz9UJSgztNreWxjyE1Q+g7gckrj@lists.postgresql.org X-Gm-Message-State: AOJu0YxbuLcwWCGiaRNVBoW5ookFx03k45SEJvTET7roj+KEfWZwrRAL KYqA7hSb8SANMmUZ5mKkgIdE02b9RtsKbWJ6YvVbQ6/HmL1QKMnx7+jaq1ikwG1HfkOUMyQOSbZ 9dXTGbcebe3omktm2GPAvQ5nwKD8= X-Gm-Gg: ASbGncusydyU64Ap0Gmyj6Q014KwzY/GqrfeIwZKkci4KOZNaPdfy4CZFugS1TWe/dh Dt0pXXstiLKmMIChVn1AxoGHP6tKAeyArJoqtcat+GvDSGaAtv7h0yP9N3304Y14iX5wMfT8= X-Google-Smtp-Source: AGHT+IGX2ghnMdPxWLqp0sE5DBtBJ3yU0q9ePEGRjemRMDNT+hgMPu2mxbTWCfFE/F3uKDnmPBR56VFmmvAzFrzFrx0= X-Received: by 2002:a05:6830:6517:b0:718:1863:a3fa with SMTP id 46e09a7af769-72656766054mr22208994a34.10.1738693254011; Tue, 04 Feb 2025 10:20:54 -0800 (PST) MIME-Version: 1.0 Received: by 2002:a05:6802:a0c:b0:577:9519:f64a with HTTP; Tue, 4 Feb 2025 10:20:53 -0800 (PST) In-Reply-To: <628D4022-5365-4D00-810B-10349A90874A@kleczek.org> References: <628D4022-5365-4D00-810B-10349A90874A@kleczek.org> From: "David G. Johnston" Date: Tue, 4 Feb 2025 11:20:53 -0700 X-Gm-Features: AWEUYZkzjNkR28QBdMaL8ONDQT0uAinRUrW7RMHTD6RsYGhJufCp60OgNidyUCo Message-ID: Subject: Re: Lookup tables To: =?UTF-8?B?TWljaGHFgiBLxYJlY3plaw==?= Cc: Thiemo Kellner , "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000c76471062d5511ab" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c76471062d5511ab Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tuesday, February 4, 2025, Micha=C5=82 K=C5=82eczek = wrote: > > > On 4 Feb 2025, at 18:27, Thiemo Kellner > wrote: > > > > =EF=BB=BF Unless the lookup table is actually a check constraint one ca= n use to > populate dropdown boxes in an interface. > > That is even worse because it ceases being transactional and users might > select something different than what they see on the screen. > What is the process flow for this happening? How big a bug needs to exist in the system for something bad to happen as opposed to some kind of =E2=80= =9Cvalue not found=E2=80=9D error and a refresh with the correct matching values sho= wing up in the UI? David J. --000000000000c76471062d5511ab Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tuesday, February 4, 2025, Micha=C5=82 K=C5=82eczek <michal@kleczek.org> wrote:

> On 4 Feb 2025, at 18:27, Thiemo Kellner <thiemo@gelassene-pferde.biz> wrote:
>
> =EF=BB=BF Unless the lookup table is actually a check constraint one c= an use to populate dropdown boxes in an interface.

That is even worse because it ceases being transactional and users might se= lect something different than what they see on the screen.

What is the process flow for this happenin= g?=C2=A0 How big a bug needs to exist in the system for something bad to ha= ppen as opposed to some kind of =E2=80=9Cvalue not found=E2=80=9D error and= a refresh with the correct matching values showing up in the UI?

David J.
=C2=A0
--000000000000c76471062d5511ab-- From thiemo@gelassene-pferde.biz Fri May 15 23:24:00 2026 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfOFP-0024yd-RG for pgsql-general@arkaria.postgresql.org; Tue, 04 Feb 2025 19:06:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tfOFO-007PRY-RF for pgsql-general@arkaria.postgresql.org; Tue, 04 Feb 2025 19:06:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfOCs-007Jht-PA for pgsql-general@lists.postgresql.org; Tue, 04 Feb 2025 19:03:34 +0000 Received: from sm-r-006-dus.org-dns.com ([84.19.1.234]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tfOCq-003HpI-0O for pgsql-general@lists.postgresql.org; Tue, 04 Feb 2025 19:03:34 +0000 Received: from smarthost-dus.org-dns.com (localhost [127.0.0.1]) by smarthost-dus.org-dns.com (Postfix) with ESMTP id 3D824A0A1E for ; Tue, 4 Feb 2025 20:03:31 +0100 (CET) Received: by smarthost-dus.org-dns.com (Postfix, from userid 1001) id 31727A14F2; Tue, 4 Feb 2025 20:03:31 +0100 (CET) X-Spam-Status: No, score=-0.9 required=5.0 tests=AWL,BAYES_00,KAM_INFOUSMEBIZ, RCVD_IN_VALIDITY_RPBL_BLOCKED,RCVD_IN_VALIDITY_SAFE_BLOCKED, SPF_HELO_PASS,SPF_PASS autolearn=no autolearn_force=no version=3.4.6 Received: from ha01s018.org-dns.com (ha01s018.org-dns.com [62.108.32.138]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by smarthost-dus.org-dns.com (Postfix) with ESMTPS id 9DC5AA0A1E for ; Tue, 4 Feb 2025 20:03:30 +0100 (CET) Authentication-Results: ha01s018.org-dns.com; spf=pass (sender IP is 146.185.68.202) smtp.mailfrom=thiemo@gelassene-pferde.biz smtp.helo=dummy.faircode.eu Received-SPF: pass (ha01s018.org-dns.com: connection is authenticated) Date: Tue, 4 Feb 2025 20:03:27 +0100 (GMT+01:00) From: Thiemo Kellner To: "pgsql-generallists.postgresql.org" Message-ID: In-Reply-To: <628D4022-5365-4D00-810B-10349A90874A@kleczek.org> References: <628D4022-5365-4D00-810B-10349A90874A@kleczek.org> Subject: Re: Lookup tables MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Correlation-ID: X-PPP-Message-ID: <173869581050.1884766.18296606378674076678@ha01s018.org-dns.com> X-PPP-Vhost: gelassene-pferde.biz X-POWERED-BY: wint.global - AV:CLEAN SPAM:OK List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 04.02.2025 18:31:09 Micha=C5=82 K=C5=82eczek : >> =EF=BB=BF Unless the lookup table is actually a check constraint one can= use to populate dropdown boxes in an interface. > > That is even worse because it ceases being transactional and users might = select something different than what they see on the screen. In how far is a real check constraint less transactional? And in how far is= it more advisable to have a real check constraint and fill your dropdown b= oxes from another source and having to keep that source on sync with the re= al check constraint? From thiemo@gelassene-pferde.biz Fri May 15 23:22:15 2026 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfQgi-002KCz-E0 for pgsql-general@arkaria.postgresql.org; Tue, 04 Feb 2025 21:42:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tfQgh-008y3O-IZ for pgsql-general@arkaria.postgresql.org; Tue, 04 Feb 2025 21:42:31 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfQfy-008tJ6-TB for pgsql-general@lists.postgresql.org; Tue, 04 Feb 2025 21:41:46 +0000 Received: from sm-r-006-dus.org-dns.com ([84.19.1.234]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tfQfv-003C7I-2H for pgsql-general@lists.postgresql.org; Tue, 04 Feb 2025 21:41:45 +0000 Received: from smarthost-dus.org-dns.com (localhost [127.0.0.1]) by smarthost-dus.org-dns.com (Postfix) with ESMTP id DF9CEA151A for ; Tue, 4 Feb 2025 22:41:40 +0100 (CET) Received: by smarthost-dus.org-dns.com (Postfix, from userid 1001) id D2F44A1518; Tue, 4 Feb 2025 22:41:40 +0100 (CET) X-Spam-Status: No, score=-0.9 required=5.0 tests=AWL,BAYES_00,KAM_INFOUSMEBIZ, RCVD_IN_VALIDITY_RPBL_BLOCKED,RCVD_IN_VALIDITY_SAFE_BLOCKED, SPF_HELO_PASS,SPF_PASS autolearn=no autolearn_force=no version=3.4.6 Received: from ha01s018.org-dns.com (ha01s018.org-dns.com [62.108.32.138]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by smarthost-dus.org-dns.com (Postfix) with ESMTPS id 8B5ADA151A for ; Tue, 4 Feb 2025 22:41:40 +0100 (CET) Authentication-Results: ha01s018.org-dns.com; spf=pass (sender IP is 146.185.68.202) smtp.mailfrom=thiemo@gelassene-pferde.biz smtp.helo=dummy.faircode.eu Received-SPF: pass (ha01s018.org-dns.com: connection is authenticated) Date: Tue, 4 Feb 2025 22:41:38 +0100 (GMT+01:00) From: Thiemo Kellner To: "pgsql-generallists.postgresql.org" Message-ID: <64603f36-b6d5-469b-8d6d-30376842241b@gelassene-pferde.biz> In-Reply-To: <628D4022-5365-4D00-810B-10349A90874A@kleczek.org> References: <628D4022-5365-4D00-810B-10349A90874A@kleczek.org> Subject: Re: Lookup tables MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Correlation-ID: <64603f36-b6d5-469b-8d6d-30376842241b@gelassene-pferde.biz> X-PPP-Message-ID: <173870530033.2048734.10841686077407417097@ha01s018.org-dns.com> X-PPP-Vhost: gelassene-pferde.biz X-POWERED-BY: wint.global - AV:CLEAN SPAM:OK List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 04.02.2025 18:31:09 Micha=C5=82 K=C5=82eczek : >=20 >> On 4 Feb 2025, at 18:27, Thiemo Kellner wr= ote: >>=20 >> =EF=BB=BF Unless the lookup table is actually a check constraint one can= use to populate dropdown boxes in an interface. >=20 > That is even worse because it ceases being transactional and users might = select something different than what they see on the screen. I might see what you want to point out. E.g. the table is COLOURS. The rec = with id 1 is RED, the one with id 2 is BLUE, 3 is GREE and so on. Now you l= oad these values into the dropdown box that sports RED, BLUE, GREE and so o= n. While someone selects GREE, there is a maintenance release changing GREE= to YELLOW. So when that someone sends the selection by id to the backend, = not GREE is selected but YELLOW. A) Your release changed the sementics of the record 3. It's meaning changed= . I cannot recommend doing that. B) If you absolutely must change the semantic, put your application into ma= intenance mode in which noone can select anything beforehand. If the maintenance would just correct the typo from GREE to GREEN, nothing = would happen. Yor customer still ordered the lavishly green E-Bike her hear= ever desired. From Karsten.Hilbert@gmx.net Fri May 15 23:22:15 2026 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfQiW-002KKb-7U for pgsql-general@arkaria.postgresql.org; Tue, 04 Feb 2025 21:44:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tfQiV-0092G2-B5 for pgsql-general@arkaria.postgresql.org; Tue, 04 Feb 2025 21:44:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfQiV-0092Fs-0n for pgsql-general@lists.postgresql.org; Tue, 04 Feb 2025 21:44:23 +0000 Received: from mout.gmx.net ([212.227.17.20]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tfQiS-003J8a-1I for pgsql-general@lists.postgresql.org; Tue, 04 Feb 2025 21:44:22 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmx.net; s=s31663417; t=1738705460; x=1739310260; i=karsten.hilbert@gmx.net; bh=zg+zvCgpKES/jyCDP5xvQ9Sou2F7RkZ/7AS7rRqizaU=; h=X-UI-Sender-Class:Date:From:To:Subject:Message-ID:References: MIME-Version:Content-Type:Content-Transfer-Encoding:In-Reply-To: cc:content-transfer-encoding:content-type:date:from:message-id: mime-version:reply-to:subject:to; b=M6Ufxkqqa50iEhfdLBS0T6RA+IXT3VivbLxWP08Ybov5VuSgz7AVsdxBzHms0zhW DNOSycpx85wF2hhiHgC6y8IK6rXT2g3a2pwAejEWVKXDtbWGJ+XK5MWKgKQcstXYG jogsa/ki/FxDWyjFzNvg5/pN30AXZgc17/hJn+smulMQ6ibylE0+v4o5DuFy0denM MfbZVZlZhXNUaqcTJ2BHjcTW+4toP0B/juo/q4ZENN2t2UZ7RlPosty1M33jnpkNl 2R5/X9g2mlqBSiK30ipl1TyIqoojOoU0u1QlxekvkT7q1IgZlVvh/a6T1SRmbpLb+ t8Y/TVbF+Hx56PuSyQ== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from hermes ([84.144.215.252]) by mail.gmx.net (mrgmx104 [212.227.17.168]) with ESMTPSA (Nemesis) id 1MpDNf-1t4lZc3QQZ-00gcNy for ; Tue, 04 Feb 2025 22:44:19 +0100 Received: from ncq by hermes with local (Exim 4.96) (envelope-from ) id 1tfQiR-0005iF-1P for pgsql-general@lists.postgresql.org; Tue, 04 Feb 2025 22:44:19 +0100 Date: Tue, 4 Feb 2025 22:44:19 +0100 From: Karsten Hilbert To: pgsql-general@lists.postgresql.org Subject: Re: Lookup tables Message-ID: References: <628D4022-5365-4D00-810B-10349A90874A@kleczek.org> <64603f36-b6d5-469b-8d6d-30376842241b@gelassene-pferde.biz> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable In-Reply-To: <64603f36-b6d5-469b-8d6d-30376842241b@gelassene-pferde.biz> Ma_X_il-Followup-to: d Re_X_turn-receipt-to: Karsten.Hilbert@gmx.net Di_X_sposition-Notification-To: Karsten.Hilbert@gmx.net X-Confi_X_rm-Reading-To: Karsten.Hilbert@gmx.net X-Pri_X_ority: 2 (High) Sender: X-Provags-ID: V03:K1:h/1WrbEQ66E14QtqpnffVET8S7ch7NCMf6K6Nnuny1rJz64Kj4X 4Kne0DUTMtK/7kYgNURMDCnp6Xo5cjsyKGNTE9foJemAQfxe0E/cWMdZJd0TOYRpbgVBTmU pUqRxoUaOi8pc8Z0OXO1Eehyv6capRFdTNgY2QVByD+EdEVBDYqaaVGQngdjJVi+YWLpD3O ArIBHGbhERwUdJWAos+9w== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:+clfRLkmiI4=;wBCAj0TkZs5LhtdJ61JRFhm1d1m Ofrc5Kw+oQz/IBF60gZbU9ZVyH7y2wabbPMJOcxBXwFDERdWbbpW3UO6veJ+Gfdujnj7Ksc7X YVlFcNljLpgtxJG7gg12BnS6Xszm8RxAnR5QXR0+p7xxawf5+gEFCpWS7/ViiN9IEloKouHMt iRNagiW3CWk5yVh3FPcMjiRZaoybUqy33BZVVQEf7oIKQcygR4KPMYFCnH9bqWfuoQl7EYX6D VSkIPp1xOmfn6DPL6n7Kgl2uJbTvz8M0/FbCMl5a67Wv6hEri5oCR5yq+uHuMeaJBQ/igWEsC nf0Mv9SQJLHhB10w9MEBuiySOKixRfMl/KiFGgEDMsQ6pJf3aE85rEkEgz+FBetJF0RKREZLU 2DxNnESgUJRNv97igd3nrmwHfUoKcNr3q31H+N5m5/gbDZvDfWPuENGvK030cs4milKKmu8NE FXPJlzCQb3Wy8voygOaWpldY5EX9UhqPU7ZDsAjyASQj0kTlsxhuDIYp0wvANN6fXItOWcqCh MlPY+QX+qdlUuSbcbTM2YHUjZuQBaDGVYI9ndgde0Djr6TTV8ciklvd4dYhYEjVgLDXSTlt2X WtfvdSu3mbBxxKufAlNmAkX3S3h6FqD4p4dn/7CbDKebPdcMVxdbjaWqsOEL/M5I/wqwVnZQo NzocdiZa3dCEqr4ws2bsMtK5PWi0uIiMltbutwWlKIhwp5zAYwGlTU5aTOvxq0t47LrDFFK50 jtDknl8Ae4sbIvHtyD/08qBDa7tFbG0ZF6iS7n1dIhcywYyNScZau8z9W2YCWbNet7B0F06ZD UDbrn57v/nbJXUf1sQIoJaJbon9F5fhyPSDatRJDtBghpfraIVxrmqWB7dUsd9K5KqHTsrGmE ayAM3lmfG+JIXeEjPnbobaZfiP69EdAzSZt0TwRgsSELFoFw45hKUkMMfsnlV+feBMp8/i7LK dqt2z3i0tejrNxfir2VQogYIdRNDFCOMZD/9cJD0kws8oMoJB9YDAYkJo5ytk9W1Xgfydkg9r mVHw4JpuJ4v8gc65zgA65t5AtDmMzGY1EpwtMRlYuuqXwu+Jv7zlo7RPh3R7uAqny3c534ajp qISU8MbADivBMdJ1gvgoBCZI5d9J+owEgL8Czyzvq+I4d34rTq7O0DlfK/LMPvyjANHjP4wyi TrLMtbSuCFuTvHb+ToCGpETMoX1rjCLsNwO13eKDKoT6mSONDsuLjWPOGdGRvSMHeoiYWmudG D+215AfqTUn40dOrTVGksiDWkovLInHMKiZO0MR8MJtCdmZ3rOSvSr9ZuF8Nq//TudCWT2aXP +bsO9/24EXzpNj9DlTP4XGf9Ow0R+IaX6Gmu6VL+MPIaFVe+nQ00IVhVK61Ax9dqK+NRZ+3vd 3JYBvqERuoZbaJdDEF70Sg96KScAmniiaDqPbZocksvOCY7TKLac/obqH4 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Am Tue, Feb 04, 2025 at 10:41:38PM +0100 schrieb Thiemo Kellner: > >> On 4 Feb 2025, at 18:27, Thiemo Kellner = wrote: > >> > >> =EF=BB=BF Unless the lookup table is actually a check constraint one = can use to populate dropdown boxes in an interface. > > > > That is even worse because it ceases being transactional and users mig= ht select something different than what they see on the screen. > > I might see what you want to point out. E.g. the table is COLOURS. The r= ec with id 1 is RED, the one with id 2 is BLUE, 3 is GREE and so on. Now y= ou load these values into the dropdown box that sports RED, BLUE, GREE and= so on. While someone selects GREE, there is a maintenance release changin= g GREE to YELLOW. So when that someone sends the selection by id to the ba= ckend, not GREE is selected but YELLOW. Yep, that's what I meant and which I never thought of before. Karsten =2D- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B From michal@kleczek.org Fri May 15 23:22:46 2026 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfewr-0044IB-KX for pgsql-general@arkaria.postgresql.org; Wed, 05 Feb 2025 12:56:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tfewq-00EQIJ-N7 for pgsql-general@arkaria.postgresql.org; Wed, 05 Feb 2025 12:56:08 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfewq-00EQIB-Al for pgsql-general@lists.postgresql.org; Wed, 05 Feb 2025 12:56:08 +0000 Received: from mail-ej1-x635.google.com ([2a00:1450:4864:20::635]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tfewm-003JTp-2Y for pgsql-general@lists.postgresql.org; Wed, 05 Feb 2025 12:56:07 +0000 Received: by mail-ej1-x635.google.com with SMTP id a640c23a62f3a-ab2b29dfc65so1110484266b.1 for ; Wed, 05 Feb 2025 04:56:04 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=kleczek.org; s=google; t=1738760163; x=1739364963; darn=lists.postgresql.org; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=E6dsYgpHyXMy9VBBA2r6tT5G/HDXXq4/RdCRWdtNRuE=; b=Hz7CUeYJXL3O8/HjJNNrd4pnRLl1Z906Kt1LNotM5qE7hpkwVYzKG5SodciV5FXZmK hPZ+ycjyRh2JHj9JkUwvKlpHMwY3tQdqVO8hgtZ6gIDn9zUxswhuJ/KFRZNMU09fm508 kcHkagYZggnMCdjwi4DxpPR16TqHZS4awYhyE= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1738760163; x=1739364963; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=E6dsYgpHyXMy9VBBA2r6tT5G/HDXXq4/RdCRWdtNRuE=; b=kwt1/VXQY0MnveY76tHCD8x2/ZujHRquQAId9CBVJdcGYaDLPUFMHRG9uSSyiaPtHR YZqW/1uepGgXWWwOjqIW3/c1Bb2BVv/UWEZII9JXn0U1pRYhEXUCmsCjPpMMlpYUyi1k sa5kEduOwsmo5JPstkMM/IELXqYOIWZPChCodjFqZ0tW872T4465V9NSLnZDHzxmDTV+ PtV584/mJDGEqAHEE6vAF4PZF1cGYQRFx/uOoKtr0zjXs6RBOjTxl8VJj/xfDo03Ztcg vwZMrleYLodhs+wI4AWAxruqIv+MxqCrJVtYP6MdUCg0FqSzKJmeil2O19n3l1nBvIP8 E5aw== X-Gm-Message-State: AOJu0Yyt9qYMsjq1qjbCWDoJM3oSvfRcKgznmHUuuDDnd7Y1BgOyiU+B YjStRrwVdeyf1Oq4jFUbf3fpMv711+qz5dMwWOendBPetTQg1UapRLcqXBsLLGFeaDobLvG1kNL M3pI= X-Gm-Gg: ASbGncv+K0nDmxNGtTNzT7jZRlHkZwxvON6T6XQXX0RzeXnzyOkwHXfPDL6PYKJUza4 kg+3MbfJDHTEuddJc8wiWxACXTeAMm882pH/aBae+SA87g1BIX1ExafUWaGBz+Izeo/zrVCIZ9A iQlxF0ba7XvlIupnqLb8SYallqTeX/yLNouIqri5/tu+PWIFrgayR08dYxuHsOX6c9V+WFOOj+2 IIvh0aeH35TnR/AajovxTIRSrfDL0x4iwuj81ajOiAoKiezw+RMNt3c8YH0fzoR0V993pz+Ct2D 5Yxq8hybCNsuUSSSl9nXh8vUG5v10KNeD5eyIeCmX2E= X-Google-Smtp-Source: AGHT+IE62ELvkLPGW2hQ5POvdjyfVakixw/MrXwV9onE7eXFYwNZbssiMz/e5Uvja8mSlBqc0W4wrg== X-Received: by 2002:a17:907:2ce3:b0:ab2:da92:d0bc with SMTP id a640c23a62f3a-ab75e234984mr283649466b.3.1738760162904; Wed, 05 Feb 2025 04:56:02 -0800 (PST) Received: from smtpclient.apple (b15.kleczek.org. [185.20.172.182]) by smtp.gmail.com with ESMTPSA id 4fb4d7f45d1cf-5dcda03eaecsm1653987a12.49.2025.02.05.04.56.02 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 05 Feb 2025 04:56:02 -0800 (PST) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.300.87.4.3\)) Subject: Re: Lookup tables From: =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= In-Reply-To: <64603f36-b6d5-469b-8d6d-30376842241b@gelassene-pferde.biz> Date: Wed, 5 Feb 2025 13:55:51 +0100 Cc: pgsql-general Content-Transfer-Encoding: quoted-printable Message-Id: <3A08C6E4-8B03-4D24-BAA7-53860A66C1AF@kleczek.org> References: <628D4022-5365-4D00-810B-10349A90874A@kleczek.org> <64603f36-b6d5-469b-8d6d-30376842241b@gelassene-pferde.biz> To: Thiemo Kellner X-Mailer: Apple Mail (2.3826.300.87.4.3) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 4 Feb 2025, at 22:41, Thiemo Kellner = wrote: >=20 > 04.02.2025 18:31:09 Micha=C5=82 K=C5=82eczek : >=20 >>=20 >>> On 4 Feb 2025, at 18:27, Thiemo Kellner = wrote: >>>=20 >>> =EF=BB=BF Unless the lookup table is actually a check constraint one = can use to populate dropdown boxes in an interface. >>=20 >> That is even worse because it ceases being transactional and users = might select something different than what they see on the screen. >=20 > I might see what you want to point out. E.g. the table is COLOURS. The = rec with id 1 is RED, the one with id 2 is BLUE, 3 is GREE and so on. = Now you load these values into the dropdown box that sports RED, BLUE, = GREE and so on. While someone selects GREE, there is a maintenance = release changing GREE to YELLOW. So when that someone sends the = selection by id to the backend, not GREE is selected but YELLOW. >=20 > A) Your release changed the sementics of the record 3. It's meaning = changed. I cannot recommend doing that. That=E2=80=99s what using natural keys and FK=E2=80=99s restricting = their changes guarantee: no (accidental) changes to meaning of data. Even with cascading updates you still have transactional semantics (ie. = the user selects what=E2=80=99s on the screen or gets an error). > B) If you absolutely must change the semantic, put your application = into maintenance mode in which noone can select anything beforehand. All this error prone hassle and downtime can be avoided with natural = keys and guarantees that DBMS gives you. >=20 > If the maintenance would just correct the typo from GREE to GREEN, = nothing would happen. Yor customer still ordered the lavishly green = E-Bike her hear ever desired. The question is: how do you _ensure_ that? =E2=80=94 Michal= From thiemo@gelassene-pferde.biz Fri May 15 23:24:15 2026 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfm5f-004yDs-Pl for pgsql-general@arkaria.postgresql.org; Wed, 05 Feb 2025 20:33:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tfm5e-000l4f-Lk for pgsql-general@arkaria.postgresql.org; Wed, 05 Feb 2025 20:33:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfm5e-000l4V-Ah for pgsql-general@lists.postgresql.org; Wed, 05 Feb 2025 20:33:42 +0000 Received: from sm-r-008-dus.org-dns.com ([84.19.1.236]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tfm5b-003b5u-2S for pgsql-general@lists.postgresql.org; Wed, 05 Feb 2025 20:33:41 +0000 Received: from smarthost-dus.org-dns.com (localhost [127.0.0.1]) by smarthost-dus.org-dns.com (Postfix) with ESMTP id 8A5A6A16E3 for ; Wed, 5 Feb 2025 21:33:39 +0100 (CET) Received: by smarthost-dus.org-dns.com (Postfix, from userid 1001) id 7CEB2A15E7; Wed, 5 Feb 2025 21:33:39 +0100 (CET) X-Spam-Status: No, score=-1.0 required=5.0 tests=AWL,BAYES_00,KAM_INFOUSMEBIZ, RCVD_IN_VALIDITY_RPBL_BLOCKED,RCVD_IN_VALIDITY_SAFE_BLOCKED, SPF_HELO_PASS,SPF_PASS autolearn=no autolearn_force=no version=3.4.6 Received: from ha01s018.org-dns.com (ha01s018.org-dns.com [62.108.32.138]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by smarthost-dus.org-dns.com (Postfix) with ESMTPS id F3FE6A16FC for ; Wed, 5 Feb 2025 21:33:38 +0100 (CET) Authentication-Results: ha01s018.org-dns.com; spf=pass (sender IP is 146.185.68.202) smtp.mailfrom=thiemo@gelassene-pferde.biz smtp.helo=[192.168.178.23] Received-SPF: pass (ha01s018.org-dns.com: connection is authenticated) Message-ID: <8a118426-7b8a-4d8b-a22b-3bdee2e5e187@gelassene-pferde.biz> Date: Wed, 5 Feb 2025 21:33:38 +0100 MIME-Version: 1.0 Subject: Re: Lookup tables To: pgsql-general References: <628D4022-5365-4D00-810B-10349A90874A@kleczek.org> <64603f36-b6d5-469b-8d6d-30376842241b@gelassene-pferde.biz> <3A08C6E4-8B03-4D24-BAA7-53860A66C1AF@kleczek.org> Content-Language: de-CH-frami, en-GB, it-CH, fr-CH, es-CL, es-ES From: Thiemo Kellner In-Reply-To: <3A08C6E4-8B03-4D24-BAA7-53860A66C1AF@kleczek.org> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-PPP-Message-ID: <173878761886.3424846.10138728774167101038@ha01s018.org-dns.com> X-PPP-Vhost: gelassene-pferde.biz X-POWERED-BY: wint.global - AV:CLEAN SPAM:OK List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk El 05-02-25 a las 13:55, Michał Kłeczek escribió: >> A) Your release changed the sementics of the record 3. It's meaning changed. I cannot recommend doing that. > That’s what using natural keys and FK’s restricting their changes guarantee: no (accidental) changes to meaning of data. > Even with cascading updates you still have transactional semantics (ie. the user selects what’s on the screen or gets an error). Sorry, that is utter nonsense. You cannot ever guarantee an update does not mess up the semantics on the updated field, change the meaning. You would need a check constraint which in it turn needs to get set up where one can mess up things. >> B) If you absolutely must change the semantic, put your application into maintenance mode in which noone can select anything beforehand. > All this error prone hassle and downtime can be avoided with natural keys and guarantees that DBMS gives you. And I thought you would have denied the need of changing semantics above. And no, changing your natural keys semantically ALWAYS requires downtime to make sure you do not run into the race condition described above. >> If the maintenance would just correct the typo from GREE to GREEN, nothing would happen. Yor customer still ordered the lavishly green E-Bike her hear ever desired. > The question is: how do you _ensure_ that? Ensure, the update goes from GREE to GREEN? You cannot, simple as that. You just can minimize the risk by testing, testing, testing. But that holds equally true for the business key of a surrogate key table as natural key table. That's why the surrogate key is such an elegant construct. You can change business key of the record with id 3 from GREE to GREEN, VERT, GRÜN, VERDE or ASéLDHK()*NSLDFHP)(*Z . It keeps its meaning of the perception of the human eye of electromagnetic waves of the wavelength roughly between 495-570 nm (according to Wikipedia). From michal@kleczek.org Fri May 15 23:22:09 2026 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfmoF-00543R-HL for pgsql-general@arkaria.postgresql.org; Wed, 05 Feb 2025 21:19:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tfmoE-001Grs-37 for pgsql-general@arkaria.postgresql.org; Wed, 05 Feb 2025 21:19:46 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfmoD-001Grk-Mq for pgsql-general@lists.postgresql.org; Wed, 05 Feb 2025 21:19:45 +0000 Received: from mail-ej1-x632.google.com ([2a00:1450:4864:20::632]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tfmoB-003bxB-0Z for pgsql-general@lists.postgresql.org; Wed, 05 Feb 2025 21:19:45 +0000 Received: by mail-ej1-x632.google.com with SMTP id a640c23a62f3a-aaec111762bso65396066b.2 for ; Wed, 05 Feb 2025 13:19:43 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=kleczek.org; s=google; t=1738790382; x=1739395182; darn=lists.postgresql.org; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=InU4yLCtiRXQyb/Vp2rYz4yfpAYhacDz2D31wl1Xb5k=; b=EZQeADJyDIfrMFuanLScDBZ7bsEE487Z5T8A14kGZbqullodg30wSaEPNwooQNaO+6 7c27ELLsjKi/cQO06RATsQenTp9X3s2XRe+XdMcW/dLF7+LGoVylimu6e1ScZDBiSMet 57QbFYnZNIEOTfX9j00x4neUr8nNLG+2lXO4E= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1738790382; x=1739395182; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=InU4yLCtiRXQyb/Vp2rYz4yfpAYhacDz2D31wl1Xb5k=; b=P0UCk1I4nls08P8Y0X5XuoFg7i/0TOA7/F9/GCgfE3ZKIFYWSre3bI4TerTEr16Ukt Xs0CXz2dvMgiZwvEY0iRoauALfANGX3VeNMZSAig7ZaDftGe8kZDgkrhkaIYkUmVWnWT C/Zi3k2IwPd8nSorzYMswFRXTgr7UEZWvmMkk+2Nq+KHJ47VEPWLJk5E1CUMFKpSaxjG MZkbKYqMT3luXtDAb926RvA8FDqoFTFC9lfNixE6GkZgz4Ip/g9a9aQHg5fgevaYxEef /mxPYobxshJwAZ05o4+EQwtyf9ewVHkW0sAEJmwf29KpmUwttEqO6VKz+eWuYbE3DxM0 +EBQ== X-Gm-Message-State: AOJu0YyLsFs2wLYx5gnOnHu/okhr20DCT4WgFb/ntTrmPBjWw2Lvd+PV qTabja/84w901aGee2MUNzEKlBF2ArM1VyvSiZlizIi1mrN823MMJ1vLM4AkB8tbVimv5dX/kCf nXN8= X-Gm-Gg: ASbGnctfT0lGCbXkn6bVO7pw6PzLb9z8WOh7FaLZF9Jmqd3PW396BjH4DIjYQ3kwJEj gI34Jrfq0LbzSEFO+OQ4+ioN6jxav9i3wdZG1glEBfU3l/OyHYLQ00wyyIB6D4jw/0CuGpj3p7J HoUiQorrqjwDGo45P+hq6+MsN8Vt/QYbRbY004J+gf+H2JtunTXQQ89EhLpyIbBmHoH8HVij5y2 82o+FUUGgMaw3T2A4v5hfO4rWFWuVI8iAwdelnpXKXR3zLKHvBrFyTV9Rf/nr4ANkj/lKHcO6k/ RnX+opbSg6ZkAPauDgA03pgYA9Y1J5NQeGexJNUj3LE= X-Google-Smtp-Source: AGHT+IH9IeeaQsJGrMyDOE8vLio0kf2czMOIdlfUaTJ3YxQZ6Bf0xozs9rYlY6skQZAsuLxSi3Q6uA== X-Received: by 2002:a17:906:1915:b0:ab7:6c4b:796a with SMTP id a640c23a62f3a-ab76c4b8378mr156807466b.39.1738790382289; Wed, 05 Feb 2025 13:19:42 -0800 (PST) Received: from smtpclient.apple (b15.kleczek.org. [185.20.172.182]) by smtp.gmail.com with ESMTPSA id a640c23a62f3a-ab7656d5d5asm128454966b.48.2025.02.05.13.19.41 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 05 Feb 2025 13:19:41 -0800 (PST) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.300.87.4.3\)) Subject: Re: Lookup tables From: =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= In-Reply-To: <8a118426-7b8a-4d8b-a22b-3bdee2e5e187@gelassene-pferde.biz> Date: Wed, 5 Feb 2025 22:19:30 +0100 Cc: pgsql-general Content-Transfer-Encoding: quoted-printable Message-Id: <878194FA-E9F8-4688-9904-06B8D2A4DD5C@kleczek.org> References: <628D4022-5365-4D00-810B-10349A90874A@kleczek.org> <64603f36-b6d5-469b-8d6d-30376842241b@gelassene-pferde.biz> <3A08C6E4-8B03-4D24-BAA7-53860A66C1AF@kleczek.org> <8a118426-7b8a-4d8b-a22b-3bdee2e5e187@gelassene-pferde.biz> To: Thiemo Kellner X-Mailer: Apple Mail (2.3826.300.87.4.3) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 5 Feb 2025, at 21:33, Thiemo Kellner = wrote: >=20 >=20 > El 05-02-25 a las 13:55, Micha=C5=82 K=C5=82eczek escribi=C3=B3: >>> A) Your release changed the sementics of the record 3. It's meaning = changed. I cannot recommend doing that. >> That=E2=80=99s what using natural keys and FK=E2=80=99s restricting = their changes guarantee: no (accidental) changes to meaning of data. >> Even with cascading updates you still have transactional semantics = (ie. the user selects what=E2=80=99s on the screen or gets an error). > Sorry, that is utter nonsense. You cannot ever guarantee an update = does not mess up the semantics on the updated field, change the meaning. = Y But you can guarantee that if you change the value of the key after the = user displays it - the user will get an error on submission (whereas = with the surrogate key it would happily proceed without user noticing). > ou would need a check constraint which in it turn needs to get set up = where one can mess up things. >>> B) If you absolutely must change the semantic, put your application = into maintenance mode in which noone can select anything beforehand. >> All this error prone hassle and downtime can be avoided with natural = keys and guarantees that DBMS gives you. > And I thought you would have denied the need of changing semantics = above. And no, changing your natural keys semantically ALWAYS requires = downtime to make sure you do not run into the race condition described = above. How so? The user is going to get FK violation - you do not need any = downtime to make sure users don=E2=80=99t submit wrong values. >>> If the maintenance would just correct the typo from GREE to GREEN, = nothing would happen. Yor customer still ordered the lavishly green = E-Bike her hear ever desired. >> The question is: how do you _ensure_ that? > Ensure, the update goes from GREE to GREEN? You cannot, simple as = that. You just can minimize the risk by testing, testing, testing. You can also simply disallow updates with FK constraint eliminating = risk. > But that holds equally true for the business key of a surrogate key = table as natural key table. That's why the surrogate key is such an = elegant construct. You can change business key of the record with id 3 = from GREE to GREEN, VERT, GR=C3=9CN, VERDE or AS=C3=A9LDHK()*NSLDFHP)(*Z = . It keeps its meaning of the perception of the human eye of = electromagnetic waves of the wavelength roughly between 495-570 nm = (according to Wikipedia). And why do you think unconstrained updating of business key is a good = thing? You must implement rules governing what can and what cannot be changed = *somewhere* - not doing it in the database means you have to do it in = applications. Anyway - let=E2=80=99s agree to disagree :) =E2=80=94 Michal= From thiemo@gelassene-pferde.biz Fri May 15 23:22:52 2026 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfo0J-005Eqb-QJ for pgsql-general@arkaria.postgresql.org; Wed, 05 Feb 2025 22:36:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tfo0I-001wXk-Pf for pgsql-general@arkaria.postgresql.org; Wed, 05 Feb 2025 22:36:18 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfo0I-001wXO-Dn for pgsql-general@lists.postgresql.org; Wed, 05 Feb 2025 22:36:18 +0000 Received: from sm-r-006-dus.org-dns.com ([84.19.1.234]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tfo0F-003OVs-02 for pgsql-general@lists.postgresql.org; Wed, 05 Feb 2025 22:36:17 +0000 Received: from smarthost-dus.org-dns.com (localhost [127.0.0.1]) by smarthost-dus.org-dns.com (Postfix) with ESMTP id 3A58EA1727 for ; Wed, 5 Feb 2025 23:36:12 +0100 (CET) Received: by smarthost-dus.org-dns.com (Postfix, from userid 1001) id 2D067A1756; Wed, 5 Feb 2025 23:36:12 +0100 (CET) X-Spam-Status: No, score=-1.0 required=5.0 tests=AWL,BAYES_00,KAM_INFOUSMEBIZ, RCVD_IN_VALIDITY_RPBL_BLOCKED,RCVD_IN_VALIDITY_SAFE_BLOCKED, SPF_HELO_PASS,SPF_PASS autolearn=no autolearn_force=no version=3.4.6 Received: from ha01s018.org-dns.com (ha01s018.org-dns.com [62.108.32.138]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by smarthost-dus.org-dns.com (Postfix) with ESMTPS id BE6EBA1727 for ; Wed, 5 Feb 2025 23:36:11 +0100 (CET) Authentication-Results: ha01s018.org-dns.com; spf=pass (sender IP is 146.185.68.202) smtp.mailfrom=thiemo@gelassene-pferde.biz smtp.helo=[192.168.178.23] Received-SPF: pass (ha01s018.org-dns.com: connection is authenticated) Message-ID: <3eccccd3-e2f1-47fa-8195-5bcdea13721f@gelassene-pferde.biz> Date: Wed, 5 Feb 2025 23:36:10 +0100 MIME-Version: 1.0 Subject: Re: Lookup tables To: pgsql-general References: <628D4022-5365-4D00-810B-10349A90874A@kleczek.org> <64603f36-b6d5-469b-8d6d-30376842241b@gelassene-pferde.biz> <3A08C6E4-8B03-4D24-BAA7-53860A66C1AF@kleczek.org> <8a118426-7b8a-4d8b-a22b-3bdee2e5e187@gelassene-pferde.biz> <878194FA-E9F8-4688-9904-06B8D2A4DD5C@kleczek.org> Content-Language: de-CH-frami, en-GB, it-CH, fr-CH, es-CL, es-ES From: Thiemo Kellner In-Reply-To: <878194FA-E9F8-4688-9904-06B8D2A4DD5C@kleczek.org> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-PPP-Message-ID: <173879497165.3584999.1011030810686534587@ha01s018.org-dns.com> X-PPP-Vhost: gelassene-pferde.biz X-POWERED-BY: wint.global - AV:CLEAN SPAM:OK List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk El 05-02-25 a las 22:19, Michał Kłeczek escribió: > But you can guarantee that if you change the value of the key after the user displays it - the user will get an error on submission (whereas with the surrogate key it would happily proceed without user noticing). As you very rightly say happily proceed, because that is, what you actually want. The user chose GREE meaning green, which is updated in the meantime to GREEN. All good. Your solution throws an error for nothing. > How so? The user is going to get FK violation - you do not need any downtime to make sure users don’t submit wrong values. Sorry, I was not aware, your aim is to bother the customer with FK violation messages, if you can avoid it. >> Ensure, the update goes from GREE to GREEN? You cannot, simple as that. You just can minimize the risk by testing, testing, testing. > You can also simply disallow updates with FK constraint eliminating risk. This is nothing that is specific to surrogate or natural keys. If one disallow updates, one has to live with typos and everything. >> But that holds equally true for the business key of a surrogate key table as natural key table. That's why the surrogate key is such an elegant construct. You can change business key of the record with id 3 from GREE to GREEN, VERT, GRÜN, VERDE or ASéLDHK()*NSLDFHP)(*Z . It keeps its meaning of the perception of the human eye of electromagnetic waves of the wavelength roughly between 495-570 nm (according to Wikipedia). > And why do you think unconstrained updating of business key is a good thing? I am not sure what is your take on "unconstrained". As already mentioned, testing is the only constraint you can set up. And I am neither sure where your problem is with updating unless you have an update orgy because your ref-constraint goes on natural keys. > You must implement rules governing what can and what cannot be changed *somewhere* - not doing it in the database means you have to do it in applications. That sounds adventurous. Maybe I am not seeing what you mean, but is sound to me that you build a shadow database where you map your GREE to GREEN in the application layer so the application can display the correct value in the GUI, but no one has to update the core database. And if there is a typo in the shadow data base you build another mapping database on top of it? > Anyway - let’s agree to disagree :) Agreed :-) From hjp-pgsql@hjp.at Fri May 15 23:23:30 2026 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tg92w-00857R-1V for pgsql-general@arkaria.postgresql.org; Thu, 06 Feb 2025 21:04:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tg92v-00Bjka-5d for pgsql-general@arkaria.postgresql.org; Thu, 06 Feb 2025 21:04:25 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tg92u-00BjkP-QS for pgsql-general@lists.postgresql.org; Thu, 06 Feb 2025 21:04:24 +0000 Received: from mail.hjp.at ([212.17.106.138] helo=rorschach.hjp.at) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tg92r-003cGX-1S for pgsql-general@lists.postgresql.org; Thu, 06 Feb 2025 21:04:23 +0000 Received: by rorschach.hjp.at (Postfix, from userid 1000) id 23E0565E58; Thu, 6 Feb 2025 22:03:18 +0100 (CET) Date: Thu, 6 Feb 2025 22:03:18 +0100 From: "Peter J. Holzer" To: pgsql-general@lists.postgresql.org Subject: Re: Lookup tables Message-ID: <20250206210318.kj2j5dvliidpmesy@hjp.at> Mail-Followup-To: pgsql-general@lists.postgresql.org References: <628D4022-5365-4D00-810B-10349A90874A@kleczek.org> <64603f36-b6d5-469b-8d6d-30376842241b@gelassene-pferde.biz> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="lk4s4b6oljmuk5rj" Content-Disposition: inline In-Reply-To: <64603f36-b6d5-469b-8d6d-30376842241b@gelassene-pferde.biz> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --lk4s4b6oljmuk5rj Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On 2025-02-04 22:41:38 +0100, Thiemo Kellner wrote: > 04.02.2025 18:31:09 Micha=C5=82 K=C5=82eczek : >=20 > >=20 > >> On 4 Feb 2025, at 18:27, Thiemo Kellner = wrote: > >>=20 > >> =EF=BB=BF Unless the lookup table is actually a check constraint one > >> can use to populate dropdown boxes in an interface. > >=20 > > That is even worse because it ceases being transactional and users > > might select something different than what they see on the screen. >=20 > I might see what you want to point out. E.g. the table is COLOURS. The > rec with id 1 is RED, the one with id 2 is BLUE, 3 is GREE and so on. > Now you load these values into the dropdown box that sports RED, BLUE, > GREE and so on. While someone selects GREE, there is a maintenance > release changing GREE to YELLOW. So when that someone sends the > selection by id to the backend, not GREE is selected but YELLOW. I fail to see why use of a surrogate key is the problem here. Either changing the color from GREE to YELLOW makes sense or it doesn't. If it doesn't make sense, then it's release which is faulty, not the model. if it does make sense (I'm a bit at a loss when that might be the case, maybe the "color" is just a code word, or maybe they are colors in a design which are arbitrary but must be consistent), then the experience that the user has is exactly the same as if the maintenance release was applied just after they selected the color. Which might be a bit confusing but is almost certainly what is wanted. > A) Your release changed the sementics of the record 3. It's meaning > changed. I cannot recommend doing that. If the release changed the semantics of an existing record the release was almost certainly wrong. > B) If you absolutely must change the semantic, put your application > into maintenance mode in which noone can select anything beforehand. >=20 > If the maintenance would just correct the typo from GREE to GREEN, > nothing would happen. Yor customer still ordered the lavishly green > E-Bike her hear ever desired. Yeah, that's a good example where changing the color from GREE to YELLOW doesn't make sense. Presumably that ID 3 is used as a foreign key in lots of places, e,g. in an inventory table. Your bikes in stock won't just magically change color just because you changed some text in the database. So that change simply doesn't make sense and shouldn't be done as part of a maintenance release. Confusing a few people who just happen to open the dropdown in the wrong second is the least of your problems. hp --=20 _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" --lk4s4b6oljmuk5rj Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmelI48ACgkQ8g5IURL+ KF3r7w//bz1Ct2AFJzm1mwOzQe1fvpwg4uU1deyawXygl5ibOnISRqe6mX3BFIdq Bw7/DnD7upqTGpi/CRiZw1oPLTGaTPaAGjbb1gI16QNvPl8uw3Q3lorjsltR6EhH gzQbOQfkbscIzNq9MBRP4YsUyTun4l1svbUGTwDjRT2wFGRmCLeHtebBo194Pd25 JCYbOt75Mwhd1C4jRGFFZowTpXm2hMsO4cY6Pe4fMOQUp3lfIZAViuLlm+6bewMl aNP105LMUKLXFI7TtbAgby78JYHLmqmhFQhD8x1iHhjlvbY6Jz6x4mbkSBkHccmx QzPOU+lZUrUNiv74OCgDXOZkgakE85cLSRrrNQr7uaSSIaXwCVNpmdPyNozyMqKV DIGyyTe62KUFx7i539jZ4tbbmhoPrO6+aOPJ4SRuzHloN5Dy1R49zpaIPG3O/Aw3 2A+TnZLYta/G512YR9dUp876gSUA1Tc8vKxxZODelPu/VcMTzhdVl/Umih0SXehL VpzLdO3XuYrIRNhYmw+E9DrfFmfDxhWshQshCU9rNd6dj2iWx9q5TN+S4dF/RXxz +PHgK/xxeHTlcZjEvPegVWkLZDPJJOHLe42hPfByJakn6VRk0rTfurvZ2L4AHI1M 0yyJll2IOfBqAubalWuurpNKMPKFvpQhvCZeuhjCrc60Hsd7iDM= =IynY -----END PGP SIGNATURE----- --lk4s4b6oljmuk5rj-- From michal@kleczek.org Fri May 15 23:22:45 2026 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tgJd8-009X9H-Dr for pgsql-general@arkaria.postgresql.org; Fri, 07 Feb 2025 08:22:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tgJd7-00Gyxg-H2 for pgsql-general@arkaria.postgresql.org; Fri, 07 Feb 2025 08:22:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tgJd7-00GyxV-2U for pgsql-general@lists.postgresql.org; Fri, 07 Feb 2025 08:22:29 +0000 Received: from mail-ed1-x530.google.com ([2a00:1450:4864:20::530]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tgJd4-004KkK-1D for pgsql-general@lists.postgresql.org; Fri, 07 Feb 2025 08:22:28 +0000 Received: by mail-ed1-x530.google.com with SMTP id 4fb4d7f45d1cf-5dced61e5a3so3222960a12.0 for ; Fri, 07 Feb 2025 00:22:26 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=kleczek.org; s=google; t=1738916546; x=1739521346; darn=lists.postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=FTFMD8t1pLkNZJFlzWtQTS9uCzO5RprFqDKXOjxMB5k=; b=OI3pfB7KREjBCR6+51/+XWWiiDyn77urRSWr9HftdjvRvh8ldxv+X2ad5vwT2TErdH uNdrbwPUtXEo2UNeJb9VN2fywQnV97nWm0wWOo8g2s6oyPmEUkrHjG9KbYZhX3fIjoPU tAE6v2OaizsEoN3qJ5oIQ5kxKI9dgr7fPk2vw= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1738916546; x=1739521346; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=FTFMD8t1pLkNZJFlzWtQTS9uCzO5RprFqDKXOjxMB5k=; b=Ap9FEY7lMNDDGaKpd4oxw9I+ZptUOrpKt1MIGgpnjJ0pvnE2OgYTCKipyUHicGaqRx 4mk9eFWHgiuvAJSRZaj6wsZslROEj4v6UlI+ejBUKUgd+wQWUujCTRvUmE78YAwzqhQP 67D2RT+RcWcFf0Y2PeG15LVGyDSg+HCSf5AlavYqXSJ+Fon7sAL1GfdlvuiEpm7Y1dUT sXGGUnfyoD+F1tL9S7zDAOgdr9NAxkQJg8TAqHnHnDqTKhbadeWuYZM6qWMPDLCH6o0o 9QmixHAZI2QWH8bh8FINGtjfwl+Nvq+E5uxNtR02qnVd32EJkluydLsqPhpgX4/Ri6sh ZGXQ== X-Gm-Message-State: AOJu0Yx8SoAm5Za+5Ixwb5Tn1ZJOGdTVK73k2GGtH1/VGg757QNmlB2D FvUtkLvoA3gR5HcVcfSC5OG41qWLByZ0bPrXNuBqV6gcF7ooSXx5kLIgCfBhtY3ofduf2jmzTDm O3no= X-Gm-Gg: ASbGncvp06mvepZg/zi58z1JwiAH/HEhovN5IfA8hs3qq5quP0xXz81RI4vaPm7eZ8Z 6bNLjc9s0xVQxVjCu+7yi0u7+yFdjEXPw4S5tL0PbdmcG5s4991m28P7VIxGvViAUSO27dfnd1A onbgg4hG2xUZnYQbJmqT02PRxhnc9Stnu+opN7KtVSUPSLJ9hIDARCTxKYYzP3TB4iokbFr1rO5 tNsVF0OEpFZQE5RKvvLpfGISNVWoo7rjYRkKjAHSz0fxwLVYWR4YcZqv7uKQtSWti/L/SS8VMW+ pe3ERv2H9G+l0OwYCN8F8iYV8Zt2WAllTwegnas/mh0= X-Google-Smtp-Source: AGHT+IG+2Laljq4AQsKbD6uSgSrMwPT3O2cMvI0WSMpNxvR8lwaq2pNR7+H5ixacCkqV8JfcAnfv8A== X-Received: by 2002:a17:906:6a93:b0:aa6:a844:8791 with SMTP id a640c23a62f3a-ab789cbe584mr178485066b.45.1738916545193; Fri, 07 Feb 2025 00:22:25 -0800 (PST) Received: from smtpclient.apple (b15.kleczek.org. [185.20.172.182]) by smtp.gmail.com with ESMTPSA id a640c23a62f3a-ab77dcb9336sm171163866b.27.2025.02.07.00.22.24 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 07 Feb 2025 00:22:24 -0800 (PST) From: =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_304B57CB-BC71-47CC-B3B0-94D4F3CEE457" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.300.87.4.3\)) Subject: Re: Lookup tables Date: Fri, 7 Feb 2025 09:22:13 +0100 In-Reply-To: <20250206210318.kj2j5dvliidpmesy@hjp.at> Cc: pgsql-general@lists.postgresql.org To: "Peter J. Holzer" References: <628D4022-5365-4D00-810B-10349A90874A@kleczek.org> <64603f36-b6d5-469b-8d6d-30376842241b@gelassene-pferde.biz> <20250206210318.kj2j5dvliidpmesy@hjp.at> X-Mailer: Apple Mail (2.3826.300.87.4.3) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_304B57CB-BC71-47CC-B3B0-94D4F3CEE457 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On 6 Feb 2025, at 22:03, Peter J. Holzer wrote: >=20 > On 2025-02-04 22:41:38 +0100, Thiemo Kellner wrote: >>=20 >> I might see what you want to point out. E.g. the table is COLOURS. = The >> rec with id 1 is RED, the one with id 2 is BLUE, 3 is GREE and so on. >> Now you load these values into the dropdown box that sports RED, = BLUE, >> GREE and so on. While someone selects GREE, there is a maintenance >> release changing GREE to YELLOW. So when that someone sends the >> selection by id to the backend, not GREE is selected but YELLOW. >=20 > I fail to see why use of a surrogate key is the problem here. >=20 > Either changing the color from GREE to YELLOW makes sense or it = doesn't. >=20 > If it doesn't make sense, then it's release which is faulty, not the > model. >=20 > if it does make sense (I'm a bit at a loss when that might be the = case, > maybe the "color" is just a code word, or maybe they are colors in a > design which are arbitrary but must be consistent), then the = experience > that the user has is exactly the same as if the maintenance release = was > applied just after they selected the color. Which might be a bit > confusing but is almost certainly what is wanted. >=20 This is simply saying =E2=80=9Cwhat is implemented is certainly wanted, = so what=E2=80=99s the point=E2=80=9D. The discussion is about *defining* what is wanted and using DBMS to = *enforce* that. >=20 >> A) Your release changed the sementics of the record 3. It's meaning >> changed. I cannot recommend doing that. >=20 > If the release changed the semantics of an existing record the release > was almost certainly wrong. Is it possible to minimize the risk of =E2=80=9Cwrong releases=E2=80=9D = using mechanisms that DBMS provides? >=20 >> B) If you absolutely must change the semantic, put your application >> into maintenance mode in which noone can select anything beforehand. >>=20 >> If the maintenance would just correct the typo from GREE to GREEN, >> nothing would happen. Yor customer still ordered the lavishly green >> E-Bike her hear ever desired. >=20 > Yeah, that's a good example where changing the color from GREE to = YELLOW > doesn't make sense. Presumably that ID 3 is used as a foreign key in > lots of places, e,g. in an inventory table. Your bikes in stock won't > just magically change color just because you changed some text in the > database. So that change simply doesn't make sense and shouldn't be = done > as part of a maintenance release. Confusing a few people who just = happen > to open the dropdown in the wrong second is the least of your = problems. We are in agreement here. What I am also saying is: having color name as PK and all FKs referencing it *prevents* these = unwanted updates. =E2=80=94 Michal= --Apple-Mail=_304B57CB-BC71-47CC-B3B0-94D4F3CEE457 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On 6 Feb 2025, at 22:03, Peter J. Holzer = <hjp-pgsql@hjp.at> wrote:

On 2025-02-04 22:41:38 +0100, Thiemo = Kellner wrote:

I might see what you want to point out. E.g. = the table is COLOURS. The
rec with id 1 is RED, the one with id 2 is = BLUE, 3 is GREE and so on.
Now you load these values into the = dropdown box that sports RED, BLUE,
GREE and so on. While someone = selects GREE, there is a maintenance
release changing GREE to YELLOW. = So when that someone sends the
selection by id to the backend, not = GREE is selected but YELLOW.

I fail = to see why use of a surrogate key is the problem here.

Either changing the color from GREE to = YELLOW makes sense or it doesn't.

If it doesn't make = sense, then it's release which is faulty, not the
model.

if it = does make sense (I'm a bit at a loss when that might be the = case,
maybe the "color" is = just a code word, or maybe they are colors in a
design which are arbitrary but must be = consistent), then the experience
that = the user has is exactly the same as if the maintenance release = was
applied just after they = selected the color. Which might be a bit
confusing but is almost certainly what is wanted.


This is simply saying = =E2=80=9Cwhat is implemented is certainly wanted, so what=E2=80=99s the = point=E2=80=9D.

The discussion is about = *defining* what is wanted and using DBMS to *enforce* = that.


A) Your release changed the sementics of the = record 3. It's meaning
changed. I cannot recommend doing = that.

If the = release changed the semantics of an existing record the = release
was almost certainly = wrong.

Is it possible to minimize = the risk of =E2=80=9Cwrong releases=E2=80=9D using mechanisms that DBMS = provides?


B) If you absolutely must change the semantic, put your = application
into maintenance mode in which noone can select anything = beforehand.

If the maintenance would just correct the typo from = GREE to GREEN,
nothing would happen. Yor customer still ordered the = lavishly green
E-Bike her hear ever desired.

Yeah, that's a good example where changing = the color from GREE to YELLOW
doesn't = make sense. Presumably that ID 3 is used as a foreign key in
lots of places, e,g. in an inventory table. = Your bikes in stock won't
just = magically change color just because you changed some text in = the
database. So that change = simply doesn't make sense and shouldn't be done
as part of a maintenance release. Confusing = a few people who just happen
to open = the dropdown in the wrong second is the least of your = problems.

We are in agreement here. = What I am also saying is:
having color name as PK and all FKs = referencing it *prevents* these unwanted = updates.

=E2=80=94
Michal
= --Apple-Mail=_304B57CB-BC71-47CC-B3B0-94D4F3CEE457-- From hjp-pgsql@hjp.at Fri May 15 23:22:34 2026 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tgTLU-00Ayvf-Me for pgsql-general@arkaria.postgresql.org; Fri, 07 Feb 2025 18:44:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tgTLT-004Svg-A2 for pgsql-general@arkaria.postgresql.org; Fri, 07 Feb 2025 18:44:55 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tgTIu-004OOg-Qw for pgsql-general@lists.postgresql.org; Fri, 07 Feb 2025 18:42:16 +0000 Received: from mail.hjp.at ([212.17.106.138] helo=rorschach.hjp.at) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tgTIo-003oVX-30 for pgsql-general@lists.postgresql.org; Fri, 07 Feb 2025 18:42:15 +0000 Received: by rorschach.hjp.at (Postfix, from userid 1000) id 44D43671F8; Fri, 7 Feb 2025 19:42:08 +0100 (CET) Date: Fri, 7 Feb 2025 19:42:08 +0100 From: "Peter J. Holzer" To: pgsql-general@lists.postgresql.org Subject: Re: Lookup tables Message-ID: <20250207184208.y6htcxbhlravtrte@hjp.at> Mail-Followup-To: pgsql-general@lists.postgresql.org References: <628D4022-5365-4D00-810B-10349A90874A@kleczek.org> <64603f36-b6d5-469b-8d6d-30376842241b@gelassene-pferde.biz> <20250206210318.kj2j5dvliidpmesy@hjp.at> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="iqn7eqyu362qmivx" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --iqn7eqyu362qmivx Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On 2025-02-07 09:22:13 +0100, Micha=C5=82 K=C5=82eczek wrote: >=20 >=20 > On 6 Feb 2025, at 22:03, Peter J. Holzer wrote: >=20 > On 2025-02-04 22:41:38 +0100, Thiemo Kellner wrote: >=20 >=20 > I might see what you want to point out. E.g. the table is COLOURS= =2E The > rec with id 1 is RED, the one with id 2 is BLUE, 3 is GREE and so= on. > Now you load these values into the dropdown box that sports RED, = BLUE, > GREE and so on. While someone selects GREE, there is a maintenance > release changing GREE to YELLOW. So when that someone sends the > selection by id to the backend, not GREE is selected but YELLOW. >=20 >=20 > I fail to see why use of a surrogate key is the problem here. >=20 > Either changing the color from GREE to YELLOW makes sense or it doesn= 't. >=20 >=20 > If it doesn't make sense, then it's release which is faulty, not the > model. >=20 > if it does make sense (I'm a bit at a loss when that might be the cas= e, > maybe the "color" is just a code word, or maybe they are colors in a > design which are arbitrary but must be consistent), then the experien= ce > that the user has is exactly the same as if the maintenance release w= as > applied just after they selected the color. Which might be a bit > confusing but is almost certainly what is wanted. >=20 >=20 >=20 > This is simply saying =E2=80=9Cwhat is implemented is certainly wanted, Not at all. It may or may not be what is wanted but we can't tell unless we know what is wanted. We have a single table without any context. What does this table represent? What is the identity here? If it's the color itself, changing green to yellow doesn't make any sense ("it's the same color, but now it's yellow instead of green" is self-contradictory). If the identity has some application-defined semantics, it may make sense. > so what=E2=80=99s the point=E2=80=9D. Indeed. Without knowing the semantics discussing the data model or the advisability of certain updates is indeed rather pointless. Disagreements will be more likely to be due to unstated assumptions about the application than because of general principles. > A) Your release changed the sementics of the record 3. It's meani= ng > changed. I cannot recommend doing that. >=20 >=20 > If the release changed the semantics of an existing record the release > was almost certainly wrong. >=20 >=20 > Is it possible to minimize the risk of =E2=80=9Cwrong releases=E2=80=9D u= sing mechanisms that > DBMS provides? Maybe. But the person who thought that update colors set name =3D 'YELLOW' where id =3D 3; -- YELLOW is the ne= w GREE was a good idea might also think that=20 update doodads set color =3D 'YELLOW' where color =3D 'GREE'; is a good idea. Is it? I don't know. Maybe it is. Maybe it isn't. There is no way to know unless we know the requirements. > If the maintenance would just correct the typo from GREE to GREEN, > nothing would happen. Yor customer still ordered the lavishly gre= en > E-Bike her hear ever desired. >=20 >=20 > Yeah, that's a good example where changing the color from GREE to YEL= LOW > doesn't make sense. Presumably that ID 3 is used as a foreign key in > lots of places, e,g. in an inventory table. Your bikes in stock won't > just magically change color just because you changed some text in the > database. So that change simply doesn't make sense and shouldn't be d= one > as part of a maintenance release. Confusing a few people who just hap= pen > to open the dropdown in the wrong second is the least of your problem= s. >=20 >=20 > We are in agreement here. What I am also saying is: > having color name as PK and all FKs referencing it *prevents* these unwan= ted > updates. I'm not convinced. hp --=20 _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" --iqn7eqyu362qmivx Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmemU/oACgkQ8g5IURL+ KF3w1w/+Ko8VJWc/6H76AsuxwzUb6vP54DqD4TaBXr5/JiRp1mIkmu7foN3wBiAv quuM/FQBjJckgeaXQ9litZHB41HRDkyjUUA9nJuEkxPtIjH2B3mGgWcB3YUVpP8J S+yuz6AbQquivdE/SuM6E13zRH9B2CBQWlFVLBgzQ2lNBP57uG2qKR27xkmkEe7j TIgdVaIow5V2MIsOOlcFJ3mT31ssujgLflCxa8UpKblS7kNqi0Pdza01cA6ZFP07 Q61AeNTZxhKsm1zAsuE2E6bUMX614nt95CRUiBD+Q40S+01ZGLVQmhLsnDd8MZWi aZkx20RyFVme8yu2/w4n9NR5u+s5jIWf+dhO5uI+8/dspafauXo33ogEVPS7Oos2 uQ+lqF3aaCiHVjDbNrpJ1PdV3uVhgB8xiyXRdJUA+CVOj3TkrovoxnoaKar/t2O/ UFPO5zmOLNPn0XebYs6Bsmq/VY2q2L+Ldwm4u/6vjuWO+nuXKpQ6qe8q6JJeEa2i j7Lpqlr1jggubgkeuvsUBAOGrsKLTqNidI7+07CZcOvb7gUGuftZpnb0bnCM+oK2 vDR43R54yk1nMIT7XeExWr0ZR0C+Icu4Zr429YHwWQ9EByhVfbZSZBV8Dxr97IP8 NogzGfFoAW7rTGPPu1bg9bbBFGCpCZgydJgEALvTQa1XIFqhU9s= =XjjO -----END PGP SIGNATURE----- --iqn7eqyu362qmivx--