Transactions, Triggers and Error Messages
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
Ledina Hido <lh1101@ecs.soton.ac.uk> writes:
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"
What are you running this in? ISTM this is a problem with bad structure
of client-side code, not something to be fixed on the server side.
regards, tom lane
Quoting Tom Lane <tgl@sss.pgh.pa.us>:
What are you running this in? ISTM this is a problem with bad
structure
of client-side code, not something to be fixed on the server side.
regards, tom lane
I'm using pgAdmin3. Basically when I run the query the first time it
gives the correct error but if I re-run it, then it says "ERROR:
current transaction is aborted, commands ignored until end of
transaction block". Should I be doing something else, ie should I be
explicitly rolling back once the exception is raised so I don't get
this error? And if so how can I "catch" the exception. Sorry for my
ignorance :(
Import Notes
Resolved by subject fallback
On Tue, Nov 08, 2005 at 11:03:50PM +0000, Ledina Hido wrote:
Quoting Tom Lane <tgl@sss.pgh.pa.us>:
What are you running this in? ISTM this is a problem with bad
structure
of client-side code, not something to be fixed on the server side.
regards, tom lane
I'm using pgAdmin3. Basically when I run the query the first time it
gives the correct error but if I re-run it, then it says "ERROR:
current transaction is aborted, commands ignored until end of
transaction block". Should I be doing something else, ie should I be
explicitly rolling back once the exception is raised so I don't get
Yes.
this error? And if so how can I "catch" the exception. Sorry for my
http://lnk.nu/postgresql.org/5sl.html
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
That's great. Thank you very much for you help.
Ledina
On Tue, Nov 08, 2005 at 11:03:50PM +0000, Ledina Hido wrote:
Quoting Tom Lane <tgl@sss.pgh.pa.us>:
What are you running this in? ISTM this is a problem with bad
structure
of client-side code, not something to be fixed on the server side.
regards, tom lane
I'm using pgAdmin3. Basically when I run the query the first time it
gives the correct error but if I re-run it, then it says "ERROR:
current transaction is aborted, commands ignored until end of
transaction block". Should I be doing something else, ie should I be
explicitly rolling back once the exception is raised so I don't get
Yes.
this error? And if so how can I "catch" the exception. Sorry for my
Import Notes
Resolved by subject fallback
On Tue, Nov 08, 2005 at 11:03:50PM +0000, Ledina Hido wrote:
Quoting Tom Lane <tgl@sss.pgh.pa.us>:
What are you running this in? ISTM this is a problem with bad
structure
of client-side code, not something to be fixed on the server side.
regards, tom lane
I'm using pgAdmin3. Basically when I run the query the first time it
gives the correct error but if I re-run it, then it says "ERROR:
current transaction is aborted, commands ignored until end of
transaction block". Should I be doing something else, ie should I be
explicitly rolling back once the exception is raised so I don't getYes.
this error? And if so how can I "catch" the exception. Sorry for my
Thinking about it, the EXCEPTION statement would be inside my user-
defined function (where I raise the exception in the first place), so
I cannot see how that would help. As far as I could understand, I
cannot call "ROLLBACK" (which is what I want to do) inside a user
defined function. I tried calling it and it was simply ignored. Or am
I missing something here?
Ledina Hido wrote:
Thinking about it, the EXCEPTION statement would be inside my user-
defined function (where I raise the exception in the first place), so I
cannot see how that would help. As far as I could understand, I cannot
call "ROLLBACK" (which is what I want to do) inside a user defined
function. I tried calling it and it was simply ignored. Or am I missing
something here?
Yes - you want to read up on SAVEPOINTs to handle exceptions at the
applicaton level. You do something like:
SAVEPOINT foo;
...command that works...
...command that works...
...oops, this one gives me an error...
ROLLBACK TO SAVEPOINT foo;
Exceptions in plpgsql are just a wrapper to this process.
--
Richard Huxton
Archonet Ltd