有关于会话变量在实际应用中可能会用到,而会话变量的类型也比较多,常见的值主要有以下几种:
enable_experimental_alter_column_type_general
:默认值为false,如设置为true,则在一般情况下用作列类型更改。experimental_enable_hash_sharded_indexes
: 默认值为'off',如果设置为'on',用于散列分片索引。experimental_enable_temp_tables
: 默认值为'off',如果设置为'on',用于临时对象,包括临时表、临时视图。对更改列数据类型的支持是的,有一定的限制。要启用列类型更改,请将enable_experimental_alter_column_type_general
会话变量设置为true.以下在 hubble中是等价的:
更改数据类型的限制,在以下情况下,不能更改列的数据类型:
string改成varchar(n),必须保证n大于现有数据的最大长度,否则会导致数据缺失。 字段只能进行扩长,不能进行缩减。 要更改数据的类型,首先要将会话变量设置为true,可以理解为先开会话,后改数据类型。
create table cust(id varchar(20),
age int,
sal DECIMAL(12,2),
pers DECIMAL(12,2));
SET enable_experimental_alter_column_type_general = true;
alter table cust alter age type string;
alter table cust alter sal type string;
show columns from cust;
column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden
--------------+---------------+-------------+----------------+-----------------------+-----------+------------
id | VARCHAR(20) | true | NULL | | {} | false
age | STRING | true | NULL | | {} | false
sal | STRING | true | NULL | | {} | false
pers | DECIMAL(12,2) | true | NULL | | {} | false
rowid | INT8 | false | unique_rowid() | | {primary} | true
ALTER TABLE cust ALTER pers TYPE DECIMAL(14,2);
show columns from cust;
column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden
--------------+---------------+-------------+----------------+-----------------------+-----------+------------
id | VARCHAR(20) | true | NULL | | {} | false
age | STRING | true | NULL | | {} | false
sal | STRING | true | NULL | | {} | false
pers | DECIMAL(14,2) | true | NULL | | {} | false
rowid | INT8 | false | unique_rowid() | | {primary} | true
细节说明
experimental_enable_temp_tables
为onSET experimental_enable_temp_tables=on;
CREATE TEMP TABLE users (
id UUID,
city STRING,
name STRING,
address STRING,
CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC)
);
SHOW CREATE TABLE users;
table_name | create_statement
-------------+-----------------------------------------------------------
users | CREATE TEMP TABLE pg_temp_1648014738602055526_1.users (
| id UUID NOT NULL,
| city STRING NOT NULL,
| name STRING NULL,
| address STRING NULL,
| CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
| FAMILY "primary" (id, city, name, address)
| )
CREATE TEMP TABLE vehicles (
id UUID NOT NULL,
city STRING NOT NULL,
type STRING,
owner_id UUID,
creation_time TIMESTAMP,
CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
CONSTRAINT fk_city_ref_users FOREIGN KEY (city, owner_id) REFERENCES users(city, id)
);
SHOW CREATE TABLE vehicles;
table_name | create_statement
-------------+--------------------------------------------------------------------------------------------
vehicles | CREATE TEMP TABLE vehicles (
| id UUID NOT NULL,
| city STRING NOT NULL,
| type STRING NULL,
| owner_id UUID NULL,
| creation_time TIMESTAMP NULL,
| CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
| CONSTRAINT fk_city_ref_users FOREIGN KEY (city, owner_id) REFERENCES users(city, id),
| INDEX vehicles_auto_index_fk_city_ref_users (city ASC, owner_id ASC),
| FAMILY "primary" (id, city, type, owner_id, creation_time)
| )
(1 row)
SHOW TABLES FROM pg_temp;
schema_name | table_name | type | owner | estimated_row_count | locality
--------------------------------+------------+-------+-------+---------------------+-----------
pg_temp_1648014738602055526_1 | users | table | root | 0 | NULL
pg_temp_1648014738602055526_1 | vehicles | table | root | 0 | NULL
SHOW session_id;
session_id
------------------------------------
15fd69f9831c1ed00000000000000001
(1 row)
CANCEL SESSION '15fd69f9831c1ed00000000000000001';
细节说明
experimental_enable_temp_tables
为onSET experimental_enable_temp_tables=on;
CREATE TEMP VIEW temp_user_view (sname ,sage )
as select
name,
age
from custs;
SELECT * FROM temp_user_view;
sname | sage
---------+-------
张三丰 | 20
李四娘 | 20
ALTER TABLE t EXPERIMENTAL_AUDIT SET READ WRITE;
SHOW EXPERIMENTAL_FINGERPRINTS FROM TABLE custs;
index_name | fingerprint
------------+---------------------
primary | 1999042440040364641
SET tracing = on;
SELECT * from custs;
SET tracing = off;
SHOW EXPERIMENTAL_REPLICA TRACE FOR SESSION;
timestamp | node_id | store_id | replica_id
----------------------------------+---------+----------+------------
2018-10-18 15:50:13.345879+00:00 | 3 | 3 | 7
2018-10-18 15:50:20.628383+00:00 | 2 | 2 | 26