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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
