Dear hackers,
I was looking at how foreign keys deal with collations, and I came across this comment about not
re-checking a foreign key if the column type changes in a compatible way:
* Since we require that all collations share the same notion of
* equality (which they do, because texteq reduces to bitwise
* equality), we don't compare collation here.
But now that we have nondeterministic collations, isn't that out of date?
For instance I could make this foreign key:
paul=# create collation itext (provider = 'icu', locale = 'und-u-ks-level1',
deterministic = false);
CREATE COLLATION
paul=# create table t1 (id text collate itext primary key);
CREATE TABLE
paul=# create table t2 (id text, parent_id text references t1);
CREATE TABLE
And then:
paul=# insert into t1 values ('a');
INSERT 0 1
paul=# insert into t2 values ('.', 'A');
INSERT 0 1
So far that behavior seems correct, because the user told us 'a' and 'A' were
equivalent,
but now I can change the collation on the referenced table and the FK doesn't
complain:
paul=# alter table t1 alter column id type text collate "C";
ALTER TABLE
The constraint claims to be valid, but I can't drop & add it:
paul=# alter table t2 drop constraint t2_parent_id_fkey;
ALTER TABLE
paul=# alter table t2 add constraint t2_parent_id_fkey foreign key (parent_id)
references t1;
ERROR: insert or update on table "t2" violates foreign key constraint
"t2_parent_id_fkey"
DETAIL: Key (parent_id)=(A) is not present in table "t1".
Isn't that a problem?
Perhaps if the previous collation was nondeterministic we should force a
re-check.
(Tested on 17devel 697f8d266c and also 16.)
Yours,
--
Paul ~{:-)
p...@illuminatedcomputing.com
