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.
