issue and workaround examples

issue_and_workarounds.sql

/*
   TRYING TO UNDERSTAND THIS:

   #1 - call twice nameit directly with expected results
   #2 - call twice nameit from caller1 with unexpected results!
   #3 - call nameit twice from caller2 which works
   #4 - the issue can also be avoided by calling 2nd time in another session
*/

-- setup
drop schema if exists issue_repo cascade;
create schema issue_repo; set schema 'issue_repo';

create or replace procedure caller1 () as $proc$
  begin call nameit('t'); end;
$proc$ language plpgsql;

create or replace procedure caller2 () as $proc$
  --fix: cast parameter ('t') to text when calling
  begin call nameit('t'::text); end;
$proc$ language plpgsql;

create or replace procedure nameit (target regclass) as $proc$
  begin raise notice '👻 target: %', target; end;
$proc$ language plpgsql;


-- examples:
create table t();

-- EX #1: as expected, notice shows name of table
call nameit ('t');
drop table t;
create table t();
call nameit ('t');

-- EX #2: 2nd notice shows OID of table!
call caller1();
drop table t;
create table t();
call caller1();

-- EX #3: as expected, notice shows name of table
call caller2 (); -- fixed version
drop table t;
create table t();
call caller2 ();

-- EX #4: re-connecting also prevents the unexpected result
-- re-connect
\c
set schema 'issue_repo';
call caller1();
drop table t;
create table t();
-- re-connect between proc calls
\c
set schema 'issue_repo';
call caller1();

🙂 No longer an issue with postgres 16.