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,"invalid value 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,"invalid value 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",,0It'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:
/messages/by-id/5DF49366-10D1-42A4-99BF-F9A7DC3AB0F4@mailbox.org
Answer:
/messages/by-id/1273542.1712326418@sss.pgh.pa.us
--
Adrian Klaver
adrian.klaver@aklaver.com
On May 13, 2024, at 10:48, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
Yes:/messages/by-id/5DF49366-10D1-42A4-99BF-F9A7DC3AB0F4@mailbox.org
Answer:
Thanks! Similar, but I don't think it's that. This was a sudden change in a long-running connection that had issued a ton of ` 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,"invalid value 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",,0It'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]/messages/by-id/5DF49366-10D1-42A4-99BF-F9A7DC3AB0F4@mailbox.org (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]: /messages/by-id/5DF49366-10D1-42A4-99BF-F9A7DC3AB0F4@mailbox.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:/messages/by-id/5DF49366-10D1-42A4-99BF-F9A7DC3AB0F4@mailbox.org
Answer:
Thanks! Similar, but I don't think it's that. This was a sudden change in a long-running connection that had issued a ton 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:
Thanks! Similar, but I don't think it's that. This was a sudden change in a long-running connection that had issued a ton 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