pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Karsten Hilbert <Karsten.Hilbert@gmx.net>
To: Daniel Verite <daniel@manitou-mail.org>
Cc: Christophe Pettus <xof@thebuild.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Q: fixing collation version mismatches
Date: Mon, 14 Nov 2022 18:31:43 +0100
Message-ID: <Y3J7f9HY3AivTbTz@hermes.hilbert.loc> (raw)
In-Reply-To: <206a0d6b-d91d-4fc9-a467-2bcf426fed06@manitou-mail.org>
References: <Y3FojZ4gPMoJuMfx@hermes.hilbert.loc>
	<206a0d6b-d91d-4fc9-a467-2bcf426fed06@manitou-mail.org>

Am Mon, Nov 14, 2022 at 05:42:16PM +0100 schrieb Daniel Verite:

> > Which is why my question still stands: does the above
> > three-strikes operation safely take care of any collation
> > issues that may currently exist in a database ?
>
> For the indexes, yes, but theorically, all constraints involving collatable
> types need a recheck.
>
> For foreign key constraints with non-deterministic collations, there
> might be equality tests that pass with an older Unicode version and fail
> with a newer Unicode version.

Which gives weight to the argument that using real-world data
(instead of surrogate keys) may lead to trouble.

> For check constraints as well, checks applied to strings with recent
> Unicode characters can give different results after an upgrade.

Thanks for pointing this out more clearly. My thinking
already evolved towards also including VALIDATE CONSTRAINT.

I shall, for the record, update the sequence in question:

	-- indices
	REINDEX DATABASE db_in_question;
	-- constraints (check, foreign key)
	UPDATE pg_constraint SET convalidated = false WHERE all_check_and_FK_constraints;
	ALTER TABLE table_with_constraint VALIDATE CONSTRAINT constraint_on_that_table;
	-- other things, see below
	-- ...
	-- refresh collation versions if no errors above
	ALTER DATABASE db_in_question REFRESH COLLATION VERSION;
	ALTER COLLATION every_collation_from_pg_collation REFRESH VERSION;

What else needs to be taken care of, and how ?

partitions

	Need to re-sort rows into the proper partition as needed.

	Can this be achievd by

		UPDATE each_partitioned_table SET each_partitioned_key = each_partitioned_key;

	?

	Courtesy of (ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION):

		Updating the partition key of a row will cause it to
		be moved into a different partition if it no longer
		satisfies the partition bounds of its original
		partition.

range types

	Will this

		UPDATE table_with_range_type_column SET ranged_column = ranged_column

	find all relevant issues ?

domains

	Will this

		UPDATE table_with_domain_type_column SET domained_column = domained_column

	find all relevant issues ?

custom types

	??

function immutability ??

	It can be argued that functions marked IMMUTABLE really
	are not in case they involve sorting of a collatable data
	type, and are thus wrongly marked as IMMUTABLE.

	IOW pre-existing user error.

If all this has been discussed in detail, I'd be glad for a
pointer into the archive.

Thanks,
Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B





view thread (7+ messages)

Message-ID: <Y3J7f9HY3AivTbTz@hermes.hilbert.loc>
Permalink:  ../Y3J7f9HY3AivTbTz@hermes.hilbert.loc/
Also on:    postgresql.org/message-id/Y3J7f9HY3AivTbTz@hermes.hilbert.loc

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: Karsten.Hilbert@gmx.net, daniel@manitou-mail.org, xof@thebuild.com
  Subject: Re: Q: fixing collation version mismatches
  In-Reply-To: <Y3J7f9HY3AivTbTz@hermes.hilbert.loc>
  References: <Y3FojZ4gPMoJuMfx@hermes.hilbert.loc> <206a0d6b-d91d-4fc9-a467-2bcf426fed06@manitou-mail.org> <Y3J7f9HY3AivTbTz@hermes.hilbert.loc>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox