Re: Lock leaking out of Transaction?

On Wed, 2020-01-15 at 10:42 +1100, James Sewell wrote:
> I am trying to chase down a locking issue - it looks like a materialized view refresh is being
> held up by a relation lock which is held by an out of transaction session. My understanding was that
> this was not possible (see SQL output below).
>
> The locking session is making progress (I can see query_start advancing), which makes it even more confusing.
>
> Any advice?
>
> # select * from pg_locks l join pg_stat_activity a on l.pid = a.pid where relation = 1438729::regclass;
> -[ RECORD 1 ]------+---------------------------------------------------------------------------------
> locktype | relation
> database | 16428
> relation | 1438729
> mode | ShareUpdateExclusiveLock
> granted | f
> fastpath | f
> pid | 88955
> wait_event_type | Lock
> wait_event | relation
> state | active
> query | autovacuum: VACUUM supply_nodes (to prevent wraparound)
> backend_type | autovacuum worker
> -[ RECORD 2 ]------+---------------------------------------------------------------------------------
> locktype | relation
> database | 16428
> relation | 1438729
> mode | ExclusiveLock
> granted | f
> pid | 6839
> wait_event_type | Lock
> wait_event | relation
> state | active
> query | REFRESH MATERIALIZED VIEW CONCURRENTLY supply_nodes ;
> backend_type | client backend
> -[ RECORD 3 ]------+---------------------------------------------------------------------------------
> locktype | relation
> database | 16428
> relation | 1438729
> mode | ExclusiveLock
> granted | t
> pid | 65447
> application_name | PostgreSQL JDBC Driver
> wait_event_type | Client
> wait_event | ClientRead
> state | idle
> query | COMMIT
> backend_type | client backend

I cannot explain that either; could it be shared memory corruption?

What I would try is

SELECT pg_terminate_backend(65447);

and see if the session and its lock go away.

If that does not do the trick, I would restart PostgreSQL, which should get
rid of any possible memory corruption.

Then perhaps the anti-wraparoung autovacuum can succeed.
This autovacuum would also block you, but you should let it finish, since
it is an important system task.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com