Re: Constraint problem

On Thu, 16 May 2002, Josh Berkus wrote:

>
> Stephan,
>
> > I unfortunately can't think of a good workaround. AFAICT, Unique should
> > wait until after all of the updates but our implementation doesn't
> > currently do so.
> >
> > Hopefully someone will have some ideas, because the best I can think of
> > requires an additional full table scan to check uniqueness and the
> > concurrency issues would be a bear.
>
> Hmm ... I can put up with that, I think. This table doesn't get modified very
> often, and I can afford to table-level lock it during modification.
>
> However, I can't think of how I could implement a BEFORE ... FOR EACH
> STATEMENT trigger that would check for duplicates in the final result. I can
> only deal with one row at a time using the NEW record.
>
> Suggestions?

AFAIK we don't actually have statement triggers right now, anyway, but...
Actually, I guess an after trigger that just checked the new values would
work as well, right? See if there exists more than one row with the new
key value. The after trigger should fire after all the changes are done
and in the worst case you could use constraint triggers and deferment.
The hard part about this really is making it work with concurrent
modifications, and if you're willing to forgo that, it shouldn't be too
bad.

This would degenerate in the case you're modifying a large percentage of
the table, since you'd be doing one indexscan per row modified and in
general you know that you'll need to do all of them (ie there was no
violation).

As a starting point, I did something like this:

create table az(a int);
create or replace function az_uni() returns opaque as '
DECLARE
r int;
BEGIN
SELECT INTO r count(*) FROM az where a=NEW.a;
if (r>1) then
RAISE EXCEPTION ''Uniqueness on az(a) violated.'';
END IF;
return NEW;
END;
' language 'plpgsql';
create trigger az_unit after insert or update on az for each row execute
procedure az_uni();

This still relies on the user doing a lock to get around the fact that it
won't handle concurrent changes, but in really simple tests (insert the
same value twice, do something like key=key+1 where key has sequential
values) in one session it worked.