MagicStack / MagicStack/asyncpg

Cannot use datetimes before UNIX epoch on Windows

Open
#1,115 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
Python
Stars
8.1k
Forks
468
PR merge metrics
No merged PRs in 30d

Description

* **asyncpg version**: 0.29.0
* **PostgreSQL version**: 15.5
* **Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
the issue with a local PostgreSQL install?**: Local PostgreSQL install
* **Python version**: 3.10.1
* **Platform**: Windows
* **Do you use pgbouncer?**: No
* **Did you install asyncpg with pip?**: Yes
* **If you built asyncpg locally, which version of Cython did you use?**: N/A
* **Can the issue be reproduced under both asyncio and
[uvloop](https://github.com/magicstack/uvloop)?**: N/A

If I have this query:

```sql
INSERT INTO process.start_and_end (user_id, process_id, start_dt, end_dt) VALUES ($1, $2, $3, $4);
```

and in Python I do:

```python
await conn.executemany(query, [(1325, 1001, datetime.datetime(1969, 12, 31, 0, 0), None)])
```

this line throws the following exception:

```
Traceback (most recent call last):
File "asyncpg\protocol\prepared_stmt.pyx", line 175, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
File "asyncpg\protocol\codecs/base.pyx", line 227, in asyncpg.protocol.protocol.Codec.encode
File "asyncpg\protocol\codecs/base.pyx", line 129, in asyncpg.protocol.protocol.Codec.encode_scalar
File "asyncpg\pgproto\./codecs/datetime.pyx", line 222, in asyncpg.pgproto.pgproto.timestamptz_encode
OSError: [Errno 22] Invalid argument

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

Traceback (most recent call last):
File "main.py", line 919, in
loop.run_until_complete(async_main())
File "C:\Scoop\apps\python\current\lib\asyncio\base_events.py", line 641, in run_until_complete
return future.result()
[...]
File "main.py", line 401, in __insert_tape
await conn.executemany(query, [(1325, 1001, datetime.datetime(1969, 12, 31, 0, 0), None)])
File ".venv\lib\site-packages\asyncpg\connection.py", line 391, in executemany
return await self._executemany(command, args, timeout)
File ".venv\lib\site-packages\asyncpg\connection.py", line 1911, in _executemany
result, _ = await self._do_execute(query, executor, timeout)
File ".venv\lib\site-packages\asyncpg\connection.py", line 1945, in _do_execute
result = await executor(stmt, None)
File "asyncpg\protocol\protocol.pyx", line 267, in bind_execute_many
File "asyncpg\protocol\coreproto.pyx", line 974, in asyncpg.protocol.protocol.CoreProtocol._bind_execute_many_more
File "asyncpg\protocol\protocol.pyx", line 230, in genexpr
File "asyncpg\protocol\prepared_stmt.pyx", line 204, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
asyncpg.exceptions.DataError: invalid input for query argument $3 in element #0 of executemany() sequence: datetime.datetime(1969, 12, 31, 0, 0) ([Errno 22] Invalid argument)
```

Digging into it a bit more, I found that the culprit is [this line in the codec for datetime objects](https://github.com/MagicStack/py-pgproto/blob/master/codecs/datetime.pyx#L222), which is related to this old CPython issue on Windows: https://github.com/python/cpython/issues/80940.

The fix looks quite simple, but unfortunately I do not have the time to work on that, so I'm opening an issue instead of a PR.

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the linked line in py-pgproto's codecs/datetime.pyx and compare its behavior with the referenced CPython Windows issue. Reproduce the datetime.datetime(1969, 12, 31) bind through executemany() on Windows. Done means pre-UNIX-epoch datetimes no longer raise OSError during encoding.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, python
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
42/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.