Re: Strange behavior of TRIGGER

On Mon, 2007-10-01 at 23:19 +0600, Nurlan Mukhanov wrote:
> There is a table with unique rows. But before insert trigger checks
> data and returns NULL if such record exist and NEW if not.
> 
> But from time to time I'm getting an error in my log file
> 
> faled query: INSERT INTO viewed_members (member_id, viewed_id) VALUES
> ('93701','41719')
> context: ERROR: duplicate key violates unique constraint "viewed_search_members"
> 

The trigger you wrote does not lock the table. A concurrent transaction
might insert a record with the same (member_id, viewed_id) that might
not yet be visible at the time of your trigger's SELECT.

To do this kind of thing safely, your trigger needs to lock the table
against writes before the SELECT operation.

That has a performance penalty, of course. What you should probably do
instead is just rely on the unique index to report an error. If you
don't want an error, you should catch the error in pl/pgsql as described
here:

http://www.postgresql.org/docs/current/static/plpgsql-control-
structures.html#PLPGSQL-ERROR-TRAPPING

That will perform better and allow you to disregard records without a
unique (member_id, viewed_id).

Regards,
	Jeff Davis