Re: released savepoint blocking further statements

On Thu, Apr 7, 2016 at 1:26 AM, Miklós Fazekas <mfazekas@szemafor.com> wrote:


> [2.] I've tried to use the query Lock Monitoring wiki from to find out what
> is blocking:
>
> it shows that 79469 | RELEASE SAVEPOINT active_record_2_47 is the
> current_statement_in_blocking_process.
>
> https://wiki.postgresql.org/wiki/Lock_Monitoring
>
> SELECT bl.pid                 AS blocked_pid,
>
>          a.usename              AS blocked_user,
>          ka.query               AS current_statement_in_blocking_process,
>          now() - ka.query_start AS blocking_duration,
>          kl.pid                 AS blocking_pid,
>          ka.usename             AS blocking_user,
>          a.query                AS blocked_statement,
>          now() - a.query_start  AS blocked_duration
>   FROM  pg_catalog.pg_locks         bl
>    JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
>    JOIN pg_catalog.pg_locks         kl ON kl.transactionid =
> bl.transactionid AND kl.pid != bl.pid
>    JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
>   WHERE NOT bl.GRANTED;


That is probably not the current statement in the blocking process.
It is likely that the blocking process is in the state "idle in
transaction", and the RELEASE SAVEPOINT is actually the most recent
statement, not the current statement.

I've updated the wiki to clarify that, but it could use some more polishing.

So your savepoint release has executed successfully, but the outer
transaction is still holding on to locks which block the other
process.  You have to convince that outer transaction to either commit
or rollback.

Cheers,

Jeff