Postgresql – Kill concurrent materialized view refresh PID

killmaterialized-viewpostgresqlpostgresql-10

Concurrent refreshes of materialized views don't have a record in pg_stat_activity, other than the initial (~immediately completing) query to start them.

Is there some other way to obtain the PID, in order to perform pg_terminate_backend(pid)?

Best Answer

That is not true.

CREATE MATERIALIZED VIEW slow AS
SELECT i,
       pg_sleep(1) IS NULL AS no
FROM generate_series(1, 10) AS i;

CREATE UNIQUE INDEX ON slow (i);

REFRESH MATERIALIZED VIEW CONCURRENTLY slow;

That statement does not complete immediately, but hangs until the materialized view is refreshed.

Then, while the statement is running, in another session:

SELECT pid, state, query FROM pg_stat_activity ;

  pid  | state  |                      query                       
-------+--------+--------------------------------------------------
 12618 |        | 
 12620 |        | 
 13049 | active | REFRESH MATERIALIZED VIEW CONCURRENTLY slow ;
 13032 | active | SELECT pid, state, query FROM pg_stat_activity ;
 12616 |        | 
 12615 |        | 
 12617 |        | 
(7 rows)

SELECT pg_cancel_backend(13049);

 pg_cancel_backend 
-------------------
 t

The statement is canceled. In the first session, I see:

ERROR:  canceling statement due to user request

Perhaps you are confused by the fact that the query column in pg_stat_activity does not only show the current query, but the latest query that was run in that session. So if the state is idle, then the statement is done and the materialized view is already refreshed. Then of course canceling the query has no effect.

Related Question