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.

Assessment

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

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.

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?
    : N/A

If I have this query:

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

and in Python I do:

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 <module>
    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, 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.

Dominant language
Python
Stars
8.1k
Forks
469
Avg merge
2d 20h
Merged PRs (30d)
9

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.

More from MagicStack/asyncpg

All issues in MagicStack/asyncpg

Similar issues

More Python issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.