Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Dmytro Astapov
Date:
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

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

From
Tom Lane
Date:
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