diff options
24 files changed, 179 insertions, 118 deletions
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index d0a8dc72cb6..7b62818ce4b 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -2159,8 +2159,8 @@ (<structfield>collname</>, <structfield>collnamespace</>). <productname>PostgreSQL</productname> generally ignores all collations that do not have <structfield>collencoding</> equal to - either the current database's encoding or -1, and creation of new - entries matching an entry with <structfield>collencoding</> = -1 + either the current database's encoding or -1, and creation of new entries + with the same name as an entry with <structfield>collencoding</> = -1 is forbidden. Therefore it is sufficient to use a qualified SQL name (<replaceable>schema</>.<replaceable>name</>) to identify a collation, even though this is not unique according to the catalog definition. @@ -6138,8 +6138,8 @@ of the type. If the type does not support collations, this will be zero. A base type that supports collations will have <symbol>DEFAULT_COLLATION_OID</symbol> here. A domain over a - collatable type can have some other collation OID, if one was defined - for the domain. + collatable type can have some other collation OID, if one was + specified for the domain. </para></entry> </row> diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml index 15fbe0d614e..2dedb153c06 100644 --- a/doc/src/sgml/indices.sgml +++ b/doc/src/sgml/indices.sgml @@ -1004,12 +1004,11 @@ SELECT am.amname AS index_method, <sect1 id="indexes-collations"> - <title>Collations and Indexes</title> + <title>Indexes and Collations</title> <para> - An index can only support one collation for one column or - expression. If multiple collations are of interest, multiple - indexes may be created. + An index can support only one collation per index column. + If multiple collations are of interest, multiple indexes may be needed. </para> <para> @@ -1022,23 +1021,21 @@ CREATE TABLE test1c ( CREATE INDEX test1c_content_index ON test1c (content); </programlisting> - The created index automatically follows the collation of the - underlying column, and so a query of the form + The index automatically uses the collation of the + underlying column. So a query of the form <programlisting> -SELECT * FROM test1c WHERE content = <replaceable>constant</replaceable>; +SELECT * FROM test1c WHERE content > <replaceable>constant</replaceable>; </programlisting> - could use the index. - </para> - - <para> - If in addition, a query of the form, say, + could use the index, because the comparison will by default use the + collation of the column. However, this index cannot accelerate queries + that involve some other collation. So if queries of the form, say, <programlisting> SELECT * FROM test1c WHERE content > <replaceable>constant</replaceable> COLLATE "y"; </programlisting> - is of interest, an additional index could be created that supports - the <literal>"y"</literal> collation, like so: + are also of interest, an additional index could be created that supports + the <literal>"y"</literal> collation, like this: <programlisting> -CREATE INDEX test1c_content_index ON test1c (content COLLATE "y"); +CREATE INDEX test1c_content_y_index ON test1c (content COLLATE "y"); </programlisting> </para> </sect1> diff --git a/doc/src/sgml/ref/create_collation.sgml b/doc/src/sgml/ref/create_collation.sgml index fc792250011..c8535768143 100644 --- a/doc/src/sgml/ref/create_collation.sgml +++ b/doc/src/sgml/ref/create_collation.sgml @@ -108,8 +108,8 @@ CREATE COLLATION <replaceable>name</replaceable> FROM <replaceable>existing_coll <listitem> <para> The name of an existing collation to copy. The new collation - will have the same properties as the existing one, but they - will become independent objects. + will have the same properties as the existing one, but it + will be an independent object. </para> </listitem> </varlistentry> @@ -134,7 +134,8 @@ CREATE COLLATION <replaceable>name</replaceable> FROM <replaceable>existing_coll <title>Examples</title> <para> - To create a collation from the locale <literal>fr_FR.utf8</literal> + To create a collation from the operating system locale + <literal>fr_FR.utf8</literal> (assuming the current database encoding is <literal>UTF8</literal>): <programlisting> CREATE COLLATION french (LOCALE = 'fr_FR.utf8'); diff --git a/doc/src/sgml/ref/create_domain.sgml b/doc/src/sgml/ref/create_domain.sgml index 2300edefe3a..8db90f911f6 100644 --- a/doc/src/sgml/ref/create_domain.sgml +++ b/doc/src/sgml/ref/create_domain.sgml @@ -90,7 +90,7 @@ CREATE DOMAIN <replaceable class="parameter">name</replaceable> [ AS ] <replacea <para> An optional collation for the domain. If no collation is specified, the underlying data type's default collation is used. - The underlying type must be collatable when <literal>COLLATE</> + The underlying type must be collatable if <literal>COLLATE</> is specified. </para> </listitem> diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index 8ec7abbbd49..43b6499603c 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -188,9 +188,8 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ <replaceable class="parameter">name</ The name of the collation to use for the index. By default, the index uses the collation declared for the column to be indexed or the result collation of the expression to be - indexed. Indexes with nondefault collations are - available for use by queries that involve expressions using - nondefault collations. + indexed. Indexes with non-default collations can be useful for + queries that involve expressions using non-default collations. </para> </listitem> </varlistentry> @@ -538,6 +537,13 @@ CREATE INDEX ON films ((lower(title))); </para> <para> + To create an index with non-default collation: +<programlisting> +CREATE INDEX title_idx_german ON films (title COLLATE "de_DE"); +</programlisting> + </para> + + <para> To create an index with non-default sort ordering of nulls: <programlisting> CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST); diff --git a/doc/src/sgml/ref/create_type.sgml b/doc/src/sgml/ref/create_type.sgml index 98e1764b1e9..70b0325f0ec 100644 --- a/doc/src/sgml/ref/create_type.sgml +++ b/doc/src/sgml/ref/create_type.sgml @@ -355,10 +355,10 @@ CREATE TYPE <replaceable class="parameter">name</replaceable> </para> <para> - If the optional + If the optional boolean parameter <replaceable class="parameter">collatable</replaceable> is true, column definitions and expressions of the type may carry - collation information and allow the use of + collation information through use of the <literal>COLLATE</literal> clause. It is up to the implementations of the functions operating on the type to actually make use of the collation information; this does not happen diff --git a/doc/src/sgml/regress.sgml b/doc/src/sgml/regress.sgml index 874d45615e2..bb5f577852c 100644 --- a/doc/src/sgml/regress.sgml +++ b/doc/src/sgml/regress.sgml @@ -238,7 +238,7 @@ gmake check LANG=C ENCODING=EUC_JP </sect2> <sect2> - <title>Extra tests</title> + <title>Extra Tests</title> <para> The regression test suite contains a few test files that are not @@ -253,8 +253,8 @@ gmake check EXTRA_TESTS=numeric_big <screen> gmake check EXTRA_TESTS=collate.linux.utf8 LANG=en_US.utf8 </screen> - This test works only on Linux/glibc platforms and when run in a - UTF-8 locale. + The <literal>collate.linux.utf8</> test works only on Linux/glibc + platforms, and only when run in a locale that uses UTF-8 encoding. </para> </sect2> </sect1> diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c index c06a0271ca5..16979c4ea72 100644 --- a/src/backend/access/common/tupdesc.c +++ b/src/backend/access/common/tupdesc.c @@ -360,6 +360,8 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2) return false; if (attr1->attinhcount != attr2->attinhcount) return false; + if (attr1->attcollation != attr2->attcollation) + return false; /* attacl and attoptions are not even present... */ } @@ -611,7 +613,9 @@ BuildDescForRelation(List *schema) * BuildDescFromLists * * Build a TupleDesc given lists of column names (as String nodes), - * column type OIDs, and column typmods. No constraints are generated. + * column type OIDs, typmods, and collation OIDs. + * + * No constraints are generated. * * This is essentially a cut-down version of BuildDescForRelation for use * with functions returning RECORD. diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c index b3ed946530e..c459c1e2213 100644 --- a/src/backend/catalog/dependency.c +++ b/src/backend/catalog/dependency.c @@ -2227,14 +2227,16 @@ getObjectDescription(const ObjectAddress *object) case OCLASS_COLLATION: { HeapTuple collTup; + Form_pg_collation coll; collTup = SearchSysCache1(COLLOID, ObjectIdGetDatum(object->objectId)); if (!HeapTupleIsValid(collTup)) elog(ERROR, "cache lookup failed for collation %u", object->objectId); + coll = (Form_pg_collation) GETSTRUCT(collTup); appendStringInfo(&buffer, _("collation %s"), - NameStr(((Form_pg_collation) GETSTRUCT(collTup))->collname)); + NameStr(coll->collname)); ReleaseSysCache(collTup); break; } diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 8c5670fdb8c..28d5c549a32 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -644,7 +644,7 @@ AddNewAttributeTuples(Oid new_rel_oid, /* * First we add the user attributes. This is also a convenient place to - * add dependencies on their datatypes. + * add dependencies on their datatypes and collations. */ for (i = 0; i < natts; i++) { @@ -666,7 +666,9 @@ AddNewAttributeTuples(Oid new_rel_oid, referenced.objectSubId = 0; recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL); - if (OidIsValid(attr->attcollation)) + /* The default collation is pinned, so don't bother recording it */ + if (OidIsValid(attr->attcollation) && + attr->attcollation != DEFAULT_COLLATION_OID) { referenced.classId = CollationRelationId; referenced.objectId = attr->attcollation; @@ -921,7 +923,7 @@ AddNewRelationType(const char *typeName, -1, /* typmod */ 0, /* array dimensions for typBaseType */ false, /* Type NOT NULL */ - InvalidOid); /* typcollation */ + InvalidOid); /* rowtypes never have a collation */ } /* -------------------------------- @@ -1183,7 +1185,7 @@ heap_create_with_catalog(const char *relname, -1, /* typmod */ 0, /* array dimensions for typBaseType */ false, /* Type NOT NULL */ - InvalidOid); /* typcollation */ + InvalidOid); /* rowtypes never have a collation */ pfree(relarrayname); } diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index c79402c72a9..bc630a6f3ac 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -351,7 +351,6 @@ ConstructTupleDescriptor(Relation heapRelation, to->atthasdef = false; to->attislocal = true; to->attinhcount = 0; - to->attcollation = collationObjectId[i]; } else @@ -388,7 +387,6 @@ ConstructTupleDescriptor(Relation heapRelation, to->attcacheoff = -1; to->atttypmod = -1; to->attislocal = true; - to->attcollation = collationObjectId[i]; ReleaseSysCache(tuple); @@ -653,6 +651,7 @@ UpdateIndexRelation(Oid indexoid, * indexColNames: column names to use for index (List of char *) * accessMethodObjectId: OID of index AM to use * tableSpaceId: OID of tablespace to use + * collationObjectId: array of collation OIDs, one per index column * classObjectId: array of index opclass OIDs, one per index column * coloptions: array of per-index-column indoption settings * reloptions: AM-specific options @@ -871,7 +870,8 @@ index_create(Relation heapRelation, * ---------------- */ UpdateIndexRelation(indexRelationId, heapRelationId, indexInfo, - collationObjectId, classObjectId, coloptions, isprimary, is_exclusion, + collationObjectId, classObjectId, coloptions, + isprimary, is_exclusion, !deferrable, !concurrent); @@ -965,9 +965,11 @@ index_create(Relation heapRelation, } /* Store dependency on collations */ + /* The default collation is pinned, so don't bother recording it */ for (i = 0; i < indexInfo->ii_NumIndexAttrs; i++) { - if (OidIsValid(collationObjectId[i])) + if (OidIsValid(collationObjectId[i]) && + collationObjectId[i] != DEFAULT_COLLATION_OID) { referenced.classId = CollationRelationId; referenced.objectId = collationObjectId[i]; @@ -2445,8 +2447,8 @@ validate_index(Oid heapId, Oid indexId, Snapshot snapshot) ivinfo.num_heap_tuples = heapRelation->rd_rel->reltuples; ivinfo.strategy = NULL; - state.tuplesort = tuplesort_begin_datum(TIDOID, - TIDLessOperator, InvalidOid, false, + state.tuplesort = tuplesort_begin_datum(TIDOID, TIDLessOperator, + InvalidOid, false, maintenance_work_mem, false); state.htups = state.itups = state.tups_inserted = 0; diff --git a/src/backend/catalog/pg_type.c b/src/backend/catalog/pg_type.c index 9e35e73f9cf..b6912578786 100644 --- a/src/backend/catalog/pg_type.c +++ b/src/backend/catalog/pg_type.c @@ -643,8 +643,9 @@ GenerateTypeDependencies(Oid typeNamespace, recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL); } - /* Normal dependency from a domain to its base type's collation. */ - if (OidIsValid(typeCollation)) + /* Normal dependency from a domain to its collation. */ + /* We know the default collation is pinned, so don't bother recording it */ + if (OidIsValid(typeCollation) && typeCollation != DEFAULT_COLLATION_OID) { referenced.classId = CollationRelationId; referenced.objectId = typeCollation; diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 53a6aafbbfb..ff84045d4fc 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -350,7 +350,8 @@ DefineIndex(RangeVar *heapRelation, collationObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid)); classObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid)); coloptions = (int16 *) palloc(numberOfAttributes * sizeof(int16)); - ComputeIndexAttrs(indexInfo, collationObjectId, classObjectId, coloptions, attributeList, + ComputeIndexAttrs(indexInfo, collationObjectId, classObjectId, + coloptions, attributeList, exclusionOpNames, relationId, accessMethodName, accessMethodId, amcanorder, isconstraint); @@ -395,7 +396,8 @@ DefineIndex(RangeVar *heapRelation, indexRelationId = index_create(rel, indexRelationName, indexRelationId, indexInfo, indexColNames, - accessMethodId, tablespaceId, collationObjectId, classObjectId, + accessMethodId, tablespaceId, + collationObjectId, classObjectId, coloptions, reloptions, primary, isconstraint, deferrable, initdeferred, allowSystemTableMods, diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index bcf660b6de0..7660114ec2c 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -295,7 +295,8 @@ static void ATPrepAddColumn(List **wqueue, Relation rel, bool recurse, bool recu static void ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, ColumnDef *colDef, bool isOid, bool recurse, bool recursing, LOCKMODE lockmode); -static void add_column_datatype_dependency(Oid relid, int32 attnum, Oid typid, Oid collid); +static void add_column_datatype_dependency(Oid relid, int32 attnum, Oid typid); +static void add_column_collation_dependency(Oid relid, int32 attnum, Oid collid); static void ATPrepAddOids(List **wqueue, Relation rel, bool recurse, AlterTableCmd *cmd, LOCKMODE lockmode); static void ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode); @@ -4423,7 +4424,8 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, /* * Add needed dependency entries for the new column. */ - add_column_datatype_dependency(myrelid, newattnum, attribute.atttypid, attribute.attcollation); + add_column_datatype_dependency(myrelid, newattnum, attribute.atttypid); + add_column_collation_dependency(myrelid, newattnum, attribute.attcollation); /* * Propagate to children as appropriate. Unlike most other ALTER @@ -4474,7 +4476,7 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, * Install a column's dependency on its datatype. */ static void -add_column_datatype_dependency(Oid relid, int32 attnum, Oid typid, Oid collid) +add_column_datatype_dependency(Oid relid, int32 attnum, Oid typid) { ObjectAddress myself, referenced; @@ -4486,9 +4488,23 @@ add_column_datatype_dependency(Oid relid, int32 attnum, Oid typid, Oid collid) referenced.objectId = typid; referenced.objectSubId = 0; recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL); +} + +/* + * Install a column's dependency on its collation. + */ +static void +add_column_collation_dependency(Oid relid, int32 attnum, Oid collid) +{ + ObjectAddress myself, + referenced; - if (collid) + /* We know the default collation is pinned, so don't bother recording it */ + if (OidIsValid(collid) && collid != DEFAULT_COLLATION_OID) { + myself.classId = RelationRelationId; + myself.objectId = relid; + myself.objectSubId = attnum; referenced.classId = CollationRelationId; referenced.objectId = collid; referenced.objectSubId = 0; @@ -6671,7 +6687,8 @@ ATPrepAlterColumnType(List **wqueue, else { transform = (Node *) makeVar(1, attnum, - attTup->atttypid, attTup->atttypmod, attTup->attcollation, + attTup->atttypid, attTup->atttypmod, + attTup->attcollation, 0); } @@ -7052,7 +7069,7 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel, /* * Now scan for dependencies of this column on other things. The only * thing we should find is the dependency on the column datatype, which we - * want to remove, and possibly an associated collation. + * want to remove, and possibly a collation dependency. */ ScanKeyInit(&key[0], Anum_pg_depend_classid, @@ -7091,8 +7108,8 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel, heap_close(depRel, RowExclusiveLock); /* - * Here we go --- change the recorded column type. (Note heapTup is a - * copy of the syscache entry, so okay to scribble on.) + * Here we go --- change the recorded column type and collation. (Note + * heapTup is a copy of the syscache entry, so okay to scribble on.) */ attTup->atttypid = targettype; attTup->atttypmod = targettypmod; @@ -7112,8 +7129,9 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel, heap_close(attrelation, RowExclusiveLock); - /* Install dependency on new datatype */ - add_column_datatype_dependency(RelationGetRelid(rel), attnum, targettype, targetcollid); + /* Install dependencies on new datatype and collation */ + add_column_datatype_dependency(RelationGetRelid(rel), attnum, targettype); + add_column_collation_dependency(RelationGetRelid(rel), attnum, targetcollid); /* * Drop any pg_statistic entry for the column, since it's now wrong type diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 1a20b0d91be..7388e5a3db9 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -571,7 +571,7 @@ DefineType(List *names, List *parameters) -1, /* typMod (Domains only) */ 0, /* Array Dimensions of typbasetype */ false, /* Type NOT NULL */ - collation); + collation); /* type's collation */ /* * Create the array type that goes with it. @@ -611,7 +611,7 @@ DefineType(List *names, List *parameters) -1, /* typMod (Domains only) */ 0, /* Array dimensions of typbasetype */ false, /* Type NOT NULL */ - collation); + collation); /* type's collation */ pfree(array_type); } @@ -1069,7 +1069,7 @@ DefineDomain(CreateDomainStmt *stmt) basetypeMod, /* typeMod value */ typNDims, /* Array dimensions for base type */ typNotNull, /* Type NOT NULL */ - domaincoll); + domaincoll); /* type's collation */ /* * Process constraints which refer to the domain ID returned by TypeCreate @@ -1179,7 +1179,7 @@ DefineEnum(CreateEnumStmt *stmt) -1, /* typMod (Domains only) */ 0, /* Array dimensions of typbasetype */ false, /* Type NOT NULL */ - InvalidOid); /* typcollation */ + InvalidOid); /* type's collation */ /* Enter the enum's values into pg_enum */ EnumValuesCreate(enumTypeOid, stmt->vals); @@ -1219,7 +1219,7 @@ DefineEnum(CreateEnumStmt *stmt) -1, /* typMod (Domains only) */ 0, /* Array dimensions of typbasetype */ false, /* Type NOT NULL */ - InvalidOid); /* typcollation */ + InvalidOid); /* type's collation */ pfree(enumArrayName); } diff --git a/src/backend/executor/nodeMergejoin.c b/src/backend/executor/nodeMergejoin.c index 208c0fb76ac..7d27123cf05 100644 --- a/src/backend/executor/nodeMergejoin.c +++ b/src/backend/executor/nodeMergejoin.c @@ -170,13 +170,13 @@ typedef enum * the two expressions from the original clause. * * In addition to the expressions themselves, the planner passes the btree - * opfamily OID, btree strategy number (BTLessStrategyNumber or + * opfamily OID, collation OID, btree strategy number (BTLessStrategyNumber or * BTGreaterStrategyNumber), and nulls-first flag that identify the intended * sort ordering for each merge key. The mergejoinable operator is an - * equality operator in this opfamily, and the two inputs are guaranteed to be + * equality operator in the opfamily, and the two inputs are guaranteed to be * ordered in either increasing or decreasing (respectively) order according - * to this opfamily, with nulls at the indicated end of the range. This - * allows us to obtain the needed comparison function from the opfamily. + * to the opfamily and collation, with nulls at the indicated end of the range. + * This allows us to obtain the needed comparison function from the opfamily. */ static MergeJoinClause MJExamineQuals(List *mergeclauses, diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 1a9540ce068..bbff4f26f8d 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -108,7 +108,8 @@ static TidScan *make_tidscan(List *qptlist, List *qpqual, Index scanrelid, List *tidquals); static FunctionScan *make_functionscan(List *qptlist, List *qpqual, Index scanrelid, Node *funcexpr, List *funccolnames, - List *funccoltypes, List *funccoltypmods, List *funccolcollations); + List *funccoltypes, List *funccoltypmods, + List *funccolcollations); static ValuesScan *make_valuesscan(List *qptlist, List *qpqual, Index scanrelid, List *values_lists); static CteScan *make_ctescan(List *qptlist, List *qpqual, @@ -143,9 +144,9 @@ static MergeJoin *make_mergejoin(List *tlist, bool *mergenullsfirst, Plan *lefttree, Plan *righttree, JoinType jointype); -static Sort * -make_sort(PlannerInfo *root, Plan *lefttree, int numCols, -AttrNumber *sortColIdx, Oid *sortOperators, Oid *collations, bool *nullsFirst, +static Sort *make_sort(PlannerInfo *root, Plan *lefttree, int numCols, + AttrNumber *sortColIdx, Oid *sortOperators, + Oid *collations, bool *nullsFirst, double limit_tuples); static Plan *prepare_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys, @@ -738,7 +739,8 @@ create_merge_append_plan(PlannerInfo *root, MergeAppendPath *best_path) /* Now, insert a Sort node if subplan isn't sufficiently ordered */ if (!pathkeys_contained_in(pathkeys, subpath->pathkeys)) subplan = (Plan *) make_sort(root, subplan, numsortkeys, - sortColIdx, sortOperators, collations, nullsFirst, + sortColIdx, sortOperators, + collations, nullsFirst, best_path->limit_tuples); subplans = lappend(subplans, subplan); @@ -2013,10 +2015,10 @@ create_mergejoin_plan(PlannerInfo *root, } /* - * Compute the opfamily/strategy/nullsfirst arrays needed by the executor. - * The information is in the pathkeys for the two inputs, but we need to - * be careful about the possibility of mergeclauses sharing a pathkey - * (compare find_mergeclauses_for_pathkeys()). + * Compute the opfamily/collation/strategy/nullsfirst arrays needed by the + * executor. The information is in the pathkeys for the two inputs, but + * we need to be careful about the possibility of mergeclauses sharing a + * pathkey (compare find_mergeclauses_for_pathkeys()). */ nClauses = list_length(mergeclauses); Assert(nClauses == list_length(best_path->path_mergeclauses)); @@ -3316,13 +3318,14 @@ make_mergejoin(List *tlist, /* * make_sort --- basic routine to build a Sort plan node * - * Caller must have built the sortColIdx, sortOperators, and nullsFirst - * arrays already. limit_tuples is as for cost_sort (in particular, pass - * -1 if no limit) + * Caller must have built the sortColIdx, sortOperators, collations, and + * nullsFirst arrays already. + * limit_tuples is as for cost_sort (in particular, pass -1 if no limit) */ static Sort * make_sort(PlannerInfo *root, Plan *lefttree, int numCols, -AttrNumber *sortColIdx, Oid *sortOperators, Oid *collations, bool *nullsFirst, + AttrNumber *sortColIdx, Oid *sortOperators, + Oid *collations, bool *nullsFirst, double limit_tuples) { Sort *node = makeNode(Sort); @@ -3378,6 +3381,11 @@ add_sort_column(AttrNumber colIdx, Oid sortOp, Oid coll, bool nulls_first, * values that < considers equal. We need not check nulls_first * however because a lower-order column with the same sortop but * opposite nulls direction is redundant. + * + * We could probably consider sort keys with the same sortop and + * different collations to be redundant too, but for the moment + * treat them as not redundant. This will be needed if we ever + * support collations with different notions of equality. */ if (sortColIdx[i] == colIdx && sortOperators[numCols] == sortOp && @@ -3410,8 +3418,9 @@ add_sort_column(AttrNumber colIdx, Oid sortOp, Oid coll, bool nulls_first, * 'adjust_tlist_in_place' is TRUE if lefttree must be modified in-place * * We must convert the pathkey information into arrays of sort key column - * numbers and sort operator OIDs, which is the representation the executor - * wants. These are returned into the output parameters *p_numsortkeys etc. + * numbers, sort operator OIDs, collation OIDs, and nulls-first flags, + * which is the representation the executor wants. These are returned into + * the output parameters *p_numsortkeys etc. * * If the pathkeys include expressions that aren't simple Vars, we will * usually need to add resjunk items to the input plan's targetlist to @@ -3610,7 +3619,8 @@ prepare_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys, pathkey->pk_eclass->ec_collation, pathkey->pk_nulls_first, numsortkeys, - sortColIdx, sortOperators, collations, nullsFirst); + sortColIdx, sortOperators, + collations, nullsFirst); } Assert(numsortkeys > 0); @@ -3655,7 +3665,8 @@ make_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys, /* Now build the Sort node */ return make_sort(root, lefttree, numsortkeys, - sortColIdx, sortOperators, collations, nullsFirst, limit_tuples); + sortColIdx, sortOperators, collations, + nullsFirst, limit_tuples); } /* @@ -3701,13 +3712,15 @@ make_sort_from_sortclauses(PlannerInfo *root, List *sortcls, Plan *lefttree) exprCollation((Node *) tle->expr), sortcl->nulls_first, numsortkeys, - sortColIdx, sortOperators, collations, nullsFirst); + sortColIdx, sortOperators, + collations, nullsFirst); } Assert(numsortkeys > 0); return make_sort(root, lefttree, numsortkeys, - sortColIdx, sortOperators, collations, nullsFirst, -1.0); + sortColIdx, sortOperators, collations, + nullsFirst, -1.0); } /* @@ -3763,14 +3776,16 @@ make_sort_from_groupcols(PlannerInfo *root, exprCollation((Node *) tle->expr), grpcl->nulls_first, numsortkeys, - sortColIdx, sortOperators, collations, nullsFirst); + sortColIdx, sortOperators, + collations, nullsFirst); grpno++; } Assert(numsortkeys > 0); return make_sort(root, lefttree, numsortkeys, - sortColIdx, sortOperators, collations, nullsFirst, -1.0); + sortColIdx, sortOperators, collations, + nullsFirst, -1.0); } static Material * diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c index f2b586d19cc..493bc86299f 100644 --- a/src/backend/optimizer/plan/subselect.c +++ b/src/backend/optimizer/plan/subselect.c @@ -281,7 +281,7 @@ SS_assign_special_param(PlannerInfo *root) } /* - * Get the datatype of the first column of the plan's output. + * Get the datatype/typmod/collation of the first column of the plan's output. * * This information is stored for ARRAY_SUBLINK execution and for * exprType()/exprTypmod()/exprCollation(), which have no way to get at the @@ -290,7 +290,8 @@ SS_assign_special_param(PlannerInfo *root) * always. */ static void -get_first_col_type(Plan *plan, Oid *coltype, int32 *coltypmod, Oid *colcollation) +get_first_col_type(Plan *plan, Oid *coltype, int32 *coltypmod, + Oid *colcollation) { /* In cases such as EXISTS, tlist might be empty; arbitrarily use VOID */ if (plan->targetlist) @@ -478,7 +479,8 @@ build_subplan(PlannerInfo *root, Plan *plan, List *rtable, List *rowmarks, splan->subLinkType = subLinkType; splan->testexpr = NULL; splan->paramIds = NIL; - get_first_col_type(plan, &splan->firstColType, &splan->firstColTypmod, &splan->firstColCollation); + get_first_col_type(plan, &splan->firstColType, &splan->firstColTypmod, + &splan->firstColCollation); splan->useHashTable = false; splan->unknownEqFalse = unknownEqFalse; splan->setParam = NIL; @@ -976,7 +978,8 @@ SS_process_ctes(PlannerInfo *root) splan->subLinkType = CTE_SUBLINK; splan->testexpr = NULL; splan->paramIds = NIL; - get_first_col_type(plan, &splan->firstColType, &splan->firstColTypmod, &splan->firstColCollation); + get_first_col_type(plan, &splan->firstColType, &splan->firstColTypmod, + &splan->firstColCollation); splan->useHashTable = false; splan->unknownEqFalse = false; splan->setParam = NIL; diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 4947a7d837e..b2032870477 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -1357,9 +1357,9 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt) /* * Generate dummy targetlist for outer query using column names of - * leftmost select and common datatypes of topmost set operation. Also - * make lists of the dummy vars and their names for use in parsing ORDER - * BY. + * leftmost select and common datatypes/collations of topmost set + * operation. Also make lists of the dummy vars and their names for use + * in parsing ORDER BY. * * Note: we use leftmostRTI as the varno of the dummy variables. It * shouldn't matter too much which RT index they have, as long as they @@ -1371,7 +1371,9 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt) targetnames = NIL; left_tlist = list_head(leftmostQuery->targetList); - forthree(lct, sostmt->colTypes, lcm, sostmt->colTypmods, lcc, sostmt->colCollations) + forthree(lct, sostmt->colTypes, + lcm, sostmt->colTypmods, + lcc, sostmt->colCollations) { Oid colType = lfirst_oid(lct); int32 colTypmod = lfirst_int(lcm); diff --git a/src/backend/parser/parse_cte.c b/src/backend/parser/parse_cte.c index 41097263b41..ec6afd83b6c 100644 --- a/src/backend/parser/parse_cte.c +++ b/src/backend/parser/parse_cte.c @@ -286,10 +286,10 @@ analyzeCTE(ParseState *pstate, CommonTableExpr *cte) else { /* - * Verify that the previously determined output column types match - * what the query really produced. We have to check this because the - * recursive term could have overridden the non-recursive term, and we - * don't have any easy way to fix that. + * Verify that the previously determined output column types and + * collations match what the query really produced. We have to check + * this because the recursive term could have overridden the + * non-recursive term, and we don't have any easy way to fix that. */ ListCell *lctlist, *lctyp, @@ -366,11 +366,11 @@ analyzeCTETargetList(ParseState *pstate, CommonTableExpr *cte, List *tlist) Assert(cte->ctecolnames == NIL); /* - * We need to determine column names and types. The alias column names - * override anything coming from the query itself. (Note: the SQL spec - * says that the alias list must be empty or exactly as long as the output - * column set; but we allow it to be shorter for consistency with Alias - * handling.) + * We need to determine column names, types, and collations. The alias + * column names override anything coming from the query itself. (Note: + * the SQL spec says that the alias list must be empty or exactly as long + * as the output column set; but we allow it to be shorter for consistency + * with Alias handling.) */ cte->ctecolnames = copyObject(cte->aliascolnames); cte->ctecoltypes = cte->ctecoltypmods = cte->ctecolcollations = NIL; diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c index 2a94f73a9ab..5359e691dd1 100644 --- a/src/backend/parser/parse_relation.c +++ b/src/backend/parser/parse_relation.c @@ -1174,7 +1174,8 @@ addRangeTableEntryForFunction(ParseState *pstate, eref->colnames = lappend(eref->colnames, makeString(attrname)); rte->funccoltypes = lappend_oid(rte->funccoltypes, attrtype); rte->funccoltypmods = lappend_int(rte->funccoltypmods, attrtypmod); - rte->funccolcollations = lappend_oid(rte->funccolcollations, attrcollation); + rte->funccolcollations = lappend_oid(rte->funccolcollations, + attrcollation); } } else @@ -1902,7 +1903,8 @@ expandTupleDesc(TupleDesc tupdesc, Alias *eref, Var *varnode; varnode = makeVar(rtindex, attr->attnum, - attr->atttypid, attr->atttypmod, attr->attcollation, + attr->atttypid, attr->atttypmod, + attr->attcollation, sublevels_up); varnode->location = location; @@ -2009,7 +2011,7 @@ get_rte_attribute_name(RangeTblEntry *rte, AttrNumber attnum) /* * get_rte_attribute_type - * Get attribute type information from a RangeTblEntry + * Get attribute type/typmod/collation information from a RangeTblEntry */ void get_rte_attribute_type(RangeTblEntry *rte, AttrNumber attnum, diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c index 3f630147b0f..e6f9e36bbca 100644 --- a/src/backend/parser/parse_target.c +++ b/src/backend/parser/parse_target.c @@ -372,7 +372,7 @@ transformAssignedExpr(ParseState *pstate, Oid type_id; /* type of value provided */ Oid attrtype; /* type of target column */ int32 attrtypmod; - Oid attrcollation; + Oid attrcollation; /* collation of target column */ Relation rd = pstate->p_target_relation; Assert(rd != NULL); @@ -388,11 +388,12 @@ transformAssignedExpr(ParseState *pstate, /* * If the expression is a DEFAULT placeholder, insert the attribute's - * type/typmod into it so that exprType will report the right things. (We - * expect that the eventually substituted default expression will in fact - * have this type and typmod.) Also, reject trying to update a subfield - * or array element with DEFAULT, since there can't be any default for - * portions of a column. + * type/typmod/collation into it so that exprType etc will report the + * right things. (We expect that the eventually substituted default + * expression will in fact have this type and typmod. The collation + * likely doesn't matter, but let's set it correctly anyway.) Also, + * reject trying to update a subfield or array element with DEFAULT, since + * there can't be any default for portions of a column. */ if (expr && IsA(expr, SetToDefault)) { diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index e436a1ee59f..3ab90cb7d88 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -235,7 +235,8 @@ static void get_from_clause_item(Node *jtnode, Query *query, deparse_context *context); static void get_from_clause_alias(Alias *alias, RangeTblEntry *rte, deparse_context *context); -static void get_from_clause_coldeflist(List *names, List *types, List *typmods, List *collations, +static void get_from_clause_coldeflist(List *names, + List *types, List *typmods, List *collations, deparse_context *context); static void get_opclass_name(Oid opclass, Oid actual_datatype, StringInfo buf); @@ -6617,7 +6618,8 @@ get_from_clause_alias(Alias *alias, RangeTblEntry *rte, * responsible for ensuring that an alias or AS is present before it. */ static void -get_from_clause_coldeflist(List *names, List *types, List *typmods, List *collations, +get_from_clause_coldeflist(List *names, + List *types, List *typmods, List *collations, deparse_context *context) { StringInfo buf = context->buf; @@ -6651,7 +6653,8 @@ get_from_clause_coldeflist(List *names, List *types, List *typmods, List *collat appendStringInfo(buf, "%s %s", quote_identifier(attname), format_type_with_typemod(atttypid, atttypmod)); - if (attcollation && attcollation != DEFAULT_COLLATION_OID) + if (OidIsValid(attcollation) && + attcollation != get_typcollation(atttypid)) appendStringInfo(buf, " COLLATE %s", generate_collation_name(attcollation)); i++; diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/syscache.c index 2b5e37e2f05..75f510c1643 100644 --- a/src/backend/utils/cache/syscache.c +++ b/src/backend/utils/cache/syscache.c @@ -277,7 +277,7 @@ static const struct cachedesc cacheinfo[] = { Anum_pg_collation_collnamespace, 0 }, - 256 + 64 }, {CollationRelationId, /* COLLOID */ CollationOidIndexId, @@ -288,7 +288,7 @@ static const struct cachedesc cacheinfo[] = { 0, 0 }, - 256 + 64 }, {ConversionRelationId, /* CONDEFAULT */ ConversionDefaultIndexId, |