Re: Psycopg3 fails to resolve 'timezone localtime' on MacOS
> On May 5, 2022, at 8:54 PM, Jerry Sievers wrote:
>
>
>
>> On May 5, 2022, at 8:43 PM, Daniele Varrazzo
>> wrote:
>>
>> On Fri, 6 May 2022 at 03:04, Jerry Sievers 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 "", 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 "", line 1, in
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'
>>>
> 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
Re: Psycopg3 fails to resolve 'timezone localtime' on MacOS
> On May 5, 2022, at 8:43 PM, Daniele Varrazzo > wrote: > > On Fri, 6 May 2022 at 03:04, Jerry Sievers 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 > >
Re: Psycopg3 fails to resolve 'timezone localtime' on MacOS
> On May 5, 2022, at 8:29 PM, Tom Lane wrote: > > Jerry Sievers 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 > >
Re: Psycopg3 fails to resolve 'timezone localtime' on MacOS
On Fri, 6 May 2022 at 03:04, Jerry Sievers 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
Re: Psycopg3 fails to resolve 'timezone localtime' on MacOS
Jerry Sievers 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
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
unknown PostgreSQL timezone: 'localtime'; will use UTC
# 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",
line 88, 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",
line 49, 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",
line 40, 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 "", line 1030, in _gcd_import
File "", line 1007, in _find_and_load
File "", line 972, in _find_and_load_unlocked
File "", line 228, in _call_with_frames_removed
File "", line 1030, in _gcd_import
File "", line 1007, in _find_and_load
File "", 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 "", line 17, in
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.varra...@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.varra...@gmail.com
License: GNU Lesser General Public License v3 (LGPLv3)
Location: /usr/local/lib/python3.9/site-packages
Requires:
Required-by:
tmp$
Re: Displaying chat by punished users only to themselves (db fiddle attached)
Thank you, that was it!
Re: Displaying chat by punished users only to themselves (db fiddle attached)
On Thursday, May 5, 2022, Alexander Farber wrote: > Good evening, I still have a problem with my JOIN expression - > when I add more games, then messages from other games are displayed: > > https://dbfiddle.uk/?rdbms=postgres_14= > e2ff211f59090d1eeab879498148f907 > > CREATE OR REPLACE FUNCTION words_get_chat( > in_gid integer, > in_uid integer > ) RETURNS TABLE ( > out_mine integer, > out_game text, > out_msg text > ) AS > $func$ > SELECT > CASE WHEN c.uid = in_uid THEN 1 ELSE 0 END, > 'game #' || c.gid, > c.msg > FROMwords_chat c > JOINwords_games g USING (gid) > JOINwords_users opponent ON (opponent.uid IN (g.player1, > g.player2) AND opponent.uid <> in_uid) > WHERE c.gid = in_gid > -- always show myself my own chat messages > AND c.uid = in_uid > -- otherwise only show messages by not muted opponents > OR NOT opponent.muted > ORDER BY c.created ASC; > > $func$ LANGUAGE sql; > > I have tried making the JOIN words_users opponent even more restrictive > with: > > JOINwords_users opponent ON (opponent.uid IN (g.player1, > g.player2) AND in_uid IN (g.player1, g.player2) AND opponent.uid <> in_uid) > > but still messages from the game #20 are displayed, even though I pass > in_gid = 10 > You want: gid and (uid or muted); what you have is: (gid and uid) or muted; based upon operator precedence. David J.
Re: Displaying chat by punished users only to themselves (db fiddle attached)
Good evening, I still have a problem with my JOIN expression - when I add more games, then messages from other games are displayed: https://dbfiddle.uk/?rdbms=postgres_14=e2ff211f59090d1eeab879498148f907 CREATE OR REPLACE FUNCTION words_get_chat( in_gid integer, in_uid integer ) RETURNS TABLE ( out_mine integer, out_game text, out_msg text ) AS $func$ SELECT CASE WHEN c.uid = in_uid THEN 1 ELSE 0 END, 'game #' || c.gid, c.msg FROMwords_chat c JOINwords_games g USING (gid) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> in_uid) WHERE c.gid = in_gid -- always show myself my own chat messages AND c.uid = in_uid -- otherwise only show messages by not muted opponents OR NOT opponent.muted ORDER BY c.created ASC; $func$ LANGUAGE sql; I have tried making the JOIN words_users opponent even more restrictive with: JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND in_uid IN (g.player1, g.player2) AND opponent.uid <> in_uid) but still messages from the game #20 are displayed, even though I pass in_gid = 10 Best regards Alex
Re: Displaying chat by punished users only to themselves (db fiddle attached)
Good morning, this is a very insightful comment (among many) by you, David - On Wed, May 4, 2022 at 5:40 PM David G. Johnston wrote: > Well, that is basically why I was going on about the oddity of having > social be a part of the main query. Personally I would write it as > "myself.uid = in_uid", but you don't have an in_uid to reference. Decide > how you want to do something equivalent. > > so I will rewrite the stored functions in my game to be like that, to separate auth from functionality - https://dbfiddle.uk/?rdbms=postgres_14=dbdf1a6b82f7232be45e23b8139a8f0e CREATE OR REPLACE FUNCTION words_get_uid( in_social integer, in_sidtext ) RETURNS integer AS $func$ SELECT uid FROM words_social WHERE social = in_social AND sid = in_sid; $func$ LANGUAGE sql IMMUTABLE; CREATE OR REPLACE FUNCTION words_get_chat( in_gid integer, in_uid integer ) RETURNS TABLE ( out_mine integer, out_msg text ) AS $func$ SELECT CASE WHEN c.uid = in_uid THEN 1 ELSE 0 END, c.msg FROMwords_chat c JOINwords_games g USING (gid) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> in_uid) WHERE c.gid = in_gid -- always show myself my own chat messages AND c.uid = in_uid -- otherwise only show messages by not muted opponents OR NOT opponent.muted ORDER BY c.created ASC; $func$ LANGUAGE sql; SELECT words_get_chat(10, words_get_uid(100, 'abc')) AS nice_user; SELECT words_get_chat(10, words_get_uid(200, 'def')) AS muted_user; Thanks Alex
