Обсуждение: Non-Overlaping date interval index

Hi,

I wonder how to have a sort of "uniq" index on date interval, such
that there is no date interval overlaping in the table.

exemple:
create table test (start timestamp, end timestamp);
with the constraint: end > start

Cordialement,
Jean-Gérard Pailloncy

On Sat, Feb 18, 2006 at 10:03:11AM +0100, Pailloncy Jean-Gerard wrote:
> Hi,
>
> I wonder how to have a sort of "uniq" index on date interval, such
> that there is no date interval overlaping in the table.
>
> exemple:
> create table test (start timestamp, end timestamp);
> with the constraint: end > start

Unfortunatly no. There have been discussions about extending unique
indexes to something other than just single values but no code has been
produced yet.

You can get fairly close with a trigger though (not syntactically
correct):

create function range_check() returns trigger as $$
if exists( select 1 from test
   where (new.start < start) <> (new.end > end) then
   raise error 'Conflict'
return NEW;
$$;

create trigger blah on insert or update to test do range_check();

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

am  18.02.2006, um 10:03:11 +0100 mailte Pailloncy Jean-Gerard folgendes:
> Hi,
>
> I wonder how to have a sort of "uniq" index on date interval, such  that
> there is no date interval overlaping in the table.
>
> exemple:
> create table test (start timestamp, end timestamp);
> with the constraint: end > start

You can write a trigger to check the count of records which overlaps
(start,end):

Example:

test=# select * from foo;
 id |          s          |          e
----+---------------------+---------------------
  1 | 2006-01-01 00:00:00 | 2006-01-02 00:00:00
  2 | 2006-01-02 00:00:00 | 2006-01-03 00:00:00
  3 | 2006-01-03 00:00:00 | 2006-01-04 00:00:00
(3 rows)

test=# select count(*) from foo where (s,e) overlaps ('2006/01/05'::date, '2006/01/06'::date);
 count
-------
     0
(1 row)

test=# select count(*) from foo where (s,e) overlaps ('2006/01/01'::date, '2006/01/06'::date);
 count
-------
     3
(1 row)


Perhaps there are other solutions...

HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

On 2006-02-18, "A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote:
> am  18.02.2006, um 10:03:11 +0100 mailte Pailloncy Jean-Gerard folgendes:
>> I wonder how to have a sort of "uniq" index on date interval, such  that
>> there is no date interval overlaping in the table.
>
> You can write a trigger to check the count of records which overlaps
> (start,end):

Doesn't work reliably due to the race condition, unless you lock the table
against conflicting inserts. The problem of course is that the trigger
doesn't see uncommitted rows from concurrent transactions.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

A. Kretschmer <andreas.kretschmer@schollglas.com> schrieb:

> am  18.02.2006, um 10:03:11 +0100 mailte Pailloncy Jean-Gerard folgendes:
> > Hi,
> >
> > I wonder how to have a sort of "uniq" index on date interval, such  that
> > there is no date interval overlaping in the table.
> >
> > exemple:
> > create table test (start timestamp, end timestamp);
> > with the constraint: end > start
>
> You can write a trigger to check the count of records which overlaps
> (start,end):

Sorry, there are a problem:

the trigger can't see uncommitted inserts from concurrent transactions.

Thanks to AndrewSN for this hint.



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°