<row>

<entry role="catalog_table_entry"><para role="column_definition">

<structfield>collversion</structfield> <type>text</type>

</para>

<para>

Provider-specific version of the collation. This is recorded when the

collation is created and then checked when it is used, to detect

changes in the collation definition that could lead to data corruption.

</para></entry>

</row>

installed in the operating system. If this is different from the

value in

<structname>pg_collation</structname>.<structfield>collversion</structfield>,

then objects depending on the collation might need to be rebuilt. See

also <xref linkend="sql-altercollation"/>.

ALTER COLLATION <replaceable>name</replaceable> REFRESH VERSION

<varlistentry>

<term><literal>REFRESH VERSION</literal></term>

<listitem>

<para>

Update the collation's version.

See <xref linkend="sql-altercollation-notes"/> below.

</para>

</listitem>

</varlistentry>

<refsect1 id="sql-altercollation-notes" xreflabel="Notes">

<title>Notes</title>

<para>

When using collations provided by the ICU library, the ICU-specific version

of the collator is recorded in the system catalog when the collation object

is created. When the collation is used, the current version is

checked against the recorded version, and a warning is issued when there is

a mismatch, for example:

<screen>

WARNING: collation "xx-x-icu" has version mismatch

DETAIL: The collation in the database was created using version 1.2.3.4, but the operating system provides version 2.3.4.5.

HINT: Rebuild all objects affected by this collation and run ALTER COLLATION pg_catalog."xx-x-icu" REFRESH VERSION, or build PostgreSQL with the right library version.

</screen>

A change in collation definitions can lead to corrupt indexes and other

problems because the database system relies on stored objects having a

certain sort order. Generally, this should be avoided, but it can happen

in legitimate circumstances, such as when

using <command>pg_upgrade</command> to upgrade to server binaries linked

with a newer version of ICU. When this happens, all objects depending on

the collation should be rebuilt, for example,

using <command>REINDEX</command>. When that is done, the collation version

can be refreshed using the command <literal>ALTER COLLATION ... REFRESH

VERSION</literal>. This will update the system catalog to record the

current collator version and will make the warning go away. Note that this

does not actually check whether all affected objects have been rebuilt

correctly.

</para>

<para>

When using collations provided by <literal>libc</literal> and

<productname>PostgreSQL</productname> was built with the GNU C library, the

C library's version is used as a collation version. Since collation

definitions typically change only with GNU C library releases, this provides

some defense against corruption, but it is not completely reliable.

</para>

<para>

Currently, there is no version tracking for the database default collation.

</para>

<para>

The following query can be used to identify all collations in the current

database that need to be refreshed and the objects that depend on them:

<programlisting><![CDATA[

SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation",

pg_describe_object(classid, objid, objsubid) AS "Object"

FROM pg_depend d JOIN pg_collation c

ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid

WHERE c.collversion <> pg_collation_actual_version(c.oid)

ORDER BY 1, 2;

]]></programlisting></para>

</refsect1>

[ VERSION = <replaceable>version</replaceable> ]

<varlistentry>

<term><replaceable>version</replaceable></term>

<listitem>

<para>

Specifies the version string to store with the collation. Normally,

this should be omitted, which will cause the version to be computed

from the actual version of the collation as provided by the operating

system. This option is intended to be used

by <command>pg_upgrade</command> for copying the version from an

existing installation.

</para>

<para>

See also <xref linkend="sql-altercollation"/> for how to handle

collation version mismatches.

</para>

</listitem>

</varlistentry>

const char *collversion,

if (collversion)

values[Anum_pg_collation_collversion - 1] = CStringGetTextDatum(collversion);

else

nulls[Anum_pg_collation_collversion - 1] = true;

DefElem *versionEl = NULL;

char *collversion = NULL;

else if (strcmp(defel->defname, "version") == 0)

defelp = &versionEl;

if (versionEl)

collversion = defGetString(versionEl);

if (!collversion)

collversion = get_collation_actual_version(collprovider, collcollate);

collversion,

AlterCollation(AlterCollationStmt *stmt)

{

Relation rel;

Oid collOid;

HeapTuple tup;

Form_pg_collation collForm;

Datum collversion;

bool isnull;

char *oldversion;

char *newversion;

ObjectAddress address;

rel = table_open(CollationRelationId, RowExclusiveLock);

collOid = get_collation_oid(stmt->collname, false);

if (!pg_collation_ownercheck(collOid, GetUserId()))

aclcheck_error(ACLCHECK_NOT_OWNER, OBJECT_COLLATION,

NameListToString(stmt->collname));

tup = SearchSysCacheCopy1(COLLOID, ObjectIdGetDatum(collOid));

if (!HeapTupleIsValid(tup))

elog(ERROR, "cache lookup failed for collation %u", collOid);

collForm = (Form_pg_collation) GETSTRUCT(tup);

collversion = SysCacheGetAttr(COLLOID, tup, Anum_pg_collation_collversion,

&isnull);

oldversion = isnull ? NULL : TextDatumGetCString(collversion);

newversion = get_collation_actual_version(collForm->collprovider, NameStr(collForm->collcollate));

/* cannot change from NULL to non-NULL or vice versa */

if ((!oldversion && newversion) || (oldversion && !newversion))

elog(ERROR, "invalid collation version change");

else if (oldversion && newversion && strcmp(newversion, oldversion) != 0)

{

bool nulls[Natts_pg_collation];

bool replaces[Natts_pg_collation];

Datum values[Natts_pg_collation];

ereport(NOTICE,

(errmsg("changing version from %s to %s",

oldversion, newversion)));

memset(values, 0, sizeof(values));

memset(nulls, false, sizeof(nulls));

memset(replaces, false, sizeof(replaces));

values[Anum_pg_collation_collversion - 1] = CStringGetTextDatum(newversion);

replaces[Anum_pg_collation_collversion - 1] = true;

tup = heap_modify_tuple(tup, RelationGetDescr(rel),

values, nulls, replaces);

}

else

ereport(NOTICE,

(errmsg("version has not changed")));

CatalogTupleUpdate(rel, &tup->t_self, tup);

InvokeObjectPostAlterHook(CollationRelationId, collOid, 0);

ObjectAddressSet(address, CollationRelationId, collOid);

heap_freetuple(tup);

table_close(rel, NoLock);

return address;

}

get_collation_actual_version(COLLPROVIDER_LIBC, localebuf),

get_collation_actual_version(COLLPROVIDER_LIBC, locale),

get_collation_actual_version(COLLPROVIDER_ICU, collcollate),

static AlterCollationStmt *

_copyAlterCollationStmt(const AlterCollationStmt *from)

{

AlterCollationStmt *newnode = makeNode(AlterCollationStmt);

COPY_NODE_FIELD(collname);

return newnode;

}

case T_AlterCollationStmt:

retval = _copyAlterCollationStmt(from);

break;

static bool

_equalAlterCollationStmt(const AlterCollationStmt *a, const AlterCollationStmt *b)

{

COMPARE_NODE_FIELD(collname);

return true;

}

case T_AlterCollationStmt:

retval = _equalAlterCollationStmt(a, b);

break;