-- init
create user user1 with sysadmin password 'Show@123';
create user user2 password 'Show@456';
-- B库执行
set dolphin.b_compatibility_mode to off;
drop database if exists show_storage_engines_b;
NOTICE: database "show_storage_engines_b" does not exist, skipping
create database show_storage_engines_b dbcompatibility 'b';
\c show_storage_engines_b
-- 管理员用户执行
-- set session authorization user1 password 'Show@123';
set role user1 password 'Show@123';
-- row DEFAULT/column YES
create table row_default_tb(id int);
create table row_tb(id int) with (orientation=row);
create table column_tb(id int) with (orientation=column);
SHOW ENGINES;
Engine | Support | Comment | Transactions | XA | Savepoints
---------+---------+---------------------------------------+--------------+----+------------
ASTORE | DEFAULT | Append Update Storage Engine | YES | | YES
USTORE | YES | In-place Update Storage Engine | YES | | YES
row | DEFAULT | Row-Oriented Table Storage Engine | YES | | YES
column | YES | Column-Oriented Table Storage Engine | YES | | YES
mot_fdw | NO | Memory-Optimized Table Storage Engine | YES | | NO
(5 rows)
SHOW STORAGE ENGINES;
Engine | Support | Comment | Transactions | XA | Savepoints
---------+---------+---------------------------------------+--------------+----+------------
ASTORE | DEFAULT | Append Update Storage Engine | YES | | YES
USTORE | YES | In-place Update Storage Engine | YES | | YES
row | DEFAULT | Row-Oriented Table Storage Engine | YES | | YES
column | YES | Column-Oriented Table Storage Engine | YES | | YES
mot_fdw | NO | Memory-Optimized Table Storage Engine | YES | | NO
(5 rows)
-- ASTORE/USTORE
-- enable_ustore=on
show enable_ustore;
enable_ustore
---------------
on
(1 row)
-- enable_default_ustore_table on: ASTORE YES, USTORE DEFAULT
set enable_default_ustore_table = on;
show enable_default_ustore_table;
enable_default_ustore_table
-----------------------------
on
(1 row)
create table ustore_default_tb_1(id int);
create table astore_tb_1(id int) with (storage_type=astore);
create table ustore_tb_1(id int) with (storage_type=ustore);
SHOW ENGINES;
Engine | Support | Comment | Transactions | XA | Savepoints
---------+---------+---------------------------------------+--------------+----+------------
ASTORE | YES | Append Update Storage Engine | YES | | YES
USTORE | DEFAULT | In-place Update Storage Engine | YES | | YES
row | DEFAULT | Row-Oriented Table Storage Engine | YES | | YES
column | YES | Column-Oriented Table Storage Engine | YES | | YES
mot_fdw | NO | Memory-Optimized Table Storage Engine | YES | | NO
(5 rows)
-- enable_default_ustore_table off: ASTORE DEFAULT, USTORE YES
set enable_default_ustore_table = off;
show enable_default_ustore_table;
enable_default_ustore_table
-----------------------------
off
(1 row)
create table astore_default_tb_2(id int);
create table astore_tb_2(id int) with (storage_type=astore);
create table ustore_tb_2(id int) with (storage_type=ustore);
SHOW ENGINES;
Engine | Support | Comment | Transactions | XA | Savepoints
---------+---------+---------------------------------------+--------------+----+------------
ASTORE | DEFAULT | Append Update Storage Engine | YES | | YES
USTORE | YES | In-place Update Storage Engine | YES | | YES
row | DEFAULT | Row-Oriented Table Storage Engine | YES | | YES
column | YES | Column-Oriented Table Storage Engine | YES | | YES
mot_fdw | NO | Memory-Optimized Table Storage Engine | YES | | NO
(5 rows)
-- mot_fdw
-- ENABLE_MOT=on
-- enable_incremental_checkpoint=on
show enable_incremental_checkpoint;
enable_incremental_checkpoint
-------------------------------
on
(1 row)
-- failed
create foreign table mot_tb_1(id int) server mot_server;
ERROR: Cannot create MOT tables while incremental checkpoint is enabled.
-- enable_incremental_checkpoint=on: mot_fdw NO
SHOW ENGINES;
Engine | Support | Comment | Transactions | XA | Savepoints
---------+---------+---------------------------------------+--------------+----+------------
ASTORE | DEFAULT | Append Update Storage Engine | YES | | YES
USTORE | YES | In-place Update Storage Engine | YES | | YES
row | DEFAULT | Row-Oriented Table Storage Engine | YES | | YES
column | YES | Column-Oriented Table Storage Engine | YES | | YES
mot_fdw | NO | Memory-Optimized Table Storage Engine | YES | | NO
(5 rows)
-- 普通用户执行
-- set session authorization user2 password 'Show@456';
set role user2 password 'Show@456';
SHOW ENGINES;
Engine | Support | Comment | Transactions | XA | Savepoints
---------+---------+---------------------------------------+--------------+----+------------
ASTORE | DEFAULT | Append Update Storage Engine | YES | | YES
USTORE | YES | In-place Update Storage Engine | YES | | YES
row | DEFAULT | Row-Oriented Table Storage Engine | YES | | YES
column | YES | Column-Oriented Table Storage Engine | YES | | YES
mot_fdw | NO | Memory-Optimized Table Storage Engine | YES | | NO
(5 rows)
SHOW STORAGE ENGINES;
Engine | Support | Comment | Transactions | XA | Savepoints
---------+---------+---------------------------------------+--------------+----+------------
ASTORE | DEFAULT | Append Update Storage Engine | YES | | YES
USTORE | YES | In-place Update Storage Engine | YES | | YES
row | DEFAULT | Row-Oriented Table Storage Engine | YES | | YES
column | YES | Column-Oriented Table Storage Engine | YES | | YES
mot_fdw | NO | Memory-Optimized Table Storage Engine | YES | | NO
(5 rows)
-- 非B库执行
set role user1 password 'Show@123';
set dolphin.b_compatibility_mode to off;
drop database if exists show_storage_engines_nb;
NOTICE: database "show_storage_engines_nb" does not exist, skipping
create database show_storage_engines_nb;
\c show_storage_engines_nb
SHOW ENGINES;
ERROR: unrecognized configuration parameter "engines"
SHOW STORAGE ENGINES;
ERROR: syntax error at or near "ENGINES"
LINE 1: SHOW STORAGE ENGINES;
^
