Re: [HACKERS] Trigger aborted on error

On Sun, 31 Oct 1999, Jan Wieck wrote:

> >
> > Hi.
> > I've such problem :
> > I table with primary key and in trigger try to insert into this table data
> > wich violate constrain (not uniq). When ectually executing SPI_execp I got
> > a message "ERROR: cannot insert a duplicate key into a unique index" and
> > trigger executing is aborted. What should I do in order to get this error
> > as a result from SPI_execp and continue trigger execution?
> >
> > Thanks in advance,
> > Andriy Korud, Lviv, Ukraine
> 
>     No  chance,  ERROR  messages cannot be caught in any way by a
>     trigger.  They abort the entire transaction.
> 
>     The only possibility you have is to check via SELECT prior to
>     the  INSERT.  Unfortunately you would need an exclusive table
>     lock to avoid race conditions.
> 
> 
> Jan
> 
Let's make another question: Is there some way to insert uniq data into
table without first cheking using SELECT. Because this table contain >1M
records and SELECT on it is very slow. If there is no way of doing it I
should consider moving from Postgres to other database :(

Andriy Korud