PostgreSQL 14 preview - pg_stat_progress_copy增强 , COPY 导入数据支持进度监控 , 导入多少行, 排除多少行(where filter)

作者

digoal

日期

2021-03-10

标签

PostgreSQL , copy , pg_stat_progress_copy , 进度

背景

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9d2d45700928d49212fb7ed140feeaebe3a6014f

```
Add support for more progress reporting in COPY master github/master
author Michael Paquier michael@paquier.xyz
Tue, 9 Mar 2021 05:21:03 +0000 (14:21 +0900)
committer Michael Paquier michael@paquier.xyz
Tue, 9 Mar 2021 05:21:03 +0000 (14:21 +0900)
commit 9d2d45700928d49212fb7ed140feeaebe3a6014f
tree 88c79348c8b63167cae1cf4636185cc48e24785b tree | snapshot
parent f9264d1524baa19e4a0528f033681ef16f61b137 commit | diff
Add support for more progress reporting in COPY

The command (TO or FROM), its type (file, pipe, program or callback),
and the number of tuples excluded by a WHERE clause in COPY FROM are
added to the progress reporting already available.

The column "lines_processed" is renamed to "tuples_processed" to
disambiguate the meaning of this column in the cases of CSV and BINARY
COPY and to be more consistent with the other catalog progress views.

Bump catalog version, again.

Author: Matthias van de Meent
Reviewed-by: Michael Paquier, Justin Pryzby, Bharath Rupireddy, Josef
Šimánek, Tomas Vondra
Discussion: https://postgr.es/m/CAEze2WiOcgdH4aQA8NtZq-4dgvnJzp8PohdeKchPkhMY-jWZXA@mail.gmail.com
```

SELECT S.pid AS pid, S.datid AS datid, D.datname AS datname, S.relid AS relid, + CASE S.param5 WHEN 1 THEN 'COPY FROM' + WHEN 2 THEN 'COPY TO' + END AS command, + CASE S.param6 WHEN 1 THEN 'FILE' + WHEN 2 THEN 'PROGRAM' + WHEN 3 THEN 'PIPE' + WHEN 4 THEN 'CALLBACK' + END AS "type", S.param1 AS bytes_processed, S.param2 AS bytes_total, - S.param3 AS lines_processed + S.param3 AS tuples_processed, + S.param4 AS tuples_excluded FROM pg_stat_get_progress_info('COPY') AS S LEFT JOIN pg_database D ON S.datid = D.oid;

```
+ It is set to 0 if not available.
+
+
+
+
+
+ tuples_processed bigint
+
+
+ Number of tuples already processed by COPY command.

  <row>  
   <entry role="catalog_table_entry"><para role="column_definition">

• lines_processed bigint
• tuples_excluded bigint
  
  
• Number of lines already processed by COPY command.
• Number of tuples not processed because they were excluded by the
• WHERE clause of the COPY command.
  ```

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.