Re: released savepoint blocking further statements

Jeff,

Thanks much for your help. There was an issue with rails 5 beta connection
pool where in some cases a connection with an open transaction returned to
pool.

Regards,
Miklós

On Fri, Apr 8, 2016 at 8:00 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Thu, Apr 7, 2016 at 1:26 AM, Miklós Fazekas <mfazekas(at)szemafor(dot)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
>