Обсуждение: Exclusion constraint issue

I'm getting an error message that doesn't make sense to me. Using PostgreSQL 9.0.0 on CentOS 5.5.


Given the following table and function definitions

CREATE TABLE test3
(
  test3_id serial NOT NULL,
  fk_id integer,
  data_3 text,
  effect_date date NOT NULL,
  expire_date date NOT NULL,
  CONSTRAINT test3_pkey PRIMARY KEY (test3_id)
)

CREATE OR REPLACE FUNCTION period(timestamp with time zone, timestamp with time zone)
  RETURNS period AS
$BODY$
    SELECT CASE WHEN $1 <= $2
    THEN ($1, $2)::period
    ELSE ($2, $1)::period END;
$BODY$
  LANGUAGE sql IMMUTABLE STRICT


and the period datatype with it's associated functions and operators installed from http://pgfoundry.org/projects/timespan/

why would I get the following error, since the period() function is in fact declared as immutable?

test=# ALTER TABLE test3 ADD exclude using gist(period(effect_date::timestamptz, expire_date::timestamptz) with && );
ERROR:  functions in index expression must be marked IMMUTABLE

Thanks in advance for any assistance.
-Eric

Eric McKeeth <eldin00@gmail.com> writes:
> why would I get the following error, since the period() function is in fact
> declared as immutable?

> test=# ALTER TABLE test3 ADD exclude using
> gist(period(effect_date::timestamptz, expire_date::timestamptz) with && );
> ERROR:  functions in index expression must be marked IMMUTABLE

period() might be immutable, but those casts from date to timestamptz
are not, because they depend on the TimeZone parameter.

            regards, tom lane

On Fri, Sep 24, 2010 at 05:22:15PM -0400, Tom Lane wrote:
> Eric McKeeth <eldin00@gmail.com> writes:
> > why would I get the following error, since the period() function
> > is in fact declared as immutable?
>
> > test=# ALTER TABLE test3 ADD exclude using
> > gist(period(effect_date::timestamptz, expire_date::timestamptz)
> > with && ); ERROR:  functions in index expression must be marked
> > IMMUTABLE
>
> period() might be immutable, but those casts from date to
> timestamptz are not, because they depend on the TimeZone parameter.

How hard would it be to point out the first expression found to be
mutable?  All of them?

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david@fetter.org> writes:
> On Fri, Sep 24, 2010 at 05:22:15PM -0400, Tom Lane wrote:
>> period() might be immutable, but those casts from date to
>> timestamptz are not, because they depend on the TimeZone parameter.

> How hard would it be to point out the first expression found to be
> mutable?

I looked at that yesterday.  It would take significant restructuring
of the code involved :-( ... the place that throws the error doesn't
know exactly what subnode was found to be mutable, and IIRC it hasn't
got access to the original command string anyway.

            regards, tom lane

On Sun, Sep 26, 2010 at 10:15:00AM -0400, Tom Lane wrote:
> David Fetter <david@fetter.org> writes:
> > On Fri, Sep 24, 2010 at 05:22:15PM -0400, Tom Lane wrote:
> >> period() might be immutable, but those casts from date to
> >> timestamptz are not, because they depend on the TimeZone
> >> parameter.
>
> > How hard would it be to point out the first expression found to be
> > mutable?
>
> I looked at that yesterday.  It would take significant restructuring
> of the code involved :-( ... the place that throws the error doesn't
> know exactly what subnode was found to be mutable, and IIRC it
> hasn't got access to the original command string anyway.

How much restructuring are we talking about here?

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Thanks for pointing out what I was overlooking. After a bit of further investigation and testing it seems like the period type I found isn't going to work without modification for my constraint, so I ended up with the following to get the semantics I need:

alter table test3 add exclude using gist(
    box(
        point(
            case when effect_date = '-Infinity'::date
            then '-Infinity'::double precision
            else date_part('epoch'::text, effect_date)
            end,
            1
        ),
        point(
            case when expire_date = 'Infinity'::date
            then 'Infinity'::double precision
            else date_part('epoch', expire_date) - 1
            end,
            1
        )
    )
    with &&
);

This is ugly, but it does seem to enforce the constraint I need, of non-overlapping dates where sharing an endpoint is not considered an overlap. The case blocks are because the date_part bit always returns 0 for infinite dates, which seemed a bit counter-intuitive. Any suggestions on how I could improve on it?

On Tue, 2010-09-28 at 12:18 -0600, Eric McKeeth wrote:

> This is ugly, but it does seem to enforce the constraint I need, of
> non-overlapping dates where sharing an endpoint is not considered an
> overlap.

The period type supports different inclusivity/exclusivity combinations.
So, the period:

   '[2009-01-02, 2009-01-03)'

Does not overlap with:

   '[2009-01-03, 2009-01-04)'

Because "[" or "]" means "inclusive" and "(" or ")" means "exclusive".

For further discussion, you can join the temporal-general@pgfoundry.org
mailing list (sign up at
http://pgfoundry.org/mailman/listinfo/temporal-general ). If this still
does not solve your use case, I'd like to see if it can be modified to
do so.

Regards,
    Jeff Davis

I've subscribed to the temporal-general list, so we can move this discussion there if that's more appropriate.

Thanks,
Eric