Amazon Redshift
数据库开发人员指南
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

STL_DDLTEXT

捕获在系统上运行的以下 DDL 语句。

这些 DDL 语句包括以下查询和对象:

  • CREATE SCHEMA、TABLE、VIEW

  • DROP SCHEMA、TABLE、VIEW

  • ALTER SCHEMA、TABLE

另请参阅 STL_QUERYTEXTSTL_UTILITYTEXTSVL_STATEMENTTEXT。这些表提供了在系统上执行的 SQL 命令的时间表,此历史记录非常适用于故障排除目的以及创建所有系统活动的审核跟踪。

使用 STARTTIME 和 ENDTIME 列了解在某个给定时间段内记录了哪些语句。SQL 文本的长数据块已分为 200 个字符长的行;SEQUENCE 列标识了属于一个语句的文本片段。

此表 对所有用户可见。超级用户可以查看所有行;普通用户只能看到自己的数据。有关更多信息,请参阅 系统表及视图中数据的可见性

表列

列名称 数据类型 描述
userid integer 生成该条目的用户 ID。
xid bigint 与语句关联的事务 ID。
pid integer 与语句关联的进程 ID。
label character(30) 用于运行查询的文件的名称或使用 SET QUERY_GROUP 命令定义的标签。如果查询并非基于文件或未设置 QUERY_GROUP 参数,则此字段为空。
starttime timestamp 开始执行查询的时间 (用 UTC 表示),有 6 位数字精度,可精确到小数秒。例如:2009-06-12 11:29:19.131358
endtime timestamp 完成查询的时间 (用 UTC 表示),有 6 位数字精度,可精确到小数秒。例如:2009-06-12 11:29:19.131358
sequence integer 当一个语句包含 200 多个字符时,将为该语句记录额外的行。序列 0 是第一行,1 是第二行,依此类推。
text character(200) SQL 文本,以 200 个字符递增。此字段可能包含反斜杠 (\\) 和换行符 (\n) 等特殊字符。

查询示例

以下查询显示了四个 CREATE TABLE 语句的 DDL。为便于阅读,已截断 DDL 文本列。

select xid, starttime, sequence, substring(text,1,40) as text from stl_ddltext order by xid desc, sequence; xid | starttime | sequence | text ------+----------------------------+----------+------------------------------------------ 1806 | 2013-10-23 00:11:14.709851 | 0 | CREATE TABLE supplier ( s_suppkey int4 N 1806 | 2013-10-23 00:11:14.709851 | 1 | s_comment varchar(101) NOT NULL ) 1805 | 2013-10-23 00:11:14.496153 | 0 | CREATE TABLE region ( r_regionkey int4 N 1804 | 2013-10-23 00:11:14.285986 | 0 | CREATE TABLE partsupp ( ps_partkey int8 1803 | 2013-10-23 00:11:14.056901 | 0 | CREATE TABLE part ( p_partkey int8 NOT N 1803 | 2013-10-23 00:11:14.056901 | 1 | ner char(10) NOT NULL , p_retailprice nu (6 rows)

重新构造存储的 SQL

要重新构造存储在 STL_DDLTEXT 的 text 列中的 SQL,请运行 SELECT 语句,以从 text 列中的一个或多个部分创建 SQL。在运行重新构造的 SQL 之前,将任何 (\n) 特殊字符替换为新行。以下 SELECT 语句的结果是 query_statement 字段中重新构造的 SQL 的行。

SELECT query, LISTAGG(CASE WHEN LEN(RTRIM(text)) = 0 THEN text ELSE RTRIM(text) END) WITHIN GROUP (ORDER BY sequence) as query_statement, COUNT(*) as row_count FROM stl_ddltext GROUP BY query ORDER BY query desc;

例如,以下查询运行多个 DDL 语句。查询本身超过 200 个字符,并存储在 STL_DDLTEXT 中的几个部分内。

DROP TABLE IF EXISTS public.t_tx_trunc; CREATE TABLE public.t_tx_trunc(a varchar); CREATE OR REPLACE PROCEDURE public.sp_truncate_top_level() LANGUAGE plpgsql AS $$ DECLARE row_cnt int; BEGIN INSERT INTO public.t_tx_trunc VALUES ('Insert in SP: Before Truncate 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'); select count(*) into row_cnt from public.t_tx_trunc; RAISE INFO 'sp_truncate_top_level: RowCount after 1st Insert: %', row_cnt; truncate table public.t_tx_trunc; select count(*) into row_cnt from public.t_tx_trunc; RAISE INFO 'sp_truncate_top_level: RowCount After Truncate: %', row_cnt; INSERT INTO public.t_tx_trunc VALUES ('Insert 1 in SP: After Truncate'); select count(*) into row_cnt from public.t_tx_trunc; RAISE INFO 'sp_truncate_top_level: RowCount after 2nd Insert: %', row_cnt; INSERT INTO public.t_tx_trunc VALUES ('Insert 2 in SP: After Truncate'); select count(*) into row_cnt from public.t_tx_trunc; RAISE INFO 'sp_truncate_top_level: RowCount after 3rd Insert: %', row_cnt; END $$; DROP PROCEDURE sp_truncate_top_level(); DROP TABLE IF EXISTS public.t_tx_trunc;

在此示例中,查询存储在 STL_DDLTEXT 的 text 列的多个部分(行)中。

select starttime, sequence, text from stl_ddltext where query=pg_last_query_id() order by starttime, sequence limit 10;
starttime | sequence | text ----------------------------+----------+------------------------------------------------------------------------------------------------------------------------- 2019-07-23 23:08:15.672457 | 0 | DROP TABLE IF EXISTS public.t_tx_trunc; 2019-07-23 23:08:15.676281 | 0 | CREATE TABLE public.t_tx_trunc(a varchar); 2019-07-23 23:08:15.727303 | 0 | CREATE OR REPLACE PROCEDURE public.sp_truncate_top_level()\nLANGUAGE plpgsql\nAS $$\nDECLARE\n row_cnt int;\nBEGIN\n INSERT INTO public.t_tx_trunc VALUES ('Insert in SP: Before Truncate 000000000 2019-07-23 23:08:15.727303 | 1 | 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000');\n select count(*) into row_cnt from public.t 2019-07-23 23:08:15.727303 | 2 | _tx_trunc;\n RAISE INFO 'sp_truncate_top_level: RowCount after 1st Insert: %', row_cnt;\n truncate table public.t_tx_trunc;\n select count(*) into row_cnt from public.t_tx_trunc;\n RAISE INFO 'sp_ 2019-07-23 23:08:15.727303 | 3 | truncate_top_level: RowCount After Truncate: %', row_cnt;\n INSERT INTO public.t_tx_trunc VALUES ('Insert 1 in SP: After Truncate');\n select count(*) into row_cnt from public.t_tx_trunc;\n RAISE I 2019-07-23 23:08:15.727303 | 4 | NFO 'sp_truncate_top_level: RowCount after 2nd Insert: %', row_cnt;\n INSERT INTO public.t_tx_trunc VALUES ('Insert 2 in SP: After Truncate');\n select count(*) into row_cnt from public.t_tx_trunc; 2019-07-23 23:08:15.727303 | 5 | \n RAISE INFO 'sp_truncate_top_level: RowCount after 3rd Insert: %', row_cnt;\nEND\n$$; 2019-07-23 23:08:15.76039 | 0 | DROP PROCEDURE sp_truncate_top_level(); 2019-07-23 23:08:16.454956 | 0 | DROP TABLE IF EXISTS public.t_tx_trunc;

要重新构造存储在 STL_DDLTEXT 中的 SQL,请运行以下 SQL。

SELECT LISTAGG(CASE WHEN LEN(RTRIM(text)) = 0 THEN text ELSE RTRIM(text) END) WITHIN GROUP (ORDER BY sequence) as query_statement FROM stl_ddltext GROUP BY xid order by xid;

要在客户端中使用生成的重新构造的 SQL,需将任何 (\n) 特殊字符替换为新行。

query_statement --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- DROP TABLE IF EXISTS public.t_tx_trunc; CREATE TABLE public.t_tx_trunc(a varchar); CREATE OR REPLACE PROCEDURE public.sp_truncate_top_level()\nLANGUAGE plpgsql\nAS $$\nDECLARE\n row_cnt int;\nBEGIN\n INSERT INTO public.t_tx_trunc VALUES ('Insert in SP: Before Truncate 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000');\n select count(*) into row_cnt from public.t_tx_trunc;\n RAISE INFO 'sp_truncate_top_level: RowCount after 1st Insert: %', row_cnt;\n truncate table public.t_tx_trunc;\n select count(*) into row_cnt from public.t_tx_trunc;\n RAISE INFO 'sp_truncate_top_level: RowCount After Truncate: %', row_cnt;\n INSERT INTO public.t_tx_trunc VALUES ('Insert 1 in SP: After Truncate');\n select count(*) into row_cnt from public.t_tx_trunc;\n RAISE INFO 'sp_truncate_top_level: RowCount after 2nd Insert: %', row_cnt;\n INSERT INTO public.t_tx_trunc VALUES ('Insert 2 in SP: After Truncate');\n select count(*) into row_cnt from public.t_tx_trunc;\n RAISE INFO 'sp_truncate_top_level: RowCount after 3rd Insert: %', row_cnt;\nEND\n$$; DROP PROCEDURE sp_truncate_top_level(); DROP TABLE IF EXISTS public.t_tx_trunc;

本页内容: