Re: [HACKERS] Trigger aborted on error
Re: [HACKERS] Trigger aborted on error
От:
wieck@debis.com (Jan Wieck)
Дата:
>
> 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) #Re: [HACKERS] Trigger aborted on error
От:
"D'Arcy" "J.M." Cain <darcy@druid.net>
Дата:
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 | 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.
Trigger aborted on error
От:
"Andrij Korud" <akorud@polynet.lviv.ua>
Дата:
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
Re: [HACKERS] Trigger aborted on error
От:
"Andrij Korud" <akorud@polynet.lviv.ua>
Дата:
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
