Huge unread notifications combined with huge Exec may deadlock

[jackc]

This is an extreme corner case that is extremely unlikely to occur without purposely triggering it.

If the PostgreSQL server sends more data than can fit in the client's incoming network buffers and the client then sends more data than can fit in the server's incoming network buffer a deadlock will occur. The exact amount depends on the platform. On MacOS Catalina 1MB in each direction is enough to deadlock when using TCP on localhost. Less is necessary when using a Unix socket.

The only known way to trigger this is for a connection to listen on a channel and not perform any action until the network buffers are full of unread notifications. Then it needs to send a huge query that overfills the network buffers the other direction.

There are two possible solutions:

1. Read before any writes. As there is no non-blocking read available (proposal: net: non-blocking Read on Conn golang/go#36973) it is unclear how much performance cost there would be or if it could be done reliably. (What is the magic time we wait for the read deadline? How short is too short?)
2. Use goroutines for network IO. The problems with this approach are performance (a proof of concept test had a 20+% penalty in one test), additional complexity, and backwards compatibility. On the plus side, this would remove the need for the special casing to avoid deadlocks in batch operations and the copy protocol. It could also make it easy to detect network interruptions or database shutdowns (Database restarts are not handled gracefully pgx#672).

Both solutions have a large cost so it is unclear if this corner case is worth fixing.

func TestConnExecNotificationsFillReadBufferBeforeAttemptingWrite(t *testing.T) {
	t.Parallel()

	ctx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
	defer cancel()

	pgConn, err := pgconn.Connect(ctx, os.Getenv("PGX_TEST_CONN_STRING"))
	require.NoError(t, err)
	defer closeConn(t, pgConn)

	_, err = pgConn.Exec(ctx, `listen a`).ReadAll()
	require.NoError(t, err)

	notifierConn, err := pgconn.Connect(ctx, os.Getenv("PGX_TEST_CONN_STRING"))
	require.NoError(t, err)
	defer closeConn(t, notifierConn)

	kilobyte := strings.Repeat("x", 1024)

	mrr := notifierConn.Exec(ctx, fmt.Sprintf(`select pg_notify('a', '%s' || n) from generate_series(1, %d) n`, kilobyte, 1000))
	err = mrr.Close()
	require.NoError(t, err)

	buf := &bytes.Buffer{}
	for i := 0; i < 1000; i++ {
		buf.WriteString(fmt.Sprintf("select '%s';\n", kilobyte))
	}

	_, err = pgConn.Exec(ctx, buf.String()).ReadAll()
	require.NoError(t, err, "apparent deadlock when PostgreSQL has sent too many messages before Exec")
}

[jackc]

A slightly more realistic way to trigger this is to set client_min_messages = debug5. Then run try to send a very large query. For example, TestConnExecParamsMaxNumberOfParams hands with client_min_messages = debug5.

[jackc]

Fixed in pgx v5.