Hi all,

we (GNUmed) run a medical database on PostgreSQL. We are
very pleased with it (PostgreSQL, that is ;-) in all aspects.

The date-of-birth field in our table holding patients is of
type "timestamp with time zone". One of our patient search
queries uses the date-of-birth field to find matches. Since
users enter day, month, and year but not hour, minute, and
second of the DOB we run the query with

select
..
where
.. and
date_trunc('day', dob) = date_trunc('day', what_the_user_entered_as_dob) and
..
;

(appropriately escaped, of course)

The actual DOB is indeed stored with hour, minute and second
so the time information is not redundant but we don't need
it for searching.

So I figured it would make sense to add a functional index
on date_trunc('day', dob) to the patients table. Which
worked (appeared to, at least) with PG 7.4.

One of our users is on PG 8.2 and gets the warning that
date_trunc() is not immutable and can thus not be used in a
functional index. Makes sense all by itself. What I don't
understand, however, is exactly *why* date_trunc is not
immutable ? All it does is extracting part of the
information that's there anyways. One would assume it to be
the timestamp equivalent of substring(), no ? (not a good
example, perhaps, as that might depend on encoding
settings...) It *shouldn't* really depend on, say, date/time
related locale settings, should it ?

I'd be happy to provide more details if that is needed for
which I'd have to contact the user in question.

Thanks for any insight offered,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

[ Post a follow-up to this message ]

followup to self:

On Sun, Feb 18, 2007 at 12:29:17PM +0100, Karsten Hilbert wrote:

> So I figured it would make sense to add a functional index
> on date_trunc('day', dob) to the patients table. Which
> worked (appeared to, at least) with PG 7.4.
>
> One of our users is on PG 8.2
PostgreSQL 8.1 I was to say.

> and gets the warning that
> date_trunc() is not immutable and can thus not be used in a
> functional index.
The code to create the index:

create index idx_identity_dob_ymd on dem.identity(date_trunc('day', dob))

The exact error it emits:

functions in index expression must be marked IMMUTABLE

Those were lifted from the error log without further editing.

I know that I could fake immutability by wrapping
date_trunc() in a stored procedure marked IMMUTABLE but
I wonder what pitfalls that might hold.

Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[ Post a follow-up to this message ]

On Feb 18, 2007, at 20:29 , Karsten Hilbert wrote:

> What I don't understand, however, is exactly *why* date_trunc is
> not immutable ?

I believe it's because the result of date_trunc depends on the time
zone setting for the session.

test=# select date_trunc('day', current_timestamp);
date_trunc
------------------------
2007-02-18 00:00:00+09
(1 row)

test=# set time zone 'EST5EDT';
SET
test=# select date_trunc('day', current_timestamp);
date_trunc
------------------------
2007-02-18 00:00:00-05
(1 row)

So, given the same arguments, ('day', and current_timestamp),
date_trunc is returning two different results. (Casting to date has
the same issue.)

Michael Glaesemann
grzm seespotcode net



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

[ Post a follow-up to this message ]

On Sun, Feb 18, 2007 at 09:19:43PM +0900, Michael Glaesemann wrote:
 
>
> I believe it's because the result of date_trunc depends on the time
> zone setting for the session.

...

> So, given the same arguments, ('day', and current_timestamp),
> date_trunc is returning two different results. (Casting to date has
> the same issue.)

Ah, I see. That makes sense.

Now, if I'd write a

date_trunc_utc(precision, timestamp with time zone)

which converts input timestamps to UTC I could fairly safely
mark that IMMUTABLE, no ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[ Post a follow-up to this message ]

Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:[vbcol=seagreen] 

For the record, this was changed just before 8.0 release:
http://archives.postgresql.org/pgsq...10/msg00030.php

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

[ Post a follow-up to this message ]

Michael Glaesemann wrote:
>
> On Feb 18, 2007, at 20:29 , Karsten Hilbert wrote:
> 
>
> I believe it's because the result of date_trunc depends on the time zone
> setting for the session.

I understand the reasoning, but _under the conditions_ it is being used
by the OP it could have been immutable, right?

The index values will still match up with the queried values if they are
in the same time zone.

I'm not asking to change it back to immutable (it isn't), I just
realized that the stability of functions may actually be conditional.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

[ Post a follow-up to this message ]

On Feb 19, 2007, at 18:04 , Alban Hertroys wrote:

> Michael Glaesemann wrote: 
>
> I understand the reasoning, but _under the conditions_ it is being
> used
> by the OP it could have been immutable, right?

*Under the conditions* doesn't really make sense wrt immutable
functions. Immutable means is immutable under all conditions.

Michael Glaesemann
grzm seespotcode net



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

[ Post a follow-up to this message ]

On Feb 18, 2007, at 23:12 , Karsten Hilbert wrote:

> On Sun, Feb 18, 2007 at 09:19:43PM +0900, Michael Glaesemann wrote:
> 
>
> ...
> 
>
> Ah, I see. That makes sense.
>
> Now, if I'd write a
>
> 	date_trunc_utc(precision, timestamp with time zone)
>
> which converts input timestamps to UTC I could fairly safely
> mark that IMMUTABLE, no ?


Yeah, I think if you normalized it to UTC you could mark your new
function as immutable.

Michael Glaesemann
grzm seespotcode net



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[ Post a follow-up to this message ]

Michael Glaesemann wrote:
>
> On Feb 19, 2007, at 18:04 , Alban Hertroys wrote:
> 
>
> *Under the conditions* doesn't really make sense wrt immutable
> functions. Immutable means is immutable under all conditions.

What I'm trying to say is not that it _is_ immutable, but that it
_behaves_ immutable (under said conditions).

This could imply that if a certain condition is available in a query on
which such a function operates, it would behave immutable.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

[ Post a follow-up to this message ]

On Mon, Feb 19, 2007 at 10:16:12AM +0100, Alban Hertroys wrote:

> What I'm trying to say is not that it _is_ immutable, but that it
> _behaves_ immutable (under said conditions).
>
> This could imply that if a certain condition is available in a query on
> which such a function operates, it would behave immutable.
That is precisely why I didn't get the idea upfront that
date_trunc() wouldn't be immutable just so.

I'll solve it with a date_trunc_utc() wrapper.

Thanks to all who chipped in. Something new to learn every day.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

[ Post a follow-up to this message ]