pgq_node: avoid concurrency in promote/demote

2 files changed, 4 insertions, 2 deletions

diff --git a/sql/pgq_node/functions/pgq_node.demote_root.sql b/sql/pgq_node/functions/pgq_node.demote_root.sql
index d3f68d03..a1c67aaf 100644
--- a/sql/pgq_node/functions/pgq_node.demote_root.sql
+++ b/sql/pgq_node/functions/pgq_node.demote_root.sql
@@ -36,7 +36,8 @@ declare
 begin
     select node_type, worker_name into n_type, w_name
         from pgq_node.node_info
-        where queue_name = i_queue_name;
+        where queue_name = i_queue_name
+        for update;
     if not found then
         select 404, 'Node not initialized for queue: ' || i_queue_name
           into ret_code, ret_note;
diff --git a/sql/pgq_node/functions/pgq_node.promote_branch.sql b/sql/pgq_node/functions/pgq_node.promote_branch.sql
index 9e2c7a26..c19511a9 100644
--- a/sql/pgq_node/functions/pgq_node.promote_branch.sql
+++ b/sql/pgq_node/functions/pgq_node.promote_branch.sql
@@ -26,7 +26,8 @@ declare
 begin
     select node_name, node_type, worker_name into n_name, n_type, w_name
         from pgq_node.node_info
-        where queue_name = i_queue_name;
+        where queue_name = i_queue_name
+        for update;
     if not found then
         select 404, 'Node not initialized for queue: ' || i_queue_name
           into ret_code, ret_note;