Subject: Re: [18] Policy on IMMUTABLE functions and Unicode updates

From: Jeff Davis <[email-masked]>
Date: Wed, 24 Jul 2024 12:12:34 -0700

On Wed, 2024-07-24 at 14:47 -0400, Robert Haas wrote:
> On Wed, Jul 24, 2024 at 1:45 PM Jeff Davis <[email-masked]> wrote:
> > There's a qualitative difference between a collation update which
> > can
> > break your PKs and FKs, and a ctype update which definitely will
> > not.
> 
> I don't think that's true. All you need is a unique index on
> UPPER(somecol).

Primary keys are on plain column references, not expressions; and don't
support WHERE clauses, so I don't see how a ctype update would affect a
PK.

In any case, you are correct that Unicode updates could put some
constraints at risk, including unique indexes, CHECK, and partition
constraints. But someone has to actually use one of the affected
functions somewhere, and that's the main distinction that I'm trying to
draw.

The reason why collation is qualitatively a much bigger problem is
because there's no obvious indication that you are doing anything
related to collation at all. A very plain "CREATE TABLE x(t text
PRIMARY KEY)" is at risk.

Regards,
	Jeff Davis

---

From: Robert Haas <[email-masked]>
Date: Wed, 24 Jul 2024 15:19:55 -0400

> PRIMARY KEY)" is at risk.

Well, I don't know. I agree that collation is a much bigger problem,
but not for that reason. I think a user who is familiar with the
problems in this area will see the danger either way, and one who
isn't, won't. For me, the only real difference is that a unique index
on a text column is a lot more common than one that involves UPPER.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

---

From: Jeremy Schneider <[email-masked]>
Date: Wed, 24 Jul 2024 13:43:44 -0600

On Wed, Jul 24, 2024 at 12:47 PM Robert Haas <[email-masked]> wrote:

On Wed, Jul 24, 2024 at 1:45 PM Jeff Davis <[email-masked]> wrote:
> There's a qualitative difference between a collation update which can
> break your PKs and FKs, and a ctype update which definitely will not.

I don't think that's true. All you need is a unique index on UPPER(somecol).

I doubt it’s common to have unique on upper()

But non-unique indexes for case insensitive searches will be more common.
Historically this is the most common way people did case insensitive on
oracle.

Changing ctype would mean these queries can return wrong results

The impact would be similar to the critical problem TripAdvisor hit in 2014
with their read replicas, in the Postgres email thread I linked above

-Jeremy

---

From: Robert Haas <[email-masked]>
Date: Wed, 24 Jul 2024 15:47:12 -0400

On Wed, Jul 24, 2024 at 3:43 PM Jeremy Schneider
<[email-masked]> wrote:
> But non-unique indexes for case insensitive searches will be more common. Historically this is the most common way people did case insensitive on oracle.
>

Yeah. I mentioned earlier that I very recently saw a customer query
with UPPER() in the join condition. If someone is doing foo JOIN bar
ON upper(foo.x) = upper(bar.x), it is not unlikely that one or both of
those expressions are indexed. Not guaranteed, of course, but very
plausible.