Behavior of PL/pgSQL function following drop and re-create of a table that it uses - Mailing list pgsql-general
| From | Bryn Llewellyn |
|---|---|
| Subject | Behavior of PL/pgSQL function following drop and re-create of a table that it uses |
| Date | |
| Msg-id | 46DE0CE7-7E90-480D-8395-74E5309F7D26@yugabyte.com Whole thread Raw |
| Responses |
Re: Behavior of PL/pgSQL function following drop and re-create of a table that it uses
("David G. Johnston" <david.g.johnston@gmail.com>)
|
| List | pgsql-general |
I copied my self-contained testcase, and its output (using PG Version 15.2), at the end.
I read the doc section "43.11. PL/pgSQL under the Hood": www.postgresql.org/docs/15/plpgsql-implementation.html
Is my mental model, described below, right?
1. With function "s.f()" as presented, the attempt to create it when table "s.t" doesn't yet exist fails with the 42601 syntax error: « invalid type name "s.t.v%type" ». This is expected because some tests must succeed at "create time" else the attempt becomes a no-op.
2. Following creating "s.f()" after creating table "s.t", executing it, and then dropping "s.t", the pg_proc row for "s.f()" remains intact with the original source text. This reflects the fact that the successful "create" didn't record any dependency info so that PG doesn't know what the human observer knows.
3. After "s.t" is re-instated, now with a different data type for "t.v", the SQL query reports the new column data type (and the new content). After all, this is just ordinary query behavior reflecting the current state of the db. However the reported type of the local variable "v_out" is still "text" (as it was at create time) and not "varchar" as it now is. This nominal error reflects the fact that the representation of the to-be-interpreted function, in session memory, was built when "s.f()" was first executed and is now cached. Because there are no dependencies, nothing tells PG to rebuild the representation of the to-be-interpreted function, in session memory.
5. After re-connecting, we have a brand-new session with as yet no cached info. Therefore, the representation of the to-be-interpreted function must be rebuilt when it's first referenced. And now, it sees table "s.t" with a "varchar" column.
6. All this leads to rather obvious practice advice: DDLs on objects that an application uses (at least when the app's database artifacts include PL/pgSQL subprograms) are unsafe while the app is in use. You must stop all client-sessions before doing such DDLs and re-start them only when all DDLs are done successfully.
________________________________________________________________________________
-- Connect as an ordinary user to a convenient database.
\c d0 d0$u0
prepare f_prosrc as
select prosrc
from
pg_proc p
inner join
pg_namespace n
on p.pronamespace = n.oid
inner join
pg_roles r
on p.proowner = r.oid
where p.proname = 'f'
and n.nspname = 's'
and r.rolname = $1;
create schema s;
create table s.t(k serial primary key, v text);
insert into s.t(v) values ('cat-1'), ('dog-1'), ('mouse-1');
create function s.f(k_in in int)
returns text
security definer
set search_path = pg_catalog, pg_temp
language plpgsql
as $body$
declare
v_type text not null := '';
v_out s.t.v%type not null := '';
begin
select pg_typeof(t.v)::text, t.v
into strict v_type, v_out
from s.t
where t.k = k_in;
return 'pg_typeof(t.v): ' ||v_type ||' / '||
'pg_typeof(v_out): '||pg_typeof(v_out)::text ||' / '||
'value: ' ||v_out;
end;
$body$;
select '----------';
select s.f(1);
drop table s.t cascade;
select '----------';
execute f_prosrc('d0$u0');
create table s.t(k serial primary key, v varchar(10));
insert into s.t(v) values ('cat-2'), ('dog-2'), ('mouse-2');
-- "s.f()" still reports "text" for "pg_typeof(v_out)".
select '----------';
select s.f(1);
\c d0 d0$u0
-- Only now have we lost the cached result of "compiling" the function "s.f()".
-- Now reports "character varying” for "pg_typeof(v_out)".
select '----------';
select s.f(1);
\c d0 d0$u0
prepare f_prosrc as
select prosrc
from
pg_proc p
inner join
pg_namespace n
on p.pronamespace = n.oid
inner join
pg_roles r
on p.proowner = r.oid
where p.proname = 'f'
and n.nspname = 's'
and r.rolname = $1;
create schema s;
create table s.t(k serial primary key, v text);
insert into s.t(v) values ('cat-1'), ('dog-1'), ('mouse-1');
create function s.f(k_in in int)
returns text
security definer
set search_path = pg_catalog, pg_temp
language plpgsql
as $body$
declare
v_type text not null := '';
v_out s.t.v%type not null := '';
begin
select pg_typeof(t.v)::text, t.v
into strict v_type, v_out
from s.t
where t.k = k_in;
return 'pg_typeof(t.v): ' ||v_type ||' / '||
'pg_typeof(v_out): '||pg_typeof(v_out)::text ||' / '||
'value: ' ||v_out;
end;
$body$;
select '----------';
select s.f(1);
drop table s.t cascade;
select '----------';
execute f_prosrc('d0$u0');
create table s.t(k serial primary key, v varchar(10));
insert into s.t(v) values ('cat-2'), ('dog-2'), ('mouse-2');
-- "s.f()" still reports "text" for "pg_typeof(v_out)".
select '----------';
select s.f(1);
\c d0 d0$u0
-- Only now have we lost the cached result of "compiling" the function "s.f()".
-- Now reports "character varying” for "pg_typeof(v_out)".
select '----------';
select s.f(1);
RESULTS (using “\t on” mode)
----------
pg_typeof(t.v): text / pg_typeof(v_out): text / value: cat-1
----------
< The expected "language plpgsql" source text — verbatim as it was entered, including "%type" (untranslated). >
pg_typeof(t.v): text / pg_typeof(v_out): text / value: cat-1
----------
< The expected "language plpgsql" source text — verbatim as it was entered, including "%type" (untranslated). >
----------
pg_typeof(t.v): character varying / pg_typeof(v_out): text / value: cat-2
----------
pg_typeof(t.v): character varying / pg_typeof(v_out): character varying / value: cat-2
pg_typeof(t.v): character varying / pg_typeof(v_out): text / value: cat-2
----------
pg_typeof(t.v): character varying / pg_typeof(v_out): character varying / value: cat-2
pgsql-general by date: