Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the operating specifications of DDL in citus". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "what are the operating specifications of DDL in citus"?

Background

Citus is a plug-in of PG. The plug-in mainly carries out some ROUTE processing for ordinary SQL (non-UTILITY) plus HOOK, and uses UDF to create a new partition on the table.

What if the user wants to execute DDL and CITUS cannot take over?

There are two scenarios, one that needs to be executed on all nodes (CN and WORKER), and some that only needs to be executed on the CN node.

DDL that needs to be executed on all nodes (CN and WORKER)

Because these DDL are not automatically executed in WORKER when they are executed on CN nodes, they need to be executed on all nodes.

Common DDL includes:

1. Create a new user

Command: CREATEROLE Description: define a new database role Syntax: CREATEROLE name [[WITH] option [...] where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION | NOREPLICATION | BYPASSRLS | NOBYPASSRLS | CONNECTION LIMIT connlimit | [ENCRYPTED] PASSWORD 'password' | VALID UNTIL' timestamp' | IN ROLE role_name [ | IN GROUP role_name [,...] | ROLE role_name [,...] | ADMIN role_name [,...] | USER role_name [,...] | SYSID uid

2. Create a new database

Command: CREATE DATABASE Description: create a new database Syntax: CREATE DATABASE name [WITH] [OWNER [=] user_name] [TEMPLATE [=] template] [ENCODING [=] encoding] [LC_COLLATE [=] lc_collate] [LC_CTYPE [=] lc_ctype] [TABLESPACE [=] tablespace_name] [ALLOW_CONNECTIONS [=] allowconn] [CONNECTION LIMIT [=] connlimit] [IS_TEMPLATE [=] istemplate]]

After all nodes create a new database, don't forget:

2.1.Add the citus plug-in to the new DB of all nodes

Create extension citus

2.2. Add a worker node only in the CN node and connect to the new database. Don't forget that every new DB needs to be repeated. So you can put it into the template library, when you create a new DB, use the template to create a new one. )

For example

Su-postgres-c "psql-c\" SELECT * from master_add_node ('xxx.xxx.xxx.224', 1921);\ "" su-postgres-c "psql-c\" SELECT * from master_add_node (' xxx.xxx.xxx.230', 1921);\ "" su-postgres-c "psql-c\" SELECT * from master_add_node ('xxx.xxx.xxx.231', 1921) \ "su-postgres-c" psql-c\ "SELECT * from master_add_node ('xxx.xxx.xxx.225', 1921);\"su-postgres-c" psql-c\ "SELECT * from master_add_node (' xxx.xxx.xxx.227', 1921);\"su-postgres-c" psql-c\ "SELECT * from master_add_node ('xxx.xxx.xxx.232', 1921) \ "su-postgres-c" psql-c\ "SELECT * from master_add_node ('xxx.xxx.xxx.226', 1921);\"su-postgres-c" psql-c\ "SELECT * from master_add_node (' xxx.xxx.xxx.229', 1921);\"postgres=# SELECT * FROM master_get_active_worker_nodes () Node_name | node_port-+-xxx.xxx.xxx.227 | 1921 xxx.xxx.xxx.229 | 1921 xxx.xxx.xxx.231 | 1921 xxx.xxx.xxx.225 | 1921 xxx.xxx.xxx.224 | 1921 xxx.xxx.xxx.226 | 1921 Xxx.xxx.xxx.230 | 1921 xxx.xxx.xxx.232 | 1921 (8 rows)

2.3.If you use the MX function, you need to add it again.

Referenc

"PostgreSQL sharding: citus Series 1-Multi-machine deployment (including OLTP (TPC-B) testing)"

# CN MX: expansion of OLTP read and write capabilities

3. Create a new schema

Command: CREATE SCHEMA Description: define a new schema Syntax: CREATE SCHEMA schema_name [AUTHORIZATION role_specification] [schema_element [...]] CREATE SCHEMA AUTHORIZATION role_specification [schema_element [...]] CREATE SCHEMA IF NOT EXISTS schema_name [AUTHORIZATION role_specification] CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification where role_specification can be: user_name | CURRENT_USER | SESSION_USER

4. Create a new function

Custom function Command: CREATE FUNCTION Description: define a new function Syntax: CREATE [OR REPLACE] FUNCTION name ([[argmode] [argname] argtype [{DEFAULT | =} default_expr] [,...]]) [RETURNS rettype | RETURNS TABLE (column_name column_type [,...])] {LANGUAGE lang_name | TRANSFORM {FOR TYPE type_name} [ | WINDOW | IMMUTABLE | STABLE | VOLATILE | [NOT] LEAKPROOF | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT | [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER | PARALLEL {UNSAFE | RESTRICTED | SAFE} | COST execution_cost | ROWS result_rows | SET configuration_parameter {TO value | = value | FROM CURRENT} | AS 'definition' | AS' obj_file', 'link_symbol'}. [WITH (attribute [,...])]

5. New operator

Custom operator

6. Create a new type

Custom type

7. Create a new plugin

Command: CREATE EXTENSION Description: install an extension Syntax: CREATE EXTENSION [IF NOT EXISTS] extension_name [WITH] [SCHEMA schema_name] [VERSION version] [FROM old_version] [CASCADE]

The above are commonly used DDL and need to be executed on all nodes.

Only the DDL executed on the CN node is required

1. Create a new table and call the citus function to create shards.

Examples

Create table test (id int primary key, info text); select create_distributed_table ('test','id')

2. Create a new view

Command: CREATE VIEW Description: define a new view Syntax: CREATE [OR REPLACE] [TEMP | TEMPORARY] [RECURSIVE] VIEW name [(column_name [,...])] [WITH (view_option_name [= view_option_value] [,...])] AS query [WITH [CASCADED | LOCAL] CHECK OPTION]

The view only needs to be operated on the CN node, because the SQL will eventually parse into the contents of the view. For example

Create view v2 as select * from pgbench_accounts where aid=1; create view v3 as select * from v2; postgres=# explain verbose select * from v2 QUERY PLAN -Custom Scan (Citus Router) (cost=0.00..0.00 rows=0 width=0) Output: remote_scan.aid Remote_scan.bid, remote_scan.abalance, remote_scan.filler Task Count: 1 Tasks Shown: All-> Task Node: host=172.24.211.232 port=1921 dbname=postgres-> Index Scan using pgbench_accounts_pkey_106819 on public.pgbench_accounts_106819 pgbench_accounts (cost=0.28..2.50 rows=1 width=97) Output: pgbench_accounts.aid, pgbench_accounts.bid, pgbench_accounts.abalance Pgbench_accounts.filler Index Cond: (pgbench_accounts.aid = 1) (9 rows) postgres=# explain verbose select * from v3 QUERY PLAN -Custom Scan (Citus Router) (cost=0.00..0.00 rows=0 width=0) Output: remote_scan.aid Remote_scan.bid, remote_scan.abalance, remote_scan.filler Task Count: 1 Tasks Shown: All-> Task Node: host=172.24.211.232 port=1921 dbname=postgres-> Index Scan using pgbench_accounts_pkey_106819 on public.pgbench_accounts_106819 pgbench_accounts (cost=0.28..2.50 rows=1 width=97) Output: pgbench_accounts.aid, pgbench_accounts.bid, pgbench_accounts.abalance Pgbench_accounts.filler Index Cond: (pgbench_accounts.aid = 1) (9 rows) so far I believe that you have a deeper understanding of "what are the DDL operating specifications in citus?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.