convert insert_transfer_details into stored procedure, may still need some debugging, but tests (still) pass

4 files changed, 330 insertions, 241 deletions

diff --git a/src/backenddb/pg_insert_transfer.c b/src/backenddb/pg_insert_transfer.c
index 6e8b8adf..f47a6832 100644
--- a/src/backenddb/pg_insert_transfer.c
+++ b/src/backenddb/pg_insert_transfer.c
@@ -41,7 +41,7 @@ TMH_PG_insert_transfer (
     GNUNET_PQ_query_param_string (exchange_url),
     GNUNET_PQ_query_param_auto_from_type (wtid),
     TALER_PQ_query_param_amount_with_currency (pg->conn,
-                                 credit_amount),
+                                               credit_amount),
     GNUNET_PQ_query_param_string (payto_uri),
     GNUNET_PQ_query_param_bool (confirmed),
     GNUNET_PQ_query_param_string (instance_id),
diff --git a/src/backenddb/pg_insert_transfer_details.c b/src/backenddb/pg_insert_transfer_details.c
index 66859649..facb5763 100644
--- a/src/backenddb/pg_insert_transfer_details.c
+++ b/src/backenddb/pg_insert_transfer_details.c
@@ -42,261 +42,129 @@ TMH_PG_insert_transfer_details (
   const struct TALER_EXCHANGE_TransferData *td)
 {
   struct PostgresClosure *pg = cls;
+  unsigned int len = td->details_length;
+  struct TALER_Amount coin_values[GNUNET_NZL (len)];
+  struct TALER_Amount deposit_fees[GNUNET_NZL (len)];
+  const struct TALER_CoinSpendPublicKeyP *coin_pubs[GNUNET_NZL (len)];
+  const struct TALER_PrivateContractHashP *contract_terms[GNUNET_NZL (len)];
   enum GNUNET_DB_QueryStatus qs;
-  uint64_t credit_serial;
-  unsigned int retries;
 
-  retries = 0;
-  check_connection (pg);
-
-  PREPARE (pg,
-           "lookup_credit_serial",
-           "SELECT"
-           " credit_serial"
-           " FROM merchant_transfers"
-           " WHERE exchange_url=$1"
-           "   AND wtid=$4"
-           "   AND account_serial="
-           "        (SELECT account_serial"
-           "           FROM merchant_accounts"
-           "          WHERE payto_uri=$2"
-           "            AND exchange_url=$1"
-           "            AND merchant_serial="
-           "            (SELECT merchant_serial"
-           "               FROM merchant_instances"
-           "              WHERE merchant_id=$3))");
-  PREPARE (pg,
-           "insert_transfer_signature",
-           "INSERT INTO merchant_transfer_signatures"
-           "(credit_serial"
-           ",signkey_serial"
-           ",credit_amount"
-           ",wire_fee"
-           ",execution_time"
-           ",exchange_sig) "
-           "SELECT $1, signkey_serial, $2, $3, $4, $5"
-           " FROM merchant_exchange_signing_keys"
-           " WHERE exchange_pub=$6"
-           "  ORDER BY start_date DESC"
-           "  LIMIT 1");
-  PREPARE (pg,
-           "insert_transfer_to_coin_mapping",
-           "INSERT INTO merchant_transfer_to_coin"
-           "(deposit_serial"
-           ",credit_serial"
-           ",offset_in_exchange_list"
-           ",exchange_deposit_value"
-           ",exchange_deposit_fee) "
-           "SELECT dep.deposit_serial, $1, $2, $3, $4"
-           " FROM merchant_deposits dep"
-           " JOIN merchant_deposit_confirmations dcon"
-           "   USING (deposit_confirmation_serial)"
-           " JOIN merchant_contract_terms cterm"
-           "   USING (order_serial)"
-           " WHERE dep.coin_pub=$5"
-           "   AND cterm.h_contract_terms=$6"
-           "   AND cterm.merchant_serial="
-           "     (SELECT merchant_serial"
-           "        FROM merchant_instances"
-           "       WHERE merchant_id=$7)");
-  PREPARE (pg,
-           "update_wired_by_coin_pub",
-           "WITH affected_orders AS" /* select orders affected by the coin */
-           "(SELECT mcon.order_serial"
-           "   FROM merchant_deposits dep"
-           /* Next 2 joins ensure transfers exist in the first place */
-           "   JOIN merchant_deposit_to_transfer"
-           "     USING (deposit_serial)"
-           "   JOIN merchant_transfers mtrans"
-           "     USING (credit_serial)"
-           "   JOIN merchant_deposit_confirmations mcon"
-           "     USING (deposit_confirmation_serial)"
-           "  WHERE dep.coin_pub=$1)"
-           "UPDATE merchant_contract_terms "
-           " SET wired=TRUE "
-           " WHERE order_serial IN "
-           "  (SELECT order_serial"
-           "    FROM merchant_deposit_confirmations dcon"
-           "    JOIN affected_orders"
-           "      USING (order_serial)"
-           "    WHERE NOT EXISTS "
-           "    (SELECT 1"
-           "       FROM merchant_deposits dep"
-           "       JOIN merchant_deposit_to_transfer"
-           "         USING (deposit_serial)"
-           "       JOIN merchant_transfers mtrans"
-           "         USING (credit_serial)"
-           "      WHERE dep.deposit_confirmation_serial = dcon.deposit_confirmation_serial"
-           "        AND NOT mtrans.confirmed))");
-
-RETRY:
-  if (MAX_RETRIES < ++retries)
-    return GNUNET_DB_STATUS_SOFT_ERROR;
-  if (GNUNET_OK !=
-      TMH_PG_start_read_committed (pg,
-                                   "insert transfer details"))
+  for (unsigned int i = 0; i<len; i++)
   {
-    GNUNET_break (0);
-    return GNUNET_DB_STATUS_HARD_ERROR;
-  }
+    const struct TALER_TrackTransferDetails *tdd = &td->details[i];
 
-  /* lookup credit serial */
-  {
-    struct GNUNET_PQ_QueryParam params[] = {
-      GNUNET_PQ_query_param_string (exchange_url),
-      GNUNET_PQ_query_param_string (payto_uri),
-      GNUNET_PQ_query_param_string (instance_id),
-      GNUNET_PQ_query_param_auto_from_type (wtid),
-      GNUNET_PQ_query_param_end
-    };
-    struct GNUNET_PQ_ResultSpec rs[] = {
-      GNUNET_PQ_result_spec_uint64 ("credit_serial",
-                                    &credit_serial),
-      GNUNET_PQ_result_spec_end
-    };
-
-    qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn,
-                                                   "lookup_credit_serial",
-                                                   params,
-                                                   rs);
-    if (0 > qs)
-    {
-      GNUNET_break (GNUNET_DB_STATUS_SOFT_ERROR == qs);
-      TMH_PG_rollback (pg);
-      if (GNUNET_DB_STATUS_SOFT_ERROR == qs)
-        goto RETRY;
-      GNUNET_log (GNUNET_ERROR_TYPE_INFO,
-                  "'lookup_credit_serial' for account %s and amount %s failed with status %d\n",
-                  payto_uri,
-                  TALER_amount2s (&td->total_amount),
-                  qs);
-      return qs;
-    }
-    if (GNUNET_DB_STATUS_SUCCESS_NO_RESULTS == qs)
-    {
-      TMH_PG_rollback (pg);
-      GNUNET_log (GNUNET_ERROR_TYPE_INFO,
-                  "'lookup_credit_serial' for account %s failed with transfer unknown\n",
-                  payto_uri);
-      return GNUNET_DB_STATUS_SUCCESS_NO_RESULTS;
-    }
+    coin_values[i] = tdd->coin_value;
+    deposit_fees[i] = tdd->coin_fee;
+    coin_pubs[i] = &tdd->coin_pub;
+    contract_terms[i] = &tdd->h_contract_terms;
   }
 
-  /* update merchant_transfer_signatures table */
+  check_connection (pg);
+  PREPARE (pg,
+           "insert_transfer_details",
+           "SELECT"
+           " out_no_instance"
+           ",out_no_account"
+           ",out_no_exchange"
+           ",out_duplicate"
+           ",out_conflict"
+           " FROM merchant_do_insert_transfer_details"
+           " ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13);");
+
+  for (unsigned int retries = 0;
+       retries < MAX_RETRIES;
+       retries++)
   {
-    struct GNUNET_PQ_QueryParam params[] = {
-      GNUNET_PQ_query_param_uint64 (&credit_serial),
-      TALER_PQ_query_param_amount_with_currency (pg->conn,
-                                                 &td->total_amount),
-      TALER_PQ_query_param_amount_with_currency (pg->conn,
-                                                 &td->wire_fee),
-      GNUNET_PQ_query_param_timestamp (&td->execution_time),
-      GNUNET_PQ_query_param_auto_from_type (&td->exchange_sig),
-      GNUNET_PQ_query_param_auto_from_type (&td->exchange_pub),
-      GNUNET_PQ_query_param_end
-    };
-
-    qs = GNUNET_PQ_eval_prepared_non_select (pg->conn,
-                                             "insert_transfer_signature",
-                                             params);
-    if (0 > qs)
+    if (GNUNET_OK !=
+        TMH_PG_start_read_committed (pg,
+                                     "insert transfer details"))
     {
-      GNUNET_break (GNUNET_DB_STATUS_SOFT_ERROR == qs);
-      TMH_PG_rollback (pg);
-      if (GNUNET_DB_STATUS_SOFT_ERROR == qs)
-        goto RETRY;
-      GNUNET_log (GNUNET_ERROR_TYPE_INFO,
-                  "'insert_transfer_signature' failed with status %d\n",
-                  qs);
-      return qs;
-    }
-    if (0 == qs)
-    {
-      TMH_PG_rollback (pg);
-      GNUNET_log (GNUNET_ERROR_TYPE_INFO,
-                  "'insert_transfer_signature' failed with status %d\n",
-                  qs);
+      GNUNET_break (0);
       return GNUNET_DB_STATUS_HARD_ERROR;
     }
-  }
-
-  /* Update transfer-coin association table */
-  GNUNET_log (GNUNET_ERROR_TYPE_DEBUG,
-              "Updating transfer-coin association table\n");
-  for (unsigned int i = 0; i<td->details_length; i++)
-  {
-    const struct TALER_TrackTransferDetails *d = &td->details[i];
-    uint64_t i64 = (uint64_t) i;
-    struct GNUNET_PQ_QueryParam params[] = {
-      GNUNET_PQ_query_param_uint64 (&credit_serial),
-      GNUNET_PQ_query_param_uint64 (&i64),
-      TALER_PQ_query_param_amount_with_currency (pg->conn,
-                                                 &d->coin_value),
-      TALER_PQ_query_param_amount_with_currency (pg->conn,
-                                                 &d->coin_fee), /* deposit fee */
-      GNUNET_PQ_query_param_auto_from_type (&d->coin_pub),
-      GNUNET_PQ_query_param_auto_from_type (&d->h_contract_terms),
-      GNUNET_PQ_query_param_string (instance_id),
-      GNUNET_PQ_query_param_end
-    };
 
-    qs = GNUNET_PQ_eval_prepared_non_select (pg->conn,
-                                             "insert_transfer_to_coin_mapping",
-                                             params);
-    if (0 > qs)
     {
-      GNUNET_break (GNUNET_DB_STATUS_SOFT_ERROR == qs);
-      TMH_PG_rollback (pg);
-      if (GNUNET_DB_STATUS_SOFT_ERROR == qs)
-        goto RETRY;
+      struct GNUNET_PQ_QueryParam params[] = {
+        GNUNET_PQ_query_param_string (instance_id),
+        GNUNET_PQ_query_param_string (exchange_url),
+        GNUNET_PQ_query_param_string (payto_uri),
+        GNUNET_PQ_query_param_auto_from_type (wtid),
+        GNUNET_PQ_query_param_timestamp (&td->execution_time),
+        GNUNET_PQ_query_param_auto_from_type (&td->exchange_pub),
+        GNUNET_PQ_query_param_auto_from_type (&td->exchange_sig),
+        TALER_PQ_query_param_amount_with_currency (pg->conn,
+                                                   &td->total_amount),
+        TALER_PQ_query_param_amount_with_currency (pg->conn,
+                                                   &td->wire_fee),
+        TALER_PQ_query_param_array_amount_with_currency (
+          len,
+          coin_values,
+          pg->conn),
+        TALER_PQ_query_param_array_amount_with_currency (
+          len,
+          deposit_fees,
+          pg->conn),
+        GNUNET_PQ_query_param_array_ptrs_auto_from_type (
+          len,
+          coin_pubs,
+          pg->conn),
+        GNUNET_PQ_query_param_array_ptrs_auto_from_type (
+          len,
+          contract_terms,
+          pg->conn),
+        GNUNET_PQ_query_param_end
+      };
+      bool no_instance;
+      bool no_account;
+      bool no_exchange;
+      bool duplicate;
+      bool conflict;
+      struct GNUNET_PQ_ResultSpec rs[] = {
+        GNUNET_PQ_result_spec_bool ("out_no_instance",
+                                    &no_instance),
+        GNUNET_PQ_result_spec_bool ("out_no_account",
+                                    &no_account),
+        GNUNET_PQ_result_spec_bool ("out_no_exchange",
+                                    &no_exchange),
+        GNUNET_PQ_result_spec_bool ("out_duplicate",
+                                    &duplicate),
+        GNUNET_PQ_result_spec_bool ("out_conflict",
+                                    &conflict),
+        GNUNET_PQ_result_spec_end
+      };
+
+      qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn,
+                                                     "insert_transfer_details",
+                                                     params,
+                                                     rs);
+      if (0 >= qs)
+      {
+        GNUNET_break (GNUNET_DB_STATUS_SOFT_ERROR == qs);
+        TMH_PG_rollback (pg);
+        if (GNUNET_DB_STATUS_SOFT_ERROR == qs)
+          continue;
+        GNUNET_log (GNUNET_ERROR_TYPE_INFO,
+                    "'insert_transfer_details' failed with status %d\n",
+                    qs);
+        return qs;
+      }
       GNUNET_log (GNUNET_ERROR_TYPE_INFO,
-                  "'insert_transfer_to_coin_mapping' failed with status %d\n",
-                  qs);
-      return qs;
-    }
-    if (0 == qs)
-    {
-      GNUNET_log (GNUNET_ERROR_TYPE_WARNING,
-                  "'insert_transfer_to_coin_mapping' failed at %u: deposit unknown\n",
-                  i);
+                  "Transfer details inserted: %s%s%s%s%s\n",
+                  no_instance ? "no instance " : "",
+                  no_account ? "no account " : "",
+                  no_exchange ? "no exchange ": "",
+                  duplicate ? "duplicate ": "",
+                  conflict ? "conflict" : "");
     }
-  }
-  /* Update merchant_contract_terms 'wired' status: for all coins
-     that were wired, set the respective order's "wired" status to
-     true, *if* all other deposited coins associated with that order
-     have also been wired (this time or earlier) */
-  GNUNET_log (GNUNET_ERROR_TYPE_DEBUG,
-              "Updating contract terms 'wired' status\n");
-  for (unsigned int i = 0; i<td->details_length; i++)
-  {
-    const struct TALER_TrackTransferDetails *d = &td->details[i];
-    struct GNUNET_PQ_QueryParam params[] = {
-      GNUNET_PQ_query_param_auto_from_type (&d->coin_pub),
-      GNUNET_PQ_query_param_end
-    };
 
-    qs = GNUNET_PQ_eval_prepared_non_select (pg->conn,
-                                             "update_wired_by_coin_pub",
-                                             params);
-    if (0 > qs)
-    {
-      GNUNET_break (GNUNET_DB_STATUS_SOFT_ERROR == qs);
-      TMH_PG_rollback (pg);
-      if (GNUNET_DB_STATUS_SOFT_ERROR == qs)
-        goto RETRY;
-      GNUNET_log (GNUNET_ERROR_TYPE_INFO,
-                  "'update_wired_by_coin_pub' failed with status %d\n",
-                  qs);
-      return qs;
-    }
+    GNUNET_log (GNUNET_ERROR_TYPE_DEBUG,
+                "Committing transaction...\n");
+    qs = TMH_PG_commit (pg);
+    if (GNUNET_DB_STATUS_SUCCESS_NO_RESULTS == qs)
+      return GNUNET_DB_STATUS_SUCCESS_ONE_RESULT;
+    GNUNET_break (GNUNET_DB_STATUS_SOFT_ERROR == qs);
+    if (GNUNET_DB_STATUS_SOFT_ERROR != qs)
+      break;
   }
-  GNUNET_log (GNUNET_ERROR_TYPE_DEBUG,
-              "Committing transaction...\n");
-  qs = TMH_PG_commit (pg);
-  if (GNUNET_DB_STATUS_SUCCESS_NO_RESULTS == qs)
-    return GNUNET_DB_STATUS_SUCCESS_ONE_RESULT;
-  GNUNET_break (GNUNET_DB_STATUS_SOFT_ERROR == qs);
-  if (GNUNET_DB_STATUS_SOFT_ERROR == qs)
-    goto RETRY;
   return qs;
 }
diff --git a/src/backenddb/pg_insert_transfer_details.sql b/src/backenddb/pg_insert_transfer_details.sql
new file mode 100644
index 00000000..bf57bc74
--- /dev/null
+++ b/src/backenddb/pg_insert_transfer_details.sql
@@ -0,0 +1,220 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2024 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
+-- A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING.  If not, see <http://www.gnu.org/licenses/>
+--
+
+
+CREATE OR REPLACE FUNCTION merchant_do_insert_transfer_details (
+  IN in_instance_id TEXT,
+  IN in_exchange_url TEXT,
+  IN in_payto_uri TEXT,
+  IN in_wtid BYTEA,
+  IN in_execution_time INT8,
+  IN in_exchange_pub BYTEA,
+  IN in_exchange_sig BYTEA,
+  IN in_total_amount taler_amount_currency,
+  IN in_wire_fee taler_amount_currency,
+  IN ina_coin_values taler_amount_currency[],
+  IN ina_deposit_fees taler_amount_currency[],
+  IN ina_coin_pubs BYTEA[],
+  IN ina_contract_terms BYTEA[],
+  OUT out_no_instance BOOL,
+  OUT out_no_account BOOL,
+  OUT out_no_exchange BOOL,
+  OUT out_duplicate BOOL,
+  OUT out_conflict BOOL)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  my_merchant_id INT8;
+  my_signkey_serial INT8;
+  my_credit_serial INT8;
+  my_affected_orders RECORD;
+  i INT8;
+  curs CURSOR (arg_coin_pub BYTEA) FOR
+    SELECT mcon.order_serial
+      FROM merchant_deposits dep
+      -- Next 2 joins ensure transfers exist in the first place
+      JOIN merchant_deposit_to_transfer
+        USING (deposit_serial)
+      JOIN merchant_transfers mtrans
+        USING (credit_serial)
+      JOIN merchant_deposit_confirmations mcon
+        USING (deposit_confirmation_serial)
+      WHERE dep.coin_pub=arg_coin_pub;
+  ini_coin_pub BYTEA;
+  ini_contract_term BYTEA;
+  ini_coin_value taler_amount_currency;
+  ini_deposit_fee taler_amount_currency;
+BEGIN
+
+-- Which instance are we using?
+SELECT merchant_serial
+  INTO my_merchant_id
+  FROM merchant_instances
+ WHERE merchant_id=in_instance_id;
+
+IF NOT FOUND
+THEN
+  out_no_instance=TRUE;
+  out_no_account=FALSE;
+  out_no_exchange=FALSE;
+  out_duplicate=FALSE;
+  out_conflict=FALSE;
+  RETURN;
+END IF;
+out_no_instance=FALSE;
+
+-- Determine account that was credited.
+SELECT credit_serial
+  INTO my_credit_serial
+  FROM merchant_transfers
+ WHERE exchange_url=in_exchange_url
+     AND wtid=in_wtid
+     AND account_serial=
+     (SELECT account_serial
+        FROM merchant_accounts
+       WHERE payto_uri=in_payto_uri
+         AND exchange_url=in_exchange_url
+         AND merchant_serial=my_merchant_id);
+
+IF NOT FOUND
+THEN
+  out_no_account=TRUE;
+  out_no_exchange=FALSE;
+  out_duplicate=FALSE;
+  out_conflict=FALSE;
+  RETURN;
+END IF;
+out_no_account=FALSE;
+
+-- Find exchange sign key
+SELECT signkey_serial
+  INTO my_signkey_serial
+  FROM merchant_exchange_signing_keys
+ WHERE exchange_pub=in_exchange_pub
+   ORDER BY start_date DESC
+   LIMIT 1;
+
+IF NOT FOUND
+THEN
+  out_no_exchange=TRUE;
+  out_conflict=FALSE;
+  out_duplicate=FALSE;
+  RETURN;
+END IF;
+out_no_exchange=FALSE;
+
+-- Add signature first, check for idempotent request
+INSERT INTO merchant_transfer_signatures
+  (credit_serial
+  ,signkey_serial
+  ,credit_amount
+  ,wire_fee
+  ,execution_time
+  ,exchange_sig)
+  VALUES
+   (my_credit_serial
+   ,my_signkey_serial
+   ,in_total_amount
+   ,in_wire_fee
+   ,in_execution_time
+   ,in_exchange_sig)
+  ON CONFLICT DO NOTHING;
+
+IF NOT FOUND
+THEN
+  PERFORM 1
+    FROM merchant_transfer_signatures
+    WHERE credit_serial=my_credit_serial
+      AND signkey_serial=my_signkey_serial
+      AND credit_amount=in_credit_amount
+      AND wire_fee=in_wire_fee
+      AND execution_time=in_execution_time
+      AND exchange_sig=in_exchange_sig;
+  IF FOUND
+  THEN
+    -- duplicate case
+    out_duplicate=TRUE;
+    out_conflict=FALSE;
+    RETURN;
+  END IF;
+  -- conflict case
+  out_duplicate=FALSE;
+  out_conflict=TRUE;
+  RETURN;
+END IF;
+
+out_duplicate=FALSE;
+out_conflict=FALSE;
+
+
+FOR i IN 1..array_length(ina_coin_pubs,1)
+LOOP
+  ini_coin_value=ina_coin_values[i];
+  ini_deposit_fee=ina_deposit_fees[i];
+  ini_coin_pub=ina_coin_pubs[i];
+  ini_contract_term=ina_contract_terms[i];
+
+  INSERT INTO merchant_transfer_to_coin
+    (deposit_serial
+    ,credit_serial
+    ,offset_in_exchange_list
+    ,exchange_deposit_value
+    ,exchange_deposit_fee)
+    SELECT
+        dep.deposit_serial
+       ,my_credit_serial
+       ,i
+       ,ini_coin_value
+       ,ini_deposit_fee
+      FROM merchant_deposits dep
+      JOIN merchant_deposit_confirmations dcon
+        USING (deposit_confirmation_serial)
+      JOIN merchant_contract_terms cterm
+        USING (order_serial)
+      WHERE dep.coin_pub=ini_coin_pub
+        AND cterm.h_contract_terms=ini_contract_term
+        AND cterm.merchant_serial=my_merchant_id;
+
+  RAISE NOTICE 'iterating over affected orders';
+  OPEN curs (arg_coin_pub:=ini_coin_pub);
+  LOOP
+    FETCH NEXT FROM curs INTO my_affected_orders;
+    EXIT WHEN NOT FOUND;
+
+    -- FIXME: statement below is messy, likely buggy (as we don't set WIRED to TRUE correctly...)
+    RAISE NOTICE 'checking affected order for completion';
+    UPDATE merchant_contract_terms
+       SET wired=TRUE
+     WHERE order_serial IN
+       (SELECT order_serial
+          FROM merchant_deposit_confirmations dcon
+         WHERE
+           order_serial=my_affected_orders.order_serial
+           AND NOT EXISTS
+           (SELECT 1
+              FROM merchant_deposits dep
+              JOIN merchant_deposit_to_transfer
+                USING (deposit_serial)
+              JOIN merchant_transfers mtrans
+                USING (credit_serial)
+             WHERE
+               dep.deposit_confirmation_serial = dcon.deposit_confirmation_serial
+               AND NOT mtrans.confirmed));
+  END LOOP; -- END curs LOOP
+  CLOSE curs;
+END LOOP; -- END FOR loop
+
+END $$;
diff --git a/src/backenddb/procedures.sql.in b/src/backenddb/procedures.sql.in
index 365bc94b..3ebf8b8b 100644
--- a/src/backenddb/procedures.sql.in
+++ b/src/backenddb/procedures.sql.in
@@ -19,5 +19,6 @@ BEGIN;
 SET search_path TO merchant;
 
 #include "pg_insert_deposit_to_transfer.sql"
+#include "pg_insert_transfer_details.sql"
 
 COMMIT;