在pgadmin中debug lightdb plpgsql存储过程

LightDB分布式数据库非官方技术站点 / 2023-05-18 / 原文

1、lightdb默认集成了pldebugger。pg用户也可从https://github.com/EnterpriseDB/pldebugger下载最新版本release，如1.5版本，放到contrib目录，解压，编译。

[zjh@hs-10-20-30-193 pldebugger]$ make
make -C ../../src/backend generated-headers
make[1]: Entering directory `/home/zjh/Sources/postgresql-13.3/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/home/zjh/Sources/postgresql-13.3/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/zjh/Sources/postgresql-13.3/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/home/zjh/Sources/postgresql-13.3/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/zjh/Sources/postgresql-13.3/src/backend/utils'
make[1]: Leaving directory `/home/zjh/Sources/postgresql-13.3/src/backend'
[zjh@hs-10-20-30-193 pldebugger]$ make install
make -C ../../src/backend generated-headers
make[1]: Entering directory `/home/zjh/Sources/postgresql-13.3/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/home/zjh/Sources/postgresql-13.3/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/zjh/Sources/postgresql-13.3/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/home/zjh/Sources/postgresql-13.3/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/zjh/Sources/postgresql-13.3/src/backend/utils'
make[1]: Leaving directory `/home/zjh/Sources/postgresql-13.3/src/backend'
/usr/bin/mkdir -p '/home/zjh/stage/lightdb-x/lib'
/usr/bin/mkdir -p '/home/zjh/stage/lightdb-x/share/extension'
/usr/bin/mkdir -p '/home/zjh/stage/lightdb-x/share/extension'
/usr/bin/mkdir -p '/home/zjh/stage/lightdb-x/share/doc//extension'
/usr/bin/install -c -m 755  plugin_debugger.so '/home/zjh/stage/lightdb-x/lib/plugin_debugger.so'
/usr/bin/install -c -m 644 ./pldbgapi.control '/home/zjh/stage/lightdb-x/share/extension/'
/usr/bin/install -c -m 644 ./pldbgapi--1.1.sql ./pldbgapi--unpackaged--1.1.sql ./pldbgapi--1.0--1.1.sql  '/home/zjh/stage/lightdb-x/share/extension/'
/usr/bin/install -c -m 644 ./README.pldebugger '/home/zjh/stage/lightdb-x/share/doc//extension/'

2、在lightdb.conf的shared_preload_libraries中增加plugin_debugger，如下：

shared_preload_libraries=lt_stat_statements,lt_stat_activity,lt_prewarm,lt_cron,lt_hint_plan,lt_show_plans,lt_sql_inspect,plugin_debugger

3、重启lightdb实例，lt_ctl -D $LTDATA restart

4、在对应的数据库创建pldbgapi。如下：

create extension pldbgapi;

成功后，在ide中可以看到创建了一堆函数，如下：

5、创建测试函数如下：

CREATE OR REPLACE FUNCTION public.somefunc()
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
<< outerblock >>
DECLARE
    quantity integer := 30;
BEGIN
    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 30
    quantity := 50;
    --
    -- Create a subblock
    --
    DECLARE
        quantity integer := 80;
    BEGIN
        RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 80
        RAISE NOTICE 'Outer quantity here is %', outerblock.quantity;  -- Prints 50
    END;
 
    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 50
 
    RETURN quantity;
END;
$function$
;

-- 创建测试存储过程
drop table if exists accounts;

create table accounts (
    id int generated by default as identity,
    name varchar(100) not null,
    balance dec(15,2) not null,
    primary key(id)
);

insert into accounts(name,balance)
values('Bob',10000);

insert into accounts(name,balance)
values('Alice',10000);
select * from accounts;

create or replace procedure transfer(
   sender int,
   receiver int, 
   amount dec
)
language plpgsql    
as $$
begin
    -- subtracting the amount from the sender's account 
    update accounts 
    set balance = balance - amount 
    where id = sender;

    -- adding the amount to the receiver's account
    update accounts 
    set balance = balance + amount 
    where id = receiver;

    commit;
end;$$