Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

2007-02-19 Thread Alban Hertroys
Michael Glaesemann wrote:
 
 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.

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
[EMAIL PROTECTED]

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


Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

2007-02-19 Thread Michael Glaesemann


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


Michael Glaesemann wrote:


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.


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


Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

2007-02-19 Thread Michael Glaesemann


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


On Sun, Feb 18, 2007 at 09:19:43PM +0900, Michael Glaesemann 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.


...


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 ?



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


Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

2007-02-19 Thread Alban Hertroys
Michael Glaesemann wrote:
 
 On Feb 19, 2007, at 18:04 , Alban Hertroys wrote:
 
 Michael Glaesemann wrote:

 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.

 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.

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
[EMAIL PROTECTED]

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 [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

2007-02-19 Thread Karsten Hilbert
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/


Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

2007-02-19 Thread Martijn van Oosterhout
On Mon, Feb 19, 2007 at 10:36:36AM +0100, Karsten Hilbert wrote:
 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.

It should be noted the date_truc(timestamptz) is not immutable, whereas
date_trunc(timestamp) is. Thus you should be able to make an index on:

date_trunc( timestamptz_column AT TIME ZONE 'UTC', 'foo' )

OTOH, if you're only storing times in UTC, then timestamp without
timezone might be better anyway.

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 From each according to his ability. To each according to his ability to 
 litigate.


signature.asc
Description: Digital signature


Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

2007-02-19 Thread Karsten Hilbert
On Mon, Feb 19, 2007 at 10:58:50AM +0100, Martijn van Oosterhout wrote:

  I'll solve it with a date_trunc_utc() wrapper.
 
 It should be noted the date_truc(timestamptz) is not immutable, whereas
 date_trunc(timestamp) is. Thus you should be able to make an index on:
 
 date_trunc( timestamptz_column AT TIME ZONE 'UTC', 'foo' )
Ah, that makes it clear *why* this should work.

I would assume to get meaningful results from a query using
that index I'd have to normalize input timestamps to UTC,
too, before putting them into the query, right ?

 OTOH, if you're only storing times in UTC, then timestamp without
 timezone might be better anyway.
Well, PostgreSQL itself is storing UTC anyways but we need
the timezone bit since our frontend delivers timestamps from
various timezones and they are note normalized to UTC before
they get to the database.

IOW, I want the database to force programmers to have to
think about from which timezone they deliver timestamps into
a date-of-birth field into.

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

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

2007-02-19 Thread Martijn van Oosterhout
On Mon, Feb 19, 2007 at 12:03:07PM +0100, Karsten Hilbert wrote:
  date_trunc( timestamptz_column AT TIME ZONE 'UTC', 'foo' )
 Ah, that makes it clear *why* this should work.
 
 I would assume to get meaningful results from a query using
 that index I'd have to normalize input timestamps to UTC,
 too, before putting them into the query, right ?

Well, your queries need to use the same form, ie:

SELECT blah FROM foo 
WHERE date_trunc( 'entered_timestamp'::timestamptz AT TIME ZONE 'UTC', 'foo' )

That seems a bit error prone though, so your idea of making a simple
SQL function to wrap it will probably save you much heartache. It will
also make it clearer to people reading the code *why* it is written
that way.

 Well, PostgreSQL itself is storing UTC anyways but we need
 the timezone bit since our frontend delivers timestamps from
 various timezones and they are note normalized to UTC before
 they get to the database.

Yeah, the AT TIME ZONE 'UTC' needs to be put somewhere, and in the
index is probably easier than everywhere else. Just checking you'd
thought about it. :)

 IOW, I want the database to force programmers to have to
 think about from which timezone they deliver timestamps into
 a date-of-birth field into.

Right.

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 From each according to his ability. To each according to his ability to 
 litigate.


signature.asc
Description: Digital signature


Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

2007-02-19 Thread Karsten Hilbert
On Mon, Feb 19, 2007 at 12:53:15PM +0100, Martijn van Oosterhout wrote:

 Well, your queries need to use the same form, ie:
 
 SELECT blah FROM foo 
 WHERE date_trunc( 'entered_timestamp'::timestamptz AT TIME ZONE 'UTC', 'foo' )
Thought so.

 That seems a bit error prone though, so your idea of making a simple
 SQL function to wrap it will probably save you much heartache. It will
 also make it clearer to people reading the code *why* it is written
 that way.
Yep, and the COMMENT ON FUCNTION provides for a nice place to document it :-)

  Well, PostgreSQL itself is storing UTC anyways but we need
  the timezone bit since our frontend delivers timestamps from
  various timezones and they are note normalized to UTC before
  they get to the database.
 
 Yeah, the AT TIME ZONE 'UTC' needs to be put somewhere, and in the
 index is probably easier than everywhere else. Just checking you'd
 thought about it. :)
Thanks !

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

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

2007-02-19 Thread Tom Lane
Alban Hertroys [EMAIL PROTECTED] writes:
 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.

Right, but we don't have any way to represent such a fact in
date_trunc's pg_proc entry, so we have to mark it as not immutable.

There was a related discussion awhile ago when designing the current
set of what time is it functions --- transaction_timestamp(),
statement_timestamp(), and clock_timestamp().  The original proposal
had just a single function that took a parameter telling which value
you wanted.  The trouble with that was that it'd have had to be marked
volatile, thereby defeating any ability to optimize conditions using it.
By splitting into three functions, we were able to limit the volatile
label to clock_timestamp().

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

2007-02-19 Thread Bruno Wolff III
On Sun, Feb 18, 2007 at 12:29:17 +0100,
  Karsten Hilbert [EMAIL PROTECTED] wrote:
 
 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

That seems like an odd choice. Is there some reason they didn't use a type
of date? Maybe you could get them to change it?

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


Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

2007-02-19 Thread Karsten Hilbert
On Mon, Feb 19, 2007 at 12:41:11PM -0600, Bruno Wolff III wrote:

   Karsten Hilbert [EMAIL PROTECTED] wrote:
  
  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
 
 That seems like an odd choice. Is there some reason they didn't use a type
 of date? Maybe you could get them to change it?

What time of day were you born ?

http://en.wikipedia.org/wiki/Apgar

What is the technical reason that makes you wonder ?

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


Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

2007-02-19 Thread Bruno Wolff III
On Mon, Feb 19, 2007 at 20:48:07 +0100,
  Karsten Hilbert [EMAIL PROTECTED] wrote:
 
 What time of day were you born ?
 
   http://en.wikipedia.org/wiki/Apgar
 
 What is the technical reason that makes you wonder ?

Because it would make doing the queries simpler.
If you aren't collecting the data, it doesn't make sense to deal with the
extra headaches involved with pretending you know what time of day someone
was born.

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


Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

2007-02-19 Thread Karsten Hilbert
On Mon, Feb 19, 2007 at 03:28:01PM -0600, Bruno Wolff III wrote:

  What is the technical reason that makes you wonder ?
 
 Because it would make doing the queries simpler.
 If you aren't collecting the data, it doesn't make sense to deal with the
 extra headaches involved with pretending you know what time of day someone
 was born.
Oh, I see. When I said that users don't enter the hour and
minute that was targetted at search time. They do enter the
time part when entering a new patient, of course.

So, it's surely collected. It's just not used for searching.

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


Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

2007-02-19 Thread jungmin shin

I have a question about the query optimizer of a postgres.
As long as I understood through a postgres manual, the postgres query
optimizer is implemented using  a *genetic algorithm.*

I'm thinking to modify the query optimizer.
Are there any postgres version which uses typical dynamic programming
approach for query optimization?
Also, are there any body who have tried to modify the optimizer?


jungmin


Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

2007-02-19 Thread Alvaro Herrera
jungmin shin escribió:
 I have a question about the query optimizer of a postgres.
 As long as I understood through a postgres manual, the postgres query
 optimizer is implemented using  a *genetic algorithm.*

There is an algorithm said to be genetic, but it only kicks in with
big joins; 12 tables on the default configuration.  On queries with less
tables, the optimizer uses exhaustive search and lots of smarts.

See
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/README?rev=1.39;content-type=text%2Fplain

 I'm thinking to modify the query optimizer.
 Are there any postgres version which uses typical dynamic programming
 approach for query optimization?
 Also, are there any body who have tried to modify the optimizer?

Sure, we have a very active optimizer hacker.  He is too clever for the
rest of us to follow though :-(   (I should speak only for myself here
of course).  He goes by the name of Tom Lane.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(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


[GENERAL] Why *exactly* is date_trunc() not immutable ?

2007-02-18 Thread Karsten Hilbert
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


Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

2007-02-18 Thread Karsten Hilbert
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


Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

2007-02-18 Thread Michael Glaesemann


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/


Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

2007-02-18 Thread Karsten Hilbert
On Sun, Feb 18, 2007 at 09:19:43PM +0900, Michael Glaesemann 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.

...

 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


Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

2007-02-18 Thread Tom Lane
Karsten Hilbert [EMAIL PROTECTED] writes:
 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.

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

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly