Postgres Pro
Downloads
Home   >   mailing lists

Thread: What do null column values for pg_stat_progress_vacuum mean?

What do null column values for pg_stat_progress_vacuum mean?

From
Mark Haylock
Date:
Hi,

We have an autovacuum process that has been running for almost 27 hours:

SELECT * FROM pg_stat_activity WHERE pid = 11731;
-[ RECORD 1 ]----+---------------------------------------
datid            | 16385
datname          | database_name
pid              | 11731
usesysid         |
usename          |
application_name |
client_addr      |
client_hostname  |
client_port      |
backend_start    | 2020-03-04 23:40:14.828138+00
xact_start       | 2020-03-04 23:40:14.849367+00
query_start      | 2020-03-04 23:40:14.849367+00
state_change     | 2020-03-04 23:40:14.849368+00
wait_event_type  |
wait_event       |
state            | active
backend_xid      |
backend_xmin     | 3801997676
query            | autovacuum: VACUUM public.responses
backend_type     | autovacuum worker

A row shows up in pg_stat_progress_vacuum, but it contains null values
for every column.

SELECT * FROM pg_stat_progress_vacuum WHERE pid = 11731;
-[ RECORD 1 ]------+---------------
pid                | 11731
datid              | 16385
datname            | d2j496215lfs41
relid              |
phase              |
heap_blks_total    |
heap_blks_scanned  |
heap_blks_vacuumed |
index_vacuum_count |
max_dead_tuples    |
num_dead_tuples    |

I see nothing in the documentation to suggest that this is an expected
state - what does it mean?

Thanks,
Mark.

Re: What do null column values for pg_stat_progress_vacuum mean?

From
Mark Haylock
Date:
Sorry I've failed to mention which postgres version this is with:
PostgreSQL 10.11.

On Fri, Mar 6, 2020 at 3:39 PM Mark Haylock <mh@trineo.co.nz> wrote:
>
> Hi,
>
> We have an autovacuum process that has been running for almost 27 hours:
>
> SELECT * FROM pg_stat_activity WHERE pid = 11731;
> -[ RECORD 1 ]----+---------------------------------------
> datid            | 16385
> datname          | database_name
> pid              | 11731
> usesysid         |
> usename          |
> application_name |
> client_addr      |
> client_hostname  |
> client_port      |
> backend_start    | 2020-03-04 23:40:14.828138+00
> xact_start       | 2020-03-04 23:40:14.849367+00
> query_start      | 2020-03-04 23:40:14.849367+00
> state_change     | 2020-03-04 23:40:14.849368+00
> wait_event_type  |
> wait_event       |
> state            | active
> backend_xid      |
> backend_xmin     | 3801997676
> query            | autovacuum: VACUUM public.responses
> backend_type     | autovacuum worker
>
> A row shows up in pg_stat_progress_vacuum, but it contains null values
> for every column.
>
> SELECT * FROM pg_stat_progress_vacuum WHERE pid = 11731;
> -[ RECORD 1 ]------+---------------
> pid                | 11731
> datid              | 16385
> datname            | d2j496215lfs41
> relid              |
> phase              |
> heap_blks_total    |
> heap_blks_scanned  |
> heap_blks_vacuumed |
> index_vacuum_count |
> max_dead_tuples    |
> num_dead_tuples    |
>
> I see nothing in the documentation to suggest that this is an expected
> state - what does it mean?
>
> Thanks,
> Mark.



-- 
Mark Haylock - Developer
Sydney | Christchurch | Auckland | Boulder
e: mh@trineo.com | w: trineo.com | ph: +64 3 377 4001

Re: What do null column values for pg_stat_progress_vacuum mean?

From
Adrian Klaver
Date:
On 3/5/20 6:39 PM, Mark Haylock wrote:
> Hi,
> 
> We have an autovacuum process that has been running for almost 27 hours:
> 
> SELECT * FROM pg_stat_activity WHERE pid = 11731;
> -[ RECORD 1 ]----+---------------------------------------
> datid            | 16385
> datname          | database_name
> pid              | 11731
> usesysid         |
> usename          |
> application_name |
> client_addr      |
> client_hostname  |
> client_port      |
> backend_start    | 2020-03-04 23:40:14.828138+00
> xact_start       | 2020-03-04 23:40:14.849367+00
> query_start      | 2020-03-04 23:40:14.849367+00
> state_change     | 2020-03-04 23:40:14.849368+00
> wait_event_type  |
> wait_event       |
> state            | active
> backend_xid      |
> backend_xmin     | 3801997676
> query            | autovacuum: VACUUM public.responses
> backend_type     | autovacuum worker
> 
> A row shows up in pg_stat_progress_vacuum, but it contains null values
> for every column.
> 
> SELECT * FROM pg_stat_progress_vacuum WHERE pid = 11731;
> -[ RECORD 1 ]------+---------------
> pid                | 11731
> datid              | 16385
> datname            | d2j496215lfs41
> relid              |
> phase              |
> heap_blks_total    |
> heap_blks_scanned  |
> heap_blks_vacuumed |
> index_vacuum_count |
> max_dead_tuples    |
> num_dead_tuples    |
> 
> I see nothing in the documentation to suggest that this is an expected
> state - what does it mean?

Not sure, but you might try:

https://www.postgresql.org/docs/10/view-pg-locks.html

The pid column can be joined to the pid column of the pg_stat_activity 
view to get more information on the session holding or awaiting each 
lock, for example

SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa
     ON pl.pid = psa.pid;

> 
> Thanks,
> Mark.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com