PostgreSQL Multi-Tenant Usage – Multi-Schema Batch Execution of DDL Scripts
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.
Read previous post:
PostgreSQL Multi-Tenant Usage – Performance Impact Test of set search_path
Read next post:
General Usage of PostgreSQL hll in Retention and UV Statistics
Comments
Related Products
-
PolarDB for PostgreSQL
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn More -
AnalyticDB for PostgreSQL
An online MPP warehousing service based on the Greenplum Database open source program
Learn More -
Batch Compute
Resource management and task scheduling for large-scale batch processing
Learn More -
ApsaraDB RDS for SQL Server
An on-demand database hosting service for SQL Server with automated monitoring, backup and disaster recovery capabilities
Learn More
More Posts by digoal
- Tips for Executing SQL Statements in Batch: Which Is Better, gexec or Dynamic SQL?
- What Could Be the Factor Leading to the Slow Execution of the First SQL Statement?
- How to Remove Duplicate and Similar Contents in Vector Databases
- How to Implement Parallelism and Concurrency Control (Queue) in Shell
- How to Determine Whether the Current PostgreSQL Database is in a Consistent State?
- PolarDB for PostgreSQL | Retrieval and Matching-Degree Sorting by Tags on Blogs and Websites
- Three Key Details and Principles of PostgreSQL Online Backup & Recovery
- PolarDB for PostgreSQL | Database Performance Optimization for High-Concurrency Queue Processing
- Implementing PostgreSQL Hook: Stats on Tables with Full Scans & Corresponding SQLs
- Optimization on Memory Usage During Rust Cargo Code Compiling