PostgreSQL Multi-Tenant Usage – Multi-Schema Batch Execution of DDL Scripts

This short article addresses a common issue of working batches when DDL scripts need to be executed in multiple tenants.

By digoal

Background

Multi-tenant mode has a difficult problem to solve. When DDL scripts need to be executed in multiple tenants, how can we execute in batches?

Let’s take multi-tenant schema technology as an example. Each tenant has one schema, and the structure of each schema is the same. When you need to add a table, delete a table, modify the table structure, and add an index, how can you operate in batches in all schemas?

Write a UDF function:

create or replace function ddl(   
  xs name [],  -- 排除的schema   
  sql text  -- 要执行的sql   
) returns boolean as $$  
declare  
 nsp name;  
 vs text;  
begin  
  for nsp in SELECT n.nspname FROM pg_catalog.pg_namespace n   
      WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'  
  loop  
    if not xs @> array[nsp] then   
      vs := format('set search_path=%I,public;', nsp);   
      raise notice '%', vs;   
      execute vs;   
      execute sql;   
    end if;   
  end loop;   
  reset search_path;   
  return true;   
exception when others then
  reset search_path;
  return false;
end;   
$$ language plpgsql strict;

The xs parameter specifies the schema not involved in the execution, such as schemas unrelated to the tenant. Other tenant-related schemas are involved in the execution.

SQL parameters indicate the SQL statements that need to be executed in all schemas of the tenant.

Please visit this link to check whether the DDL scripts are consistent.

0 0 0

Share on

: digoal

282 posts | 24 followers
Follow

Community

PostgreSQL Multi-Tenant Usage – Multi-Schema Batch Execution of DDL Scripts

Background

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

AnalyticDB for PostgreSQL

Batch Compute

ApsaraDB RDS for SQL Server