Re: BUG #15074: psql client never returns when creating index (long running operation)

>>>>> "PG" == PG Bug reporting form <noreply(at)postgresql(dot)org> writes:

PG> When creating an index on a table containing approx. 100M records
PG> the psql client never returns when the index is eventually created
PG> on the server. This happens both in a psql client (9.6.6) on mac
PG> osx and using the libpq client via the ruby pg gem when using
PG> active records migrations.

PG> When running the command directly op the postgres database server
PG> using the psql client it works as expected.

PG> The logs from the server:
PG> 2018-02-16 16:11:05 CET:82.161.205.254(59584):REDACTED(at)REDACTED_DB:[23141]:
PG> LOG: duration: 999618.165 ms
PG> 2018-02-16 16:11:05 CET:82.161.205.254(59584):REDACTED(at)REDACTED_DB:[23141]:
PG> LOG: could not receive data from client: Connection reset by peer

This looks like a network issue; most likely you have a NAT or stateful
firewall in between the client and server which is dropping idle
connections, with a timeout set shorter than your keepalive interval on
either client or server.

The server gets "connection reset" because it tried to send the result
of the statement, plus the "ready for query" message, and the
NAT/firewall sent back a reset because it no longer recognized the
connection as valid. The psql client itself continues to wait for input
(and will do so until the keepalive timeout is reached if keepalive is
enabled, otherwise indefinitely) because the NAT/firewall doesn't
typically send any proactive reset messages.

--
Andrew (irc:RhodiumToad)