Thread

Using current_user as an argument of pl/pgsql function affects collation of other arguments

Dmytro Astapov <dastapov@gmail.com> — 2024-08-14T14:09:17Z

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