Thread: Adventures in PostgreSQL

Folks,

I've recently started a new series of articles at TechDocs (
http://techdocs.postgresql.org ).   This will be a biweekly (or
triweekly if I'm busy) column, with articles covering an entire range
of issues around my professional support of PostgreSQL.

Currently there are two articles up, both oriented toward
beginning-intermediate PostgreSQL users:

1. Restoring a corrupted Template 1 Database
2. Part I of "The Joy Of Index"

There will be a few articles oriented toward advanced users, but most
will keep the focus of the first two.   Please take a look, and check
back regularly for new columns.

-Josh Berkus
 Aglio Database Solutions
 San Francisco

P.S. I'm posting this because the articles were inspired by questions I
was asked on these lists.

P.P.S. I still need book review submissions for the PostgreSQL Book
Review page at TechDocs!

I'm trying to create a index from a timestamp+tz field and want the index
to be date_trunc'd down to just the date

when i try to do a

create idxfoo on foo (date(footime));

i get a

ERROR:  DefineIndex: index function must be marked IMMUTABLE

and it chokes on when i try to use the date_trunc() function as well

create idxfoo on foo (date_trunc('day',footime));

ERROR:  parser: parse error at or near "'day'" at character 53

Any suggestions/workarounds (other than creating additional date-only
columns in the schema and indexing those???)

-d

On Mon, Sep 27, 2004 at 19:14:09 -0500,
  "D. Duccini" <duccini@backpack.com> wrote:
>
> I'm trying to create a index from a timestamp+tz field and want the index
> to be date_trunc'd down to just the date
>
> when i try to do a
>
> create idxfoo on foo (date(footime));
>
> i get a
>
> ERROR:  DefineIndex: index function must be marked IMMUTABLE
>
> and it chokes on when i try to use the date_trunc() function as well
>
> create idxfoo on foo (date_trunc('day',footime));
>
> ERROR:  parser: parse error at or near "'day'" at character 53
>
> Any suggestions/workarounds (other than creating additional date-only
> columns in the schema and indexing those???)

The reason this doesn't work is that the timestamp to date conversion
depends on the time zone setting. In theory you should be able to avoid
this by specifying the time zone to check the date in. I tried something
like the following which I think should work, but doesn't:
create idxfoo on foo (date(timezone('UTC',footime)));

The conversion of the timestamp stored in footime should be immutable
and then taking the date should work. I did find that date of a timestamp
without time zone is treated as immutable.

I am not sure how to check if the supplied function for converting
a timestamp with time zone to a timestamp without timezone using a
specified time zone is immutable. I think this function should be
immutable, but that it probably isn't.

On Fri, Oct 01, 2004 at 13:28:30 -0500,
  Bruno Wolff III <bruno@wolff.to> wrote:
>
> I am not sure how to check if the supplied function for converting
> a timestamp with time zone to a timestamp without timezone using a
> specified time zone is immutable. I think this function should be
> immutable, but that it probably isn't.

I found that most of the various timezone functions are marked as stable
instead of immutable. I think at least a couple of these should be
marked as immutable and I will try reporting this as a bug.

Bruno Wolff III <bruno@wolff.to> writes:
> I am not sure how to check if the supplied function for converting
> a timestamp with time zone to a timestamp without timezone using a
> specified time zone is immutable. I think this function should be
> immutable, but that it probably isn't.

Yup.  In 7.4:

regression=# select provolatile from pg_proc where oid = 'timezone(text,timestamptz)'::regprocedure;
 provolatile
-------------
 s
(1 row)

regression=#

This is a thinko that's already been corrected for 8.0:

regression=# select provolatile from pg_proc where oid = 'timezone(text,timestamptz)'::regprocedure;
 provolatile
-------------
 i
(1 row)

regression=#

If you wanted you could just UPDATE pg_proc to correct this mistake.
Another possibility is to create a function that's an IMMUTABLE
wrapper around the standard function.

Looking at this, I realize that date_trunc() is mismarked: the
timestamptz variant is strongly dependent on the timezone setting
and so should be STABLE not IMMUTABLE.  Ooops.

            regards, tom lane

> The reason this doesn't work is that the timestamp to date conversion
> depends on the time zone setting. In theory you should be able to avoid
> this by specifying the time zone to check the date in. I tried something
> like the following which I think should work, but doesn't:
> create idxfoo on foo (date(timezone('UTC',footime)));
>
> The conversion of the timestamp stored in footime should be immutable
> and then taking the date should work. I did find that date of a timestamp
> without time zone is treated as immutable.
>
> I am not sure how to check if the supplied function for converting
> a timestamp with time zone to a timestamp without timezone using a
> specified time zone is immutable. I think this function should be
> immutable, but that it probably isn't.

I think we found a way around it!


CREATE OR REPLACE FUNCTION date_immutable( timestamptz ) RETURNS date AS
'SELECT date( $1 ) ;' LANGUAGE 'sql' IMMUTABLE ;

CREATE INDEX "new_event_dt" ON "the_events" USING btree (
date_immutable( "event_dt_tm" ) ) ;



-----------------------------------------------------------------------------
david@backpack.com            BackPack Software, Inc.        www.backpack.com
+1 651.645.7550 voice       "Life is an Adventure.
+1 651.645.9798 fax            Don't forget your BackPack!"
-----------------------------------------------------------------------------

"D. Duccini" <duccini@backpack.com> writes:
> I think we found a way around it!

> CREATE OR REPLACE FUNCTION date_immutable( timestamptz ) RETURNS date AS
> 'SELECT date( $1 ) ;' LANGUAGE 'sql' IMMUTABLE ;

No, you just found a way to corrupt your index.  Pretending that
date(timestamptz) is immutable does not make it so.  The above
*will* break the first time someone uses the table with a different
timezone setting.

What you can do safely is date(footime AT TIME ZONE 'something'),
since this nails down the zone in which the date is interpreted.

            regards, tom lane