Psycopg3 fails to resolve 'timezone localtime' on MacOS

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

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!

On May 5, 2022, at 8:54 PM, Jerry Sievers <gsievers19@comcast.net> wrote:

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.

Fresh venv with nothing but psycopg and tzdata. Psycopg raised the
same error and here's what a simple ZoneInfo lookup says...

It just apparently thinks the zone setting should resolve to a file
path in the tzdata directory which makes sense.

I just recently upgraded to Python 3.9.12 but as we're just starting
to dig into Psycopg3, I may very well have not ever queried a
TimestampTZ value till this morning :-)

this is a straight venv which I believe doesn't load site packages by
default.

Anyhow as we get ready for Pg14, it looks like the localtime will have
to be changed on the servers regardless.

import zoneinfo
zoneinfo.ZoneInfo('localtime')

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", line 91, in open_binary
return reader.open_resource(resource)
File "<frozen importlib._bootstrap_external>", line 1055, in open_resource
FileNotFoundError: [Errno 2] No such file or directory: '/private/tmp/.venv/lib/python3.9/site-packages/tzdata/zoneinfo/localtime'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "<stdin>", line 1, 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'

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.