merchant

commit 9dee32f283879f33ad216d676b60e8ee909e36de
parent 46f46553c52cea776c5ed3e1563a51cc595aa0b5
Author: Christian Grothoff <christian@grothoff.org>
Date:   Fri,  7 Aug 2026 18:03:32 +0200

fix schema migration problem with procedures changing data type once instances exist

Diffstat:
M
src/backenddb/create_tables.sql
 | 
32
+++++++++++++++++++++++++++++++-

1 file changed, 31 insertions(+), 1 deletion(-)
diff --git a/src/backenddb/create_tables.sql b/src/backenddb/create_tables.sql
@@ -23,10 +23,40 @@ AS $$
 DECLARE
   rec RECORD;
   r RECORD;
+  my_schema TEXT;
   my_schema_name TEXT;
   v_new_def TEXT;
 BEGIN
-  my_schema_name = format('merchant_instance_%s.', in_merchant_serial);
+  my_schema = format('merchant_instance_%s', in_merchant_serial);
+  my_schema_name = my_schema || '.';
+
+  -- First remove the existing routines: CREATE OR REPLACE FUNCTION cannot
+  -- change the signature or the return type of an existing function, so
+  -- without dropping first, syncing a routine whose arguments changed fails
+  -- with 'cannot change return type of existing function'.
+  -- Trigger functions are deliberately excluded: the triggers of the
+  -- instance schema (created by the merchant_NNNN_init() fixups) depend on
+  -- them, and are not part of what we copy over here.  Their signature is
+  -- fixed at '() RETURNS TRIGGER' anyway, so CREATE OR REPLACE always
+  -- suffices for them.
+  FOR r IN
+    SELECT p.oid::REGPROCEDURE AS signature,
+           CASE p.prokind
+             WHEN 'p' THEN 'PROCEDURE'
+             ELSE 'FUNCTION'
+           END AS kind
+      FROM pg_proc p
+      JOIN pg_namespace n
+        ON n.oid = p.pronamespace
+     WHERE n.nspname = my_schema
+       AND p.prokind IN ('f', 'p')
+       AND p.prorettype <> 'pg_catalog.trigger'::REGTYPE
+  LOOP
+    EXECUTE format ('DROP %s %s',
+                    r.kind,
+                    r.signature);
+  END LOOP;
+
   FOR r IN
     SELECT pg_get_functiondef(p.oid) AS definition
       FROM pg_proc p