Обсуждение: can't create index with 'dowcast' row

Поиск
Список
Период
Сортировка

can't create index with 'dowcast' row

От
Louis-David Mitterrand
Дата:
Hi,

To constraint unique'ness of my visitors to a 24h periode I tried
created a index including the 'date' part of the created_on timestamp:

    CREATE UNIQUE INDEX visit_idx ON visit_buffer USING btree (id_session,
    id_story, created_on::date);

    psql:visit_pkey.sql:5: ERROR:  syntax error at or near "::"
    LINE 1: ...buffer USING btree (id_session, id_story, created_on::date);

and this:

    CREATE UNIQUE INDEX visit_idx ON visit_buffer USING btree (id_session, id_story, extract(date from created_on));
    psql:visit_pkey.sql:4: ERROR:  functions in index expression must be marked IMMUTABLE

How can I achieve what I am trying?

Thanks,

Re: can't create index with 'dowcast' row

От
marcelo Cortez
Дата:
Louis

 what if you create one "wrapper" function immutable?
 some thing like this.

CREATE OR REPLACE FUNCTION myextract(timestamp )
  RETURNS date  AS
$BODY$
BEGIN
  return extract(date from $1)   ;
END;
$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE

 best regards
 mdc




--- Louis-David Mitterrand
<vindex+lists-pgsql-general@apartia.org> escribió:

> Hi,
>
> To constraint unique'ness of my visitors to a 24h
> periode I tried
> created a index including the 'date' part of the
> created_on timestamp:
>
>     CREATE UNIQUE INDEX visit_idx ON visit_buffer USING
> btree (id_session,
>     id_story, created_on::date);
>
>     psql:visit_pkey.sql:5: ERROR:  syntax error at or
> near "::"
>     LINE 1: ...buffer USING btree (id_session,
> id_story, created_on::date);
>
> and this:
>
>     CREATE UNIQUE INDEX visit_idx ON visit_buffer USING
> btree (id_session, id_story, extract(date from
> created_on));
>     psql:visit_pkey.sql:4: ERROR:  functions in index
> expression must be marked IMMUTABLE
>
> How can I achieve what I am trying?
>
> Thanks,
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>



      Yahoo! Encuentros.

Ahora encontrar pareja es mucho más fácil, probá el nuevo Yahoo! Encuentros
http://yahoo.cupidovirtual.com/servlet/NewRegistration

Re: can't create index with 'dowcast' row

От
marcelo Cortez
Дата:
Sorry forgot to mention

later try

CREATE UNIQUE INDEX visit_idx ON visit_buffer(
id_session, id_story ,myextract(created_on));


best regards

> Louis
>
>  what if you create one "wrapper" function
> immutable?
>  some thing like this.
>
> CREATE OR REPLACE FUNCTION myextract(timestamp )
>   RETURNS date  AS
> $BODY$
> BEGIN
>   return extract(date from $1)   ;
> END;
> $BODY$
>   LANGUAGE 'plpgsql' IMMUTABLE
>
>  best regards
>  mdc
>
>
>
>
> --- Louis-David Mitterrand
> <vindex+lists-pgsql-general@apartia.org> escribió:
>
> > Hi,
> >
> > To constraint unique'ness of my visitors to a 24h
> > periode I tried
> > created a index including the 'date' part of the
> > created_on timestamp:
> >
> >     CREATE UNIQUE INDEX visit_idx ON visit_buffer
> USING
> > btree (id_session,
> >     id_story, created_on::date);
> >
> >     psql:visit_pkey.sql:5: ERROR:  syntax error at or
> > near "::"
> >     LINE 1: ...buffer USING btree (id_session,
> > id_story, created_on::date);
> >
> > and this:
> >
> >     CREATE UNIQUE INDEX visit_idx ON visit_buffer
> USING
> > btree (id_session, id_story, extract(date from
> > created_on));
> >     psql:visit_pkey.sql:4: ERROR:  functions in index
> > expression must be marked IMMUTABLE
> >
> > How can I achieve what I am trying?
> >
> > Thanks,
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 6: explain analyze is your friend
> >
>
>
>
>       Yahoo! Encuentros.
>
> Ahora encontrar pareja es mucho más fácil, probá el
> nuevo Yahoo! Encuentros
>
http://yahoo.cupidovirtual.com/servlet/NewRegistration
>



      Tarjeta de crédito Yahoo! de Banco Supervielle.
Solicitá tu nueva Tarjeta de crédito. De tu PC directo a tu casa. www.tuprimeratarjeta.com.ar

Re: can't create index with 'dowcast' row

От
Tom Lane
Дата:
Louis-David Mitterrand <vindex+lists-pgsql-general@apartia.org> writes:
>     CREATE UNIQUE INDEX visit_idx ON visit_buffer USING btree (id_session,
>     id_story, created_on::date);

>     psql:visit_pkey.sql:5: ERROR:  syntax error at or near "::"

The reason that didn't work is that you need parentheses around an index
expression (otherwise the CREATE INDEX syntax would be ambiguous).

>     CREATE UNIQUE INDEX visit_idx ON visit_buffer USING btree (id_session, id_story, extract(date from created_on));
>     psql:visit_pkey.sql:4: ERROR:  functions in index expression must be marked IMMUTABLE

I take it created_on is timestamp with time zone, not plain timestamp?
The problem here is that the coercion to date is not immutable because
it depends on the timezone setting.  (The other way would have failed
too, once you got past the syntax detail.)  You need to figure out
what your intended semantics are --- in particular, whose idea of
midnight should divide one day from the next --- and then use a
unique index on something like

    ((created_on AT TIME ZONE 'Europe/Paris')::date)

Note that the nearby recommendation to override the immutability
test with a phonily-immutable wrapper function would be a real bad
idea, because such an index would misbehave anytime someone changed
their timezone setting.

            regards, tom lane

Re: can't create index with 'dowcast' row

От
Louis-David Mitterrand
Дата:
On Fri, Jan 25, 2008 at 12:17:16AM -0500, Tom Lane wrote:
> Louis-David Mitterrand <vindex+lists-pgsql-general@apartia.org> writes:
> >     CREATE UNIQUE INDEX visit_idx ON visit_buffer USING btree (id_session,
> >     id_story, created_on::date);
>
> >     psql:visit_pkey.sql:5: ERROR:  syntax error at or near "::"
>
> The reason that didn't work is that you need parentheses around an index
> expression (otherwise the CREATE INDEX syntax would be ambiguous).

This worked fine once I changed the type to a simple 'timestamp'.

> >     CREATE UNIQUE INDEX visit_idx ON visit_buffer USING btree (id_session, id_story, extract(date from
created_on));
> >     psql:visit_pkey.sql:4: ERROR:  functions in index expression must be marked IMMUTABLE
>
> I take it created_on is timestamp with time zone, not plain timestamp?
> The problem here is that the coercion to date is not immutable because
> it depends on the timezone setting.  (The other way would have failed
> too, once you got past the syntax detail.)  You need to figure out
> what your intended semantics are --- in particular, whose idea of
> midnight should divide one day from the next --- and then use a
> unique index on something like
>
>     ((created_on AT TIME ZONE 'Europe/Paris')::date)
>
> Note that the nearby recommendation to override the immutability
> test with a phonily-immutable wrapper function would be a real bad
> idea, because such an index would misbehave anytime someone changed
> their timezone setting.

Thanks Tom for that explanation.