From: | Dmytro Astapov <dastapov(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Using current_user as an argument of pl/pgsql function affects collation of other arguments |
Date: | 2024-08-14 14:09:17 |
Message-ID: | CAFQUnFhL0xCA9Y+OkBR73F1wwpAT0vDshVt0aTFdb7FkiGhMXA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2024-08-14 14:53:42 | BUG #18583: jsonb_populate_record return values cannot be queried correctly in subselects |
Previous Message | Tomas Vondra | 2024-08-14 11:31:02 | Re: FDW INSERT batching can change behavior |