Thread: CHECK constraint and trigger

Hi,

I have one table where I want one column ("canceled_date") to be updated by a trigger function whenever the boolean
"canceled"column is set to true. The problem is that I also want to have a CHECK constraint defined on that table that
makessure no overlapping bookings exist, and the trigger works fine until I add the chk_not_overlaps contraint (see
below).

=> insert into booking(customer, resource, start_time, end_time)
values (1,1,'2006-12-01','2006-12-02');

Query returned successfully: 1 rows affected, 20 ms execution time.

=> update booking set canceled=true where id=1;

ERROR: new row for relation "booking" violates check constraint "chk_not_overlaps"
SQL state: 23514

=> alter table booking drop constraint chk_not_overlaps;

Query returned successfully: 1 rows affected, 20 ms execution time.

=> update booking set canceled=true where id=1;

Query returned successfully: 1 rows affected, 20 ms execution time.

I have tried altering the trigger event (before/after insert or update) without success. Any ideas?

Pg version is 8.2b for Windows (PostgreSQL 8.2beta2 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special))

----------------------------------------------------------------------------------------
create table booking
(
    id bigserial,
    customer integer not null,
    resource integer not null,
    added_date timestamp not null default now(),
    canceled boolean not null default false,
    canceled_date timestamp,
    start_time timestamp not null,
    end_time timestamp not null,
    payment_recieved boolean not null default false,
    pickup_time timestamp,
    return_time timestamp,
    constraint pk_booking primary key (id)
);

create or replace function not_overlaps(bigint, timestamp, timestamp) returns boolean as
'select
    case when sum(id) > 0 then
        false
    else
        true
    end
from booking
where resource = $1
and (start_time, end_time) overlaps ($2, $3)
and canceled = false
group by resource'
language sql;

alter table booking
    add constraint chk_not_overlaps check (not_overlaps(resource, start_time, end_time));

create or replace function tf_set_canceled_date() returns trigger as
$$
declare
begin
    if NEW.canceled=true then
        NEW.canceled_date = now();
    end if;
    return NEW;
end;
$$
language plpgsql;

create trigger tgr_booking_modified
    before insert or update on booking
    for each row execute procedure tf_set_canceled_date();

----------------------------------------------------------------------------------------


Koppla av och tjäna pengar på din skicklighet på Spray Spel! http://www.spray.se/underhallning/spel/

"Mikael Carneholm" <carniz@spray.se> writes:
> create or replace function not_overlaps(bigint, timestamp, timestamp) returns boolean as
> 'select
>     case when sum(id) > 0 then
>         false
>     else
>         true
>     end
> from booking
> where resource = $1
> and (start_time, end_time) overlaps ($2, $3)
> and canceled = false
> group by resource'
> language sql;

> alter table booking
>     add constraint chk_not_overlaps check (not_overlaps(resource, start_time, end_time));

This really isn't going to work as-is, because the check constraint is
evaluated before the actual row update occurs.  This means that the
existing row (with canceled = false) is found by the SQL query --- so
*any* update on a canceled = false row is going to fail, except perhaps
one that updates both start_time and end_time in such a way that they
don't overlap the previous version of the row.  You'd need to fix the
query to exclude the specific row being checked --- perhaps pass in the
row's id so you can do that.

Also, why are you checking sum(id) rather than count(*), and what's the
point of the GROUP BY?  In fact, all you really care about is existence
of at least one conflicting row, so the right way to code this is along
the lines of

select not exists(select 1 from booking
  where id != $1 and
    resource = $2 and
    (start_time, end_time) overlaps ($3, $4)
    and not canceled)

            regards, tom lane

> This really isn't going to work as-is, because the check constraint is
> evaluated before the actual row update occurs.  This means that the
> existing row (with canceled = false) is found by the SQL query --- so
> *any* update on a canceled = false row is going to fail, except perhaps
> one that updates both start_time and end_time in such a way that they
> don't overlap the previous version of the row.  You'd need to fix the
> query to exclude the specific row being checked --- perhaps pass in the
> row's id so you can do that.

Ok. But a DEFERABLE constraint would fix this, right? (if it was possible for CHECK
constraints)

> Also, why are you checking sum(id) rather than count(*), and what's the
> point of the GROUP BY?  In fact, all you really care about is existence

I just thought that a sum() would be faster than a count(*) (don't ask me why...), and the GROUP
BY is there to make sure only one tuple is returned (more than one row
could be returned by the overlaps())

> select not exists(select 1 from booking
>   where id != $1 and
>     resource = $2 and
>     (start_time, end_time) overlaps ($3, $4)
>     and not canceled)
>

That works perfectly - thanks!

Regards,
Mikael


Nätets roligaste filmer hittar du på Spray Crazy. http://crazy.spray.se/