PostgreSQL plpgsql Debug - Black Screen and Text Mode Storage Procedure Debugging

By digoal

Background

Debugging the pgadmin plpgsql storage procedure relies on the pldebugger plug-in.

You do not need to use the pldebugger or pgadmin graphical mode. You can debug storage procedures in text mode. If you do not need control, print the variables in the execution process and raise them directly.

Example:

create or replace function test() returns void as $$  
declare  
begin  
  for i in 1..10 loop  
    raise notice 'i: %', i;  
  end loop;  
end;  
$$ language plpgsql strict;  
  
postgres=# select test();  
NOTICE:  i: 1  
NOTICE:  i: 2  
NOTICE:  i: 3  
NOTICE:  i: 4  
NOTICE:  i: 5  
NOTICE:  i: 6  
NOTICE:  i: 7  
NOTICE:  i: 8  
NOTICE:  i: 9  
NOTICE:  i: 10  
 test   
------  
   
(1 row)

If the control needs to be added, the following design method can be used:

1. Breakpoint Control Table

Each record indicates a breakpoint. You can update the status field to enable or disable a breakpoint.

2. Breakpoint Control Function

You can call this operation to read the status of the breakpoint control table and respond to the breakpoint status.

3. The Debugged Function

Implant breakpoint control functions
The developer executes the control of the breakpoint by modifying the value of the breakpoint control table.
Print variables

Example

External Control Table:

create table bk (  
  n name primary key,  -- 断点名称  
  status boolean default true   -- true表示开启断点, false表示退出断点  
);

Internal Control Table:

create table bk_tmp (  -- 临时状态, 用于断点重复出现的情况  
  n name primary key,  -- 断点名称  
  status boolean default true   -- true表示开启断点, false表示退出断点  
);

Breakpoint Functions:

create or replace function bkf(name) returns void as $$  
declare  
  v_stat1 boolean;  
  v_stat2 boolean;  
begin  
  select status into v_stat1 from bk where n=$1;  
  select status into v_stat2 from bk_tmp where n=$1;  
  if v_stat1 is null then  
    raise notice 'The % bk name not set, or bk deleted, will skip this bk and continue!', $1;   
    return;  
  end if;  
  
  if (v_stat1 is not null and v_stat2 is null) then  
    insert into bk_tmp values ($1) on conflict (n) do nothing;  
  end if;  
  
  loop  
    select status into v_stat1 from bk where n=$1;  -- 查看当前断点状态  
    select status into v_stat2 from bk_tmp where n=$1;  -- 查看当前断点状态  
  
    if v_stat1 <> v_stat2 then  -- 用户退出断点   
      update bk_tmp set status=v_stat1 where n=$1;   
      return;  -- 返回   
    end if;  
  
    if v_stat1 is null then   -- 用户删除断点  
      delete from bk_tmp where n=$1;  
      raise notice 'The % bk deleted, exit this bk!', $1;  
      return;  
    end if;  
  
    perform pg_sleep(1);  -- 睡眠  
  end loop;  
end;  
$$ language plpgsql strict;

Print variables-yes: The interface that needs to query pl stack context

Modify variables-yes: The interface for modifying the pl stack context needs to be modified.

The two interfaces above are not supported for the time being, so printing should be placed in the main function.

Debug the following functions, implant breakpoints, print the variables you want to know before the breakpoints, or modify the variables you want to know:

create or replace function testf (int, int) returns boolean as $$  
declare  
  res int :=0;  
begin  
  for i in $1..$2 loop  
    res := i+res;  
    raise notice 'res: %', res;  -- 打印变量  
    perform bkf('testbk');       -- 使用testbk这个断点名称  
  end loop;  
  if res >10000 then return true; else return false; end if;  
end;  
$$ language plpgsql strict;

Inserting breakpoint names into external control tables:

insert into bk values ('testbk');

Execute the function to view the breakpoint effect:

select testf(1,100);

The following figure shows the status of the executor pods when the application was run:

postgres=# select testf(1,100);  
NOTICE:  res: 1  
中断  -- 遇到断点

update bk set status =not status where n='testbk';  -- 继续

继续  
NOTICE:  res: 3  
中断

update bk set status =not status where n='testbk';  -- 继续

继续  
NOTICE:  res: 6  
中断

delete from bk where n='testbk';  -- 删除断点

After deleting a breakpoint, you can get the following results:

...
NOTICE:  The testbk bk deleted, exit this bk!  
NOTICE:  res: 21  
NOTICE:  The testbk bk name not set, or bk deleted, will skip this bk and continue!  
NOTICE:  res: 28  
NOTICE:  The testbk bk name not set, or bk deleted, will skip this bk and continue!  
NOTICE:  res: 36  
...  
NOTICE:  res: 5050  
NOTICE:  The testbk bk name not set, or bk deleted, will skip this bk and continue!  
 testf   
-------  
 f  
(1 row)

Community

PostgreSQL plpgsql Debug - Black Screen and Text Mode Storage Procedure Debugging

Background

Example

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

Database Overview

Storage Capacity Unit

AnalyticDB for PostgreSQL