commit 1030eb67707813fd87b92e95fbb032b90fe77a05
parent 3674e6606c3ced341e9edf80f39cf8cb71602b6c
Author: Christian Grothoff <christian@grothoff.org>
Date: Sun, 7 Jun 2026 00:05:45 +0200
fix #11415
Diffstat:
8 files changed, 348 insertions(+), 289 deletions(-)
diff --git a/src/backend/taler-merchant-httpd_delete-private-products-PRODUCT_ID.c b/src/backend/taler-merchant-httpd_delete-private-products-PRODUCT_ID.c
@@ -48,7 +48,7 @@ TMH_private_delete_products_ID (const struct TMH_RequestHandler *rh,
GNUNET_assert (NULL != hc->infix);
TALER_MHD_parse_request_bool (connection,
"force",
- true,
+ false,
&force);
qs = TALER_MERCHANTDB_delete_product (TMH_db,
mi->settings.id,
diff --git a/src/backend/taler-merchant-httpd_post-private-orders.c b/src/backend/taler-merchant-httpd_post-private-orders.c
@@ -858,9 +858,11 @@ clean_order (void *cls)
/* ***************** ORDER_PHASE_EXECUTE_ORDER **************** */
/**
- * Compute remaining stock (integer and fractional parts) for a product.
+ * Compute the quantity (integer and fractional parts) of a product that is
+ * actually available for a new order. This excludes units already sold,
+ * lost, or currently reserved by locks (shopping carts and unpaid orders).
*
- * @param pd product details with current totals/sold/lost
+ * @param pd product details with current totals/sold/lost/locked
* @param[out] available_value remaining whole units (normalized, non-negative)
* @param[out] available_frac remaining fractional units (0..TALER_MERCHANT_UNIT_FRAC_BASE-1)
*/
@@ -886,10 +888,12 @@ compute_available_quantity (
value = (int64_t) pd->total_stock
- (int64_t) pd->total_sold
- - (int64_t) pd->total_lost;
+ - (int64_t) pd->total_lost
+ - (int64_t) pd->total_locked;
frac = (int64_t) pd->total_stock_frac
- (int64_t) pd->total_sold_frac
- - (int64_t) pd->total_lost_frac;
+ - (int64_t) pd->total_lost_frac
+ - (int64_t) pd->total_locked_frac;
if (frac < 0)
{
diff --git a/src/backenddb/lookup_product.c b/src/backenddb/lookup_product.c
@@ -57,6 +57,8 @@ TALER_MERCHANTDB_lookup_product (
",mi.total_sold_frac"
",mi.total_lost"
",mi.total_lost_frac"
+ ",mi.total_locked"
+ ",mi.total_locked_frac"
",mi.image"
",mi.address::TEXT"
",mi.next_restock"
@@ -131,6 +133,10 @@ TALER_MERCHANTDB_lookup_product (
&pd->total_lost),
GNUNET_PQ_result_spec_uint32 ("total_lost_frac",
&pd->total_lost_frac),
+ GNUNET_PQ_result_spec_uint64 ("total_locked",
+ &pd->total_locked),
+ GNUNET_PQ_result_spec_uint32 ("total_locked_frac",
+ &pd->total_locked_frac),
GNUNET_PQ_result_spec_string ("image",
&my_image),
TALER_PQ_result_spec_json ("address",
diff --git a/src/backenddb/pg_do_handle_inventory_changes.sql b/src/backenddb/pg_do_handle_inventory_changes.sql
@@ -1,6 +1,6 @@
--
-- This file is part of TALER
--- Copyright (C) 2024, 2025 Taler Systems SA
+-- Copyright (C) 2024, 2025, 2026 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
@@ -21,296 +21,159 @@ DECLARE
resolved_body TEXT;
webhook RECORD; -- To iterate over all matching webhooks
my_instance_id INT8;
+ my_event_type TEXT;
+ my_do_new BOOLEAN;
+ my_do_old BOOLEAN;
BEGIN
SELECT SUBSTRING(current_schema()::TEXT
FROM 'merchant_instance_([0-9]+)')::INT8
INTO my_instance_id;
- -- INSERT case: Notify webhooks for inventory addition
- IF TG_OP = 'INSERT' THEN
- FOR webhook IN
- SELECT
- webhook_serial
- ,url
- ,http_method
- ,body_template
- FROM merchant_webhook
- WHERE event_type = 'inventory_added'
- LOOP
- -- Resolve placeholders for the current webhook
- resolved_body := webhook.body_template;
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'webhook_type',
- 'inventory_added');
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'product_serial',
- NEW.product_serial::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'product_id',
- NEW.product_id);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'description',
- NEW.description);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'description_i18n',
- NEW.description_i18n::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'unit',
- NEW.unit);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'image',
- NEW.image);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'taxes',
- NEW.taxes::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'price',
- NEW.price_array[1]::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'unit_price',
- NEW.price_array::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'total_stock',
- NEW.total_stock::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'total_sold',
- NEW.total_sold::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'total_lost',
- NEW.total_lost::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'address',
- NEW.address::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'next_restock',
- NEW.next_restock::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'minimum_age',
- NEW.minimum_age::TEXT);
+ CASE TG_OP
+ WHEN 'INSERT' THEN
+ my_event_type := 'inventory_added';
+ my_do_new := TRUE;
+ my_do_old := FALSE;
+ WHEN 'DELETE' THEN
+ my_event_type := 'inventory_deleted';
+ my_do_new := FALSE;
+ my_do_old := FALSE;
+ WHEN 'UPDATE' THEN
+ my_event_type := 'inventory_updated';
+ my_do_new := TRUE;
+ my_do_old := TRUE;
+ IF (to_jsonb(OLD) - 'total_locked' - 'total_locked_frac')
+ IS NOT DISTINCT FROM
+ (to_jsonb(NEW) - 'total_locked' - 'total_locked_frac')
+ THEN
+ RETURN NULL;
+ END IF;
+ ELSE
+ RETURN NULL;
+ END CASE;
- -- Insert into pending webhooks for this webhook
- INSERT INTO merchant.merchant_pending_webhooks
- (merchant_serial
- ,webhook_serial
- ,url
- ,http_method
- ,body
- ) VALUES (
- my_instance_id
- ,webhook.webhook_serial
- ,webhook.url
- ,webhook.http_method
- ,resolved_body
- );
- END LOOP;
-
- -- Notify the webhook service
- NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG;
- END IF;
-
- -- UPDATE case: Notify webhooks for inventory update
- IF TG_OP = 'UPDATE' THEN
- FOR webhook IN
- SELECT
- webhook_serial
- ,url
- ,http_method
- ,body_template
- FROM merchant_webhook
- WHERE event_type = 'inventory_updated'
- LOOP
- -- Resolve placeholders for the current webhook
- resolved_body := webhook.body_template;
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'webhook_type',
- 'inventory_updated');
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'product_serial',
- NEW.product_serial::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'product_id',
- NEW.product_id);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'old_description',
- OLD.description);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'description',
- NEW.description);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'old_description_i18n',
- OLD.description_i18n::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'description_i18n',
- NEW.description_i18n::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'old_unit',
- OLD.unit);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'unit',
- NEW.unit);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'old_image',
- OLD.image);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'image',
- NEW.image);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'old_taxes',
- OLD.taxes::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'taxes',
- NEW.taxes::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'old_price',
- OLD.price_array[1]::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'old_unit_price',
- OLD.price_array::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'price',
- NEW.price_array[1]::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'unit_price',
- NEW.price_array::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'old_total_stock',
- OLD.total_stock::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'total_stock',
- NEW.total_stock::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'old_total_sold',
- OLD.total_sold::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'total_sold',
- NEW.total_sold::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'old_total_lost',
- OLD.total_lost::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'total_lost',
- NEW.total_lost::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'old_address',
- OLD.address::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'address',
- NEW.address::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'old_next_restock',
- OLD.next_restock::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'next_restock',
- NEW.next_restock::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'old_minimum_age',
- OLD.minimum_age::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'minimum_age',
- NEW.minimum_age::TEXT);
-
- -- Insert into pending webhooks for this webhook
- INSERT INTO merchant.merchant_pending_webhooks
- (merchant_serial
- ,webhook_serial
- ,url
- ,http_method
- ,body
- ) VALUES (
- my_instance_id
- ,webhook.webhook_serial
- ,webhook.url
- ,webhook.http_method
- ,resolved_body
- );
- END LOOP;
-
- -- Notify the webhook service
- NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG;
- END IF;
-
- -- DELETE case: Notify webhooks for inventory deletion
- IF TG_OP = 'DELETE' THEN
- FOR webhook IN
- SELECT
- webhook_serial
- ,url
- ,http_method
- ,body_template
- FROM merchant_webhook
- WHERE event_type = 'inventory_deleted'
- LOOP
- -- Resolve placeholders for the current webhook
- resolved_body := webhook.body_template;
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'webhook_type',
- 'inventory_deleted');
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'product_serial',
- OLD.product_serial::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'product_id',
- OLD.product_id);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'description',
- OLD.description);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'description_i18n',
- OLD.description_i18n::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'unit',
- OLD.unit);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'image',
- OLD.image);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'taxes',
- OLD.taxes::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'price',
- OLD.price_array[1]::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'unit_price',
- OLD.price_array::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'total_stock',
- OLD.total_stock::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'total_sold',
- OLD.total_sold::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'total_lost',
- OLD.total_lost::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'address',
- OLD.address::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'next_restock',
- OLD.next_restock::TEXT);
- resolved_body := merchant.replace_placeholder(resolved_body,
- 'minimum_age',
- OLD.minimum_age::TEXT);
-
- -- Insert into pending webhooks for this webhook
- INSERT INTO merchant.merchant_pending_webhooks
- (merchant_serial
- ,webhook_serial
- ,url
- ,http_method
- ,body
- ) VALUES (
- my_instance_id
- ,webhook.webhook_serial
- ,webhook.url
- ,webhook.http_method
- ,resolved_body
- );
- END LOOP;
-
- -- Notify the webhook service
- NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG;
- END IF;
+ FOR webhook IN
+ SELECT
+ webhook_serial
+ ,url
+ ,http_method
+ ,body_template
+ FROM merchant_webhook
+ WHERE event_type = my_event_type
+ LOOP
+ -- Resolve placeholders for the current webhook
+ resolved_body := webhook.body_template;
+ IF my_do_new
+ THEN
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'webhook_type',
+ my_event_type);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'product_serial',
+ NEW.product_serial::TEXT);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'product_id',
+ NEW.product_id);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'description',
+ NEW.description);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'description_i18n',
+ NEW.description_i18n::TEXT);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'unit',
+ NEW.unit);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'image',
+ NEW.image);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'taxes',
+ NEW.taxes::TEXT);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'price',
+ NEW.price_array[1]::TEXT);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'unit_price',
+ NEW.price_array::TEXT);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'total_stock',
+ NEW.total_stock::TEXT);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'total_sold',
+ NEW.total_sold::TEXT);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'total_lost',
+ NEW.total_lost::TEXT);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'address',
+ NEW.address::TEXT);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'next_restock',
+ NEW.next_restock::TEXT);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'minimum_age',
+ NEW.minimum_age::TEXT);
+ END IF;
+ IF my_do_old
+ THEN
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'old_description',
+ OLD.description);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'old_description_i18n',
+ OLD.description_i18n::TEXT);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'old_unit',
+ OLD.unit);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'old_image',
+ OLD.image);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'old_taxes',
+ OLD.taxes::TEXT);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'old_price',
+ OLD.price_array[1]::TEXT);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'old_unit_price',
+ OLD.price_array::TEXT);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'old_total_stock',
+ OLD.total_stock::TEXT);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'old_total_sold',
+ OLD.total_sold::TEXT);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'old_total_lost',
+ OLD.total_lost::TEXT);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'old_address',
+ OLD.address::TEXT);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'old_next_restock',
+ OLD.next_restock::TEXT);
+ resolved_body := merchant.replace_placeholder(resolved_body,
+ 'old_minimum_age',
+ OLD.minimum_age::TEXT);
+ END IF;
+ -- Insert into pending webhooks for this webhook
+ INSERT INTO merchant.merchant_pending_webhooks
+ (merchant_serial
+ ,webhook_serial
+ ,url
+ ,http_method
+ ,body
+ ) VALUES (
+ my_instance_id
+ ,webhook.webhook_serial
+ ,webhook.url
+ ,webhook.http_method
+ ,resolved_body
+ );
+ END LOOP;
+ -- Notify the webhook service
+ NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
diff --git a/src/backenddb/pg_update_inventory_locked.sql b/src/backenddb/pg_update_inventory_locked.sql
@@ -0,0 +1,65 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2026 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/>
+--
+
+
+-- Trigger function that adjusts merchant_inventory.total_locked(_frac)
+-- whenever a row in one of the lock tables is inserted, updated or
+-- deleted (including cascade deletes when orders expire or are removed).
+CREATE OR REPLACE FUNCTION update_inventory_locked()
+ RETURNS TRIGGER
+ LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_product_serial INT8;
+ my_delta NUMERIC := 0;
+BEGIN
+ CASE TG_OP
+ WHEN 'INSERT' THEN
+ my_product_serial := NEW.product_serial;
+ my_delta := NEW.total_locked::NUMERIC * 1000000
+ + NEW.total_locked_frac::NUMERIC;
+ WHEN 'DELETE' THEN
+ my_product_serial := OLD.product_serial;
+ my_delta := - (OLD.total_locked::NUMERIC * 1000000
+ + OLD.total_locked_frac::NUMERIC);
+ WHEN 'UPDATE' THEN
+ my_product_serial := NEW.product_serial;
+ my_delta := (NEW.total_locked::NUMERIC * 1000000
+ + NEW.total_locked_frac::NUMERIC)
+ - (OLD.total_locked::NUMERIC * 1000000
+ + OLD.total_locked_frac::NUMERIC);
+ ELSE
+ RETURN NULL;
+ END CASE;
+ IF (my_delta <> 0)
+ THEN
+ -- Use div()/% (truncating, never rounding) to split the combined
+ -- micro-unit amount back into whole and fractional parts.
+ UPDATE merchant_inventory
+ SET total_locked =
+ div (total_locked::NUMERIC * 1000000
+ + total_locked_frac::NUMERIC + my_delta,
+ 1000000)::INT8,
+ total_locked_frac =
+ ((total_locked::NUMERIC * 1000000
+ + total_locked_frac::NUMERIC + my_delta) % 1000000)::INT4
+ WHERE product_serial = my_product_serial;
+ END IF;
+ RETURN NULL;
+END $$;
+
+COMMENT ON FUNCTION update_inventory_locked
+ IS 'Trigger function that adjusts merchant_inventory.total_locked(_frac) whenever a row in one of the lock tables is inserted, updated or deleted (including cascade deletes when orders expire or are removed). The same trigger is installed on BOTH the merchant_inventory_locks and merchant_order_locks tables for INSERT, UPDATE and DELETE (and thus relies on the fact that the columns we are about have the same names in both tables).';
diff --git a/src/backenddb/sql-schema/merchant-0038.sql b/src/backenddb/sql-schema/merchant-0038.sql
@@ -0,0 +1,107 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2026 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/>
+
+-- @file merchant-0038.sql
+-- @brief Track the number of currently locked units per product in a
+-- denormalized total_locked/total_locked_frac counter on
+-- merchant_inventory. The counter is kept up to date by a trigger on
+-- the two lock tables (merchant_inventory_locks and merchant_order_locks)
+-- and is used to report the quantity actually available for new orders
+-- (which excludes units reserved by shopping carts and unpaid orders).
+-- @author Christian Grothoff
+
+BEGIN;
+
+SELECT _v.register_patch('merchant-0038', NULL, NULL);
+
+SET search_path TO merchant;
+
+CREATE PROCEDURE merchant.merchant_0038_init(s TEXT)
+ LANGUAGE plpgsql
+ AS $OUTER$
+BEGIN
+ EXECUTE format('SET LOCAL search_path TO %I', s);
+
+ -- Denormalized counter of how many units of each product are currently
+ -- reserved by locks (shopping cart locks plus unpaid order locks). Kept
+ -- in sync by update_inventory_locked() below. total_locked_frac is in
+ -- units of 1/1000000 of the base value, mirroring total_stock_frac etc.
+ ALTER TABLE merchant_inventory
+ ADD COLUMN total_locked INT8 NOT NULL DEFAULT 0,
+ ADD COLUMN total_locked_frac INT4 NOT NULL DEFAULT 0;
+ COMMENT ON COLUMN merchant_inventory.total_locked
+ IS 'Number of units currently reserved by locks (cart and unpaid order locks); these units are unavailable for new orders. Maintained by the update_inventory_locked() trigger.';
+ COMMENT ON COLUMN merchant_inventory.total_locked_frac
+ IS 'Fractional part of total_locked in units of 1/1000000 of the base value';
+
+ -- Backfill the counter for instances that already hold locks. We
+ -- temporarily disable the user triggers on merchant_inventory so that
+ -- the backfill does not emit spurious "inventory_updated" webhooks.
+ ALTER TABLE merchant_inventory DISABLE TRIGGER USER;
+ UPDATE merchant_inventory mi
+ SET total_locked = div (agg.micros, 1000000)::INT8,
+ total_locked_frac = (agg.micros % 1000000)::INT4
+ FROM (
+ SELECT product_serial,
+ SUM(micros) AS micros
+ FROM (
+ SELECT product_serial,
+ total_locked::NUMERIC * 1000000
+ + total_locked_frac::NUMERIC AS micros
+ FROM merchant_inventory_locks
+ UNION ALL
+ SELECT product_serial,
+ total_locked::NUMERIC * 1000000
+ + total_locked_frac::NUMERIC
+ FROM merchant_order_locks
+ ) all_locks
+ GROUP BY product_serial
+ ) agg
+ WHERE mi.product_serial = agg.product_serial;
+ ALTER TABLE merchant_inventory ENABLE TRIGGER USER;
+
+ -- Dummy function so we can create the trigger, actual
+ -- trigger will be loaded later via instance_procedures.sql.
+ CREATE OR REPLACE FUNCTION update_inventory_locked()
+ RETURNS TRIGGER
+ LANGUAGE plpgsql
+ AS $LOCKED$
+ BEGIN
+ RETURN NULL;
+ END $LOCKED$;
+
+ -- Add trigger to keep the new columns always up-to-date
+ CREATE TRIGGER trigger_inventory_locks_locked
+ AFTER INSERT OR UPDATE OR DELETE
+ ON merchant_inventory_locks
+ FOR EACH ROW EXECUTE FUNCTION update_inventory_locked();
+
+ CREATE TRIGGER trigger_order_locks_locked
+ AFTER INSERT OR UPDATE OR DELETE
+ ON merchant_order_locks
+ FOR EACH ROW EXECUTE FUNCTION update_inventory_locked();
+END
+$OUTER$;
+
+INSERT INTO merchant.instance_fixups
+ (migration_name
+ ,version)
+ VALUES
+ ('merchant_0038_init'
+ ,38);
+-- Apply new fix-up to existing instances
+CALL merchant.fixup_instance_schema (38::INT8);
+
+COMMIT;
diff --git a/src/backenddb/sql-schema/meson.build b/src/backenddb/sql-schema/meson.build
@@ -62,6 +62,7 @@ sql_instance_procedures = [
'../pg_merchant_send_kyc_notification.sql',
'../pg_merchant_kyc_trigger.sql',
'../pg_triggers.sql',
+ '../pg_update_inventory_locked.sql',
]
iprocedures_sql = custom_target('instance_procedures',
@@ -111,6 +112,7 @@ generated_sql = [
['merchant-0034.sql'],
['merchant-0035.sql'],
['merchant-0037.sql'],
+ ['merchant-0038.sql'],
]
foreach g : generated_sql
diff --git a/src/include/merchantdb_lib.h b/src/include/merchantdb_lib.h
@@ -377,6 +377,18 @@ struct TALER_MERCHANTDB_ProductDetails
uint32_t total_lost_frac;
/**
+ * Number of units currently reserved by locks (shopping cart locks and
+ * locks held by unpaid orders). These units are unavailable for new
+ * orders. Maintained by the database, not set by the application.
+ */
+ uint64_t total_locked;
+
+ /**
+ * Fractional part of locked units in units of 1/1000000 of the base value.
+ */
+ uint32_t total_locked_frac;
+
+ /**
* Identifies where the product is in stock, possibly an empty map.
*/
json_t *address;
