From: Tom Lane <tgl@sss.pgh.pa.us>
To: andrewbille@gmail.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17746: Partitioning by hash of a text depends on icu version
 when text collation is not deterministic
In-reply-to: <17746-83bc7dd7e202dc70@postgresql.org>
References: <17746-83bc7dd7e202dc70@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Wed, 11 Jan 2023 15:09:21 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <530247.1673458746.1@sss.pgh.pa.us>
Date: Wed, 11 Jan 2023 12:39:06 -0500
Message-ID: <530248.1673458746@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/530248.1673458746%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> What bothers me is that partitioning depends on the hash that can be
> computed differently with the OS upgrade/migration.

There's basically no way to avoid such problems with a non-deterministic
collation.  The hash function is required to compute the same hash for
all values that compare equal, and that set can change if the collation
does.  Even if the collation hasn't changed in any user-visible way,
what we are hashing for such cases is the result of ucol_getSortKey(),
and the new collation version might well produce a different answer.

Personally, I think hash partitioning is an anti-pattern that ought
to come with bright red warning flags in the docs.  If you think you
want it, you're generally wrong, for a number of reasons beyond this.

(Admittedly, range partitioning can also get broken by collation
updates, but at least that doesn't happen without user-visible
behavioral changes in the collation.)

			regards, tom lane