Zobrazení zdroje stránky Write a PL/pgSQL debugger alias advanced techniques of programming in PostgreSQL

[[Category:Articles]]
Translated by Vojtěch Fořt

Try to write your own debugger of stored procedures. You will soon find out that it is not a piece of cake. Why? Partly debugger is typically interactive application and area of stored procedures is hardly interactive. Moreover you are trying to debug code, which is processed remotely. I will say it right away. Without editing source codes and extending communication protocol you have no chance (I have already written one almost real PL/pgSQL debugger). But when you resign to some restriction and when you use library orface, there is some hope.

For this moment, I will restrict the functionality of debuggers to stepping trough the code. Then function of debugger is to stop on specific point of processing code, inform the service about reaching the point of interruption and wait on orders from service. It is on user if he end debugging application or have the list of variables written or he will keep debugging. There is a little snag again. Relative to PL/pgSQ implementation there is no way how to get to variables. That is a pity. But we works with database, so we can the content of variables in code save into tables and read it. But this table must be read from session debugged application. PL/pgSQL functions are run in transaction and to the finish (and confirmation of translation) the are not any changes noticeable. So we have two problems: a) pause the code, b)duplex communication in client/server environment. If you know PL/pgSQL language, then you know that solution of these problems is beyond possibilities of this language. So how? Quite brief is for PostgreSQL available supplement [http://pgfoundry.org/projects/orafce/ Orafce], which by the way contains inplemantation of packages [http://www.utexas.edu/its/unix/reference/oracledocs/v92/B10501_01/appdev.920/a96612/d_pipe.htm dbms_pipe RDBMS Oracle]. The functions from this package enable asynchronous multisession communication and that is exactly what we need. On the part of user it enable to establish named pipes and realize communication between two sessions. Pipes in RDBMS behave like system sisters. Process, which is trying to read from an empty pipe, is stopped and waits until another process write itself into the pipe. And this is the way how we can directly stop processing of an PL/pgSQL function.

Let me shortly describe library [[Oracle functionality (en)#DBMS_PIPE|dbms_pipe]]. Pipe is data abstraction (something like file) enabling communication between database clients. It is basically shared memory to which is one client writing and another is reading. Data, which is transported by pipe, are structured i.e. every message, which is saved to pipe can contain several items of different types. Whole process of the transfer consist of a few steps:
* in local memory is composed a message by adding individual items,
* the content of local memory is moved to the shared memory. From this moment any PostgresSQL client has access to the message,
* the content of shared memory is moved to the client`s local memory and the message is removed from shared memory
* the item of message is progressively loaded from local memory

We can both pipe and message take like a FIFO queues. Each pipe can be shared by several clients. But each pipe is read once only -  client, which come to it like a first, get it and remove it from shared memory.

I do not want to re-write whole dbms_pipe documentation here, which you can easy look up on google. For a start we get by with functions:


{|
|-
| dbms_pipe.pack_message(value) || save value to buffer of built-up message.
|-
| dbms_pipe.send_message(name_of_queue) || moves the message to the named queue of messages.
|-
| dbms_pipe.receive_message(name_of_queue) || from named queue of messages moves the first one to the buffer of read message.
|-
|dbms_pipe.unpack_message_type() || returns value of given type, which is first in buffer of read message.
|-
|}
For example moving number and text between two logged-in clients would be performed by following sequence of calling functions.
<pre>
SELECT dbms_pipe.pack_message(0);
SELECT dbms_pipe.pack_message('Hello');
SELECT dbms_pipe.send_message('my pipe');

SELECT dbms_pipe.receive_message('my pipe');
SELECT dbms_pipe.unpack_message_number();
SELECT dbms_pipe.unpack_message_text(); 
</pre>
Now, you have enough knowledge to write function trace(...), wich would send it`s arguments to debug client and before finishing wait on outside signal. Debugged function had to be manually appended by calling trace. For example:
<pre>
CREATE OR REPLACE FUNCTION test_loop() 
RETURNS void AS $$
BEGIN
  FOR i IN 1..10 LOOP
    trace('test_loop', 3, i::text);
  END LOOP;
END;
$$ LANGUAGE plpgsql;
</pre>
You need two functions. Mentioned function trace() and function cont, which display debugging data and push forward function trace (signal to end itself).
<pre>
CREATE OR REPLACE FUNCTION trace(_name varchar, _ln integer, _value varchar)
RETURNS void AS $$
BEGIN
  PERFORM dbms_pipe.pack_message(_name);
  PERFORM dbms_pipe.pack_message(_ln);
  PERFORM dbms_pipe.pack_message(_va);
  PERFORM dbms_pipe.send_message(dbms_pipe.unique_session_name()||'$DBG'); -- send data
  PERFORM dbms_pipe.receive_message(dbms_pipe.unique_session_name()||'$DBG$CONT'); -- wait for a signal
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION cont(_pipe varchar, OUT _fn varchar, OUT _ln integer, OUT _value varchar)
RETURNS record AS $$
BEGIN
  -- are any data available?
  PERFORM 1 FROM dbms_pipe.db_pipes  WHERE name = _name AND items > 0;
  IF FOUND THEN
    PERFORM dbms_pipe.receive_message(_pipe||'$DBG');
    _fn := dbms_pipe.unpack_message_text();
    _ln := dbms_pipe.unpack_message_number();
    _value := dbms_pipe.unpack_message_text();
    RETURN;
  ELSE
    -- is somebody waiting for signal continue?
    PERFORM 1 FROM dbms_pipe.db_pipes WHERE name = _name || '$DBG$CONT' AND items = 0;
    IF FOUND THEN
      PERFORM dbms_pipe.send_message(_name||'$DBG$CONT');
    END IF;
    -- waiting for data again
    PERFORM dbms_pipe.receive_message(_name);
    _fn := dbms_pipe.unpack_message_text();
    _ln := dbms_pipe.unpack_message_number();
    _value := dbms_pipe.unpack_message_text();
    RETURN;
  END IF;
END;
$$ LANGUAGE plpgsql;
</pre>
You can do stepping itself this way: in one window run debugging function and in another get session`s identifier by displaying table db_pipes, and repeat calling function cont.This version has disadvantage - you are not able to detect the end of processing debugged function and you have to manually cancel function cont after last cycle.
<pre>
postgres=# select test_loop();

postgres=# select * from dbms_pipe.db_pipes;
          name           | items | size | limit | private | owner
-------------------------+-------+------+-------+---------+-------
 PG$PIPE$1$4652$DBG      |     1 |   32 |       | f       |
 PG$PIPE$1$4652$DBG$CONT |     0 |    0 |       | f       |
(2 rows)

postgres=# select cont('PG$PIPE$1$4652');
       cont
------------------
 (test_loop,40,1)
(1 row)

postgres=# select cont('PG$PIPE$1$4652');
       cont
------------------
 (test_loop,40,2)
(1 row)
  
    ...
    
postgres=# select cont('PG$PIPE$1$4652');
Cancel request sent
ERROR:  canceling statement due to user request
CONTEXT:  SQL function "receive_message" statement 1
SQL statement "SELECT  dbms_pipe.receive_message( $1 ||'$DBG')"
PL/pgSQL function "cont" line 3 at perform
</pre>
You can not get by with communicating without-state protocol. It is quite complicated to implement any other functions. Two-state protocol itself solves synchronization of communication. In the first state debugged protocol reports that it has reached state of interruption and it is waiting for an order. In the second state is debugged client sending the result of processed command. I can describe mutual communication by this schema:
<pre>
A, I have reached the point    B, Wait until client ask for
   of interuption, I am           command, then send it.
   waiting for a command.         Wait for the result. 
   
A, I have processed command,   B, Display result and finish.
   sendin result and finish. 
</pre>
Process A - debugged client, Process B - debugging client. If did not tried to write debugger in PL/pgSQL I would implement process B as a loop containing display of debugging information, getting reaction from user and processing of input. Because stored procedures on principle do not offer any instrumentality how to ensure interaction with user(On the conference I recently ran into a question, how to implement MessageBox and InputBox. Answer: on principle - it is impossible), this schema can not be used. I must have all data from user already in time of calling the function. That is why I have written functions cont, exec and stop.
<pre>
CREATE OR REPLACE FUNCTION trace(_desc varchar, _ln integer, _value varchar)
RETURNS void AS $$
  DECLARE
    _request integer;
    _r record;
    _v varchar;
BEGIN
  PERFORM dbms_pipe.pack_message(0);
  -- send info about reaching point of interruption and wait for a command
  PERFORM dbms_pipe.send_message(dbms_pipe.unique_session_name()||'$DBG');
  PERFORM dbms_pipe.receive_message(dbms_pipe.unique_session_name()||'$DBG$CONT');
  _request := dbms_pipe.unpack_message_number();
  PERFORM dbms_pipe.pack_message(1);
  PERFORM dbms_pipe.pack_message(_desc);
  PERFORM dbms_pipe.pack_message(_ln);
  IF _request = 1 THEN -- return parameters
    PERFORM dbms_pipe.pack_message(_value);
  ELSIF _request = 2 THEN -- process request
    -- for retyping record->varchar is necessary to use PL/pgSQL conversion
  EXECUTE dbms_pipe.unpack_message_text() INTO _r;
    _v := _r; PERFORM dbms_pipe.pack_message(_v);
  ELSIF _request = 3 THEN -- finish of debugging
    PERFORM dbms_pipe.pack_message('Stop debuging');
    PERFORM dbms_pipe.send_message(dbms_pipe.unique_session_name()||'$DBG');
    RAISE EXCEPTION 'Stop debuging';
  END IF;
  -- send data
  PERFORM dbms_pipe.send_message(dbms_pipe.unique_session_name()||'$DBG');
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION cont(_pipe varchar, OUT _desc varchar, OUT _ln integer, OUT _value varchar)
RETURNS record AS $$
 declare  i integer;
BEGIN
  PERFORM dbms_pipe.receive_message(_pipe||'$DBG');
  IF 0 <> dbms_pipe.unpack_message_number() THEN
    RAISE EXCEPTION 'Synchronisation error';
  END IF;
  PERFORM dbms_pipe.pack_message(1);
  PERFORM dbms_pipe.send_message(_pipe||'$DBG$CONT');
  PERFORM dbms_pipe.receive_message(_pipe||'$DBG');
  IF 1 <> dbms_pipe.unpack_message_number() THEN
    RAISE EXCEPTION 'Synchronisation error';
  END IF;
  _desc := dbms_pipe.unpack_message_text();
  _ln := dbms_pipe.unpack_message_number();
  _value := dbms_pipe.unpack_message_text();
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION stop(_pipe varchar, OUT _desc varchar, OUT _ln integer, OUT _value varchar)
RETURNS record AS $$
 declare  i integer;
BEGIN
  PERFORM dbms_pipe.receive_message(_pipe||'$DBG');
  IF 0 <> dbms_pipe.unpack_message_number() THEN
    RAISE EXCEPTION 'Synchronisation error';
  END IF;
  PERFORM dbms_pipe.pack_message(2);
  PERFORM dbms_pipe.send_message(_pipe||'$DBG$CONT');
  PERFORM dbms_pipe.receive_message(_pipe||'$DBG');
  IF 1 <> dbms_pipe.unpack_message_number() THEN
    RAISE EXCEPTION 'Synchronisation error';
  END IF;
  _desc := dbms_pipe.unpack_message_text();
  _ln := dbms_pipe.unpack_message_number();
  _value := dbms_pipe.unpack_message_text();
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION exec(_pipe varchar, _query varchar, OUT _desc varchar, OUT _ln integer, OUT _value varchar)
RETURNS record AS $$
 declare  i integer;
BEGIN
  PERFORM dbms_pipe.receive_message(_pipe||'$DBG');
  IF 0 <> dbms_pipe.unpack_message_number() THEN
    RAISE EXCEPTION 'Synchronisation error';
  END IF;
  PERFORM dbms_pipe.pack_message(5);
  PERFORM dbms_pipe.pack_message(_query);
  PERFORM dbms_pipe.send_message(_pipe||'$DBG$CONT');
  PERFORM dbms_pipe.receive_message(_pipe||'$DBG');
  IF 1 <> dbms_pipe.unpack_message_number() THEN
    RAISE EXCEPTION 'Synchronisation error';
  END IF;
  _desc := dbms_pipe.unpack_message_text();
  _ln := dbms_pipe.unpack_message_number();
  _value := dbms_pipe.unpack_message_text();
END;
$$ LANGUAGE plpgsql;
</pre>
Debugging of stored procedures is not only area where can be the intersession communication used. It is very often used for mutual synchronization of procedures or for realization of architecture client-server (Unfortunately PostgreSQL is not support autonomous transactions, so I can not imagine client-server application above PL/pgSQL practically. So far.).

I do not imagine I wrote useful debugger. Communicating protocol is primitive without opportunity resynchronization, the set of commands is minimal. But more sophisticated protocol means more of code so less lucidity and perspicuity of code. True is that it was more about  exhibition of possibilities of the new library orafce than about making my own debugger. Thereby I do not say, that somebody will not use my prototype debugger. I learned myself for that couple of years to live without debugger and get by with RAISE NOTICE.