Re: vacuum analyse waiting

[Nicolas Aubert <aubertnicolas11@xxxxxxxxx>]

Hello,

Here are the contents of the table pg_locks when the vacuum is in waiting mode.
13382 PID number :

  locktype  | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |          mode           | granted | fastpath
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------------+---------+----------
 relation   |    22800 |    11187 |      |       |            |               |         |       |          | 63/673898          | 23929 | AccessShareLock          | t       | t
 virtualxid |          |          |      |       | 63/673898  |               |         |       |          | 63/673898          | 23929 | ExclusiveLock            | t       | t
 virtualxid |          |          |      |       | 65/10576   |               |         |       |          | 65/10576           | 13832 | ExclusiveLock            | t       | t
 relation   |    22800 |   100247 |      |       |            |               |         |       |          | 61/345             |  1219 | RowExclusiveLock         | t       | t
 virtualxid |          |          |      |       | 61/345     |               |         |       |          | 61/345             |  1219 | ExclusiveLock            | t       | t
 relation   |    22800 |    26143 |      |       |            |               |         |       |          | 2/397              |   636 | RowExclusiveLock         | t       | t
 relation   |    22800 |    25219 |      |       |            |               |         |       |          | 2/397              |   636 | RowExclusiveLock         | t       | t
 virtualxid |          |          |      |       | 2/397      |               |         |       |          | 2/397              |   636 | ExclusiveLock            | t       | t
 relation   |    22800 |    26139 |      |       |            |               |         |       |          | 13/133             |   502 | RowExclusiveLock         | t       | t
 relation   |    22800 |    25218 |      |       |            |               |         |       |          | 13/133             |   502 | RowExclusiveLock         | t       | t
 virtualxid |          |          |      |       | 13/133     |               |         |       |          | 13/133             |   502 | ExclusiveLock            | t       | t
 relation   |    22800 |    23663 |      |       |            |               |         |       |          | 2/397              |   636 | ShareUpdateExclusiveLock | t       | f
 relation   |    22800 |    23657 |      |       |            |               |         |       |          | 65/10576           | 13832 | ShareUpdateExclusiveLock | f       | f
 relation   |    22800 |   100238 |      |       |            |               |         |       |          | 61/345             |  1219 | ShareUpdateExclusiveLock | t       | f
 relation   |    22800 |    23657 |      |       |            |               |         |       |          | 13/133             |   502 | ShareUpdateExclusiveLock | t       | f

2016-03-22 7:35 GMT+01:00 Nicolas Aubert <aubertnicolas11@xxxxxxxxx>:

I'm a little lost in solving this problem.
The process vacuum waiting to disappeared. I do not think it is completed successfully.
How to verify that the vacuum passes successfully?
How to prevent it happening in modewaiting?

2016-03-21 16:37 GMT+01:00 bricklen <bricklen@xxxxxxxxx>:

On Mon, Mar 21, 2016 at 8:26 AM, Nicolas Aubert <aubertnicolas11@xxxxxxxxx> wrote:

the vacuum process is the 22510
here is what I found in pg_locks

virtualtransaction |  pid  |           mode           | granted | fastpath
------------+---------------+---------+-------+----------+--------------------+-------+--------------------------+---------+----------
                                                          3/376480           | 22510 | ExclusiveLock            | t       |
            |               |         |       |          | 3/376480           | 22510 | ShareUpdateExclusiveLock | f       | f

Have a look at the lock dependency queries at https://wiki.postgresql.org/wiki/Lock_dependency_information for more information about what is holding the locks.