Clean up SQL documentation

By Hannu Krosing, applied with some fixing

17 files changed, 59 insertions, 7 deletions

diff --git a/sql/pgq_node/functions/pgq_node.change_consumer_provider.sql b/sql/pgq_node/functions/pgq_node.change_consumer_provider.sql
index 5c9f98a2..53957a59 100644
--- a/sql/pgq_node/functions/pgq_node.change_consumer_provider.sql
+++ b/sql/pgq_node/functions/pgq_node.change_consumer_provider.sql
@@ -15,6 +15,11 @@ as $$
 --      i_queue_name  - queue name
 --      i_consumer_name  - consumer name
 --      i_new_provider - node name for new provider
+-- Returns:
+--      ret_code - error code
+--      200 - ok
+--      404 - no such consumer or new node
+--      ret_note - description
 -- ----------------------------------------------------------------------
 begin
     perform 1 from pgq_node.node_location
diff --git a/sql/pgq_node/functions/pgq_node.create_node.sql b/sql/pgq_node/functions/pgq_node.create_node.sql
index 2c060cee..eaa19f24 100644
--- a/sql/pgq_node/functions/pgq_node.create_node.sql
+++ b/sql/pgq_node/functions/pgq_node.create_node.sql
@@ -25,12 +25,18 @@ returns record as $$
 --      i_combined_queue - merge-leaf: target queue
 --
 -- Returns:
+--      200 - Ok
 --      401 - node already initialized
+--      ???? - maybe we coud use more error codes ?
 --
 -- Node Types:
 --      root - master node
 --      branch - subscriber node that can be provider to others
 --      leaf - subscriber node that cannot be provider to others
+-- Calls:
+--      None
+-- Tables directly manipulated:
+--      None
 -- ----------------------------------------------------------------------
 declare
     _wm_consumer text;
diff --git a/sql/pgq_node/functions/pgq_node.demote_root.sql b/sql/pgq_node/functions/pgq_node.demote_root.sql
index 2b2340f2..420bd281 100644
--- a/sql/pgq_node/functions/pgq_node.demote_root.sql
+++ b/sql/pgq_node/functions/pgq_node.demote_root.sql
@@ -12,14 +12,20 @@ as $$
 --
 --      Multi-step root demotion to branch.
 --
---      Step 1: disable writing to queue.
---      Step 2: wait until writers go away, do tick.
---      Step 3: change type, register.
+--      Must be be called for each step in sequence:
+--
+--      Step 1 - disable writing to queue.
+--      Step 2 - wait until writers go away, do tick.
+--      Step 3 - change type, register.
 --
 -- Parameters:
 --      i_queue_name    - queue name
 --      i_step          - step number
 --      i_new_provider  - new provider node
+-- Returns:
+--      200 - success
+--      404 - node not initialized for queue 
+--      301 - node is not root
 -- ----------------------------------------------------------------------
 declare
     n_type      text;
diff --git a/sql/pgq_node/functions/pgq_node.drop_node.sql b/sql/pgq_node/functions/pgq_node.drop_node.sql
index 19c29163..0dcfed1d 100644
--- a/sql/pgq_node/functions/pgq_node.drop_node.sql
+++ b/sql/pgq_node/functions/pgq_node.drop_node.sql
@@ -23,6 +23,10 @@ returns record as $$
 --      200 - Ok
 --      304 - No such queue
 --      406 - That is a provider
+-- Calls:
+--      None
+-- Tables directly manipulated:
+--      None
 ------------------------------------------------------------------------
 declare
     _is_local   boolean;
diff --git a/sql/pgq_node/functions/pgq_node.get_subscriber_info.sql b/sql/pgq_node/functions/pgq_node.get_subscriber_info.sql
index 70934da9..7dfd2d0d 100644
--- a/sql/pgq_node/functions/pgq_node.get_subscriber_info.sql
+++ b/sql/pgq_node/functions/pgq_node.get_subscriber_info.sql
@@ -12,7 +12,8 @@ returns setof record as $$
 --      Get subscriber list for the local node.
 --
 --      It may be out-of-date, due to in-progress
---      administrative change.  Node's local provider info
+--      administrative change.
+--      Node's local provider info ( pgq_node.get_node_info() or pgq_node.get_worker_state(1) )
 --      is the authoritative source.
 --
 -- Parameters:
diff --git a/sql/pgq_node/functions/pgq_node.is_leaf_node.sql b/sql/pgq_node/functions/pgq_node.is_leaf_node.sql
index b32d0aaf..3b48c246 100644
--- a/sql/pgq_node/functions/pgq_node.is_leaf_node.sql
+++ b/sql/pgq_node/functions/pgq_node.is_leaf_node.sql
@@ -7,6 +7,8 @@ returns bool as $$
 --
 -- Parameters:
 --      i_queue_name  - queue name
+-- Returns:
+--      true - if this this the leaf node for queue 
 -- ----------------------------------------------------------------------
 declare
     res bool;
diff --git a/sql/pgq_node/functions/pgq_node.is_root_node.sql b/sql/pgq_node/functions/pgq_node.is_root_node.sql
index e8317a05..bb7205df 100644
--- a/sql/pgq_node/functions/pgq_node.is_root_node.sql
+++ b/sql/pgq_node/functions/pgq_node.is_root_node.sql
@@ -7,6 +7,8 @@ returns bool as $$
 --
 -- Parameters:
 --      i_queue_name  - queue name
+-- Returns:
+--      true - if this this the root node for queue 
 -- ----------------------------------------------------------------------
 declare
     res bool;
diff --git a/sql/pgq_node/functions/pgq_node.promote_branch.sql b/sql/pgq_node/functions/pgq_node.promote_branch.sql
index c442285f..9e2c7a26 100644
--- a/sql/pgq_node/functions/pgq_node.promote_branch.sql
+++ b/sql/pgq_node/functions/pgq_node.promote_branch.sql
@@ -11,6 +11,11 @@ as $$
 --
 -- Parameters:
 --      i_queue_name  - queue name
+--
+-- Returns:
+--      200 - success
+--      404 - node not initialized for queue 
+--      301 - node is not branch
 -- ----------------------------------------------------------------------
 declare
     n_name      text;
diff --git a/sql/pgq_node/functions/pgq_node.register_consumer.sql b/sql/pgq_node/functions/pgq_node.register_consumer.sql
index b497c407..e21a44ab 100644
--- a/sql/pgq_node/functions/pgq_node.register_consumer.sql
+++ b/sql/pgq_node/functions/pgq_node.register_consumer.sql
@@ -22,6 +22,9 @@ returns record as $$
 --
 -- Returns:
 --      ret_code - error code
+--      200 - ok
+--      201 - already registered
+--      401 - no such queue
 --      ret_note - description
 -- ----------------------------------------------------------------------
 declare
diff --git a/sql/pgq_node/functions/pgq_node.set_consumer_completed.sql b/sql/pgq_node/functions/pgq_node.set_consumer_completed.sql
index 44c15a5a..05983df2 100644
--- a/sql/pgq_node/functions/pgq_node.set_consumer_completed.sql
+++ b/sql/pgq_node/functions/pgq_node.set_consumer_completed.sql
@@ -16,6 +16,9 @@ as $$
 --      i_queue_name - cascaded queue name
 --      i_consumer_name - cascaded consumer name
 --      i_tick_id   - tick id
+-- Returns:
+--      200 - ok
+--      404 - consumer not known
 -- ----------------------------------------------------------------------
 begin
     update pgq_node.local_state
diff --git a/sql/pgq_node/functions/pgq_node.set_consumer_error.sql b/sql/pgq_node/functions/pgq_node.set_consumer_error.sql
index 6bc7ee4e..c9560dfa 100644
--- a/sql/pgq_node/functions/pgq_node.set_consumer_error.sql
+++ b/sql/pgq_node/functions/pgq_node.set_consumer_error.sql
@@ -10,6 +10,9 @@ as $$
 -- Function: pgq_node.set_consumer_error(3)
 --
 --      If batch processing fails, consumer can store it's last error in db.
+-- Returns:
+--      100 - ok
+--      101 - consumer not known
 -- ----------------------------------------------------------------------
 begin
     update pgq_node.local_state
diff --git a/sql/pgq_node/functions/pgq_node.set_consumer_paused.sql b/sql/pgq_node/functions/pgq_node.set_consumer_paused.sql
index a2da2829..31de0c55 100644
--- a/sql/pgq_node/functions/pgq_node.set_consumer_paused.sql
+++ b/sql/pgq_node/functions/pgq_node.set_consumer_paused.sql
@@ -15,6 +15,10 @@ as $$
 --      i_queue_name - cascaded queue name
 --      i_consumer_name - cascaded consumer name
 --      i_paused   - new flag state
+-- Returns:
+--      200 - ok
+--      201 - already paused
+--      404 - consumer not found
 -- ----------------------------------------------------------------------
 declare
     old_flag    boolean;
diff --git a/sql/pgq_node/functions/pgq_node.set_consumer_uptodate.sql b/sql/pgq_node/functions/pgq_node.set_consumer_uptodate.sql
index cbf565e3..a443a1d1 100644
--- a/sql/pgq_node/functions/pgq_node.set_consumer_uptodate.sql
+++ b/sql/pgq_node/functions/pgq_node.set_consumer_uptodate.sql
@@ -17,7 +17,8 @@ returns record as $$
 --      i_uptodate - new flag state
 --
 -- Returns:
---      nothing
+--      200 - ok
+--      404 - consumer not known
 -- ----------------------------------------------------------------------
 begin
     update pgq_node.local_state
diff --git a/sql/pgq_node/functions/pgq_node.set_partition_watermark.sql b/sql/pgq_node/functions/pgq_node.set_partition_watermark.sql
index d3d69f84..1f8c95b2 100644
--- a/sql/pgq_node/functions/pgq_node.set_partition_watermark.sql
+++ b/sql/pgq_node/functions/pgq_node.set_partition_watermark.sql
@@ -17,7 +17,9 @@ returns record as $$
 --      i_watermark         - partition tick_id that came inside combined-root batch
 --
 -- Returns:
---      nothing
+--      200 - success
+--      201 - no partition queue
+--      401 - worker registration not found
 -- ----------------------------------------------------------------------
 declare
     n record;
diff --git a/sql/pgq_node/functions/pgq_node.set_subscriber_watermark.sql b/sql/pgq_node/functions/pgq_node.set_subscriber_watermark.sql
index 32450c6b..b0dbe64a 100644
--- a/sql/pgq_node/functions/pgq_node.set_subscriber_watermark.sql
+++ b/sql/pgq_node/functions/pgq_node.set_subscriber_watermark.sql
@@ -11,6 +11,8 @@ returns record as $$
 --
 --      Notify provider about subscribers lowest watermark.
 --
+--      Called on provider at interval by each worker  
+--
 -- Parameters:
 --      i_queue_name - cascaded queue name
 --      i_node_name - subscriber node name
diff --git a/sql/pgq_node/functions/pgq_node.unregister_consumer.sql b/sql/pgq_node/functions/pgq_node.unregister_consumer.sql
index b8dcbd66..26d0a03b 100644
--- a/sql/pgq_node/functions/pgq_node.unregister_consumer.sql
+++ b/sql/pgq_node/functions/pgq_node.unregister_consumer.sql
@@ -16,6 +16,8 @@ returns record as $$
 --
 -- Returns:
 --      ret_code - error code
+--      200 - ok
+--      404 - no such queue
 --      ret_note - description
 -- ----------------------------------------------------------------------
 begin
diff --git a/sql/pgq_node/functions/pgq_node.unregister_location.sql b/sql/pgq_node/functions/pgq_node.unregister_location.sql
index 78725056..62dc1651 100644
--- a/sql/pgq_node/functions/pgq_node.unregister_location.sql
+++ b/sql/pgq_node/functions/pgq_node.unregister_location.sql
@@ -20,7 +20,8 @@ returns record as $$
 --
 -- Return Codes:
 --      200 - Ok
---      404 - No such set
+--      301 - Location not found
+--      403 - Cannot drop nodes own or parent location
 -- ----------------------------------------------------------------------
 declare
     _queue_name  text;