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

Hi,

Thanks for replying. I will double check the network settings, but couldn't
find anything.

Are there any psql / postgres settings I can check, I couldn't find any
that might be affecting this.

Lars

Op ma 19 feb. 2018 om 13:27 schreef Andrew Gierth <
andrew(at)tao11(dot)riddles(dot)org(dot)uk>:

> >>>>> "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)
>