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.
