Support for Modifying Table Log Properties After Table Creation

Availability

This feature is available since MogDB 5.0.6.

Introduction

This feature supports the modification of table log properties via the ALTER TABLE statement after the table has been created.

Benefits

Enhances compatibility with Oracle, reduces the migration cost for applications, improves product usability, and enhances performance in certain scenarios.

Description

Modifying a regular table to a unlogged table can improve import efficiency when temporarily importing a large amount of data for computation. Data written to an unlogged table is not written to the write-ahead log (WAL), but there is a risk of data loss with unlogged tables in the event of conflicts, operating system restarts, database restarts, primary-standby switches, power-off operations, or abnormal shutdowns.

The ALTER TABLE syntax change is as follows:

ALTER TABLE [ IF EXISTS ] { table_name }{
    SET { LOGGED | UNLOGGED } -- PG
    | { LOGGING | NOLOGGING } -- ORACLE
}

Constraints

Partitioned tables, column-store tables, temporary tables, and global temporary tables do not support the modification of log properties.
Regular tables that are referenced by foreign key constraints of other regular tables do not support being changed to unlogged tables.
Unlogged tables that reference other unlogged tables with foreign key constraints do not support being changed to regular tables.
After the table log property is modified, the log property of the index is automatically modified as well.
This feature is applicable to both A mode and PG mode.

Example

-- Create a logged regular table
MogDB=# create table t_logged(a integer,b text);
CREATE TABLE
MogDB=# insert into t_logged values (generate_series(1, 3), 'a'|| generate_series(1, 3));
INSERT 0 3
MogDB=# select * from t_logged order by a,b;
 a | b
---+----
 1 | a1
 2 | a2
 3 | a3
(3 rows)

MogDB=# select relname, relpersistence from pg_class where relname='t_logged';
 relname  | relpersistence
----------+----------------
 t_logged | p
(1 row)

-- Modify to an unlogged/logged table
MogDB=# alter table t_logged set unlogged;
ALTER TABLE
MogDB=# select relname, relpersistence from pg_class where relname='t_logged';
 relname  | relpersistence
----------+----------------
 t_logged | u
(1 row)

MogDB=# alter  table t_logged logging;
ALTER TABLE
MogDB=# select relname, relpersistence from pg_class where relname='t_logged';
 relname  | relpersistence
----------+----------------
 t_logged | p
(1 row)

MogDB=# alter  table t_logged nologging;
ALTER TABLE
MogDB=# select relname, relpersistence from pg_class where relname='t_logged';
 relname  | relpersistence
----------+----------------
 t_logged | u
(1 row)

MogDB=# alter  table t_logged set logged;
ALTER TABLE
MogDB=# select relname, relpersistence from pg_class where relname='t_logged';
 relname  | relpersistence
----------+----------------
 t_logged | p
(1 row)

-- Clean up environment
MogDB=# drop table if exists t_logged cascade;
DROP TABLE

ALTER TABLE