Transactions, Triggers and Error Messages - Mailing list pgsql-general

From Ledina Hido
Subject Transactions, Triggers and Error Messages
Date
Msg-id 6FFE9B49-DB49-49B1-B0D1-C92561792007@ecs.soton.ac.uk
Whole thread Raw
Responses Re: Transactions, Triggers and Error Messages
List pgsql-general

Hi all,

Sorry for the trouble but I am trying to use triggers inside transactions to perform some multiplicity checking on the data inserted onto the tables but I am having some problems retrieving the error message.

I have two tables declared as follows:

>>>>>>>>>>>>>>>>>>>>>
create table pers (
   pid int not null primary key,
   pname text not null
);

create table tasks (
   taskid serial not null primary key,
   pid int not null constraint tasks__ref_p references pers deferrable,
   task text not null
);
>>>>>>>>>>>>>>>>>>>>>


My function and trigger are as below:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
create function check_mult() returns trigger as $$
declare
   cnt integer := 0;
begin
   select count(*) into cnt from tasks where pid=new.pid;
   if cnt>2 then
      raise exception '3 tasks already exists for person with pid %', new.pid;
   end if;
   return new;
end;
$$ language plpgsql;

create trigger ass_mult before insert or update on tasks for each row execute procedure check_mult();
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Now when I run the following (after inserting person with id=5 into pers table):


>>>>>>>>>>>>>>>>>>
begin;
set constraints all deferred;
insert into tasks (pid, task) values (5, 'firstTask');
insert into tasks (pid, task) values (5, 'secondTask');
insert into tasks (pid, task) values (5, 'thirdTask');
insert into tasks (pid, task) values (5, 'forthTask');
insert into tasks (pid, task) values (5, 'fifthTask');
commit;
>>>>>>>>>>>>>>>>>>


the transaction is correctly aborted but the error message I get is not the one that raised the exception ie "3 tasks already exists for person with pid 5" but the one from trying to insert the fifth task (ie last statement) which is "ERROR:  current transaction is aborted, commands ignored until end of transaction block"

Is there anyway I can stop the transaction after the exception is raised so that I can retrieve the correct error message?

I know rollbacks inside triggers are simply ignored (I tried that) but I thought maybe there was another way.

Thank you in advance for any help,
Ledina

PS My transactions have to be deferred by that shouldn't make a difference

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: [HACKERS] Troubles with array_ref
Next
From: Tom Lane
Date:
Subject: Re: Transactions, Triggers and Error Messages