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 <pgsql-general@lists.postgresql.org>
References: <c0fe3bc0-efb2-4648-ac9a-435601acc2bf@gelassene-pferde.biz>
 <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 <thiemo@gelassene-pferde.biz>
In-Reply-To: <3A08C6E4-8B03-4D24-BAA7-53860A66C1AF@kleczek.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/8a118426-7b8a-4d8b-a22b-3bdee2e5e187%40gelassene-pferde.biz>
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).