Re: [RFC: bug fix?] Connection attempt block forever when the synchronous standby is not running

On Fri, Jul 4, 2014 at 7:29 PM, MauMau <maumau307(at)gmail(dot)com> wrote:
>
> Hello,
>
> "When requesting synchronous replication, each commit of a write
transaction will wait until confirmation is received that the commit has
been written to the transaction log on disk of both the primary and standby
server.
> ...
> Read only transactions and transaction rollbacks need not wait for
replies from standby servers. Subtransaction commits do not wait for
responses from standby servers, only top-level commits."
>
>
> [Hypothesis]
> Why does the connection processing emit WAL?
>
> Probably, it did page-at-a-time vacuum during access to pg_database and
pg_authid for client authentication. src/backend/access/heap/README.HOT
describes:

I agree with your analysis that it can happen during connection
attempt.

> But the customer could not reproduce the problem when he performed the
same archive recovery from the same base backup again. Why? I guess the
autovacuum daemon vacuumed the system catalogs before he attempted to
connect to the database.
>
> Is this correct?

One way to confirm could be to perform the archive recovery by
disabling autovacuum.

>
> [How to fix]
> Of course, adding "-o '-c synchronous_commit=local'" or "-o '-c
synchronous_standby_names='" to pg_ctl start in the recovery script would
prevent the problem.
>
> But isn't there anything to fix in PostgreSQL? I think the doc needs
improvement so that users won't misunderstand that only write transactions
would block at commit.

I also think at the very least we should update docs even if we
don't have any solution for this case.

Another thing which I am wondering about is can't the same happen
even for Read Only transaction (incase someone does Select which
prunes the page).

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com