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
IMMUTABLEperiod() 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
On Fri, Sep 24, 2010 at 3:22 PM, Tom Lane <tgl@sss.pgh.pa.us> 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.regards, tom lane
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
On Tue, Sep 28, 2010 at 4:07 PM, Jeff Davis <pgsql@j-davis.com> wrote:
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".
My problem wasn't with getting the period type to represent overlaps with
the correct inclusivity/exclusivity, but in getting it to work with my
exclusion constraint. Can you show an example of how I could get that
working perhaps?
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