Thread: pg_stat_progress_copy not working as intended

pg_stat_progress_copy not working as intended

From

asalias mark

Date:

01 March 2022, 20:55:36

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=# NSERT INTO copy_tab SELECT generate_series(1, 10) AS a,
postgres-# 'a' || generate_series(1, 10) AS b;
ERROR: syntax error at or near "NSERT"
LINE 1: NSERT INTO copy_tab SELECT generate_series(1, 10) AS a,
^
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?

Re: pg_stat_progress_copy not working as intended

From

Ian Lawrence Barwick

Date:

02 March 2022, 03:17:35

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

Re: pg_stat_progress_copy not working as intended

From

asalias mark

Date:

02 March 2022, 12:48:56

Now I know what to do. It's a very powerful feature.

Your referenced website ( https://pgpedia.info. ) is fun!. Huge Thanks!

On Wed, Mar 2, 2022 at 5:47 AM Ian Lawrence Barwick <barwick@gmail.com> wrote:

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