How to query the creation time of a table in the database?

Abstract: In project delivery, people often ask "How to query the creation time of a table in the database?" So how do you find the creation time of an object in GaussDB (DWS)? This article provides three methods for reference, including dba_objecs view viewing method, audit log viewing method and CN log viewing method.

This article is shared from the HUAWEI CLOUD Community " GaussDB (DWS) Practice Series-How does GaussDB (DWS) query the creation time of an object (table)?" ", the original author: Clover.

1. Background description

In project delivery, people often ask "How to query the creation time of a table in the database?" So how do you find the creation time of an object in GaussDB (DWS)? This article provides three methods for reference, including dba_objecs view viewing method, audit log viewing method and CN log viewing method.

2. Operation drill

Method 1: View query method

The DBA_OBJECTS view stores information about all database objects in the database. GaussDB (DWS) supports querying through the DBA_OBJECTS view. The fields and detailed descriptions are as follows:

Note: You need to have system administrator authority to access.

The object_type field of the object will include TABLE, INDEX, VIEW, RULE, PROCEDURE, TYPE, OPERATOR, etc. You can filter according to this field when querying:

1. Create test table

Create a test table for subsequent query testing.

--定义一个表，使用HASH分布。
CREATE TABLE employee_info
(
    id INTEGER NOT NULL,
    name VARCHAR(16) NOT NULL,
    tel VARCHAR(11),
    addr varchar(10)
)DISTRIBUTE BY HASH(id);
 
--查看当前系统时间。
select current_timestamp;

After the test table is created, check the current system time as a reference.

2. Query creation time

View the creation time of the table object through the DBA_OBJECTS view.

select * from dba_objects where object_type='TABLE' and object_name='employee_info';

The query results are as follows:

3. Update test table

Update the test table employee_info to test whether the dba_objects view can save the last modification time of the object. The modification behavior includes ALTER operation and GRANT and REVOKE operations:

--向表中增加一个varchar列group_info。
ALTER TABLE employee_info ADD group_info varchar(30);
--查看当前系统时间。
select current_timestamp;

The relevant information is recorded as follows:

4. View the last update time

View the last update time of the object through the DBA_OBJECTS view.

select * from dba_objects where object_type='TABLE' and object_name='employee_info';

The query results are as follows:

Method 2: Viewing the audit log

The audit log statistics are more complete, turn on the audit master switch audit_enabled=on, and set the audit item audit_system_object.

audit_system_object参数说明：
该参数决定是否对GaussDB A数据库对象的CREATE、DROP、ALTER操作进行审计。GaussDB A数据库对象包括DATABASE、USER、schema、TABLE等。通过修改该配置参数的值，可以只审计需要的数据库对象的操作。
取值范围：整型，0～524287
Ø  0代表关闭数据库对象的CREATE、DROP、ALTER操作审计功能。
Ø  非0代表只审计某类或者某些数据库对象的CREATE、DROP、ALTER操作。
默认值：12295 换算成19位二进制为000 0011 0000 0000 0111

value description: The value of this parameter is obtained by the combination of 19 binary bits, which respectively represent 19 types of database objects of GaussDB (DWS). If the value of the corresponding binary bit is 0, it means that the CREATE, DROP, and ALTER operations of the corresponding database object are not audited; if the value is 1, it means that the CREATE, DROP, and ALTER operations of the corresponding database object are audited. Please refer to Table 2 for the specific audit content represented by these 19 binary bits.

converted into a 19-bit binary to be 000 0011 0000 0000 0111, and the third digit is modified to be 1. represents auditing the CREATE, DROP, ALTER, and TRUNCATE operations of the TABLE object. The modified value is 12303 (corresponding to the 19-bit binary Is 000 0011 0000 0000 1111)

The parameter settings are as follows:

gs_guc reload -Z coordinator -Z datanode -N all -I all -c "audit_enabled=on"
gs_guc reload -Z coordinator -Z datanode -N all -I all -c "audit_system_object=12303"

Screenshot of parameter setting command:

Set up successfully:

Follow the procedure in Method 1 to create and update the test table, and record the current time of the system as a reference:

View the audit log:

select * from pgxc_query_audit('2021-05-27 16:10:00','2021-05-27 16:20:00') where operation_type='ddl' and object_name='employee_info';

Screenshot below:

Method 3: CN log viewing method

Configure the postgresql.conf configuration file to determine the creation time of the table by recording the DDL information of the table.

Introduction to log_statement parameters:

log_statement参数说明：控制记录SQL语句。
该参数属于SUSET类型参数，请参考表1中对应设置方法进行设置。即使log_statement设置为all，包含简单语法错误的语句也不会被记录，因为仅在完成基本的语法分析并确定了语句类型之后才记录日志。
取值范围：枚举类型
•none表示不记录语句。
•ddl表示记录所有的数据定义语句，比如CREATE、ALTER和DROP语句。
•mod表示记录所有DDL语句，还包括数据修改语句INSERT、UPDATE、DELETE、TRUNCATE和COPY FROM 。
•all表示记录所有语句，PREPARE、EXECUTE和EXPLAIN ANALYZE语句也同样被记录。
默认值：none

Log_statement parameter setting method:

--登录CN所在的数据节点，执行
source /opt/huawei/Bigdata/mppdb/.mppdbgs_profile
gs_guc reload -Z coordinator -Z datanode -N all -I all -c "log_statement=ddl"

Screenshot of parameter setting command:

Check the CN log at the corresponding point in time, you can record the creation time of the table, as follows.

Click to follow to get to know the fresh technology of Huawei Cloud for the first time~