Exception on "unknown PostgreSQL timezone" on windows

[CaselIT]

Hi

I think there is a bug handling unknown timezones
To reproduce

import psycopg

dsn = "postgresql://scott:tiger@localhost/test"
conn = psycopg.connect(dsn)

conn.execute('SET SESSION TIME ZONE 0').close()
print(conn.execute('SELECT CURRENT_TIMESTAMP').fetchone())

# # standalone sqlalchemy as example. behaves the same as using only psycopg
# import sqlalchemy as sa
# e = sa.create_engine(dsn.replace('postgresql', 'postgresql+psycopg'))
# with e.connect() as conn:
#     conn.exec_driver_sql("SET SESSION TIME ZONE 0")
#     print(conn.execute(sa.func.current_timestamp()).scalar())

on windows this prints a the following stacktrace

# on windows
Traceback (most recent call last):
  File "psycopg_binary\\_psycopg/transform.pyx", line 612, in psycopg_binary._psycopg.Transformer._c_get_loader
  File "psycopg_binary\\types/datetime.pyx", line 701, in psycopg_binary._psycopg.TimestamptzLoader.__init__
  File "psycopg_binary\\types/datetime.pyx", line 691, in psycopg_binary._psycopg._BaseTimestamptzLoader.__init__
  File "psycopg_binary\\types/datetime.pyx", line 1106, in psycopg_binary._psycopg._timezone_from_connection
  File "C:\Users\me\miniconda3\envs\sa\lib\zoneinfo\_common.py", line 12, in load_tzdata
    return importlib.resources.open_binary(package_name, resource_name)
  File "C:\Users\me\miniconda3\envs\sa\lib\importlib\resources.py", line 46, in open_binary
    return reader.open_resource(resource)
  File "C:\Users\me\miniconda3\envs\sa\lib\importlib\abc.py", line 433, in open_resource
    return self.files().joinpath(resource).open('rb')
  File "C:\Users\me\miniconda3\envs\sa\lib\pathlib.py", line 1117, in open
    return self._accessor.open(self, mode, buffering, encoding, errors,
OSError: [Errno 22] Invalid argument: 'C:\\Users\\me\\miniconda3\\envs\\sa\\lib\\site-packages\\tzdata\\zoneinfo\\<+00>-00'
Exception ignored in: 'psycopg_binary._psycopg.Transformer.set_pgresult'
Traceback (most recent call last):
  File "psycopg_binary\\_psycopg/transform.pyx", line 612, in psycopg_binary._psycopg.Transformer._c_get_loader
  File "psycopg_binary\\types/datetime.pyx", line 701, in psycopg_binary._psycopg.TimestamptzLoader.__init__
  File "psycopg_binary\\types/datetime.pyx", line 691, in psycopg_binary._psycopg._BaseTimestamptzLoader.__init__
  File "psycopg_binary\\types/datetime.pyx", line 1106, in psycopg_binary._psycopg._timezone_from_connection
  File "C:\Users\me\miniconda3\envs\sa\lib\zoneinfo\_common.py", line 12, in load_tzdata
    return importlib.resources.open_binary(package_name, resource_name)
  File "C:\Users\me\miniconda3\envs\sa\lib\importlib\resources.py", line 46, in open_binary
    return reader.open_resource(resource)
  File "C:\Users\me\miniconda3\envs\sa\lib\importlib\abc.py", line 433, in open_resource
    return self.files().joinpath(resource).open('rb')
  File "C:\Users\me\miniconda3\envs\sa\lib\pathlib.py", line 1117, in open
    return self._accessor.open(self, mode, buffering, encoding, errors,
OSError: [Errno 22] Invalid argument: 'C:\\Users\\me\\miniconda3\\envs\\sa\\lib\\site-packages\\tzdata\\zoneinfo\\<+00>-00'

Uninstalling psycopg-binary also results on an error with similar stack trace

Stack trace

Traceback (most recent call last):
  File "C:\Users\me\miniconda3\envs\temp\lib\site-packages\psycopg\_tz.py", line 26, in get_tzinfo
    return _timezones[tzname]
KeyError: b'<+00>-00'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\me\Dev\GitHub\sqlalchemy\test2.py", line 7, in <module>
    print(conn.execute('SELECT CURRENT_TIMESTAMP').fetchone())
  File "C:\Users\me\miniconda3\envs\temp\lib\site-packages\psycopg\connection.py", line 873, in execute
    return cur.execute(query, params, prepare=prepare)
  File "C:\Users\me\miniconda3\envs\temp\lib\site-packages\psycopg\cursor.py", line 721, in execute
    self._conn.wait(
  File "C:\Users\me\miniconda3\envs\temp\lib\site-packages\psycopg\connection.py", line 954, in wait
    return waiting.wait(gen, self.pgconn.socket, timeout=timeout)
  File "C:\Users\me\miniconda3\envs\temp\lib\site-packages\psycopg\waiting.py", line 62, in wait_selector
    s = gen.send(ready)
  File "C:\Users\me\miniconda3\envs\temp\lib\site-packages\psycopg\cursor.py", line 205, in _execute_gen
    self._select_current_result(0)
  File "C:\Users\me\miniconda3\envs\temp\lib\site-packages\psycopg\cursor.py", line 517, in _select_current_result
    self._tx.set_pgresult(res, format=format)
  File "C:\Users\me\miniconda3\envs\temp\lib\site-packages\psycopg\_transform.py", line 152, in set_pgresult
    self._row_loaders = [
  File "C:\Users\me\miniconda3\envs\temp\lib\site-packages\psycopg\_transform.py", line 153, in <listcomp>
    self.get_loader(result.ftype(i), fmt).load for i in range(nf)
  File "C:\Users\me\miniconda3\envs\temp\lib\site-packages\psycopg\_transform.py", line 347, in get_loader
    loader = self._loaders[format][oid] = loader_cls(oid, self)
  File "C:\Users\me\miniconda3\envs\temp\lib\site-packages\psycopg\types\datetime.py", line 505, in __init__
    self._timezone = get_tzinfo(self.connection.pgconn if self.connection else None)
  File "C:\Users\me\miniconda3\envs\temp\lib\site-packages\psycopg\_tz.py", line 30, in get_tzinfo
    zi: tzinfo = ZoneInfo(sname)
  File "C:\Users\me\miniconda3\envs\temp\lib\zoneinfo\_common.py", line 12, in load_tzdata
    return importlib.resources.open_binary(package_name, resource_name)
  File "C:\Users\me\miniconda3\envs\temp\lib\importlib\resources.py", line 46, in open_binary
    return reader.open_resource(resource)
  File "C:\Users\me\miniconda3\envs\temp\lib\importlib\abc.py", line 433, in open_resource
    return self.files().joinpath(resource).open('rb')
  File "C:\Users\me\miniconda3\envs\temp\lib\pathlib.py", line 1117, in open
    return self._accessor.open(self, mode, buffering, encoding, errors,
OSError: [Errno 22] Invalid argument: 'C:\\Users\\me\\miniconda3\\envs\\temp\\lib\\site-packages\\tzdata\\zoneinfo\\<+00>-00'

I've tried the same code on linux and an error is printed but the script runs otherwise correctly (with or without binary):

# on linux
unknown PostgreSQL timezone: '<+00>-00'; will use UTC
(datetime.datetime(2022, 9, 13, 20, 44, 4, 325059, tzinfo=datetime.timezone.utc),)

I've noticed this error while running the sqlalchemy test suite, where it actually triggers an access violation, that I could not manage to trigger in a standalone script.
The exception happens on the following test of sqlalchemy (on windows, in linux it seems fine)

docker run --rm  -e POSTGRES_USER='scott' -e POSTGRES_PASSWORD='tiger' -e POSTGRES_DB='test' -p 127.0.0.1:5432:5432 -d --name postgres postgres:14
docker exec -ti postgres psql -U scott -c 'CREATE SCHEMA test_schema; CREATE SCHEMA test_schema_2;' test
git clone --depth 1 https://github.com/sqlalchemy/sqlalchemy.git
cd sqlalchemy
pip install -e .
pip install pytest psycopg psycopg-binary
pytest --db psycopg test\dialect\postgresql\test_query.py -k test_three

the above errors with access violation:

platform win32 -- Python 3.10.4, pytest-7.1.2, pluggy-1.0.0 -- C:\Users\me\miniconda3\envs\sa\python.exe
cachedir: .pytest_cache
rootdir: C:\Users\me\Dev\GitHub\sqlalchemy, configfile: pyproject.toml
plugins: anyio-3.6.1, forked-1.4.0, xdist-2.5.0
collected 60 items / 59 deselected / 1 selected

test/dialect/postgresql/test_query.py::ExtractTest_postgresql+psycopg_14_5::test_three Windows fatal exception: access violation

Current thread 0x0000f7b8 (most recent call first):
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\psycopg\cursor.py", line 805 in fetchone
  File "C:\Users\me\Dev\GitHub\sqlalchemy\lib\sqlalchemy\engine\cursor.py", line 959 in fetchone
  File "C:\Users\me\Dev\GitHub\sqlalchemy\lib\sqlalchemy\engine\cursor.py", line 1808 in _fetchone_impl
  File "C:\Users\me\Dev\GitHub\sqlalchemy\lib\sqlalchemy\engine\result.py", line 715 in _only_one_row
  File "C:\Users\me\Dev\GitHub\sqlalchemy\lib\sqlalchemy\engine\result.py", line 1490 in scalar
  File "C:\Users\me\Dev\GitHub\sqlalchemy\test\dialect\postgresql\test_query.py", line 1259 in test_three
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\_pytest\python.py", line 192 in pytest_pyfunc_call
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\pluggy\_callers.py", line 39 in _multicall
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\pluggy\_manager.py", line 80 in _hookexec
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\pluggy\_hooks.py", line 265 in __call__
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\_pytest\python.py", line 1761 in runtest
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\_pytest\runner.py", line 166 in pytest_runtest_call
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\pluggy\_callers.py", line 39 in _multicall
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\pluggy\_manager.py", line 80 in _hookexec
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\pluggy\_hooks.py", line 265 in __call__
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\_pytest\runner.py", line 259 in <lambda>
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\_pytest\runner.py", line 338 in from_call
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\_pytest\runner.py", line 258 in call_runtest_hook
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\_pytest\runner.py", line 219 in call_and_report
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\_pytest\runner.py", line 130 in runtestprotocol
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\_pytest\runner.py", line 111 in pytest_runtest_protocol
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\pluggy\_callers.py", line 39 in _multicall
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\pluggy\_manager.py", line 80 in _hookexec
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\pluggy\_hooks.py", line 265 in __call__
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\_pytest\main.py", line 347 in pytest_runtestloop
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\pluggy\_callers.py", line 39 in _multicall
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\pluggy\_manager.py", line 80 in _hookexec
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\pluggy\_hooks.py", line 265 in __call__
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\_pytest\main.py", line 322 in _main
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\_pytest\main.py", line 268 in wrap_session
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\_pytest\main.py", line 315 in pytest_cmdline_main
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\pluggy\_callers.py", line 39 in _multicall
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\pluggy\_manager.py", line 80 in _hookexec
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\pluggy\_hooks.py", line 265 in __call__
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\_pytest\config\__init__.py", line 164 in main
  File "C:\Users\me\miniconda3\envs\sa\lib\site-packages\_pytest\config\__init__.py", line 187 in console_main
  File "C:\Users\me\miniconda3\envs\sa\Scripts\pytest.exe\__main__.py", line 7 in <module>
  File "C:\Users\me\miniconda3\envs\sa\lib\runpy.py", line 86 in _run_code
  File "C:\Users\me\miniconda3\envs\sa\lib\runpy.py", line 196 in _run_module_as_main

python 3.10.4 on windows 10
psycopg 3.1.1
psycopg-binary 3.1.1
psycopg-pool 3.1.1
psycopg2 2.9.3

[dvarrazzo]

It doesn't seem a bug. The library to parse the timezone raises an exception upon receiving an invalid timezone. What would you have expected to happen?

[CaselIT]

I would expect it to behave consistently on windows and on Linux. The fact that it doesn't seem a bug to me, since the input data is the same

That said I don't know what the proper behaviour would be, but I think the Linux one would be more convenient (other drivers behave like psycopg on linux in this sqlalchemy test)

[dvarrazzo]

I see what you mean. There is actually a test verifying the UTC fallback behaviour:

psycopg/tests/types/test_datetime.py

Lines 384 to 400 in 68bec0b

	@pytest.mark.parametrize(
	"tzname, expr, tzoff",
	[
	("UTC", "2000-1-1", 0),
	("UTC", "2000-7-1", 0),
	("Europe/Rome", "2000-1-1", 3600),
	("Europe/Rome", "2000-7-1", 7200),
	("Europe/Rome", "1000-1-1", 2996),
	pytest.param("NOSUCH0", "2000-1-1", 0, marks=crdb_skip_invalid_tz),
	],
	)
	@pytest.mark.parametrize("fmt_out", pq.Format)
	def test_load_datetimetz_tz(self, conn, fmt_out, tzname, expr, tzoff):
	conn.execute("select set_config('TimeZone', %s, true)", [tzname])
	cur = conn.cursor(binary=fmt_out)
	ts = cur.execute("select %s::timestamptz", [expr]).fetchone()[0]
	assert ts.utcoffset().total_seconds() == tzoff

As far as I can see, in your test an OSError is generated, so the handler for this case doesn't catch it.

psycopg/psycopg/psycopg/_tz.py

Lines 29 to 33 in 68bec0b

	try:
	zi: tzinfo = ZoneInfo(sname)
	except KeyError:
	logger.warning("unknown PostgreSQL timezone: %r; will use UTC", sname)
	zi = timezone.utc

I have pushed a test to verify if I can reproduce your problem, using 0 as timezone:

psycopg/tests/types/test_datetime.py

Line 393 in a7b1da2

pytest.param("0", "2000-1-1", 0, marks=crdb_skip_invalid_tz),

[dvarrazzo]

This test actually reproduces the issue. NOSUCH0 and 0 raise different exceptions in the tzdata package.

Will release a fix.

[CaselIT]

As far as I can see, in your test an OSError is generated, so the handler for this case doesn't catch it.

It seem that the fallback pack tries to look for a file and probably windows does not allow file names with some of these chars <+00>-00 while linux is fine with them.

Will release a fix.

That's great to hear. Let me know if I can help testing the fix

[dvarrazzo]

The invalid access violation probably comes from:

OSError: [Errno 22] Invalid argument: 'C:\\Users\\me\\miniconda3\\envs\\sa\\lib\\site-packages\\tzdata\\zoneinfo\\<+00>-00'
Exception ignored in: 'psycopg_binary._psycopg.Transformer.set_pgresult'

I don't know why exception is ignored: it should bubble up. It probably results in a pointer to NULL instead of a valid Python TimeZone, which will proceed to segfault as soon as it's used.

[CaselIT]

If I remember correctly (I'm on a different pc atm) when running the stand-alone script the python would exit, so the exception was surfaced.
But that ignore is probably what's causing the access violation while running the sqlalchemy suite

[dvarrazzo]

The segfault is reproduced in the test above running psycopg test suite. Will investigate before the next release.

[dvarrazzo]

That's great to hear. Let me know if I can help testing the fix

The issue should be fixed on master, if you want to test it.

Thank you very much!

[CaselIT]

I'll test the python only version later. Thanks for the quick fix!