Thread: UTC is not a time zone?
PostgreSQL 16.3 on MacOS Sonoma. A long-running process (which held a connection open the entire time) failed with: 2024-05-13 09:12:44.719 PDT,"cyan","cyan",35926,"[local]",664214f9.8c56,3,"SELECT",2024-05-13 06:26:17 PDT,3/444460,0,ERROR,22023,"invalidvalue for parameter ""TimeZone"": ""UTC""",,,,,"while setting parameter ""TimeZone"" to""UTC"" parallel worker"," select count(1), count(1) filter (where visited > 0) from framework_seenchoice ",,,"","client backend",,0 It's not (easily) repeatable, and the system was not touched while the process was running (no installing new binaries, etc.). Does this look familiar to anyone?
On 5/13/24 10:37 AM, Christophe Pettus wrote: > PostgreSQL 16.3 on MacOS Sonoma. A long-running process (which held a connection open the entire time) failed with: > > 2024-05-13 09:12:44.719 PDT,"cyan","cyan",35926,"[local]",664214f9.8c56,3,"SELECT",2024-05-13 06:26:17 PDT,3/444460,0,ERROR,22023,"invalidvalue for parameter ""TimeZone"": ""UTC""",,,,,"while setting parameter ""TimeZone"" to""UTC"" > parallel worker"," select count(1), count(1) filter (where visited > 0) from framework_seenchoice ",,,"","client backend",,0 > > It's not (easily) repeatable, and the system was not touched while the process was running (no installing new binaries,etc.). Does this look familiar to anyone? > Yes: https://www.postgresql.org/message-id/5DF49366-10D1-42A4-99BF-F9A7DC3AB0F4%40mailbox.org Answer: https://www.postgresql.org/message-id/1273542.1712326418%40sss.pgh.pa.us -- Adrian Klaver adrian.klaver@aklaver.com
> On May 13, 2024, at 10:48, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > Yes: > > https://www.postgresql.org/message-id/5DF49366-10D1-42A4-99BF-F9A7DC3AB0F4%40mailbox.org > > Answer: > > https://www.postgresql.org/message-id/1273542.1712326418%40sss.pgh.pa.us Thanks! Similar, but I don't think it's that. This was a sudden change in a long-running connection that had issued a tonof ` SET TIMEZONE TO 'UTC'; ` before the failure.
On 2024-05-13 19:37 +0200, Christophe Pettus wrote:
> PostgreSQL 16.3 on MacOS Sonoma. A long-running process (which held a
> connection open the entire time) failed with:
>
> 2024-05-13 09:12:44.719 PDT,"cyan","cyan",35926,"[local]",664214f9.8c56,3,"SELECT",2024-05-13 06:26:17
PDT,3/444460,0,ERROR,22023,"invalidvalue for parameter ""TimeZone"": ""UTC""",,,,,"while setting parameter ""TimeZone""
to""UTC""
> parallel worker"," select count(1), count(1) filter (where visited > 0) from framework_seenchoice ",,,"","client
backend",,0
>
> It's not (easily) repeatable, and the system was not touched while the
> process was running (no installing new binaries, etc.). Does this
> look familiar to anyone?
Could be the same issue as [1] (also on macOS).
Can you trigger that error with:
SET timezone = 'UTC';
And what's the output of:
SELECT * FROM pg_timezone_names ORDER BY name;
[1] https://www.postgresql.org/message-id/5DF49366-10D1-42A4-99BF-F9A7DC3AB0F4%40mailbox.org
--
Erik
> On May 13, 2024, at 10:53, Erik Wienhold <ewie@ewie.name> wrote:
> Can you trigger that error with:
>
> SET timezone = 'UTC';
No, that works correctly:
psql (16.3)
Type "help" for help.
df=> SET timezone = 'UTC';
SET
The error popped up during a long-running connection that had issued that SET many (many!) times before.
> SELECT * FROM pg_timezone_names ORDER BY name;
Attached below (it's long, of course). Most germane, however, is this:
df=> SELECT * FROM pg_timezone_names WHERE name='UTC';
name | abbrev | utc_offset | is_dst
------+--------+------------+--------
UTC | UTC | 00:00:00 | f
(1 row)
--
df=> SELECT * FROM pg_timezone_names ORDER BY name;
name | abbrev | utc_offset | is_dst
----------------------------------+--------+------------+--------
Africa/Abidjan | GMT | 00:00:00 | f
Africa/Accra | GMT | 00:00:00 | f
Africa/Addis_Ababa | EAT | 03:00:00 | f
Africa/Algiers | CET | 01:00:00 | f
Africa/Asmara | EAT | 03:00:00 | f
Africa/Asmera | EAT | 03:00:00 | f
Africa/Bamako | GMT | 00:00:00 | f
Africa/Bangui | WAT | 01:00:00 | f
Africa/Banjul | GMT | 00:00:00 | f
Africa/Bissau | GMT | 00:00:00 | f
Africa/Blantyre | CAT | 02:00:00 | f
Africa/Brazzaville | WAT | 01:00:00 | f
Africa/Bujumbura | CAT | 02:00:00 | f
Africa/Cairo | EEST | 03:00:00 | t
Africa/Casablanca | +01 | 01:00:00 | f
Africa/Ceuta | CEST | 02:00:00 | t
Africa/Conakry | GMT | 00:00:00 | f
Africa/Dakar | GMT | 00:00:00 | f
Africa/Dar_es_Salaam | EAT | 03:00:00 | f
Africa/Djibouti | EAT | 03:00:00 | f
Africa/Douala | WAT | 01:00:00 | f
Africa/El_Aaiun | +01 | 01:00:00 | f
Africa/Freetown | GMT | 00:00:00 | f
Africa/Gaborone | CAT | 02:00:00 | f
Africa/Harare | CAT | 02:00:00 | f
Africa/Johannesburg | SAST | 02:00:00 | f
Africa/Juba | CAT | 02:00:00 | f
Africa/Kampala | EAT | 03:00:00 | f
Africa/Khartoum | CAT | 02:00:00 | f
Africa/Kigali | CAT | 02:00:00 | f
Africa/Kinshasa | WAT | 01:00:00 | f
Africa/Lagos | WAT | 01:00:00 | f
Africa/Libreville | WAT | 01:00:00 | f
Africa/Lome | GMT | 00:00:00 | f
Africa/Luanda | WAT | 01:00:00 | f
Africa/Lubumbashi | CAT | 02:00:00 | f
Africa/Lusaka | CAT | 02:00:00 | f
Africa/Malabo | WAT | 01:00:00 | f
Africa/Maputo | CAT | 02:00:00 | f
Africa/Maseru | SAST | 02:00:00 | f
Africa/Mbabane | SAST | 02:00:00 | f
Africa/Mogadishu | EAT | 03:00:00 | f
Africa/Monrovia | GMT | 00:00:00 | f
Africa/Nairobi | EAT | 03:00:00 | f
Africa/Ndjamena | WAT | 01:00:00 | f
Africa/Niamey | WAT | 01:00:00 | f
Africa/Nouakchott | GMT | 00:00:00 | f
Africa/Ouagadougou | GMT | 00:00:00 | f
Africa/Porto-Novo | WAT | 01:00:00 | f
Africa/Sao_Tome | GMT | 00:00:00 | f
Africa/Timbuktu | GMT | 00:00:00 | f
Africa/Tripoli | EET | 02:00:00 | f
Africa/Tunis | CET | 01:00:00 | f
Africa/Windhoek | CAT | 02:00:00 | f
America/Adak | HDT | -09:00:00 | t
America/Anchorage | AKDT | -08:00:00 | t
America/Anguilla | AST | -04:00:00 | f
America/Antigua | AST | -04:00:00 | f
America/Araguaina | -03 | -03:00:00 | f
America/Argentina/Buenos_Aires | -03 | -03:00:00 | f
America/Argentina/Catamarca | -03 | -03:00:00 | f
America/Argentina/ComodRivadavia | -03 | -03:00:00 | f
America/Argentina/Cordoba | -03 | -03:00:00 | f
America/Argentina/Jujuy | -03 | -03:00:00 | f
America/Argentina/La_Rioja | -03 | -03:00:00 | f
America/Argentina/Mendoza | -03 | -03:00:00 | f
America/Argentina/Rio_Gallegos | -03 | -03:00:00 | f
America/Argentina/Salta | -03 | -03:00:00 | f
America/Argentina/San_Juan | -03 | -03:00:00 | f
America/Argentina/San_Luis | -03 | -03:00:00 | f
America/Argentina/Tucuman | -03 | -03:00:00 | f
America/Argentina/Ushuaia | -03 | -03:00:00 | f
America/Aruba | AST | -04:00:00 | f
America/Asuncion | -04 | -04:00:00 | f
America/Atikokan | EST | -05:00:00 | f
America/Atka | HDT | -09:00:00 | t
America/Bahia | -03 | -03:00:00 | f
America/Bahia_Banderas | CST | -06:00:00 | f
America/Barbados | AST | -04:00:00 | f
America/Belem | -03 | -03:00:00 | f
America/Belize | CST | -06:00:00 | f
America/Blanc-Sablon | AST | -04:00:00 | f
America/Boa_Vista | -04 | -04:00:00 | f
America/Bogota | -05 | -05:00:00 | f
America/Boise | MDT | -06:00:00 | t
America/Buenos_Aires | -03 | -03:00:00 | f
America/Cambridge_Bay | MDT | -06:00:00 | t
America/Campo_Grande | -04 | -04:00:00 | f
America/Cancun | EST | -05:00:00 | f
America/Caracas | -04 | -04:00:00 | f
America/Catamarca | -03 | -03:00:00 | f
America/Cayenne | -03 | -03:00:00 | f
America/Cayman | EST | -05:00:00 | f
America/Chicago | CDT | -05:00:00 | t
America/Chihuahua | CST | -06:00:00 | f
America/Ciudad_Juarez | MDT | -06:00:00 | t
America/Coral_Harbour | EST | -05:00:00 | f
America/Cordoba | -03 | -03:00:00 | f
America/Costa_Rica | CST | -06:00:00 | f
America/Creston | MST | -07:00:00 | f
America/Cuiaba | -04 | -04:00:00 | f
America/Curacao | AST | -04:00:00 | f
America/Danmarkshavn | GMT | 00:00:00 | f
America/Dawson | MST | -07:00:00 | f
America/Dawson_Creek | MST | -07:00:00 | f
America/Denver | MDT | -06:00:00 | t
America/Detroit | EDT | -04:00:00 | t
America/Dominica | AST | -04:00:00 | f
America/Edmonton | MDT | -06:00:00 | t
America/Eirunepe | -05 | -05:00:00 | f
America/El_Salvador | CST | -06:00:00 | f
America/Ensenada | PDT | -07:00:00 | t
America/Fort_Nelson | MST | -07:00:00 | f
America/Fort_Wayne | EDT | -04:00:00 | t
America/Fortaleza | -03 | -03:00:00 | f
America/Glace_Bay | ADT | -03:00:00 | t
America/Godthab | -01 | -01:00:00 | t
America/Goose_Bay | ADT | -03:00:00 | t
America/Grand_Turk | EDT | -04:00:00 | t
America/Grenada | AST | -04:00:00 | f
America/Guadeloupe | AST | -04:00:00 | f
America/Guatemala | CST | -06:00:00 | f
America/Guayaquil | -05 | -05:00:00 | f
America/Guyana | -04 | -04:00:00 | f
America/Halifax | ADT | -03:00:00 | t
America/Havana | CDT | -04:00:00 | t
America/Hermosillo | MST | -07:00:00 | f
America/Indiana/Indianapolis | EDT | -04:00:00 | t
America/Indiana/Knox | CDT | -05:00:00 | t
America/Indiana/Marengo | EDT | -04:00:00 | t
America/Indiana/Petersburg | EDT | -04:00:00 | t
America/Indiana/Tell_City | CDT | -05:00:00 | t
America/Indiana/Vevay | EDT | -04:00:00 | t
America/Indiana/Vincennes | EDT | -04:00:00 | t
America/Indiana/Winamac | EDT | -04:00:00 | t
America/Indianapolis | EDT | -04:00:00 | t
America/Inuvik | MDT | -06:00:00 | t
America/Iqaluit | EDT | -04:00:00 | t
America/Jamaica | EST | -05:00:00 | f
America/Jujuy | -03 | -03:00:00 | f
America/Juneau | AKDT | -08:00:00 | t
America/Kentucky/Louisville | EDT | -04:00:00 | t
America/Kentucky/Monticello | EDT | -04:00:00 | t
America/Knox_IN | CDT | -05:00:00 | t
America/Kralendijk | AST | -04:00:00 | f
America/La_Paz | -04 | -04:00:00 | f
America/Lima | -05 | -05:00:00 | f
America/Los_Angeles | PDT | -07:00:00 | t
America/Louisville | EDT | -04:00:00 | t
America/Lower_Princes | AST | -04:00:00 | f
America/Maceio | -03 | -03:00:00 | f
America/Managua | CST | -06:00:00 | f
America/Manaus | -04 | -04:00:00 | f
America/Marigot | AST | -04:00:00 | f
America/Martinique | AST | -04:00:00 | f
America/Matamoros | CDT | -05:00:00 | t
America/Mazatlan | MST | -07:00:00 | f
America/Mendoza | -03 | -03:00:00 | f
America/Menominee | CDT | -05:00:00 | t
America/Merida | CST | -06:00:00 | f
America/Metlakatla | AKDT | -08:00:00 | t
America/Mexico_City | CST | -06:00:00 | f
America/Miquelon | -02 | -02:00:00 | t
America/Moncton | ADT | -03:00:00 | t
America/Monterrey | CST | -06:00:00 | f
America/Montevideo | -03 | -03:00:00 | f
America/Montreal | EDT | -04:00:00 | t
America/Montserrat | AST | -04:00:00 | f
America/Nassau | EDT | -04:00:00 | t
America/New_York | EDT | -04:00:00 | t
America/Nipigon | EDT | -04:00:00 | t
America/Nome | AKDT | -08:00:00 | t
America/Noronha | -02 | -02:00:00 | f
America/North_Dakota/Beulah | CDT | -05:00:00 | t
America/North_Dakota/Center | CDT | -05:00:00 | t
America/North_Dakota/New_Salem | CDT | -05:00:00 | t
America/Nuuk | -01 | -01:00:00 | t
America/Ojinaga | CDT | -05:00:00 | t
America/Panama | EST | -05:00:00 | f
America/Pangnirtung | EDT | -04:00:00 | t
America/Paramaribo | -03 | -03:00:00 | f
America/Phoenix | MST | -07:00:00 | f
America/Port-au-Prince | EDT | -04:00:00 | t
America/Port_of_Spain | AST | -04:00:00 | f
America/Porto_Acre | -05 | -05:00:00 | f
America/Porto_Velho | -04 | -04:00:00 | f
America/Puerto_Rico | AST | -04:00:00 | f
America/Punta_Arenas | -03 | -03:00:00 | f
America/Rainy_River | CDT | -05:00:00 | t
America/Rankin_Inlet | CDT | -05:00:00 | t
America/Recife | -03 | -03:00:00 | f
America/Regina | CST | -06:00:00 | f
America/Resolute | CDT | -05:00:00 | t
America/Rio_Branco | -05 | -05:00:00 | f
America/Rosario | -03 | -03:00:00 | f
America/Santa_Isabel | PDT | -07:00:00 | t
America/Santarem | -03 | -03:00:00 | f
America/Santiago | -04 | -04:00:00 | f
America/Santo_Domingo | AST | -04:00:00 | f
America/Sao_Paulo | -03 | -03:00:00 | f
America/Scoresbysund | -01 | -01:00:00 | t
America/Shiprock | MDT | -06:00:00 | t
America/Sitka | AKDT | -08:00:00 | t
America/St_Barthelemy | AST | -04:00:00 | f
America/St_Johns | NDT | -02:30:00 | t
America/St_Kitts | AST | -04:00:00 | f
America/St_Lucia | AST | -04:00:00 | f
America/St_Thomas | AST | -04:00:00 | f
America/St_Vincent | AST | -04:00:00 | f
America/Swift_Current | CST | -06:00:00 | f
America/Tegucigalpa | CST | -06:00:00 | f
America/Thule | ADT | -03:00:00 | t
America/Thunder_Bay | EDT | -04:00:00 | t
America/Tijuana | PDT | -07:00:00 | t
America/Toronto | EDT | -04:00:00 | t
America/Tortola | AST | -04:00:00 | f
America/Vancouver | PDT | -07:00:00 | t
America/Virgin | AST | -04:00:00 | f
America/Whitehorse | MST | -07:00:00 | f
America/Winnipeg | CDT | -05:00:00 | t
America/Yakutat | AKDT | -08:00:00 | t
America/Yellowknife | MDT | -06:00:00 | t
Antarctica/Casey | +08 | 08:00:00 | f
Antarctica/Davis | +07 | 07:00:00 | f
Antarctica/DumontDUrville | +10 | 10:00:00 | f
Antarctica/Macquarie | AEST | 10:00:00 | f
Antarctica/Mawson | +05 | 05:00:00 | f
Antarctica/McMurdo | NZST | 12:00:00 | f
Antarctica/Palmer | -03 | -03:00:00 | f
Antarctica/Rothera | -03 | -03:00:00 | f
Antarctica/South_Pole | NZST | 12:00:00 | f
Antarctica/Syowa | +03 | 03:00:00 | f
Antarctica/Troll | +02 | 02:00:00 | t
Antarctica/Vostok | +05 | 05:00:00 | f
Arctic/Longyearbyen | CEST | 02:00:00 | t
Asia/Aden | +03 | 03:00:00 | f
Asia/Almaty | +05 | 05:00:00 | f
Asia/Amman | +03 | 03:00:00 | f
Asia/Anadyr | +12 | 12:00:00 | f
Asia/Aqtau | +05 | 05:00:00 | f
Asia/Aqtobe | +05 | 05:00:00 | f
Asia/Ashgabat | +05 | 05:00:00 | f
Asia/Ashkhabad | +05 | 05:00:00 | f
Asia/Atyrau | +05 | 05:00:00 | f
Asia/Baghdad | +03 | 03:00:00 | f
Asia/Bahrain | +03 | 03:00:00 | f
Asia/Baku | +04 | 04:00:00 | f
Asia/Bangkok | +07 | 07:00:00 | f
Asia/Barnaul | +07 | 07:00:00 | f
Asia/Beirut | EEST | 03:00:00 | t
Asia/Bishkek | +06 | 06:00:00 | f
Asia/Brunei | +08 | 08:00:00 | f
Asia/Calcutta | IST | 05:30:00 | f
Asia/Chita | +09 | 09:00:00 | f
Asia/Choibalsan | +08 | 08:00:00 | f
Asia/Chongqing | CST | 08:00:00 | f
Asia/Chungking | CST | 08:00:00 | f
Asia/Colombo | +0530 | 05:30:00 | f
Asia/Dacca | +06 | 06:00:00 | f
Asia/Damascus | +03 | 03:00:00 | f
Asia/Dhaka | +06 | 06:00:00 | f
Asia/Dili | +09 | 09:00:00 | f
Asia/Dubai | +04 | 04:00:00 | f
Asia/Dushanbe | +05 | 05:00:00 | f
Asia/Famagusta | EEST | 03:00:00 | t
Asia/Gaza | EEST | 03:00:00 | t
Asia/Harbin | CST | 08:00:00 | f
Asia/Hebron | EEST | 03:00:00 | t
Asia/Ho_Chi_Minh | +07 | 07:00:00 | f
Asia/Hong_Kong | HKT | 08:00:00 | f
Asia/Hovd | +07 | 07:00:00 | f
Asia/Irkutsk | +08 | 08:00:00 | f
Asia/Istanbul | +03 | 03:00:00 | f
Asia/Jakarta | WIB | 07:00:00 | f
Asia/Jayapura | WIT | 09:00:00 | f
Asia/Jerusalem | IDT | 03:00:00 | t
Asia/Kabul | +0430 | 04:30:00 | f
Asia/Kamchatka | +12 | 12:00:00 | f
Asia/Karachi | PKT | 05:00:00 | f
Asia/Kashgar | +06 | 06:00:00 | f
Asia/Kathmandu | +0545 | 05:45:00 | f
Asia/Katmandu | +0545 | 05:45:00 | f
Asia/Khandyga | +09 | 09:00:00 | f
Asia/Kolkata | IST | 05:30:00 | f
Asia/Krasnoyarsk | +07 | 07:00:00 | f
Asia/Kuala_Lumpur | +08 | 08:00:00 | f
Asia/Kuching | +08 | 08:00:00 | f
Asia/Kuwait | +03 | 03:00:00 | f
Asia/Macao | CST | 08:00:00 | f
Asia/Macau | CST | 08:00:00 | f
Asia/Magadan | +11 | 11:00:00 | f
Asia/Makassar | WITA | 08:00:00 | f
Asia/Manila | PST | 08:00:00 | f
Asia/Muscat | +04 | 04:00:00 | f
Asia/Nicosia | EEST | 03:00:00 | t
Asia/Novokuznetsk | +07 | 07:00:00 | f
Asia/Novosibirsk | +07 | 07:00:00 | f
Asia/Omsk | +06 | 06:00:00 | f
Asia/Oral | +05 | 05:00:00 | f
Asia/Phnom_Penh | +07 | 07:00:00 | f
Asia/Pontianak | WIB | 07:00:00 | f
Asia/Pyongyang | KST | 09:00:00 | f
Asia/Qatar | +03 | 03:00:00 | f
Asia/Qostanay | +05 | 05:00:00 | f
Asia/Qyzylorda | +05 | 05:00:00 | f
Asia/Rangoon | +0630 | 06:30:00 | f
Asia/Riyadh | +03 | 03:00:00 | f
Asia/Saigon | +07 | 07:00:00 | f
Asia/Sakhalin | +11 | 11:00:00 | f
Asia/Samarkand | +05 | 05:00:00 | f
Asia/Seoul | KST | 09:00:00 | f
Asia/Shanghai | CST | 08:00:00 | f
Asia/Singapore | +08 | 08:00:00 | f
Asia/Srednekolymsk | +11 | 11:00:00 | f
Asia/Taipei | CST | 08:00:00 | f
Asia/Tashkent | +05 | 05:00:00 | f
Asia/Tbilisi | +04 | 04:00:00 | f
Asia/Tehran | +0330 | 03:30:00 | f
Asia/Tel_Aviv | IDT | 03:00:00 | t
Asia/Thimbu | +06 | 06:00:00 | f
Asia/Thimphu | +06 | 06:00:00 | f
Asia/Tokyo | JST | 09:00:00 | f
Asia/Tomsk | +07 | 07:00:00 | f
Asia/Ujung_Pandang | WITA | 08:00:00 | f
Asia/Ulaanbaatar | +08 | 08:00:00 | f
Asia/Ulan_Bator | +08 | 08:00:00 | f
Asia/Urumqi | +06 | 06:00:00 | f
Asia/Ust-Nera | +10 | 10:00:00 | f
Asia/Vientiane | +07 | 07:00:00 | f
Asia/Vladivostok | +10 | 10:00:00 | f
Asia/Yakutsk | +09 | 09:00:00 | f
Asia/Yangon | +0630 | 06:30:00 | f
Asia/Yekaterinburg | +05 | 05:00:00 | f
Asia/Yerevan | +04 | 04:00:00 | f
Atlantic/Azores | +00 | 00:00:00 | t
Atlantic/Bermuda | ADT | -03:00:00 | t
Atlantic/Canary | WEST | 01:00:00 | t
Atlantic/Cape_Verde | -01 | -01:00:00 | f
Atlantic/Faeroe | WEST | 01:00:00 | t
Atlantic/Faroe | WEST | 01:00:00 | t
Atlantic/Jan_Mayen | CEST | 02:00:00 | t
Atlantic/Madeira | WEST | 01:00:00 | t
Atlantic/Reykjavik | GMT | 00:00:00 | f
Atlantic/South_Georgia | -02 | -02:00:00 | f
Atlantic/St_Helena | GMT | 00:00:00 | f
Atlantic/Stanley | -03 | -03:00:00 | f
Australia/ACT | AEST | 10:00:00 | f
Australia/Adelaide | ACST | 09:30:00 | f
Australia/Brisbane | AEST | 10:00:00 | f
Australia/Broken_Hill | ACST | 09:30:00 | f
Australia/Canberra | AEST | 10:00:00 | f
Australia/Currie | AEST | 10:00:00 | f
Australia/Darwin | ACST | 09:30:00 | f
Australia/Eucla | +0845 | 08:45:00 | f
Australia/Hobart | AEST | 10:00:00 | f
Australia/LHI | +1030 | 10:30:00 | f
Australia/Lindeman | AEST | 10:00:00 | f
Australia/Lord_Howe | +1030 | 10:30:00 | f
Australia/Melbourne | AEST | 10:00:00 | f
Australia/NSW | AEST | 10:00:00 | f
Australia/North | ACST | 09:30:00 | f
Australia/Perth | AWST | 08:00:00 | f
Australia/Queensland | AEST | 10:00:00 | f
Australia/South | ACST | 09:30:00 | f
Australia/Sydney | AEST | 10:00:00 | f
Australia/Tasmania | AEST | 10:00:00 | f
Australia/Victoria | AEST | 10:00:00 | f
Australia/West | AWST | 08:00:00 | f
Australia/Yancowinna | ACST | 09:30:00 | f
Brazil/Acre | -05 | -05:00:00 | f
Brazil/DeNoronha | -02 | -02:00:00 | f
Brazil/East | -03 | -03:00:00 | f
Brazil/West | -04 | -04:00:00 | f
CET | CEST | 02:00:00 | t
CST6CDT | CDT | -05:00:00 | t
Canada/Atlantic | ADT | -03:00:00 | t
Canada/Central | CDT | -05:00:00 | t
Canada/Eastern | EDT | -04:00:00 | t
Canada/Mountain | MDT | -06:00:00 | t
Canada/Newfoundland | NDT | -02:30:00 | t
Canada/Pacific | PDT | -07:00:00 | t
Canada/Saskatchewan | CST | -06:00:00 | f
Canada/Yukon | MST | -07:00:00 | f
Chile/Continental | -04 | -04:00:00 | f
Chile/EasterIsland | -06 | -06:00:00 | f
Cuba | CDT | -04:00:00 | t
EET | EEST | 03:00:00 | t
EST | EST | -05:00:00 | f
EST5EDT | EDT | -04:00:00 | t
Egypt | EEST | 03:00:00 | t
Eire | IST | 01:00:00 | f
Etc/GMT | GMT | 00:00:00 | f
Etc/GMT+0 | GMT | 00:00:00 | f
Etc/GMT+1 | -01 | -01:00:00 | f
Etc/GMT+10 | -10 | -10:00:00 | f
Etc/GMT+11 | -11 | -11:00:00 | f
Etc/GMT+12 | -12 | -12:00:00 | f
Etc/GMT+2 | -02 | -02:00:00 | f
Etc/GMT+3 | -03 | -03:00:00 | f
Etc/GMT+4 | -04 | -04:00:00 | f
Etc/GMT+5 | -05 | -05:00:00 | f
Etc/GMT+6 | -06 | -06:00:00 | f
Etc/GMT+7 | -07 | -07:00:00 | f
Etc/GMT+8 | -08 | -08:00:00 | f
Etc/GMT+9 | -09 | -09:00:00 | f
Etc/GMT-0 | GMT | 00:00:00 | f
Etc/GMT-1 | +01 | 01:00:00 | f
Etc/GMT-10 | +10 | 10:00:00 | f
Etc/GMT-11 | +11 | 11:00:00 | f
Etc/GMT-12 | +12 | 12:00:00 | f
Etc/GMT-13 | +13 | 13:00:00 | f
Etc/GMT-14 | +14 | 14:00:00 | f
Etc/GMT-2 | +02 | 02:00:00 | f
Etc/GMT-3 | +03 | 03:00:00 | f
Etc/GMT-4 | +04 | 04:00:00 | f
Etc/GMT-5 | +05 | 05:00:00 | f
Etc/GMT-6 | +06 | 06:00:00 | f
Etc/GMT-7 | +07 | 07:00:00 | f
Etc/GMT-8 | +08 | 08:00:00 | f
Etc/GMT-9 | +09 | 09:00:00 | f
Etc/GMT0 | GMT | 00:00:00 | f
Etc/Greenwich | GMT | 00:00:00 | f
Etc/UCT | UTC | 00:00:00 | f
Etc/UTC | UTC | 00:00:00 | f
Etc/Universal | UTC | 00:00:00 | f
Etc/Zulu | UTC | 00:00:00 | f
Europe/Amsterdam | CEST | 02:00:00 | t
Europe/Andorra | CEST | 02:00:00 | t
Europe/Astrakhan | +04 | 04:00:00 | f
Europe/Athens | EEST | 03:00:00 | t
Europe/Belfast | BST | 01:00:00 | t
Europe/Belgrade | CEST | 02:00:00 | t
Europe/Berlin | CEST | 02:00:00 | t
Europe/Bratislava | CEST | 02:00:00 | t
Europe/Brussels | CEST | 02:00:00 | t
Europe/Bucharest | EEST | 03:00:00 | t
Europe/Budapest | CEST | 02:00:00 | t
Europe/Busingen | CEST | 02:00:00 | t
Europe/Chisinau | EEST | 03:00:00 | t
Europe/Copenhagen | CEST | 02:00:00 | t
Europe/Dublin | IST | 01:00:00 | f
Europe/Gibraltar | CEST | 02:00:00 | t
Europe/Guernsey | BST | 01:00:00 | t
Europe/Helsinki | EEST | 03:00:00 | t
Europe/Isle_of_Man | BST | 01:00:00 | t
Europe/Istanbul | +03 | 03:00:00 | f
Europe/Jersey | BST | 01:00:00 | t
Europe/Kaliningrad | EET | 02:00:00 | f
Europe/Kiev | EEST | 03:00:00 | t
Europe/Kirov | MSK | 03:00:00 | f
Europe/Kyiv | EEST | 03:00:00 | t
Europe/Lisbon | WEST | 01:00:00 | t
Europe/Ljubljana | CEST | 02:00:00 | t
Europe/London | BST | 01:00:00 | t
Europe/Luxembourg | CEST | 02:00:00 | t
Europe/Madrid | CEST | 02:00:00 | t
Europe/Malta | CEST | 02:00:00 | t
Europe/Mariehamn | EEST | 03:00:00 | t
Europe/Minsk | +03 | 03:00:00 | f
Europe/Monaco | CEST | 02:00:00 | t
Europe/Moscow | MSK | 03:00:00 | f
Europe/Nicosia | EEST | 03:00:00 | t
Europe/Oslo | CEST | 02:00:00 | t
Europe/Paris | CEST | 02:00:00 | t
Europe/Podgorica | CEST | 02:00:00 | t
Europe/Prague | CEST | 02:00:00 | t
Europe/Riga | EEST | 03:00:00 | t
Europe/Rome | CEST | 02:00:00 | t
Europe/Samara | +04 | 04:00:00 | f
Europe/San_Marino | CEST | 02:00:00 | t
Europe/Sarajevo | CEST | 02:00:00 | t
Europe/Saratov | +04 | 04:00:00 | f
Europe/Simferopol | MSK | 03:00:00 | f
Europe/Skopje | CEST | 02:00:00 | t
Europe/Sofia | EEST | 03:00:00 | t
Europe/Stockholm | CEST | 02:00:00 | t
Europe/Tallinn | EEST | 03:00:00 | t
Europe/Tirane | CEST | 02:00:00 | t
Europe/Tiraspol | EEST | 03:00:00 | t
Europe/Ulyanovsk | +04 | 04:00:00 | f
Europe/Uzhgorod | EEST | 03:00:00 | t
Europe/Vaduz | CEST | 02:00:00 | t
Europe/Vatican | CEST | 02:00:00 | t
Europe/Vienna | CEST | 02:00:00 | t
Europe/Vilnius | EEST | 03:00:00 | t
Europe/Volgograd | MSK | 03:00:00 | f
Europe/Warsaw | CEST | 02:00:00 | t
Europe/Zagreb | CEST | 02:00:00 | t
Europe/Zaporozhye | EEST | 03:00:00 | t
Europe/Zurich | CEST | 02:00:00 | t
Factory | -00 | 00:00:00 | f
GB | BST | 01:00:00 | t
GB-Eire | BST | 01:00:00 | t
GMT | GMT | 00:00:00 | f
GMT+0 | GMT | 00:00:00 | f
GMT-0 | GMT | 00:00:00 | f
GMT0 | GMT | 00:00:00 | f
Greenwich | GMT | 00:00:00 | f
HST | HST | -10:00:00 | f
Hongkong | HKT | 08:00:00 | f
Iceland | GMT | 00:00:00 | f
Indian/Antananarivo | EAT | 03:00:00 | f
Indian/Chagos | +06 | 06:00:00 | f
Indian/Christmas | +07 | 07:00:00 | f
Indian/Cocos | +0630 | 06:30:00 | f
Indian/Comoro | EAT | 03:00:00 | f
Indian/Kerguelen | +05 | 05:00:00 | f
Indian/Mahe | +04 | 04:00:00 | f
Indian/Maldives | +05 | 05:00:00 | f
Indian/Mauritius | +04 | 04:00:00 | f
Indian/Mayotte | EAT | 03:00:00 | f
Indian/Reunion | +04 | 04:00:00 | f
Iran | +0330 | 03:30:00 | f
Israel | IDT | 03:00:00 | t
Jamaica | EST | -05:00:00 | f
Japan | JST | 09:00:00 | f
Kwajalein | +12 | 12:00:00 | f
Libya | EET | 02:00:00 | f
MET | MEST | 02:00:00 | t
MST | MST | -07:00:00 | f
MST7MDT | MDT | -06:00:00 | t
Mexico/BajaNorte | PDT | -07:00:00 | t
Mexico/BajaSur | MST | -07:00:00 | f
Mexico/General | CST | -06:00:00 | f
NZ | NZST | 12:00:00 | f
NZ-CHAT | +1245 | 12:45:00 | f
Navajo | MDT | -06:00:00 | t
PRC | CST | 08:00:00 | f
PST8PDT | PDT | -07:00:00 | t
Pacific/Apia | +13 | 13:00:00 | f
Pacific/Auckland | NZST | 12:00:00 | f
Pacific/Bougainville | +11 | 11:00:00 | f
Pacific/Chatham | +1245 | 12:45:00 | f
Pacific/Chuuk | +10 | 10:00:00 | f
Pacific/Easter | -06 | -06:00:00 | f
Pacific/Efate | +11 | 11:00:00 | f
Pacific/Enderbury | +13 | 13:00:00 | f
Pacific/Fakaofo | +13 | 13:00:00 | f
Pacific/Fiji | +12 | 12:00:00 | f
Pacific/Funafuti | +12 | 12:00:00 | f
Pacific/Galapagos | -06 | -06:00:00 | f
Pacific/Gambier | -09 | -09:00:00 | f
Pacific/Guadalcanal | +11 | 11:00:00 | f
Pacific/Guam | ChST | 10:00:00 | f
Pacific/Honolulu | HST | -10:00:00 | f
Pacific/Johnston | HST | -10:00:00 | f
Pacific/Kanton | +13 | 13:00:00 | f
Pacific/Kiritimati | +14 | 14:00:00 | f
Pacific/Kosrae | +11 | 11:00:00 | f
Pacific/Kwajalein | +12 | 12:00:00 | f
Pacific/Majuro | +12 | 12:00:00 | f
Pacific/Marquesas | -0930 | -09:30:00 | f
Pacific/Midway | SST | -11:00:00 | f
Pacific/Nauru | +12 | 12:00:00 | f
Pacific/Niue | -11 | -11:00:00 | f
Pacific/Norfolk | +11 | 11:00:00 | f
Pacific/Noumea | +11 | 11:00:00 | f
Pacific/Pago_Pago | SST | -11:00:00 | f
Pacific/Palau | +09 | 09:00:00 | f
Pacific/Pitcairn | -08 | -08:00:00 | f
Pacific/Pohnpei | +11 | 11:00:00 | f
Pacific/Ponape | +11 | 11:00:00 | f
Pacific/Port_Moresby | +10 | 10:00:00 | f
Pacific/Rarotonga | -10 | -10:00:00 | f
Pacific/Saipan | ChST | 10:00:00 | f
Pacific/Samoa | SST | -11:00:00 | f
Pacific/Tahiti | -10 | -10:00:00 | f
Pacific/Tarawa | +12 | 12:00:00 | f
Pacific/Tongatapu | +13 | 13:00:00 | f
Pacific/Truk | +10 | 10:00:00 | f
Pacific/Wake | +12 | 12:00:00 | f
Pacific/Wallis | +12 | 12:00:00 | f
Pacific/Yap | +10 | 10:00:00 | f
Poland | CEST | 02:00:00 | t
Portugal | WEST | 01:00:00 | t
ROC | CST | 08:00:00 | f
ROK | KST | 09:00:00 | f
Singapore | +08 | 08:00:00 | f
Turkey | +03 | 03:00:00 | f
UCT | UTC | 00:00:00 | f
US/Alaska | AKDT | -08:00:00 | t
US/Aleutian | HDT | -09:00:00 | t
US/Arizona | MST | -07:00:00 | f
US/Central | CDT | -05:00:00 | t
US/East-Indiana | EDT | -04:00:00 | t
US/Eastern | EDT | -04:00:00 | t
US/Hawaii | HST | -10:00:00 | f
US/Indiana-Starke | CDT | -05:00:00 | t
US/Michigan | EDT | -04:00:00 | t
US/Mountain | MDT | -06:00:00 | t
US/Pacific | PDT | -07:00:00 | t
US/Samoa | SST | -11:00:00 | f
UTC | UTC | 00:00:00 | f
Universal | UTC | 00:00:00 | f
W-SU | MSK | 03:00:00 | f
WET | WEST | 01:00:00 | t
Zulu | UTC | 00:00:00 | f
(597 rows)
On 5/13/24 10:50 AM, Christophe Pettus wrote: > > >> On May 13, 2024, at 10:48, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> Yes: >> >> https://www.postgresql.org/message-id/5DF49366-10D1-42A4-99BF-F9A7DC3AB0F4%40mailbox.org >> >> Answer: >> >> https://www.postgresql.org/message-id/1273542.1712326418%40sss.pgh.pa.us > > Thanks! Similar, but I don't think it's that. This was a sudden change in a long-running connection that had issued aton of ` SET TIMEZONE TO 'UTC'; ` before the failure. You sure the timezone file did not get changed under the connection? Why is SET TIMEZONE TO 'UTC being constantly called? -- Adrian Klaver adrian.klaver@aklaver.com
> On May 13, 2024, at 10:58, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > You sure the timezone file did not get changed under the connection? Yes (at least, nothing happened on the system that would indicate that). The system wasn't touched during the execution(and, as noted, it worked after as well as before). > Why is SET TIMEZONE TO 'UTC being constantly called? ORM noise.
On 5/13/24 11:02 AM, Christophe Pettus wrote: > > >> On May 13, 2024, at 10:58, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> >> You sure the timezone file did not get changed under the connection? > > Yes (at least, nothing happened on the system that would indicate that). The system wasn't touched during the execution(and, as noted, it worked after as well as before). Hmm. What does pg_config --configure show for '--with-system-tzdata' ? > >> Why is SET TIMEZONE TO 'UTC being constantly called? > > ORM noise. -- Adrian Klaver adrian.klaver@aklaver.com
> On May 13, 2024, at 11:07, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > > What does pg_config --configure show for '--with-system-tzdata' ? It's a local compile, and was built without that. As an experiment, I'm just pounding the server with a single connection doing nothing but SET TIMEZONEs repeatedly. So far,no break, but it is *very* intermittent.
Christophe Pettus <xof@thebuild.com> writes: > On May 13, 2024, at 10:48, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> https://www.postgresql.org/message-id/1273542.1712326418%40sss.pgh.pa.us > Thanks! Similar, but I don't think it's that. This was a sudden change in a long-running connection that had issued aton of ` SET TIMEZONE TO 'UTC'; ` before the failure. The underlying cause is likely roughly similar, to wit failure to read /usr/share/zoneinfo/UTC (or the Postgres-private equivalent file). The parent process would long since have cached the zone data in its memory, but this error is in a parallel worker process, which'd have to read the file for itself during startup. What's causing that I can't say. It doesn't look like we log the errno anywhere when failing to read a zone file :-( regards, tom lane
> On May 13, 2024, at 11:17, Tom Lane <tgl@sss.pgh.pa.us> wrote: > What's causing that I can't say. It doesn't look like we log the > errno anywhere when failing to read a zone file :-( File descriptor exhaustion? (Of course, that would mean something somewhere is leaking them, which is another problem.)
On 5/13/24 11:18 AM, Christophe Pettus wrote: > > >> On May 13, 2024, at 11:17, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> What's causing that I can't say. It doesn't look like we log the >> errno anywhere when failing to read a zone file :-( > > File descriptor exhaustion? (Of course, that would mean something somewhere is leaking them, which is another problem.) If I am understanding this correctly: "The parent process would long since have cached the zone data in its memory, but this error is in a parallel worker process, which'd have to read the file for itself during startup. " Then this: "As an experiment, I'm just pounding the server with a single connection doing nothing but SET TIMEZONEs repeatedly. So far, no break, but it is *very* intermittent." May not induce the error unless there are parallel workers involved. -- Adrian Klaver adrian.klaver@aklaver.com
> On May 13, 2024, at 11:26, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > May not induce the error unless there are parallel workers involved. Indeed. I'll see about pulling together a test case that forces that.
Christophe Pettus <xof@thebuild.com> writes:
> On May 13, 2024, at 11:17, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> What's causing that I can't say. It doesn't look like we log the
>> errno anywhere when failing to read a zone file :-(
> File descriptor exhaustion? (Of course, that would mean something somewhere is leaking them, which is another
problem.)
Since this is in a pretty-new process, it would have to be global FD
exhaustion, that is ENFILE not EMFILE. (Assuming macOS even has
that concept; its BSD roots sure do, but who knows what Apple's
done to it over the years.) You'd likely have seen more side-effects
of such a condition.
I was wondering more about permissions checks gone wrong, comparable
to the antivirus-induced misbehaviors we hear about on Windows.
Not that that's a comforting answer.
regards, tom lane
Christophe Pettus <xof@thebuild.com> writes:
>> On May 13, 2024, at 11:26, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>> May not induce the error unless there are parallel workers involved.
> Indeed. I'll see about pulling together a test case that forces that.
Right. Once a backend process has loaded a zone file, it caches that
in a hash table that it will never flush (which is arguably a bug for
other reasons, since those files aren't really immutable, but that's
how it behaves today). So you've got 0 chance of hitting this via
repeat SET TIMEZONE in a single backend.
regards, tom lane