Message-ID: <7c9f8c8b-e5ca-4675-bc1c-f27789059ee7@proxel.se>
Date: Fri, 13 Sep 2024 15:05:49 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Mutable foreign key constraints
To: Laurenz Albe <laurenz.albe@cybertec.at>, Tom Lane <tgl@sss.pgh.pa.us>,
 pgsql-hackers@lists.postgresql.org
References: <4162598.1726176826@sss.pgh.pa.us>
 <560b5873c9b8129a4b7b4fe3239ec32363168599.camel@cybertec.at>
Content-Language: en-US
From: Andreas Karlsson <andreas@proxel.se>
In-Reply-To: <560b5873c9b8129a4b7b4fe3239ec32363168599.camel@cybertec.at>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/7c9f8c8b-e5ca-4675-bc1c-f27789059ee7%40proxel.se>
Precedence: bulk

On 9/13/24 4:41 AM, Laurenz Albe wrote:
> That's very broken and should not be allowed.

+1

>> A possible objection is that if anybody has such a setup and
>> hasn't noticed a problem because they never change their
>> timezone setting, they might not appreciate us breaking it.
> 
> I hope that there are few cases of that in the field, and I think it
> is OK to break them.  After all, it can be fixed with a simple
> 
>    ALTER TABLE foo ALTER id TYPE timestamptz;
> 
> If the session time zone is UTC, that wouldn't even require a rewrite.
> 
> I agree that it cannot be backpatched.

I unfortunately suspect there might be more cases than we think in the 
field due to many people not understanding the difference between 
timestamp and timestamptz but the good thing is that 
timestamp/timestamptz are rare in foreign keys, even in composite ones.

Since this is quite broken and does not have any real world usefulness I 
think we should just go ahead and disallow it and have a few people 
complain.

Andreas