Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit921b993

Browse files
committed
Fix RI_Initial_Check to use a COLLATE clause when needed in its query.
If the referencing and referenced columns have different collations,the parser will be unable to resolve which collation to use unless it'shelped out in this way. The effects are sometimes masked, if we end upusing a non-collation-sensitive plan; but if we do use a mergejoinwe'll see a failure, as recently noted by Robert Haas.The SQL spec states that the referenced column's collation should be usedto resolve RI checks, so that's what we do. Note however that we currentlydon't append a COLLATE clause when writing a query that examines only thereferencing column. If we ever support collations that have varyingnotions of equality, that will have to be changed. For the moment, though,it's preferable to leave it off so that we can use a normal index on thereferencing column.
1 parent5caa347 commit921b993

File tree

5 files changed

+128
-1
lines changed

5 files changed

+128
-1
lines changed

‎src/backend/parser/parse_relation.c

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2398,6 +2398,24 @@ attnumTypeId(Relation rd, int attid)
23982398
returnrd->rd_att->attrs[attid-1]->atttypid;
23992399
}
24002400

2401+
/*
2402+
* given attribute id, return collation of that attribute
2403+
*
2404+
*This should only be used if the relation is already heap_open()'ed.
2405+
*/
2406+
Oid
2407+
attnumCollationId(Relationrd,intattid)
2408+
{
2409+
if (attid <=0)
2410+
{
2411+
/* All system attributes are of noncollatable types. */
2412+
returnInvalidOid;
2413+
}
2414+
if (attid>rd->rd_att->natts)
2415+
elog(ERROR,"invalid attribute number %d",attid);
2416+
returnrd->rd_att->attrs[attid-1]->attcollation;
2417+
}
2418+
24012419
/*
24022420
* Generate a suitable error about a missing RTE.
24032421
*

‎src/backend/utils/adt/ri_triggers.c

Lines changed: 57 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -32,6 +32,7 @@
3232

3333
#include"access/xact.h"
3434
#include"access/sysattr.h"
35+
#include"catalog/pg_collation.h"
3536
#include"catalog/pg_constraint.h"
3637
#include"catalog/pg_operator.h"
3738
#include"catalog/pg_type.h"
@@ -82,6 +83,7 @@
8283

8384
#defineRIAttName(rel,attnum)NameStr(*attnumAttName(rel, attnum))
8485
#defineRIAttType(rel,attnum)attnumTypeId(rel, attnum)
86+
#defineRIAttCollation(rel,attnum)attnumCollationId(rel, attnum)
8587

8688
#defineRI_TRIGTYPE_INSERT 1
8789
#defineRI_TRIGTYPE_UPDATE 2
@@ -194,6 +196,7 @@ static void ri_GenerateQual(StringInfo buf,
194196
Oidopoid,
195197
constchar*rightop,Oidrightoptype);
196198
staticvoidri_add_cast_to(StringInfobuf,Oidtypid);
199+
staticvoidri_GenerateQualCollation(StringInfobuf,Oidcollation);
197200
staticintri_NullCheck(Relationrel,HeapTupletup,
198201
RI_QueryKey*key,intpairidx);
199202
staticvoidri_BuildQueryKeyFull(RI_QueryKey*key,
@@ -2681,6 +2684,9 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
26812684
* (fk.keycol1 IS NOT NULL [AND ...])
26822685
* For MATCH FULL:
26832686
* (fk.keycol1 IS NOT NULL [OR ...])
2687+
*
2688+
* We attach COLLATE clauses to the operators when comparing columns
2689+
* that have different collations.
26842690
*----------
26852691
*/
26862692
initStringInfo(&querybuf);
@@ -2707,6 +2713,8 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
27072713
{
27082714
Oidpk_type=RIAttType(pk_rel,riinfo.pk_attnums[i]);
27092715
Oidfk_type=RIAttType(fk_rel,riinfo.fk_attnums[i]);
2716+
Oidpk_coll=RIAttCollation(pk_rel,riinfo.pk_attnums[i]);
2717+
Oidfk_coll=RIAttCollation(fk_rel,riinfo.fk_attnums[i]);
27102718

27112719
quoteOneName(pkattname+3,
27122720
RIAttName(pk_rel,riinfo.pk_attnums[i]));
@@ -2716,6 +2724,8 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
27162724
pkattname,pk_type,
27172725
riinfo.pf_eq_oprs[i],
27182726
fkattname,fk_type);
2727+
if (pk_coll!=fk_coll)
2728+
ri_GenerateQualCollation(&querybuf,pk_coll);
27192729
sep="AND";
27202730
}
27212731

@@ -2978,6 +2988,53 @@ ri_add_cast_to(StringInfo buf, Oid typid)
29782988
ReleaseSysCache(typetup);
29792989
}
29802990

2991+
/*
2992+
* ri_GenerateQualCollation --- add a COLLATE spec to a WHERE clause
2993+
*
2994+
* At present, we intentionally do not use this function for RI queries that
2995+
* compare a variable to a $n parameter. Since parameter symbols always have
2996+
* default collation, the effect will be to use the variable's collation.
2997+
* Now that is only strictly correct when testing the referenced column, since
2998+
* the SQL standard specifies that RI comparisons should use the referenced
2999+
* column's collation. However, so long as all collations have the same
3000+
* notion of equality (which they do, because texteq reduces to bitwise
3001+
* equality), there's no visible semantic impact from using the referencing
3002+
* column's collation when testing it, and this is a good thing to do because
3003+
* it lets us use a normal index on the referencing column. However, we do
3004+
* have to use this function when directly comparing the referencing and
3005+
* referenced columns, if they are of different collations; else the parser
3006+
* will fail to resolve the collation to use.
3007+
*/
3008+
staticvoid
3009+
ri_GenerateQualCollation(StringInfobuf,Oidcollation)
3010+
{
3011+
HeapTupletp;
3012+
Form_pg_collationcolltup;
3013+
char*collname;
3014+
charonename[MAX_QUOTED_NAME_LEN];
3015+
3016+
/* Nothing to do if it's a noncollatable data type */
3017+
if (!OidIsValid(collation))
3018+
return;
3019+
3020+
tp=SearchSysCache1(COLLOID,ObjectIdGetDatum(collation));
3021+
if (!HeapTupleIsValid(tp))
3022+
elog(ERROR,"cache lookup failed for collation %u",collation);
3023+
colltup= (Form_pg_collation)GETSTRUCT(tp);
3024+
collname=NameStr(colltup->collname);
3025+
3026+
/*
3027+
* We qualify the name always, for simplicity and to ensure the query
3028+
* is not search-path-dependent.
3029+
*/
3030+
quoteOneName(onename,get_namespace_name(colltup->collnamespace));
3031+
appendStringInfo(buf," COLLATE %s",onename);
3032+
quoteOneName(onename,collname);
3033+
appendStringInfo(buf,".%s",onename);
3034+
3035+
ReleaseSysCache(tp);
3036+
}
3037+
29813038
/* ----------
29823039
* ri_BuildQueryKeyFull -
29833040
*

‎src/include/parser/parse_relation.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -89,5 +89,6 @@ extern List *expandRelAttrs(ParseState *pstate, RangeTblEntry *rte,
8989
externintattnameAttNum(Relationrd,constchar*attname,boolsysColOK);
9090
externNameattnumAttName(Relationrd,intattid);
9191
externOidattnumTypeId(Relationrd,intattid);
92+
externOidattnumCollationId(Relationrd,intattid);
9293

9394
#endif/* PARSE_RELATION_H */

‎src/test/regress/expected/collate.out

Lines changed: 28 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -542,13 +542,37 @@ SELECT relname, pg_get_indexdef(oid) FROM pg_class WHERE relname LIKE 'collate_t
542542
collate_test1_idx4 | CREATE INDEX collate_test1_idx4 ON collate_test1 USING btree (((b || 'foo'::text)) COLLATE "POSIX")
543543
(4 rows)
544544

545+
-- foreign keys
546+
-- force indexes and mergejoins to be used for FK checking queries,
547+
-- else they might not exercise collation-dependent operators
548+
SET enable_seqscan TO 0;
549+
SET enable_hashjoin TO 0;
550+
SET enable_nestloop TO 0;
551+
CREATE TABLE collate_test20 (f1 text COLLATE "C" PRIMARY KEY);
552+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "collate_test20_pkey" for table "collate_test20"
553+
INSERT INTO collate_test20 VALUES ('foo'), ('bar');
554+
CREATE TABLE collate_test21 (f2 text COLLATE "POSIX" REFERENCES collate_test20);
555+
INSERT INTO collate_test21 VALUES ('foo'), ('bar');
556+
INSERT INTO collate_test21 VALUES ('baz'); -- fail
557+
ERROR: insert or update on table "collate_test21" violates foreign key constraint "collate_test21_f2_fkey"
558+
DETAIL: Key (f2)=(baz) is not present in table "collate_test20".
559+
CREATE TABLE collate_test22 (f2 text COLLATE "POSIX");
560+
INSERT INTO collate_test22 VALUES ('foo'), ('bar'), ('baz');
561+
ALTER TABLE collate_test22 ADD FOREIGN KEY (f2) REFERENCES collate_test20; -- fail
562+
ERROR: insert or update on table "collate_test22" violates foreign key constraint "collate_test22_f2_fkey"
563+
DETAIL: Key (f2)=(baz) is not present in table "collate_test20".
564+
DELETE FROM collate_test22 WHERE f2 = 'baz';
565+
ALTER TABLE collate_test22 ADD FOREIGN KEY (f2) REFERENCES collate_test20;
566+
RESET enable_seqscan;
567+
RESET enable_hashjoin;
568+
RESET enable_nestloop;
545569
--
546570
-- Clean up. Many of these table names will be re-used if the user is
547571
-- trying to run any platform-specific collation tests later, so we
548572
-- must get rid of them.
549573
--
550574
DROP SCHEMA collate_tests CASCADE;
551-
NOTICE: drop cascades to12 other objects
575+
NOTICE: drop cascades to15 other objects
552576
DETAIL: drop cascades to table collate_test1
553577
drop cascades to table collate_test_like
554578
drop cascades to table collate_test2
@@ -561,3 +585,6 @@ drop cascades to view collview2
561585
drop cascades to view collview3
562586
drop cascades to type testdomain
563587
drop cascades to function dup(anyelement)
588+
drop cascades to table collate_test20
589+
drop cascades to table collate_test21
590+
drop cascades to table collate_test22

‎src/test/regress/sql/collate.sql

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -187,6 +187,30 @@ CREATE INDEX collate_test1_idx6 ON collate_test1 ((a COLLATE "POSIX")); -- fail
187187

188188
SELECT relname, pg_get_indexdef(oid)FROM pg_classWHERE relnameLIKE'collate_test%_idx%'ORDER BY1;
189189

190+
191+
-- foreign keys
192+
193+
-- force indexes and mergejoins to be used for FK checking queries,
194+
-- else they might not exercise collation-dependent operators
195+
SET enable_seqscan TO0;
196+
SET enable_hashjoin TO0;
197+
SET enable_nestloop TO0;
198+
199+
CREATETABLEcollate_test20 (f1text COLLATE"C"PRIMARY KEY);
200+
INSERT INTO collate_test20VALUES ('foo'), ('bar');
201+
CREATETABLEcollate_test21 (f2text COLLATE"POSIX"REFERENCES collate_test20);
202+
INSERT INTO collate_test21VALUES ('foo'), ('bar');
203+
INSERT INTO collate_test21VALUES ('baz');-- fail
204+
CREATETABLEcollate_test22 (f2text COLLATE"POSIX");
205+
INSERT INTO collate_test22VALUES ('foo'), ('bar'), ('baz');
206+
ALTERTABLE collate_test22 ADDFOREIGN KEY (f2)REFERENCES collate_test20;-- fail
207+
DELETEFROM collate_test22WHERE f2='baz';
208+
ALTERTABLE collate_test22 ADDFOREIGN KEY (f2)REFERENCES collate_test20;
209+
210+
RESET enable_seqscan;
211+
RESET enable_hashjoin;
212+
RESET enable_nestloop;
213+
190214
--
191215
-- Clean up. Many of these table names will be re-used if the user is
192216
-- trying to run any platform-specific collation tests later, so we

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp