Re: BUG #18676: Execute function while selecting from table with partial index using this function.

i am also facing such error but it is resolved after reconnect the session
and execute ALTER TABLE test_table OWNER TO agens;

On Mon, 28 Oct 2024 at 20:10, PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference: 18676
> Logged by: Yevhen Polyvoda
> Email address: poliv78(at)gmail(dot)com
> PostgreSQL version: 17.0
> Operating system: macos Sequoia 15.0
> Description:
>
> Please run the script below. It creates sample table with partial index
> using IMMUTABLE function.
> I think when table is changing - index in changing and thus function used
> in
> WHERE cluse can be run.
> But it is not supposed to be run on just simple SELECT from table without
> even condition to use this index.
>
> ---------------------------------------------------
> CREATE TABLE IF NOT EXISTS public.test_table
> (
> id integer NOT NULL
> );
>
> CREATE OR REPLACE FUNCTION public.test_function(
> )
> RETURNS integer
> LANGUAGE 'plpgsql'
> COST 100
> IMMUTABLE PARALLEL UNSAFE
> AS $BODY$
> DECLARE
> BEGIN
> raise notice 'test';
> RETURN 1;
> END
> $BODY$;
> REVOKE ALL ON FUNCTION public.test_function() FROM PUBLIC;
>
> CREATE INDEX IF NOT EXISTS test_idx
> ON public.test_table USING btree
> (id)
> WHERE id = public.test_function();
>
> create role test_role LOGIN PASSWORD 'test';
>
> ------------------------------------------------
> set role = 'test_role';
> select * from public.test_table;
>
> we will get permission denied because while selecting somehow function
> public.test_function() runs.
>
>

--
*Muhammad Waqas*
*Senior Technical Support Engineer - Tech Support Center (Karachi)*
Mobile: +92-322-2844150
Email: *waqas(dot)m(at)bitnine(dot)net <waqas(dot)m(at)bitnine(dot)net>*
1st floor, suit no F-04, COLABS Karachi, 8-C Khayaban-e-Tanzeem,
DHA Phase 5 Tauheed Commercial Area,
Defense V Defense Housing Authority, Karachi, Karachi City, Sindh,
75500 Pakistan.