FileNotFoundError when connecting to postgres if fd is closed and then reopened about aiopg

[and-semakin]

Some kind of workaround is to monkey patch selectors 🐒

# This import should be at the top of the file because we need to apply monkey patch
# before executing any other code.
# We want to revert this change: https://github.com/python/cpython/pull/1030
# Additional context is here: https://github.com/aio-libs/aiopg/issues/837
import selectors  # isort:skip # noqa: F401

selectors._PollLikeSelector.modify = (  # type: ignore
    selectors._BaseSelectorImpl.modify  # type: ignore
)  # noqa: E402

[valderman]

Any progress on this? This completely breaks support for hot standby scenarios.

Reliably reproducible by setting up localhost:5431 as a read-only replica of localhost:5432 and using the following minimal example:

import aiopg
import asyncio

connstr = 'postgres://postgres@localhost:5431,localhost:5432/?target_session_attrs=primary'

async def go():
    await aiopg.connect(connstr)

asyncio.run(go())

[byrgazov]

I have a similar situation. Found on Arch Linux with PostgreSQL stopped (not installed).

# -*- coding: utf-8 -*-

__requires__ = ['aiopg[sa]==1.2.1', 'cffi==1.14.5']

import sys, os
import asyncio
import aiopg.sa
import aiopg.connection


# PostgreSQL server does not running!
DATABASE_URL = 'postgresql://localhost:12345/fake'

print('* uname:', ' '.join(os.uname()))
print('* python:', sys.version.replace('\n', ' ').replace('  ', ' ').strip())
print('* asiopg:', aiopg.version.replace('\n', ' ').replace('  ', ' ').strip())
print('* psycopg2:', aiopg.connection.psycopg2.__version__)

try:
	import cffi
except ImportError:
	print('* glibc: CFFI required!')
else:
	ffi = cffi.FFI()
	ffi.cdef('const char *gnu_get_libc_version(void);')
	C = ffi.dlopen(None)
	print('* glibc:', ffi.string(C.gnu_get_libc_version()).decode())


async def test(url):
	print('Connection:', url)
	async with aiopg.sa.create_engine(url) as engine:
		print('Engine:', engine)

loop = asyncio.get_event_loop()
loop.run_until_complete(test(DATABASE_URL))

As I understand it, the problem occurs on Python> = 3.7 and Glibc 2.33.
Arch Linux

* uname: Linux a13 5.12.9-arch1-1 #1 SMP PREEMPT Thu, 03 Jun 2021 11:36:13 +0000 x86_64
* python: 3.9.5 (default, May 24 2021, 12:50:35) [GCC 11.1.0]
* asiopg: 1.2.1, Python 3.9.5 (default, May 24 2021, 12:50:35) [GCC 11.1.0]
* psycopg2: 2.8.6 (dt dec pq3 ext lo64)
* glibc: 2.33
Connection: postgresql://localhost:12345/fake
Traceback (most recent call last):
  File "/home/bw/.local/lib/python3.9/site-packages/aiopg/connection.py", line 104, in _ready
    state = self._conn.poll()
psycopg2.OperationalError: could not connect to server: Connection refused
        Is the server running on host "localhost" (::1) and accepting
        TCP/IP connections on port 12345?
could not connect to server: Connection refused
        Is the server running on host "localhost" (127.0.0.1) and accepting
        TCP/IP connections on port 12345?


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
...
  File "/home/bw/.local/lib/python3.9/site-packages/aiopg/connection.py", line 124, in _ready
    self._loop.remove_writer(self._fileno)
  File "/usr/lib/python3.9/asyncio/selector_events.py", line 351, in remove_writer
    return self._remove_writer(fd)
  File "/usr/lib/python3.9/asyncio/selector_events.py", line 325, in _remove_writer
    self._selector.modify(fd, mask, (reader, None))
  File "/usr/lib/python3.9/selectors.py", line 390, in modify
    self._selector.modify(key.fd, selector_events)
FileNotFoundError: [Errno 2] No such file or directory

* uname: Linux a13 5.12.9-arch1-1 #1 SMP PREEMPT Thu, 03 Jun 2021 11:36:13 +0000 x86_64
* python: 3.7.10 (default, May 14 2021, 23:54:07) [GCC 10.2.0]
* asiopg: 1.2.1, Python 3.7.10 (default, May 14 2021, 23:54:07) [GCC 10.2.0]
* psycopg2: 2.8.6 (dt dec pq3 ext lo64)
* glibc: 2.33
...
        Is the server running on host "localhost" (127.0.0.1) and accepting
        TCP/IP connections on port 12345?
...
FileNotFoundError: [Errno 2] No such file or directory

* uname: Linux a13 5.12.9-arch1-1 #1 SMP PREEMPT Thu, 03 Jun 2021 11:36:13 +0000 x86_64
* python: 3.6.13 (default, Jun 7 2021, 17:51:57) [GCC 11.1.0]
* asiopg: 1.2.1, Python 3.6.13 (default, Jun 7 2021, 17:51:57) [GCC 11.1.0]
* psycopg2: 2.8.6 (dt dec pq3 ext lo64)
* glibc: 2.33
...
  File "/home/bw/src/.venv-3.6/lib/python3.6/site-packages/aiopg/connection.py", line 104, in _ready
    state = self._conn.poll()
psycopg2.OperationalError: could not connect to server: Connection refused
        Is the server running on host "localhost" (::1) and accepting
        TCP/IP connections on port 12345?
could not connect to server: Connection refused
        Is the server running on host "localhost" (127.0.0.1) and accepting
        TCP/IP connections on port 12345?

Devuan GNU/Linux 3 (beowulf)

* uname: Linux d69 5.3.18-lp152.57-default #1 SMP Fri Dec 4 07:27:58 UTC 2020 (7be5551) x86_64
* python: 3.7.3 (default, Jul 25 2020, 13:03:44) [GCC 8.3.0]
* asiopg: 1.2.1, Python 3.7.3 (default, Jul 25 2020, 13:03:44) [GCC 8.3.0]
* psycopg2: 2.8.6 (dt dec pq3 ext lo64)
* glibc: 2.28
...
  File "/home/bw/.local/lib/python3.7/site-packages/aiopg/connection.py", line 104, in _ready
    state = self._conn.poll()
psycopg2.OperationalError: could not connect to server: Connection refused
        Is the server running on host "localhost" (127.0.0.1) and accepting
        TCP/IP connections on port 12345?

CRUX 3.6.1

* uname: Linux crux 5.4.80-bw2 #2 SMP Sat Jun 5 18:11:50 UTC 2021 x86_64
* python: 3.9.0 (default, Dec 6 2020, 03:55:43) [GCC 10.2.0]
* asiopg: 1.2.1, Python 3.9.0 (default, Dec 6 2020, 03:55:43) [GCC 10.2.0]
* psycopg2: 2.8.6 (dt dec pq3 ext lo64)
* glibc: 2.32
...
  File "/home/bw/.local/lib/python3.9/site-packages/aiopg/connection.py", line 104, in _ready
    state = self._conn.poll()
psycopg2.OperationalError: could not connect to server: Connection refused
        Is the server running on host "localhost" (127.0.0.1) and accepting
        TCP/IP connections on port 12345?

Etc.

[arssher]

FWIW I also bumped into this and while experimenting wrote draft patch that works at
https://github.com/arssher/aiopg/tree/handle_changed_socket
But it is not decent enough to be proposed as PR.

[carlosribas]

I was getting this same error:

    self._context.run(self._callback, *self._args)
  File "/srv/reference/venv/lib/python3.9/site-packages/aiopg/connection.py", line 837, in _ready
    self._loop.add_writer(
  File "/usr/local/lib/python3.9/asyncio/selector_events.py", line 341, in add_writer
    self._add_writer(fd, callback, *args)
  File "/usr/local/lib/python3.9/asyncio/selector_events.py", line 299, in _add_writer
    self._selector.modify(fd, mask | selectors.EVENT_WRITE,
  File "/usr/local/lib/python3.9/selectors.py", line 390, in modify
    self._selector.modify(key.fd, selector_events)
FileNotFoundError: [Errno 2] No such file or directory

as the first option on google for this error is this issue, I thought it best to comment here what my error was, as it may help others. I had changed the DB password, but I forgot to change my .env file.

As the error message says FileNotFoundError, it took me a few hours to realize that I was trying to connect with a wrong password

[pablodcar]

Any progress on this? This completely breaks support for hot standby scenarios.

Reliably reproducible by setting up localhost:5431 as a read-only replica of localhost:5432 and using the following minimal example:

import aiopg
import asyncio

connstr = 'postgres://postgres@localhost:5431,localhost:5432/?target_session_attrs=primary'

async def go():
    await aiopg.connect(connstr)

asyncio.run(go())

@valderman Have you found any solution to this? failover does not work for me as well.