Postgres Pro
Downloads
Home   >   mailing lists

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  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-general
Tree view
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 }

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():

 name | type | security |                        proconfig                        | volatility |  parallel  
------+------+----------+---------------------------------------------------------+------------+------------
 f    | func | invoker  |                                                         | volatile   | unsafe   

This statement:

alter function s1.f()
security definer
immutable
parallel restricted;

brings this new status:

 name | type | security |                        proconfig                        | volatility |  parallel  
------+------+----------+---------------------------------------------------------+------------+------------
 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.

alter function s1.f()
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

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

--------------------------------------------------------------------------------
-- 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;

--------------------------------------------------------------------------------
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


pgsql-general by date:

Previous
From: Michael Lewis
Date:
Subject: LwLocks contention
Next
From: "David G. Johnston"
Date:
Subject: Re: Unexpected result from ALTER FUNCTION— looks like a bug