Thread: Revisiting extract(epoch from timestamp)
A long time ago, we had this bug report: http://archives.postgresql.org/pgsql-bugs/2003-02/msg00069.php in consequence of which, I changed timestamp_part() so that it would rotate a timestamp-without-timezone from the local timezone to GMT before extracting the epoch offset (commit 191ef2b407f065544ceed5700e42400857d9270f). Recent discussion makes it seem like this was a bad idea: http://archives.postgresql.org/pgsql-general/2012-01/msg00649.php The big problem is that timestamp_part() is marked as immutable, which is a correct statement for every other field type that it can extract, but wrong for epoch if that depends on the setting of the timezone GUC. So if we leave this behavior alone, we're going to have to downgrade timestamp_part() to stable, which is quite likely to break applications using it in index expressions. Furthermore, while you could still get the current behavior by explicitly casting the timestamp to timestamptz before extracting the epoch, there is currently no convenient way to get a non-timezone-aware epoch value from a timestamp. Which seems rather silly given that one point of the timestamp type is to not be timezone sensitive. So I'm kind of inclined to revert that old change. Back in the day we thought it was a relatively insignificant bug fix and applied it in a minor release, but I think now our standards are higher and we'd want to treat this as a release-notable incompatibility. The above-linked discussion also brings up a different point, which is that extracting the epoch from a timestamptz is an immutable operation, but because it's provided in the context of timestamptz_part we can only mark it stable. (That is correct because the other cases depend on the timezone setting ... but epoch doesn't.) It seems like it might be worth providing a single-purpose function equivalent to extract(epoch), so that we could mark it immutable. On the other hand, it's not entirely apparent why people would need to create indexes on the epoch value rather than just indexing the timestamp itself, so I'm a tad less excited about this angle of it. Thoughts? regards, tom lane
On Mon, Apr 9, 2012 at 1:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > A long time ago, we had this bug report: > http://archives.postgresql.org/pgsql-bugs/2003-02/msg00069.php > in consequence of which, I changed timestamp_part() so that it would > rotate a timestamp-without-timezone from the local timezone to GMT > before extracting the epoch offset (commit > 191ef2b407f065544ceed5700e42400857d9270f). > > Recent discussion makes it seem like this was a bad idea: > http://archives.postgresql.org/pgsql-general/2012-01/msg00649.php > The big problem is that timestamp_part() is marked as immutable, which > is a correct statement for every other field type that it can extract, > but wrong for epoch if that depends on the setting of the timezone GUC. > So if we leave this behavior alone, we're going to have to downgrade > timestamp_part() to stable, which is quite likely to break applications > using it in index expressions. Furthermore, while you could still get > the current behavior by explicitly casting the timestamp to timestamptz > before extracting the epoch, there is currently no convenient way to get > a non-timezone-aware epoch value from a timestamp. Which seems rather > silly given that one point of the timestamp type is to not be timezone > sensitive. > > So I'm kind of inclined to revert that old change. Back in the day > we thought it was a relatively insignificant bug fix and applied it in a > minor release, but I think now our standards are higher and we'd want to > treat this as a release-notable incompatibility. +1 to all the above. > The above-linked discussion also brings up a different point, which is > that extracting the epoch from a timestamptz is an immutable operation, > but because it's provided in the context of timestamptz_part we can only > mark it stable. (That is correct because the other cases depend on the > timezone setting ... but epoch doesn't.) It seems like it might be > worth providing a single-purpose function equivalent to extract(epoch), > so that we could mark it immutable. On the other hand, it's not > entirely apparent why people would need to create indexes on the epoch > value rather than just indexing the timestamp itself, so I'm a tad less > excited about this angle of it. If somebody needs it I'd probably be in favor of doing it. I'm not sure I'd do it on spec. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > so that we could mark it immutable. On the other hand, it's not > entirely apparent why people would need to create indexes on the epoch > value rather than just indexing the timestamp itself Well, it makes for smaller indexes if you don't really care about sub-second resolutions. - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201204091345 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAk+DIJcACgkQvJuQZxSWSsiLsQCgrA8Sxcljm+HPJ1jQY7l0u3UZ UTwAnjBGM7SstLCnihtRkxDJrMax2Ikl =Kjic -----END PGP SIGNATURE-----
Robert Haas <robertmhaas@gmail.com> writes: > On Mon, Apr 9, 2012 at 1:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> http://archives.postgresql.org/pgsql-general/2012-01/msg00649.php >> The above-linked discussion also brings up a different point, which is >> that extracting the epoch from a timestamptz is an immutable operation, >> but because it's provided in the context of timestamptz_part we can only >> mark it stable. (That is correct because the other cases depend on the >> timezone setting ... but epoch doesn't.) It seems like it might be >> worth providing a single-purpose function equivalent to extract(epoch), >> so that we could mark it immutable. On the other hand, it's not >> entirely apparent why people would need to create indexes on the epoch >> value rather than just indexing the timestamp itself, so I'm a tad less >> excited about this angle of it. > If somebody needs it I'd probably be in favor of doing it. I'm not > sure I'd do it on spec. Hmm, I thought depesz was asking for such a function here: http://archives.postgresql.org/pgsql-hackers/2012-01/msg01690.php but now that I look more closely, he may have just meant that as an alternative to touching the existing behavior of timestamp_part. But providing a new function wouldn't be enough to solve the problem that timestamp_part's immutability marking is wrong. regards, tom lane
"Greg Sabino Mullane" <greg@turnstep.com> writes:
>> so that we could mark it immutable. On the other hand, it's not
>> entirely apparent why people would need to create indexes on the epoch
>> value rather than just indexing the timestamp itself
> Well, it makes for smaller indexes if you don't really care about
> sub-second resolutions.
Well, maybe in principle, but in practice it's an 8-byte value either
way. I guess you could down-convert to an int4 if you plan to be
safely dead before 2038 ...
regards, tom lane
Excerpts from Tom Lane's message of lun abr 09 15:04:10 -0300 2012: > Robert Haas <robertmhaas@gmail.com> writes: > > On Mon, Apr 9, 2012 at 1:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> http://archives.postgresql.org/pgsql-general/2012-01/msg00649.php > >> The above-linked discussion also brings up a different point, which is > >> that extracting the epoch from a timestamptz is an immutable operation, > >> but because it's provided in the context of timestamptz_part we can only > >> mark it stable. (That is correct because the other cases depend on the > >> timezone setting ... but epoch doesn't.) It seems like it might be > >> worth providing a single-purpose function equivalent to extract(epoch), > >> so that we could mark it immutable. On the other hand, it's not > >> entirely apparent why people would need to create indexes on the epoch > >> value rather than just indexing the timestamp itself, so I'm a tad less > >> excited about this angle of it. > > > If somebody needs it I'd probably be in favor of doing it. I'm not > > sure I'd do it on spec. > > Hmm, I thought depesz was asking for such a function here: > http://archives.postgresql.org/pgsql-hackers/2012-01/msg01690.php > but now that I look more closely, he may have just meant that as an > alternative to touching the existing behavior of timestamp_part. > But providing a new function wouldn't be enough to solve the problem > that timestamp_part's immutability marking is wrong. It would be useful to have a simple function to use with timestamp in constraint exclusion without having to use contorted expressions ... An immutable extract_epoch(timestamptz) would fit the bill. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes:
>> Robert Haas <robertmhaas@gmail.com> writes:
>>> If somebody needs it I'd probably be in favor of doing it. I'm not
>>> sure I'd do it on spec.
> It would be useful to have a simple function to use with timestamp in
> constraint exclusion without having to use contorted expressions ...
> An immutable extract_epoch(timestamptz) would fit the bill.
What exactly would you do with it there that you couldn't do more easily
and clearly with plain timestamp comparisons? I'm willing to be
convinced, but I want to see a case where it really is the best way.
regards, tom lane
Excerpts from Tom Lane's message of lun abr 09 15:38:21 -0300 2012: > > Alvaro Herrera <alvherre@commandprompt.com> writes: > >> Robert Haas <robertmhaas@gmail.com> writes: > >>> If somebody needs it I'd probably be in favor of doing it. I'm not > >>> sure I'd do it on spec. > > > It would be useful to have a simple function to use with timestamp in > > constraint exclusion without having to use contorted expressions ... > > An immutable extract_epoch(timestamptz) would fit the bill. > > What exactly would you do with it there that you couldn't do more easily > and clearly with plain timestamp comparisons? I'm willing to be > convinced, but I want to see a case where it really is the best way. You mean, having the constraint declaration rotate the timestamptz column to timestamp and then extract the epoch from that? If you go that route, then the queries that wish to take advantage of constraint exclusion would have to do likewise, which becomes ugly rather quickly. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Excerpts from Tom Lane's message of lun abr 09 15:38:21 -0300 2012:
>> What exactly would you do with it there that you couldn't do more easily
>> and clearly with plain timestamp comparisons? I'm willing to be
>> convinced, but I want to see a case where it really is the best way.
> You mean, having the constraint declaration rotate the timestamptz
> column to timestamp and then extract the epoch from that? If you go
> that route, then the queries that wish to take advantage of constraint
> exclusion would have to do likewise, which becomes ugly rather quickly.
No, I'm wondering why the partition constraints wouldn't just be
tstzcol >= '2012-04-01 00:00' and tstzcol < '2012-05-01 00:00'
or similar. What sort of constraint have you got in mind that is more
naturally expressed involving extract(epoch)? (And will the planner
think so too?)
regards, tom lane