Subject: Re: UTC is not a time zone?

From: Tom Lane <[email-masked]>
Date: Mon, 13 May 2024 14:17:05 -0400

Christophe Pettus <[email-masked]> writes:
> On May 13, 2024, at 10:48, Adrian Klaver <[email-masked]> 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 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

---

From: Christophe Pettus <[email-masked]>
Date: Mon, 13 May 2024 11:18:12 -0700

> On May 13, 2024, at 11:17, Tom Lane <[email-masked]> wrote:

File descriptor exhaustion?  (Of course, that would mean something somewhere is leaking them, which is another problem.)

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 13 May 2024 11:26:40 -0700

> 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
[email-masked]

---

From: Christophe Pettus <[email-masked]>
Date: Mon, 13 May 2024 11:27:34 -0700

> On May 13, 2024, at 11:26, Adrian Klaver <[email-masked]> wrote:

Indeed.  I'll see about pulling together a test case that forces that.

---

From: Tom Lane <[email-masked]>
Date: Mon, 13 May 2024 14:27:57 -0400

> 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

---

From: Tom Lane <[email-masked]>
Date: Mon, 13 May 2024 14:36:12 -0400

> 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