Обсуждение: [GENERAL] "Canceling authentication due to timeout" with idle transaction andreindex

Hello,
I am experiencing the following while using PostgreSQL 
9.6.3-1.pgdg16.04+1 (deb package from official repository):

1. with "\set AUTOCOMMIT off" in my psqlrc, issue a
"SELECT * FROM pg_stat_activity;" and leave it there

2. in a different connection, issue a database REINDEX (of any database 
different from 'postgres')

* 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.

* The REINDEX doesn't actually complete unless I end the transaction 
started at point 1.

Is this expected? I am failing to see the relation between an idle 
transaction in the 'postgres' database, a reindex operation and 
subsequent logins.

Thank you very much for your attention,
Best regards

-- 
https://s19n.net


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Sep 15, 2017 at 7:25 PM, s19n <mailbox@s19n.net> wrote:
> Is this expected? I am failing to see the relation between an idle
> transaction in the 'postgres' database, a reindex operation and subsequent
> logins.

REINDEX DATABASE processes as well system indexes, and takes an
exclusive lock on them in order to process. The lock being hold by the
transaction of session 1 conflicts by what REINDEX tries to take, and
REINDEX is able to process only when the index is free from any
lookups. The reason why logins are not possible is this was likely
waiting for a lock of an index of pg_authid which is looked up at
authentication.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

On 09/15/2017 06:34 AM, Justin Pryzby wrote:
[snip]
> But you might consider: 1) looping around tables/indices rather than "REINDEX
> DATABASE", and then setting a statement_timeout=9s for each REINDEX statement;

Is there a way to do that within psql?  (Doing it from bash is trivial, but 
I'd rather do it from SQL.)

-- 
World Peace Through Nuclear Pacification



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Sep 15, 2017 at 06:49:06AM -0500, Ron Johnson wrote:
> On 09/15/2017 06:34 AM, Justin Pryzby wrote:
> [snip]
> >But you might consider: 1) looping around tables/indices rather than "REINDEX
> >DATABASE", and then setting a statement_timeout=9s for each REINDEX statement;
> 
> Is there a way to do that within psql?  (Doing it from bash is trivial, but
> I'd rather do it from SQL.)

Not that I know, but it wouldn't help me, since our script also calls pg_repack
(for indices on system and some other tables), and also has logic to handle
differently historic partition 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