Re: pg_stat_progress_copy not working as intended

2022年3月2日(水) 2:56 asalias mark <postgres.rocks@gmail.com>:
>
> tutorial: https://paquier.xyz/postgresql-2/postgres-14-monitoring-copy/
> screenshot:  https://prnt.sc/PROhSi8YOfc0
>
>
> postgres=# CREATE TABLE copy_tab (a int, b text);
> CREATE TABLE
>
> postgres=# INSERT INTO copy_tab SELECT generate_series(1, 10) AS a,
>   generate_series(1, 10) ASpostgres-#   'a' ||  generate_series(1, 10) AS b;
> INSERT 0 10
> postgres=# COPY copy_tab TO '/tmp/copy_tab_data.txt';
> COPY 10
> postgres=# SELECT relid::regclass, command, type, bytes_processed, tuples_processed
> postgres-#      FROM pg_stat_progress_copy;
>  relid | command | type | bytes_processed | tuples_processed
> -------+---------+------+-----------------+------------------
> (0 rows)
>
> postgres=# select version();
>                                                              version
> ----------------------------------------------------------------------------------------------------------------------------------
>  PostgreSQL 14.2 (Ubuntu 14.2-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
> (1 row)
>
>
> But I should get:
>
> SELECT relid::regclass, command, type, bytes_processed, tuples_processed
>      FROM pg_stat_progress_copy;
>   relid   | command | type | bytes_processed | tuples_processed
> ----------+---------+------+-----------------+------------------
>  copy_tab | COPY TO | FILE |              52 |               10
> (1 row)
>
>
> Why are things not working as intended?

"pg_stat_progress_copy" contains statistics for a *running* COPY operation. If
no COPY operation is running, it will be empty, which is the case in the example
you show above.

If you want to check the contents of "pg_stat_progress_copy" , you'll need to:
  a) execute a long-running COPY operation
  b) query "pg_stat_progress_copy" from another session while it is running

See also example here:

    https://pgpedia.info/p/pg_stat_progress_copy.html#examples

Regards

Ian Barwick


--
EnterpriseDB: https://www.enterprisedb.com