How to debug PostgreSQL function with pgAdminIII

來源:互聯網
上載者:User

標籤:

How to debug plpgsql with pgAdminIII

[[email protected] soft_bak]# git clone git://git.postgresql.org/git/pldebugger.git

Initialized empty Git repository in /opt/soft_bak/pldebugger/.git/

remote: Counting objects: 445, done.

remote: Compressing objects: 100% (341/341), done.

remote: Total 445 (delta 285), reused 171 (delta 104)

Receiving objects: 100% (445/445), 170.50 KiB | 54 KiB/s, done.

Resolving deltas: 100% (285/285), done.

[[email protected] soft_bak]# cd pldebugger/

[[email protected] pldebugger]# ls

dbgcomm.c   Makefile           pldbgapi.control               pldebugger.proj     plugin_debugger.def  uninstall_pldbgapi.sql

dbgcomm.h   pldbgapi--1.0.sql  pldbgapi--unpackaged--1.0.sql  plpgsql_debugger.c  README.pldebugger

globalbp.h  pldbgapi.c         pldebugger.h                   plugin_debugger.c   settings.projinc

[[email protected] soft_bak]# cd postgresql-9.4.5/contrib/

[[email protected] contrib]# cp  -R /opt/soft_bak/pldebugger/ ./

[[email protected] contrib]# cd pldebugger/

[[email protected] pldebugger]# make

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I../../src/pl/plpgsql/src -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o plpgsql_debugger.o plpgsql_debugger.c

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o plugin_debugger.o plugin_debugger.c

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o dbgcomm.o dbgcomm.c

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o pldbgapi.o pldbgapi.c

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -shared -o plugin_debugger.so plpgsql_debugger.o plugin_debugger.o dbgcomm.o pldbgapi.o -L../../src/port -L../../src/common -Wl,--as-needed -Wl,-rpath,‘/ext4/pgdefaultgcc530/lib‘,--enable-new-dtags 

[[email protected] pldebugger]# make install

/bin/mkdir -p ‘/ext4/pgdefaultgcc530/lib/postgresql‘

/bin/mkdir -p ‘/ext4/pgdefaultgcc530/share/postgresql/extension‘

/bin/mkdir -p ‘/ext4/pgdefaultgcc530/share/postgresql/extension‘

/bin/mkdir -p ‘/ext4/pgdefaultgcc530/share/doc//postgresql/extension‘

/usr/bin/install -c -m 755  plugin_debugger.so ‘/ext4/pgdefaultgcc530/lib/postgresql/plugin_debugger.so‘

/usr/bin/install -c -m 644 pldbgapi.control ‘/ext4/pgdefaultgcc530/share/postgresql/extension/‘

/usr/bin/install -c -m 644 pldbgapi--1.0.sql pldbgapi--unpackaged--1.0.sql ‘/ext4/pgdefaultgcc530/share/postgresql/extension/‘

/usr/bin/install -c -m 644 README.pldebugger ‘/ext4/pgdefaultgcc530/share/doc//postgresql/extension/‘

 

[[email protected] ~]# vim /ext4/pgdefaultgcc530/data/postgresql.conf

shared_preload_libraries = ‘$libdir/plugin_debugger‘

[[email protected] ~]# su - postgres

[[email protected] ~]$ cd /ext4/pgdefaultgcc530/bin/

[[email protected] bin]$ ./pg_ctl -D /ext4/pgdefaultgcc530/data/ restart

waiting for server to shut down.... done

server stopped

server starting

[[email protected] bin]$ LOG:  redirecting log output to logging collector process

HINT:  Future log output will appear in directory "pg_log".

 

[[email protected] bin]$ ./psql -h localhost -U postgres

psql (9.4.5)

Type "help" for help.

 

postgres=# CREATE EXTENSION pldbgapi;

CREATE EXTENSION

postgres=# CREATE TABLE accounts(owner text, balance numeric, amount numeric);

CREATE TABLE

postgres=# INSERT INTO accounts VALUES (‘Bob‘,100);

INSERT 0 1

postgres=# INSERT INTO accounts VALUES (‘Mary‘,200);

INSERT 0 1

postgres=# select * from accounts ;

 owner | balance | amount

-------+---------+--------

 Bob   |     100 |      

 Mary  |     200 |      

(2 rows)

postgres=# CREATE OR REPLACE FUNCTION transfer(

postgres(# i_payer text,

postgres(# i_recipient text,

postgres(# i_amount numeric(15,2))

postgres-# RETURNS text

postgres-# AS

postgres-# $$

postgres$# DECLARE

postgres$# payer_bal numeric;

postgres$# BEGIN

postgres$# SELECT balance INTO payer_bal

postgres$# FROM accounts

postgres$# WHERE owner = i_payer FOR UPDATE;

postgres$# IF NOT FOUND THEN

postgres$# RETURN ‘Payer account not found‘;

postgres$# END IF;

postgres$# IF payer_bal < i_amount THEN

postgres$# RETURN ‘Not enough funds‘;

postgres$# END IF;

postgres$# UPDATE accounts

postgres$# SET balance = balance + i_amount

postgres$# WHERE owner = i_recipient;

postgres$# IF NOT FOUND THEN

postgres$# RETURN ‘Recipient does not exist‘;

postgres$# END IF;

postgres$# UPDATE accounts

postgres$# SET balance = balance - i_amount

postgres$# WHERE owner = i_payer;

postgres$# RETURN ‘OK‘;

postgres$# END;

postgres$# $$ LANGUAGE plpgsql;

CREATE FUNCTION

postgres=# \df

                                                          List of functions

 Schema |            Name             | Result data type |                       Argument data types                        |  Type 

--------+-----------------------------+------------------+------------------------------------------------------------------+--------

 public | pldbg_abort_target          | SETOF boolean    | session integer                                                  | normal

 public | pldbg_attach_to_port        | integer          | portnumber integer                                               | normal

 public | pldbg_continue              | breakpoint       | session integer                                                  | normal

 public | pldbg_create_listener       | integer          |                                                                  | normal

 public | pldbg_deposit_value         | boolean          | session integer, varname text, linenumber integer, value text    | normal

 public | pldbg_drop_breakpoint       | boolean          | session integer, func oid, linenumber integer                    | normal

 public | pldbg_get_breakpoints       | SETOF breakpoint | session integer                                                  | normal

 public | pldbg_get_proxy_info        | proxyinfo        |                                                                  | normal

 public | pldbg_get_source            | text             | session integer, func oid                                        | normal

 public | pldbg_get_stack             | SETOF frame      | session integer                                                  | normal

 public | pldbg_get_target_info       | targetinfo       | signature text, targettype "char"                                | normal

 public | pldbg_get_variables         | SETOF var        | session integer                                                  | normal

 public | pldbg_oid_debug             | integer          | functionoid oid                                                  | normal

 public | pldbg_select_frame          | breakpoint       | session integer, frame integer                                   | normal

 public | pldbg_set_breakpoint        | boolean          | session integer, func oid, linenumber integer                    | normal

 public | pldbg_set_global_breakpoint | boolean          | session integer, func oid, linenumber integer, targetpid integer | normal

 public | pldbg_step_into             | breakpoint       | session integer                                                  | normal

 public | pldbg_step_over             | breakpoint       | session integer                                                  | normal

 public | pldbg_wait_for_breakpoint   | breakpoint       | session integer                                                  | normal

 public | pldbg_wait_for_target       | integer          | session integer                                                  | normal

 public | plpgsql_oid_debug           | integer          | functionoid oid                                                  | normal

 public | transfer                    | text             | i_payer text, i_recipient text, i_amount numeric                 | normal

(22 rows)

Connect to PostgreSQL Server with pgAdminIII

Find to postgreSQL function to be debuged transfer

Right click the transfer function and Input the parameter to be test

 

How to debug PostgreSQL function with pgAdminIII

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.