Date: Tue, 18 Sep 2018 08:48:35 -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: <20180918124835.GZ4184@tamriel.snowman.net>
References: 
 <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>
 <20180916210215.GE4184@tamriel.snowman.net>
 <CAEepm=1wPRi4-YZaSvLXhpzO-6BEyPjBz0qBGpuQ-nmYKAhuvQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="u6ak1upF5j4uU0dQ"
Content-Disposition: inline
In-Reply-To: 
 <CAEepm=1wPRi4-YZaSvLXhpzO-6BEyPjBz0qBGpuQ-nmYKAhuvQ@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--u6ak1upF5j4uU0dQ
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Thomas Munro (thomas.munro@enterprisedb.com) wrote:
> On Mon, Sep 17, 2018 at 9:02 AM Stephen Frost <sfrost@snowman.net> wrote:
> > * Thomas Munro (thomas.munro@enterprisedb.com) wrote:
> > > On Mon, Sep 17, 2018 at 6:13 AM Douglas Doole <dougdoole@gmail.com> w=
rote:
> > > > On Sun, Sep 16, 2018 at 1:20 AM Thomas Munro <thomas.munro@enterpri=
sedb.com> 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 tractio=
n, so I'll try it again ;-)
> > >
> > > 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, th=
e least of the problems. You definitely have to worry about indexes, but th=
ey can be easily rebuilt. What about other places where collation is harden=
ed into the system, such as constraints?
> > >
> > > 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.
> > >
> > > 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...
>=20
> So to be more concrete: pg_depend could have a new column
> "refobjversion".  Whenever indexes are created or rebuilt, we'd
> capture the current version string in the pg_depend rows that link
> index attributes and collations.  Then we'd compare those against the
> current value when we first open an index and complain if they don't
> match.  (In this model there would be no "collversion" column in the
> pg_collation catalog.)

I'm really not sure why you're pushing to have this in pg_depend..

> That'd leave a place for other kinds of database objects (CHECKs,
> PARTITIONS, ...) to store their version dependency, if someone later
> wants to add support for that.

Isn't what matters here where the data's stored, as in, in a column..?

All of those would already have dependencies on the column so that they
can be tracked back there.

> I'm not sure if my idea about updating the default collation row in
> newly created databases has legs though.  Any thoughts on that?

My initial reaction is that we should have a version included basically
everywhere and then let users decide how they want to change it.  For a
new cluster, I'd agree with using the latest available (while allowing
it to be chosen if a user wishes for something else) but I'm not sure
I'd go farther than that.

Thanks!

Stephen

--u6ak1upF5j4uU0dQ
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIcBAEBCgAGBQJboPQjAAoJEO1sijiDR2RVfuwQALexa2+ySsJl6Q7qdKzUrNid
5g3cLQuCXmtFfwPPXhRD30aiL9NlaCO2AX2omWILpw14/ZxaY0xT9vEAoZEmvv9A
plAwWWjGu1rv0ALVjiurTIHQryHXKqoUo5UvWSQBB6iK0XrW4roQAebyXT8I757N
2/+CdQKTSuM3koY9Q4kS4kjQ976YH0+C2o8/9tI8VKf4ycyGe+c8mxenFXDazNEP
XKnlQct9XNeTLeJtE8pFubBAZdyVHCkg3Z4/ujGryUQKz7/jJ0V1bwyNci179OWm
nZ3y9iZRDnDkyUp7Hz5mnTlsoQkxZGUyh2IbJWhWAuG5tLgI4FDF0IKdWL9DRqPy
JOe/LqTDwMQdbTMPQui0dSqtqGNWFgSutkN0Dp+bql8i6HNGv6Hd/o3wbOTWLXdZ
HA7j2SNZGD05NeMOF3W/NqZpneag2NGKN6I6hhPFxmxiiZzg7RDXSQZW6FCohHxn
lWX/NbZlM7Octtb+y9ijBsXkynXnFIfcaaa98tEj8S6il81QQsv87/tJcU5wACet
bS7GkmC0spxGZa1x6Z3KefAR+ZejjhIvkGHbmEcDHzWMZ2ConozC6hEnBRkgr96Z
gUYYbc5ezId8BLYjfGSCWKaqhmx2q0D3+nfOLofx7raGQuTPcFWIX0pk4qdc2R2d
8sZDjZ9ujUG6m+N1DBDu
=hIsI
-----END PGP SIGNATURE-----

--u6ak1upF5j4uU0dQ--