Re: unique amount more than one table

On Tue, 2011-04-05 at 17:02 -0500, Perry Smith wrote:
> CREATE OR REPLACE FUNCTION unique_xxx ( ) RETURNS boolean AS $$
> SELECT ( SELECT max(cnt) FROM ( SELECT count(*) AS cnt FROM xxx GROUP BY name ) AS foo ) = 1;
> $$ LANGUAGE SQL;
>
> Next I added a check constraint with:
>
> ALTER TABLE table1 ADD CHECK ( unique_xxx() );

...

> After I insert a row that I want to be rejected, I can do:
>
> select unique_xxx();
> unique_xxx
> ------------
> f
> (1 row)
>
> but the insert was not rejected. I'm guessing because the check constraint runs before the insert?

Yes. But even if it ran afterward, there is still a potential race
condition, because the query in the CHECK constraint doesn't see the
results of concurrent transactions.

To make this work, you should be using LOCK TABLE inside of a trigger
(probably a BEFORE trigger that locks the table, then looks to see if
the value exists in the view already, and if so, throws an exception).
CHECK is not the right place for this kind of thing.

Keep in mind that the performance will not be very good, however. There
is not a good way to make this kind of constraint perform well,
unfortunately. But that may not be a problem in your case -- try it and
see if the performance is acceptable.

Regards,
Jeff Davis