Postgres Pro
Downloads
Home   >   mailing lists

Using current_user as an argument of pl/pgsql function affects collation of other arguments - Mailing list pgsql-bugs

From Dmytro Astapov
Subject Using current_user as an argument of pl/pgsql function affects collation of other arguments
Date
Msg-id CAFQUnFhL0xCA9Y+OkBR73F1wwpAT0vDshVt0aTFdb7FkiGhMXA@mail.gmail.com
Whole thread Raw
Responses Re: Using current_user as an argument of pl/pgsql function affects collation of other arguments
List pgsql-bugs
Tree view
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

pgsql-bugs by date:

Previous
From: "Yeddula, Madhusudhan reddy [CONTINGENT WORKER]"
Date:
Subject: RE: BUG #18569: Memory leak in Postgres Enterprise server
Next
From: PG Bug reporting form
Date:
Subject: BUG #18583: jsonb_populate_record return values cannot be queried correctly in subselects