Re: "Canceling authentication due to timeout" with idle transaction and reindex

On Fri, Sep 15, 2017 at 12:25:58PM +0200, s19n wrote:

> 1. with "\set AUTOCOMMIT off" in my psqlrc, issue a
> "SELECT * FROM pg_stat_activity;" and leave it there
This probably obtains a read lock on some shared, system tables/indices..

> 2. in a different connection, issue a database REINDEX (of any database
> different from 'postgres')
.. and this waits to get an EXCLUSIVE lock on those tables/inds, but has to
wait on the read lock;

> * Any further attempt to create new connections to the server, to any
> database, does not succeed and leads to a "FATAL: canceling authentication
> due to timeout" in the server logs.
.. and logins are apparently waiting on the reindex (itself waiting to get
exclusive) lock.

You can look at the locks (granted vs waiting) in SELECT * FROM pg_locks

But you might consider: 1) looping around tables/indices rather than "REINDEX
DATABASE", and then setting a statement_timeout=9s for each REINDEX statement;
and/or, 2) use pg_repack, but I don't think it handles system tables.

Justin