Search Postgresql Archives

Behavior of PL/pgSQL function following drop and re-create of a table that it uses

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux