Обсуждение: Best way to prevent overlapping dates

I need to disable rows with overlapping dates in 8.1+
I created the following trigger procedure for this.

Is this best way ?
Will it prevent overlapping rows in all cases ?

Andrus.

create table puhkus (reanr serial primary key,   palgus date, plopp date);

CREATE OR REPLACE FUNCTION puhkus_sequenced_pkey() RETURNS trigger AS $$

DECLARE
   OverlappingRow INTEGER;

BEGIN
  SELECT reanr
  INTO OverlappingRow
FROM puhkus
WHERE puhkus.reanr<>NEW.reanr AND doverlaps( puhkus.palgus, puhkus.plopp,
NEW.palgus, NEW.plopp );

IF found THEN
  RAISE EXCEPTION 'Changed row % overlaps with existing row %', NEW.reanr,
OverLappingRow ;
  END IF;
RETURN NEW;
END;

$$ LANGUAGE plpgsql;

CREATE TRIGGER puhkus_sequenced_trigger BEFORE INSERT OR UPDATE ON puhkus
    FOR EACH ROW EXECUTE PROCEDURE puhkus_sequenced_pkey();

CREATE OR REPLACE FUNCTION public.doverlaps(date,
        date, date, date, out bool) IMMUTABLE AS
$_$
SELECT coalesce($1, date '0001-01-01')<=coalesce($4, date '9999-12-31') AND
     coalesce($2, date '9999-12-31')>=coalesce($3, date '0001-01-01' );
$_$ language sql;

CREATE OR REPLACE FUNCTION PUBLIC.GOMONTH(DATE, INTEGER, OUT DATE) IMMUTABLE
AS
$_$
SELECT ($1 + ($2 * '1 MONTH'::INTERVAL))::DATE;
$_$ LANGUAGE SQL;



I also tried code from SNODGRASS book
"Developing Time-Oriented Database Applications" but this causes error.

create table puhkus (palgus date, plopp date);

alter table puhkus add check
(NOT EXISTS ( SELECT *
FROM puhkus AS I1
WHERE 1 < (SELECT COUNT(*)
FROM puhkus AS I2
WHERE doverlaps(i1.palgus, i1.plopp, i2.palgus, i2.plopp)
) ));

but got error

ERROR: cannot use subquery in check constraint

On May 25, 2007, at 3:22 , Andrus wrote:

> CREATE TRIGGER puhkus_sequenced_trigger BEFORE INSERT OR UPDATE ON
> puhkus
>    FOR EACH ROW EXECUTE PROCEDURE puhkus_sequenced_pkey();

You can also use CREATE CONSTRAINT TRIGGER, which allows you to have
deferrable constraints. This is useful if statements within a
transaction may temporarily put the database in a state where it
violates the constraint: the constraint will be called at the end of
the transaction to make sure integrity is maintained.

http://www.postgresql.org/docs/8.2/interactive/sql-createconstraint.html

> create table puhkus (palgus date, plopp date);
>
> alter table puhkus add check
> (NOT EXISTS ( SELECT *
> FROM puhkus AS I1
> WHERE 1 < (SELECT COUNT(*)
> FROM puhkus AS I2
> WHERE doverlaps(i1.palgus, i1.plopp, i2.palgus, i2.plopp)
> ) ));
>
> but got error
>
> ERROR: cannot use subquery in check constraint

Right. As the error says, subqueries in CHECK constraints is not
implemented in PostgreSQL.


Michael Glaesemann
grzm seespotcode net

On Tue, 2007-05-29 at 10:59 -0500, Michael Glaesemann wrote:
> On May 25, 2007, at 3:22 , Andrus wrote:
>
> > CREATE TRIGGER puhkus_sequenced_trigger BEFORE INSERT OR UPDATE ON
> > puhkus
> >    FOR EACH ROW EXECUTE PROCEDURE puhkus_sequenced_pkey();
>
> You can also use CREATE CONSTRAINT TRIGGER, which allows you to have
> deferrable constraints. This is useful if statements within a
> transaction may temporarily put the database in a state where it
> violates the constraint: the constraint will be called at the end of
> the transaction to make sure integrity is maintained.
>
> http://www.postgresql.org/docs/8.2/interactive/sql-createconstraint.html
>

The docs say:

"CREATE CONSTRAINT TRIGGER is used within CREATE TABLE/ALTER TABLE and
by pg_dump to create the special triggers for referential integrity. It
is not intended for general use."

Is there harm in using constraint triggers outside of pg_dump? It seems
like deferrable constraints would be useful for many applications, but
the docs imply that it's a bad idea.

Regards,
    Jeff Davis

PS: Nice to meet you at PGCon. Hope you enjoyed it as much as I did!

Jeff Davis <pgsql@j-davis.com> writes:
> Is there harm in using constraint triggers outside of pg_dump? It seems
> like deferrable constraints would be useful for many applications, but
> the docs imply that it's a bad idea.

It's un-deprecated as of 8.3.

            regards, tom lane

On May 29, 2007, at 12:41 , Jeff Davis wrote:

> "CREATE CONSTRAINT TRIGGER is used within CREATE TABLE/ALTER TABLE and
> by pg_dump to create the special triggers for referential
> integrity. It
> is not intended for general use."
>
> Is there harm in using constraint triggers outside of pg_dump? It
> seems
> like deferrable constraints would be useful for many applications, but
> the docs imply that it's a bad idea.

It's a PostgreSQL extension, so it's nonstandard, but my
understanding is that it's safe to use and there are no current plans
to make it go away. As long as you understand what you're doing, I
don't know of any problems with using CREATE CONSTRAINT TRIGGER. I've
asked similar questions in the past, so you might want to check the
archives.

Michael Glaesemann
grzm seespotcode net


> PS: Nice to meet you at PGCon. Hope you enjoyed it as much as I did!

It was good to meet you too, Jeff! Hope to see you at the next
gathering :)