pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing current_user as an argument of pl/pgsql function affects collation of other arguments
2+ messages / 2 participants
[nested] [flat]
* Using current_user as an argument of pl/pgsql function affects collation of other arguments
@ 2024-08-14 14:09 Dmytro Astapov <dastapov@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Dmytro Astapov @ 2024-08-14 14:09 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Hi!
OS: Debian, Rock Linux
Postgres versions: 13.6, 15.6
As the subject implies, I am observing that
current_user/session_user/current_database/current_schema, when used as an
argument for pl/pgsql function, could affect execution plans of unrelated
queries inside that pl/pgsql function -- because they seemingly affect
collation for other arguments (I am not 100% sure about this last claim,
but the observed effects suggest that this might be the case).
Consider this function which does not use its second argument at all and
prints out EXPLAIN for a simple query that looks up rows in a table by a
value of an indexed column:
create table tbl(id text, payload text);
create index on tbl(id);
insert into tbl(id, payload) values ('1','111'), ('2','222'), ('3','333');
CREATE OR REPLACE FUNCTION select_test(id_to_update text, unused_string
text)
RETURNS void
LANGUAGE plpgsql
AS $function$
declare
_line text;
begin
for _line in
explain select *
from tbl
where id = id_to_update
loop
raise notice '%', _line;
end loop;
end;
$function$;
If we pass a string literal as a second argument (select
select_test('1','')) then everything works as expected: query does index
scan over 'tbl', using the index on 'id':
# select select_test('1','');
NOTICE: 00000: Bitmap Heap Scan on tbl (cost=1.28..5.48 rows=4 width=64)
NOTICE: 00000: Recheck Cond: (id = '1'::text)
NOTICE: 00000: -> Bitmap Index Scan on tbl_id_idx (cost=0.00..1.28
rows=4 width=0)
NOTICE: 00000: Index Cond: (id = '1'::text)
However, if we pass current_user as a second argument (which is completely
unused in the body of the function), the plan suddenly changes:
# select select_test('1',current_user);
NOTICE: 00000: Seq Scan on tbl (cost=0.00..21.00 rows=4 width=64)
NOTICE: 00000: Filter: (id = '1'::text COLLATE "C")
Note that '1' had suddenly become ('1'::text COLLATE "C"), and this
prevents the use of the index (which has collation 'en_US.UTF8').
The same behavior could be observed by using current_user as a first
argument, or as a part of an expression for any of the arguments, such as:
select select_test('1',''||current_user);
select select_test(current_user,'');
select select_test('1',current_user::text);
Same thing happens with session_user, current_schema and current_database()
The only variant that works (that I was able to find) is:
select select_test('1','current_user'::text);
I am observing this on 13.6, 15.6.
My untested theory is that this has something to do with
current_user/session_user/etc having InvalidOid as the collation id here:
https://github.com/postgres/postgres/blob/master/src/backend/executor/execExprInterp.c#L2687
Best regards, Dmytro
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Using current_user as an argument of pl/pgsql function affects collation of other arguments
@ 2024-08-15 20:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dmytro Astapov <dastapov@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2024-08-15 20:32 UTC (permalink / raw)
To: Dmytro Astapov <dastapov@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
Dmytro Astapov <dastapov@gmail.com> writes:
> As the subject implies, I am observing that
> current_user/session_user/current_database/current_schema, when used as an
> argument for pl/pgsql function, could affect execution plans of unrelated
> queries inside that pl/pgsql function -- because they seemingly affect
> collation for other arguments (I am not 100% sure about this last claim,
> but the observed effects suggest that this might be the case).
This is expected behavior. Those functions return type "name" not
"text", and "name" is considered to have C collation. Then, in
a call such as
select_test('1',current_user);
that is the only source of collation in the expression and so
select_test is invoked with an input collation of "C", rather
than whatever the database's default is.
The most robust solution probably is to write
explain select *
from tbl
where id = id_to_update COLLATE "default"
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-08-15 20:32 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-08-14 14:09 Using current_user as an argument of pl/pgsql function affects collation of other arguments Dmytro Astapov <dastapov@gmail.com>
2024-08-15 20:32 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox