Date: Sun, 16 Sep 2018 17:02:15 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Thomas Munro <thomas.munro@enterprisedb.com>
Cc: Douglas Doole <dougdoole@gmail.com>,
	Peter Eisentraut <peter.eisentraut@2ndquadrant.com>,
	Christoph Berg <myon@debian.org>,
	Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Collation versioning
Message-ID: <20180916210215.GE4184@tamriel.snowman.net>
References: <b7ce31bb-dfe5-c689-06c4-3a8536f73e6a@2ndquadrant.com>
 <CAEepm=01SJne1beXkuU0XxO3UdoG+n+BaE9X_ADXjG95s0nqbQ@mail.gmail.com>
 <CAEepm=1xGTsLDx63UEdcJ8MdG63CNJ-tsDWHbH9djtvxRH5ZWw@mail.gmail.com>
 <20180912081547.GA24584@msg.df7cb.de>
 <0447ec7b-cdb6-7252-7943-88a4664e7bb7@2ndquadrant.com>
 <20180912112548.GC24584@msg.df7cb.de>
 <4f60612c-a7b5-092d-1532-21ff7a106bd5@2ndquadrant.com>
 <CAEepm=33gHF92UD8bkoZzzbs+GkVhHFzgV4CPf8ZxFRqZ_UCHQ@mail.gmail.com>
 <CADE5jYKn6FA0a35P+_HJuSb_abq-D5Z-nE6rO9brjpu8jBTMgA@mail.gmail.com>
 <CAEepm=3-rxTzk3anR1QA=tuNrbAQ_ejJ2rj5Hp_y5hdJQr=rEw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="l118U0+vX1D/6gtA"
Content-Disposition: inline
In-Reply-To: 
 <CAEepm=3-rxTzk3anR1QA=tuNrbAQ_ejJ2rj5Hp_y5hdJQr=rEw@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--l118U0+vX1D/6gtA
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Thomas Munro (thomas.munro@enterprisedb.com) wrote:
> On Mon, Sep 17, 2018 at 6:13 AM Douglas Doole <dougdoole@gmail.com> wrote:
> > On Sun, Sep 16, 2018 at 1:20 AM Thomas Munro <thomas.munro@enterprisedb=
=2Ecom> wrote:
> >> 3.  Fix the tracking of when reindexes need to be rebuilt, so that you
> >> can't get it wrong (as you're alluding to above).
> >
> > I've mentioned this in the past, but didn't seem to get any traction, s=
o I'll try it again ;-)
>=20
> Probably because we agree with you, but don't have all the answers :-)

Agreed.

> > The focus on indexes when a collation changes is, in my opinion, the le=
ast of the problems. You definitely have to worry about indexes, but they c=
an be easily rebuilt. What about other places where collation is hardened i=
nto the system, such as constraints?
>=20
> We have to start somewhere and indexes are the first thing that people
> notice, and are much likely to actually be a problem (personally I've
> encountered many cases of index corruption due to collation changes in
> the wild, but never a constraint corruption, though I fully understand
> the theoretical concern).  Several of us have observed specifically
> that the same problems apply to CHECK constraints and PARTITION
> boundaries, and there may be other things like that.  You could
> imagine tracking collation dependencies on those, requiring a RECHECK
> or REPARTITION operation to update them after a depended-on collation
> version changes.
>=20
> Perhaps that suggests that there should be a more general way to store
> collation dependencies -- something more like pg_depend, rather than
> bolting something like indcollversion onto indexes and every other
> kind of catalog that might need it.  I don't know.

Agreed.  If we start thinking about pg_depend then maybe we realize
that this all comes back to pg_attribute as the holder of the
column-level information and maybe what we should be thinking about is a
way to encode version information into the typmod for text-based
types...

> > And constraints problems are even easier than triggers. Consider a data=
base with complex BI rules that are implemented through triggers that fire =
when values are/are not equal. If the equality of strings change, there cou=
ld be bad data throughout the tables. (At least with constraints the inter-=
column dependencies are explicit in the catalogs. With triggers anything go=
es.)
>=20
> Once you get into downstream effects of changes (whether they are
> recorded in the database or elsewhere), I think it's basically beyond
> our event horizon.  Why and when did the collation definition change
> (bug fix in CLDR, decree by the Acad=E9mie Fran=E7aise taking effect on 1
> January 2019, ...)?  We could all use bitemporal databases and
> multi-version ICU, but at some point it all starts to look like an
> episode of Dr Who.  I think we should make a clear distinction between
> things that invalidate the correct working of the database, and more
> nebulous effects that we can't possibly track in general.

I tend to agree in general, but I don't think it's beyond us to consider
multi-version ICU and being able to perform online reindexing (such that
a given system could be migrated from one collation to another over a
time while the system is still online, instead of having to take a
potentially long downtime hit to rebuild indexes after an upgrade, or
having to rebuild the entire system using some kind of logical
replication...).

Thanks!

Stephen

--l118U0+vX1D/6gtA
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJbnsTXAAoJEO1sijiDR2RVk5MQAK2Jjp0s/HNH6mVlI8KdjQuS
dLnFSTBxEHMFO7zSNKrqEfa/+q2zeIqPkCOUfAaCU26u+kmpVfiUvGWSK5ijB0El
/HLKzA521ON9zSGcsM3yH+G9o7g6m7Li4CjUxl45ddjSmdsMai7CF/ILTq0lMou4
Ave4f2d31A/SUgfbqgP+cAjmcY22OGmWbj2Kbc2vOla6Iq3Tabj5vS6PWtnWduek
CX0gsQ4GuUIJDsqIx073cSf8q3HOTYzbuIUtlHiKDMTpBCwXwK8kPnhsbsibT5o1
25N7d78piFD3BkpBGHtL3WftCs6BJ3myZJX30HncxntHPikY5hYm0aBCody/D+ld
RjzTYK+bIhZMsJ8CoD7elX8rr0VOtKYj+rDDvQxQUPdJtzFZNHWVIcIoRkqhxwcR
8AZZvVu3BEfA5Jb2SDlDp82WbtdTYav7I5asfoD/NCJ0TyClTBcikdR71jJUlzWr
+PyFSfL9yG4vkYYl3PUiYx0232mXFvJ65BjVcelklvQD+b4IdA7lP7FKVprxF4KS
gOpPyFMb4V3ZpnxNmeVvdV0OOCEr8ISDPDnG29t1Wi1HgUToZmJVQHmfvQTH9pTa
DBgKGQaf6PZtdm7RhazGl2UnkVR1oHyEYS1nOyP6UT5XYRHGk8HgZdZXM4r1esJh
g0o/d3VTrrJaY2DdHeq2
=svSO
-----END PGP SIGNATURE-----

--l118U0+vX1D/6gtA--