Re: altering a column's collation leaves an invalid foreign key
От | Peter Eisentraut |
---|---|
Тема | Re: altering a column's collation leaves an invalid foreign key |
Дата | |
Msg-id | 84980314-6ed5-4238-9aae-4ed7b2bb6b1a@eisentraut.org обсуждение исходный текст |
Ответ на | Re: altering a column's collation leaves an invalid foreign key (jian he <jian.universality@gmail.com>) |
Список | pgsql-hackers |
On 25.10.24 16:26, jian he wrote: > drop table if exists pktable, fktable; > CREATE TABLE pktable (x text COLLATE "POSIX" PRIMARY KEY); > CREATE TABLE fktable (x text COLLATE "C" REFERENCES pktable on update > cascade on delete cascade); > INSERT INTO pktable VALUES ('A'), ('Å'); > INSERT INTO fktable VALUES ('A'); > > update pktable set x = 'a' collate "C" where x = 'A' collate "POSIX"; > > the cascade update fktable query string is: > UPDATE ONLY "public"."fktable" SET "x" = $1 WHERE $2 OPERATOR(pg_catalog.=) "x" > ideally it should be > UPDATE ONLY "public"."fktable" SET "x" = $1 collate "C" WHERE $2 > collate "POSIX" OPERATOR(pg_catalog.=) "x" > > as we already mentioned in several places: PK-FK tie either they have to > both be deterministic or else they both have to be the same collation > oid. > so the reduction to > UPDATE ONLY "public"."fktable" SET "x" = $1 WHERE $2 OPERATOR(pg_catalog.=) "x" > is safe. > but you look at SPI_execute_snapshot, _SPI_convert_params. then we can see > the collation metadata is not keeped. > >> + We don't need to use >> + * this function for RI queries that compare a variable to a $n parameter. >> + * Since parameter symbols always have default collation, the effect will be >> + * to use the variable's collation. > > so I think a better description is >> + We don't need to use >> + * this function for RI queries that compare a variable to a $n parameter. >> + * Since parameter symbols don't have collation information, the effect will be >> + * to use the variable's collation. > > you can see related discovery in > https://www.postgresql.org/message-id/CACJufxEtPBWAk7nEn69ww2LKi9w1i4dLwd5gnjD1DQ2vaYoi2g%40mail.gmail.com I don't know. I don't think there is anything wrong with the existing code in this respect. Notably a parameter gets assigned its type's default collation (see src/backend/parser/parse_param.c), so the change of the comment as you suggest it is not correct. Also, I don't think this is actually related to the patch discussed in this thread.
В списке pgsql-hackers по дате отправления: