Date: Tue, 18 Sep 2018 17:54:51 -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: <20180918215451.GQ4184@tamriel.snowman.net>
References: <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>
 <20180918124835.GZ4184@tamriel.snowman.net>
 <CAEepm=1XCyNbhEaU+1tG_dFDVje=q98dg2uQJoJPWMu6HEjW2g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="BgY/xPMXw0GQoUOb"
Content-Disposition: inline
In-Reply-To: 
 <CAEepm=1XCyNbhEaU+1tG_dFDVje=q98dg2uQJoJPWMu6HEjW2g@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--BgY/xPMXw0GQoUOb
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 Wed, Sep 19, 2018 at 12:48 AM Stephen Frost <sfrost@snowman.net> wrote:
> > * Thomas Munro (thomas.munro@enterprisedb.com) wrote:
> > > 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.
>=20
> Suppose I have a table "emp" and indexes "emp_firstname_idx" and
> "emp_lastname_idx".  Suppose I created them in a sequence like this:
>=20
> 0: collation fr_CA has version "30"
> 1: create table emp (firstname text collate "fr_CA", lastname text
> collate "fr_CA");
> 2: create index on emp(firstname);
> 3: [upgrade operating system]; now collation fr_CA has version "31"
> 4: create index on emp(lastname);
>=20
> Now I have two indexes, built when different versions of the collation
> were in effect.  One of them is potentially corrupted, the other
> isn't.  Where are you going to record that?  Earlier I suggested that
> pg_index could have an indcollversion column, so that
> emp_firstname_idx's row would hold {"30"} and emp_lastname_idx's row
> would hold {"31"}.  It would be captured at CREATE INDEX time, and
> after that the only way to change it would be to REINDEX, and whenever
> it disagrees with the current version according to the provider you'd
> get a warning that you can only clear by running REINDEX.  Then I
> suggested that perhaps pg_depend might be a better place for it,
> because it would generalise to other kinds of object too.

For indexes, just like for tables, we have entries in pg_attribute where
that information would go.

> For example, suppose I create a constraint CHECK (foo < 'c=F4t=E9') [evil
> laugh].  The pg_depend row that links the constraint and the collation
> could record the current version as of the moment the constraint was
> defined.  After an OS upgrade that changes the reported version, I'd
> see a warning whenever loading the check constraint, and the only way
> to clear it would be to drop and recreate the constraint.  (I'm not
> proposing we do that, just trying to demonstrate that pg_depend might
> be a tidier and more general solution than adding 'collation version'
> columns holding arrays of version strings to multiple catalogs.  So
> help me Codd.)

The CHECK constraint doesn't need to directly track that information-
it should have a dependency on the column in the table and that's where
the information would be recorded about the current collation version.

Maybe I'm missing something but I have to admit that I feel like
pg_depend is being looked at here because everything goes through it-
but everything goes through it because it's simple and we just use it to
get to other things that have the complete definition of the object.

Lots and lots of things in pg_depend would have zero use for such a
field and I'm a bit worried you'd possibly also get into cases where
you've got different collation versions for the same object because of
the different dependencies into it...  even if you don't, you're
duplicating that information into every dependency, aren't you?

Thanks!

Stephen

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

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

iQIcBAEBCgAGBQJboXQrAAoJEO1sijiDR2RVARsQAI63ZP9tFn2pKTZCIG+338gy
NuVPTqLXvu3f8qmTQ1cmzE/uo4+eFyll1cXJZBavpC13pFm+roUu3810ZAy01f/U
XPlN1/pfDQkQLSI8Zzd5QJXIgBFJbrAuyAb6uakg0gxSXBXDLUjKD3tbuzJvWfJJ
2N/koGY4iIUYH/qTDkIDnL09ECIJQ4SBx2cLhSucf9Sn7ytRGjtodynxdi4rDTX0
L0s1EbpA9QZqQXxNkYlUPQM7eWSYoWguHLDjy9WJeJy1nKUYnPLxu1hvWcrGNVqA
SaHcRZs4+zc0Z72Xz+h64VRyB5OdCPVxcPh194CUSO5tGSWHltgSiaOZv8LXH7tH
4Y2y28GPFFUfi/n2ucHIZ07ff5Xq/EqUnANo69zavtd+8SHKN3nXdXyWUDdT1zDR
Sx6LCeJsA9/PbO+LvIjkHVdgX+8OxgkTvd9uc1GYBv+ZjETPMblwRNG836D0xmVw
grNR6pBzXApPXj4Gg5w+kID72i6WsHWsZbCcW7qfbNcmKVw0tGCJsnSDUENiWQmu
/ENjJx2BW7aDLEhsJbIBBngcExHk4+v63BGPJ5g3x0cLGR87PCZWfSS/pRawZAIy
yOOd0gwB82CPFYAEYyDeKDRblxwtEIzejWBCsSaVimB4T3xCOLYnPMXHNnw7+1Ua
WHFgc2ycFu/8lIBN52Ou
=VkHt
-----END PGP SIGNATURE-----

--BgY/xPMXw0GQoUOb--