pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18676: Execute function while selecting from table with partial index using this function.
3+ messages / 3 participants
[nested] [flat]
* BUG #18676: Execute function while selecting from table with partial index using this function.
@ 2024-10-28 14:58 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 3+ messages in thread
From: PG Bug reporting form @ 2024-10-28 14:58 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: poliv78@gmail.com
The following bug has been logged on the website:
Bug reference: 18676
Logged by: Yevhen Polyvoda
Email address: poliv78@gmail.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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18676: Execute function while selecting from table with partial index using this function.
@ 2024-10-28 15:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2024-10-28 15:42 UTC (permalink / raw)
To: poliv78@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> 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.
[ shrug... ] There is no bug here. The planner is entitled to
investigate the properties of indexes attached to a table it's
trying to plan for. Sure, this index is not relevant to this
query --- but how's the planner going to know that without
examining the index? And part of that examination involves
reducing any partial-index predicate to standard form, which
includes constant-folding. Since your function is marked
IMMUTABLE, that means it should be evaluated and reduced to
a constant.
Part of the contract for marking a function IMMUTABLE is that
it has no interesting side-effects. This example breaks that
in two ways: the RAISE NOTICE is a side-effect, and the
possibility of a permissions failure is another one.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18676: Execute function while selecting from table with partial index using this function.
@ 2024-10-29 05:09 Muhammad Waqas <waqas.m@bitnine.net>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 3+ messages in thread
From: Muhammad Waqas @ 2024-10-29 05:09 UTC (permalink / raw)
To: poliv78@gmail.com; pgsql-bugs@lists.postgresql.org
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@postgresql.org>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18676
> Logged by: Yevhen Polyvoda
> Email address: poliv78@gmail.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.m@bitnine.net <waqas.m@bitnine.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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-10-29 05:09 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-10-28 14:58 BUG #18676: Execute function while selecting from table with partial index using this function. PG Bug reporting form <noreply@postgresql.org>
2024-10-28 15:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-10-29 05:09 ` Muhammad Waqas <waqas.m@bitnine.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox