Unexpected result from ALTER FUNCTION— looks like a bug - Mailing list pgsql-general
| From | Bryn Llewellyn |
|---|---|
| Subject | Unexpected result from ALTER FUNCTION— looks like a bug |
| Date | |
| Msg-id | 8AC9A37F-99BD-446F-A2F7-B89AD0022774@yugabyte.com Whole thread Raw |
| Responses |
Re: Unexpected result from ALTER FUNCTION— looks like a bug
|
| List | pgsql-general |
SUMMARY
This part of the syntax diagram for "alter function":
ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] action [ … ]
says that the first "action" can be followed (without punctuation) by zero, one, or many other actions. A semantic rule says that no particular action can be specified more than once. My tests used these possible actions:
SECURITY { INVOKER | DEFINER }
SET configuration_parameter TO value
IMMUTABLE | STABLE | VOLATILE
PARALLEL { UNSAFE | RESTRICTED | SAFE }
IMMUTABLE | STABLE | VOLATILE
PARALLEL { UNSAFE | RESTRICTED | SAFE }
The values of the properties set this way can be seen with a suitable query against "pg_catalog.pg_proc". (See the complete testcase below.) Suppose that the history of events shows this status for the function s1.f():
------+------+----------+---------------------------------------------------------+------------+------------
f | func | invoker | | volatile | unsafe
This statement:
alter function s1.f()
security definer
immutable
parallel restricted;
brings this new status:
security definer
immutable
parallel restricted;
brings this new status:
name | type | security | proconfig | volatility | parallel
------+------+----------+---------------------------------------------------------+------------+------------
f | func | definer | | immutable | restricted
------+------+----------+---------------------------------------------------------+------------+------------
f | func | definer | | immutable | restricted
confirming that the three specified changes have been made using just a single "alter function" statement.
However, when "SET configuration_parameter" is specified along with other changes, then the "parallel" specification (but only this) is ignored. The other three specifications are honored.
security invoker
set timezone = 'UTC'
stable
parallel safe;
It brings this new status:
name | type | security | proconfig | volatility | parallel
------+------+----------+---------------------------------------------------------+------------+------------
f | func | invoker | {TimeZone=UTC} | stable | restricted
------+------+----------+---------------------------------------------------------+------------+------------
f | func | invoker | {TimeZone=UTC} | stable | restricted
This is the bug.
Notice that with "alter procedure", the semantic difference between a procedure and a function means that you cannot specify "parallel" here, and so you can't demonstrate the bug here.
SELF-CONTAINED, RE-RUNNABLE TESTCASE tested using PG Version 14.1
--------------------------------------------------------------------------------
-- demo.sql
-----------
\o spool.txt
\c postgres postgres
set client_min_messages = warning;
drop database if exists db;
create database db owner postgres;
\c db postgres
set client_min_messages = warning;
drop schema if exists public cascade;
create schema s1 authorization postgres;
\i prepare-qry.sql
create function s1.f()
returns int
language plpgsql
as $body$
begin
return 0;
end;
$body$;
\t off
execute qry;
alter function s1.f()
security definer
immutable
parallel restricted;
\t on
execute qry;
-- Here is the bug. The test is meaningful only for a function.
alter function s1.f()
security invoker
set timezone = 'UTC'
stable
parallel safe;
execute qry;
\o
\c postgres postgres
set client_min_messages = warning;
drop database if exists db;
create database db owner postgres;
\c db postgres
set client_min_messages = warning;
drop schema if exists public cascade;
create schema s1 authorization postgres;
\i prepare-qry.sql
create function s1.f()
returns int
language plpgsql
as $body$
begin
return 0;
end;
$body$;
\t off
execute qry;
alter function s1.f()
security definer
immutable
parallel restricted;
\t on
execute qry;
-- Here is the bug. The test is meaningful only for a function.
alter function s1.f()
security invoker
set timezone = 'UTC'
stable
parallel safe;
execute qry;
\o
--------------------------------------------------------------------------------
-- prepare-qry.sql
------------------
drop view if exists s1.subprograms cascade;
create view s1.subprograms(
name,
pronamespace,
type,
security,
proconfig,
volatility,
parallel)
as
select
proname::text as name,
pronamespace::regnamespace::text,
case prokind
when 'a' then 'agg'
when 'w' then 'window'
when 'p' then 'proc'
else 'func'
end,
case
when prosecdef then 'definer'
else 'invoker'
end,
coalesce(proconfig::text, '') as proconfig,
case
when provolatile = 'i' then 'immutable'
when provolatile = 's' then 'stable'
when provolatile = 'v' then 'volatile'
end,
case
when proparallel = 'r' then 'restricted'
when proparallel = 's' then 'safe'
when proparallel = 'u' then 'unsafe'
end
from pg_catalog.pg_proc
where
proowner::regrole::text = 'postgres' and
pronamespace::regnamespace::text = 's1' and
pronargs = 0;
prepare qry as
select
rpad(name, 4) as name,
rpad(type, 4) as type,
rpad(security, 8) as security,
rpad(proconfig, 55) as proconfig,
rpad(volatility, 10) as volatility,
rpad(parallel, 10) as parallel
from s1.subprograms
where type in ('func', 'proc')
and pronamespace::regnamespace::text = 's1'
order by name;
create view s1.subprograms(
name,
pronamespace,
type,
security,
proconfig,
volatility,
parallel)
as
select
proname::text as name,
pronamespace::regnamespace::text,
case prokind
when 'a' then 'agg'
when 'w' then 'window'
when 'p' then 'proc'
else 'func'
end,
case
when prosecdef then 'definer'
else 'invoker'
end,
coalesce(proconfig::text, '') as proconfig,
case
when provolatile = 'i' then 'immutable'
when provolatile = 's' then 'stable'
when provolatile = 'v' then 'volatile'
end,
case
when proparallel = 'r' then 'restricted'
when proparallel = 's' then 'safe'
when proparallel = 'u' then 'unsafe'
end
from pg_catalog.pg_proc
where
proowner::regrole::text = 'postgres' and
pronamespace::regnamespace::text = 's1' and
pronargs = 0;
prepare qry as
select
rpad(name, 4) as name,
rpad(type, 4) as type,
rpad(security, 8) as security,
rpad(proconfig, 55) as proconfig,
rpad(volatility, 10) as volatility,
rpad(parallel, 10) as parallel
from s1.subprograms
where type in ('func', 'proc')
and pronamespace::regnamespace::text = 's1'
order by name;
--------------------------------------------------------------------------------
spool.txt
---------
name | type | security | proconfig | volatility | parallel
------+------+----------+---------------------------------------------------------+------------+------------
f | func | invoker | | volatile | unsafe
f | func | definer | | immutable | restricted
f | func | invoker | {TimeZone=UTC} | stable | restricted
------+------+----------+---------------------------------------------------------+------------+------------
f | func | invoker | | volatile | unsafe
f | func | definer | | immutable | restricted
f | func | invoker | {TimeZone=UTC} | stable | restricted
pgsql-general by date: