From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jakob Teuber <jakob.teuber@tum.de>
cc: pgsql-general@lists.postgresql.org
Subject: Re: Infinite loop for generate_series with timestamp arguments
In-reply-to: <05676a44-9408-4c15-9309-c0f39ecd511a@tum.de>
References: <05676a44-9408-4c15-9309-c0f39ecd511a@tum.de>
Comments: In-reply-to Jakob Teuber <jakob.teuber@tum.de>
	message dated "Mon, 03 Mar 2025 17:05:10 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <4063618.1741029046.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Mon, 03 Mar 2025 14:10:46 -0500
Message-ID: <4063619.1741029046@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/4063619.1741029046%40sss.pgh.pa.us>
Precedence: bulk

Jakob Teuber <jakob.teuber@tum.de> writes:
> I noticed, that the following query will send Postgres into an infinite 
> loop:
> (a)  select generate_series(timestamp '2025-01-30', timestamp 
> '2025-02-01', interval '1 month -29 days');
> One could certainly argue that “go and do an infinite loop” is plainly 
> the intended semantics of this query, but in other cases, Postgres tries 
> to guard against these types of looping generate_series statements:
> (b)  select generate_series(timestamp '2025-03-31', timestamp 
> '2025-04-01', interval '1 month -30 days');
>          → ERROR:  step size cannot equal zero

It does what it can, but there's no chance of being entirely perfect.
The core problem here is to figure out which direction the series
is intended to advance, so as to know whether the termination test
should be "less than" or "greater than" the end timestamp.  The
way generate_series_timestamp does that is to see whether
interval comparison thinks the interval value is less than or
greater than a zero interval --- and if it happens to be exactly
equal, you get the whine about zero step size.  But interval
comparison is far from bright:

 * Interval comparison is based on converting interval values to a linear
 * representation expressed in the units of the time field (microseconds,
 * in the case of integer timestamps) with days assumed to be always 24 hours
 * and months assumed to be always 30 days.

We could perhaps do better by doing the initial addition of the
interval and seeing if that produces a value greater than, less
than, or equal to the start timestamp.  But I'm afraid that
doesn't move the goalposts very far, because as this example
shows, we might get different results in different months.

Another idea is to check, after doing each addition, to make
sure that the timestamp actually advanced in the expected
direction.  But should we error out if not, or just stop?

			regards, tom lane