Thread: Psycopg3 fails to resolve 'timezone localtime' on MacOS

Has anyone run into This?

Psycopg3 fails to resolve timezone localtime on MacOS (Catalina).

It falls back to UTC regardless of whether running with/without the
tzdata package which I did try.

There is a /etc/localtime symlink on this box pointed at the correct
file in the tz data directory, which apparently doesn't get used.

Adding a temporary symlink directly into the tz data directory got
things working but I'm skeptical of such a hack.

Also tested w/PSYCOPG_IMPL=python.  Same results.

Psycopg2 had no issues.

Please advise.

Thx

----

tmp$ cat z
import psycopg, psycopg2, zoneinfo

for module in psycopg, psycopg2:
    try:
        print(module)
        conn = module.connect()
        cur = conn.cursor()
        cur.execute('select now()')
        cur.fetchone
    except Exception as e:
        print(e)
    print()

zoneinfo.ZoneInfo('localtime')

tmp$ python --version
Python 3.9.12

tmp$ python <z
<module 'psycopg' from '/usr/local/lib/python3.9/site-packages/psycopg/__init__.py'>
unknown PostgreSQL timezone: 'localtime'; will use UTC

<module 'psycopg2' from '/usr/local/lib/python3.9/site-packages/psycopg2/__init__.py'>
    # Ok here

From ZoneInfo...

Traceback (most recent call last):
  File
"/usr/local/Cellar/python@3.9/3.9.12/Frameworks/Python.framework/Versions/3.9/lib/python3.9/zoneinfo/_common.py",line
12,in load_tzdata 
    return importlib.resources.open_binary(package_name, resource_name)
  File
"/usr/local/Cellar/python@3.9/3.9.12/Frameworks/Python.framework/Versions/3.9/lib/python3.9/importlib/resources.py",
line88, in open_binary 
    package = _get_package(package)
  File
"/usr/local/Cellar/python@3.9/3.9.12/Frameworks/Python.framework/Versions/3.9/lib/python3.9/importlib/resources.py",
line49, in _get_package 
    module = _resolve(package)
  File
"/usr/local/Cellar/python@3.9/3.9.12/Frameworks/Python.framework/Versions/3.9/lib/python3.9/importlib/resources.py",
line40, in _resolve 
    return import_module(name)
  File
"/usr/local/Cellar/python@3.9/3.9.12/Frameworks/Python.framework/Versions/3.9/lib/python3.9/importlib/__init__.py",line
127,in import_module 
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 1030, in _gcd_import
  File "<frozen importlib._bootstrap>", line 1007, in _find_and_load
  File "<frozen importlib._bootstrap>", line 972, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
  File "<frozen importlib._bootstrap>", line 1030, in _gcd_import
  File "<frozen importlib._bootstrap>", line 1007, in _find_and_load
  File "<frozen importlib._bootstrap>", line 984, in _find_and_load_unlocked
ModuleNotFoundError: No module named 'tzdata'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<stdin>", line 17, in <module>
  File
"/usr/local/Cellar/python@3.9/3.9.12/Frameworks/Python.framework/Versions/3.9/lib/python3.9/zoneinfo/_common.py",line
24,in load_tzdata 
    raise ZoneInfoNotFoundError(f"No time zone found with key {key}")
zoneinfo._common.ZoneInfoNotFoundError: 'No time zone found with key localtime'
ERROR
tmp$ pip show psycopg psycopg_c
Name: psycopg
Version: 3.0.12
Summary: PostgreSQL database adapter for Python
Home-page: https://psycopg.org/psycopg3/
Author: Daniele Varrazzo
Author-email: daniele.varrazzo@gmail.com
License: GNU Lesser General Public License v3 (LGPLv3)
Location: /usr/local/lib/python3.9/site-packages
Requires:
Required-by:
---
Name: psycopg-c
Version: 3.0.12
Summary: PostgreSQL database adapter for Python -- C optimisation distribution
Home-page: https://psycopg.org/psycopg3/
Author: Daniele Varrazzo
Author-email: daniele.varrazzo@gmail.com
License: GNU Lesser General Public License v3 (LGPLv3)
Location: /usr/local/lib/python3.9/site-packages
Requires:
Required-by:
tmp$

Jerry Sievers <gsievers19@comcast.net> writes:
> Psycopg3 fails to resolve timezone localtime on MacOS (Catalina).

I doubt Postgres will accept that zone name anywhere.  It's
not a standard name as far as the tzdb data set is concerned.

(Having said that, it's far from clear to me whether this code
fragment is trying to set the PG server's timezone setting or
not.  Your issue may be purely a Python one.)

            regards, tom lane

On Fri, 6 May 2022 at 03:04, Jerry Sievers <gsievers19@comcast.net> wrote:
>
> Has anyone run into This?
>
> Psycopg3 fails to resolve timezone localtime on MacOS (Catalina).

That warning is typical on Windows, which doesn't have a system
timezone database, and on minimal Alpine installations, where the apk
package 'tzdata' must be installed. Never seen it reported on macOS
before.

The workaround on Windows is to install the python package tzdata
(https://pypi.org/project/tzdata/), which is a windows dependency of
psycopg.

Maybe you have a broken python package called tzdata? Your Python
installation seems mildly broken.

'localtime' is a weird timezone. However it does work on Linux for me.


-- Daniele

> On May 5, 2022, at 8:29 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> Jerry Sievers <gsievers19@comcast.net> writes:
>> Psycopg3 fails to resolve timezone localtime on MacOS (Catalina).
> 
> I doubt Postgres will accept that zone name anywhere.  It's
> not a standard name as far as the tzdb data set is concerned.
> 
> (Having said that, it's far from clear to me whether this code
> fragment is trying to set the PG server's timezone setting or
> not.  Your issue may be purely a Python one.)
> 
> Yep, understood.

Well the setting seems to be permitted for versions < 14.

Er, and we've got hundreds of them config'd as such presently.

More work to do.

I'm still curious though why it stopped working for Psycopg3.

Thx
>             regards, tom lane
> 
> 

> On May 5, 2022, at 8:43 PM, Daniele Varrazzo <daniele.varrazzo@gmail.com> wrote:
>
> On Fri, 6 May 2022 at 03:04, Jerry Sievers <gsievers19@comcast.net> wrote:
>>
>> Has anyone run into This?
>>
>> Psycopg3 fails to resolve timezone localtime on MacOS (Catalina).
>
> That warning is typical on Windows, which doesn't have a system
> timezone database, and on minimal Alpine installations, where the apk
> package 'tzdata' must be installed. Never seen it reported on macOS
> before.
>
> The workaround on Windows is to install the python package tzdata
> (https://pypi.org/project/tzdata/), which is a windows dependency of
> psycopg.
>
> Maybe you have a broken python package called tzdata? Your Python
> installation seems mildly broken.
>
Hmmm,dunno everything else is, and has worked perfectly.

I'll try a clean venv  to include tzdata and see if that helps anything.

Already did try installing tzdata and got a backtrace showoing that it was being used.  Still literally says something
like"FileNotFoundError, $tzdir/localtime". 

As for Psycopg3 in general... Very, very nice work.

Cheers!

> 'localtime' is a weird timezone. However it does work on Linux for me.
>
>
> -- Daniele
>
>

Jerry:

On Fri, 6 May 2022 at 03:04, Jerry Sievers <gsievers19@comcast.net> wrote:
> Has anyone run into This?
> Psycopg3 fails to resolve timezone localtime on MacOS (Catalina).
>
> It falls back to UTC regardless of whether running with/without the
> tzdata package which I did try.
>
> There is a /etc/localtime symlink on this box pointed at the correct
> file in the tz data directory, which apparently doesn't get used.
>
> Adding a temporary symlink directly into the tz data directory got
> things working but I'm skeptical of such a hack.

I'm not using Mac, but Linux, and not python, but I think you have
some concepts mixed.

In Linux, and it seems Mac is pretty similar, to define a timezone you
put something readable in tzfile(5) format  ( i.e. a file or a symlink
to a file as you did ) in /usr/share/zoneinfo ( this is the "tzdata
directory" ).

Then, to set the system default timezone you copy ( or link ) a tzfile
in /etc/localtime. Note this DOES NOT define a named timezone that you
can use. And there is NOT a timezone named "localtime".

Normaly you use a symbolic link from etc/localtime to the timezone
directory, which has the nice side effect of letting you read the link
to know the name, but it does not have to be. I think this is leading
you into thinking "localtime" is a valid name, which is not, or was
not until you defined it but creating the name by adding the temporary
symlink.

If you want to have a zone named "localtime" pointing to the default
zone, adding a symlink in the tzdata dir named "localtime" ( directory
entries is what define the names ) pointint to /etc/localtime will do
it, but I would not recommend it.

Your errors are pretty extrange. Are you exporting TZ or whichever env
var MacOs uses or a similar thing set to "localtime" in any of the
relevant environments ( i.e., the server or client process ), or
setting the server timezone to "localtime"?

Because normally when you want "localtime" what you want is the
process system default timezone, so you do not set anything in the
process ($TZ) included, and let the library fallback to the system
default timezone ( unnamed ). This seems like some configuration
problem.

Framcisco Olarte.