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


31 Oct 1999 21:34:23 +0200
"Andrij Korud" <akorud@polynet.lviv.ua>
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

    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) #



Sun, 31 Oct 1999 22:32:51 +0100 (MET)
wieck@debis.com (Jan Wieck)
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
 


1 Nov 1999 09:03:56 +0200
"Andrij Korud" <akorud@polynet.lviv.ua>
Re: [HACKERS] Trigger aborted on error
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.

Mon, 1 Nov 1999 06:55:16 -0500 (EST)
"D'Arcy" "J.M." Cain <darcy@druid.net>
Re: [HACKERS] Trigger aborted on error