Thread: Trigger aborted on error
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
>
> 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
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
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
Thus spake Andrij Korud
> > 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.
> >
> 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 :(
Have you put an index on the field in question? It shouldn't matter how
many records you have if you do. If you don't, no other database will
help you any better.
The following declaration will create the field, give it the default
and put a unique index on it. How are you declaring the field now?
CREATE TABLE t (pk SERIAL PRIMARY KEY, ...
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.