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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general