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