Thread: error handling
Hello, I have a table with an UNIQUE constraint. Is it possible to have something like a trigger (or error handler) which is called every time a insert statement would break this constraint? Or in some simple cases that this record is just dropped silently, without reporting an error? Tanks in advance, Verena
On Thursday 27 April 2006 09:29 am, Verena Ruff <lists@triosolutions.at> thus communicated: --> Hello, --> --> I have a table with an UNIQUE constraint. Is it possible to have --> something like a trigger (or error handler) which is called every time --> a insert statement would break this constraint? Or in some simple cases --> that this record is just dropped silently, without reporting an error? --> --> Tanks in advance, --> Verena --> --> ---------------------------(end of broadcast)--------------------------- --> TIP 6: explain analyze is your friend --> I believe that a UNIQUE constraint will stop the insert before any trigger ever fires. Maybe someone else knows for sure.
> --> I have a table with an UNIQUE constraint. Is it possible to have > --> something like a trigger (or error handler) which is called every time > --> a insert statement would break this constraint? Or in some simple cases > --> that this record is just dropped silently, without reporting an error? > I believe that a UNIQUE constraint will stop the insert before any trigger > ever fires. Maybe someone else knows for sure. I do not know if there is an specific error handler built in postgresql (besides error-codes). If you break the unique constraint an error will be thrown. A workaround may be to launch a trigger BEFORE the insertion and do whatever is necessary within the trigger. http://www.postgresql.org/docs/8.1/static/sql-createtrigger.html -- Oscar
On Thu, Apr 27, 2006 at 15:29:07 +0200, Verena Ruff <lists@triosolutions.at> wrote: > Hello, > > I have a table with an UNIQUE constraint. Is it possible to have > something like a trigger (or error handler) which is called every time > a insert statement would break this constraint? Or in some simple cases > that this record is just dropped silently, without reporting an error? Unique constraints are not deferrable in Postgres. It would be possible to have a function do the insert and trap errors. I don't know if that will help in your situation though.
>From: Bruno Wolff III <bruno@wolff.to>
>To: Verena Ruff <lists@triosolutions.at>
>CC: pgsql-novice@postgresql.org
>Subject: Re: [NOVICE] error handling
>Date: Thu, 27 Apr 2006 14:48:03 -0500
>
>On Thu, Apr 27, 2006 at 15:29:07 +0200,
> Verena Ruff <lists@triosolutions.at> wrote:
> > Hello,
> >
> > I have a table with an UNIQUE constraint. Is it possible to have
> > something like a trigger (or error handler) which is called every time
> > a insert statement would break this constraint? Or in some simple cases
> > that this record is just dropped silently, without reporting an error?
>
>Unique constraints are not deferrable in Postgres. It would be possible to
>have a function do the insert and trap errors. I don't know if that will
>help in your situation though.
>
In the following code i show two ways to handle errors in Postgres. The
first one is the simple version, the second one is very usefull in client
side programming.
besides : if there is some interest on this topic I could write an article
about it ..
-------
-- error example
-------
DROP SCHEMA test CASCADE;
CREATE SCHEMA test;
CREATE TABLE test.ref(
id INTEGER NOT NULL PRIMARY KEY
);
CREATE TABLE test.test(
id SERIAL NOT NULL PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
ref_id INTEGER NOT NULL REFERENCES test.ref(id)
);
CREATE OR REPLACE FUNCTION test.check_on_insert() RETURNS TRIGGER AS $$
BEGIN
IF (NEW.name IS NULL) OR (CHAR_LENGTH(NEW.name) = 0) THEN -- check the
not null constaint
RAISE NOTICE 'name must not be null or empty'; -- only in the demo
version
RETURN NULL; -- don't insert
END IF;
IF EXISTS(SELECT * FROM test.test WHERE name = NEW.name) THEN -- check
the unique constaint
RAISE NOTICE 'value "%" not unique in table test',new.NAME; -- it's
a demo
RETURN NULL;
END IF;
IF NOT EXISTS(SELECT * FROM test.ref WHERE id = NEW.ref_id) THEN -- check
foreign constaint
RAISE NOTICE 'invalid ref_id "%"',NEW.ref_id; -- it's a demo
RETURN NULL;
END IF;
RETURN NEW;
END; $$
LANGUAGE plpgsql;
CREATE TRIGGER test_on_insert_trigger BEFORE INSERT ON test.test
FOR EACH ROW EXECUTE PROCEDURE test.check_on_insert();
INSERT INTO test.ref (id) VALUES(1);
INSERT INTO test.test(name,ref_id) VALUES('dummy',1); -- will work
INSERT INTO test.test(name,ref_id) VALUES(NULL,1);
INSERT INTO test.test(name,ref_id) VALUES('',1);
INSERT INTO test.test(name,ref_id) VALUES('dummy',1);
INSERT INTO test.test(name,ref_id) VALUES ('joe',1); -- will work
INSERT INTO test.test(name,ref_id) VALUES('mike',2);
SELECT * FROM test.test;
-- -----------------------
-- 'client friendly' version
-- -----------------------
DROP TRIGGER test_on_insert_trigger ON test.test;
DROP FUNCTION test.check_on_insert();
CREATE TABLE test.error(
id SERIAL NOT NULL,
message TEXT
);
INSERT INTO test.error(id,message) VALUES(-1,'column "name" > value must not
be NULL or empty');
INSERT INTO test.error(id,message) VALUES(-2,'column "name" > value must be
UNIQUE');
INSERT INTO test.error(id,message) VALUES(-3,'column "ref_id" > invalid
reference');
-- check function
CREATE OR REPLACE FUNCTION test.client_check_on_insert(pname TEXT,pref_id
INTEGER) RETURNS INTEGER AS $$
BEGIN
IF (pname IS NULL) OR (CHAR_LENGTH(pname) = 0) THEN -- check the not null
constaint
RETURN -1;
END IF;
IF EXISTS(SELECT * FROM test.test WHERE name = pname) THEN -- check the
unique constaint
RETURN -2;
END IF;
IF NOT EXISTS(SELECT * FROM test.ref WHERE id = pref_id) THEN -- check
foreign constaint
RETURN -3;
END IF;
RETURN 0;
END; $$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION test.check_on_insert() RETURNS TRIGGER AS $$
DECLARE ret INTEGER;
BEGIN
ret := test.client_check_on_insert(NEW.name,NEW.ref_id);
IF ret <> 0 THEN
RAISE EXCEPTION '# %',ret; -- mark these errors with "#"
END IF;
RETURN NEW;
END; $$
LANGUAGE plpgsql;
CREATE TRIGGER test_on_insert_trigger BEFORE INSERT ON test.test
FOR EACH ROW EXECUTE PROCEDURE test.check_on_insert();
INSERT INTO test.test(name,ref_id) VALUES('hans',1); -- will work
INSERT INTO test.test(name,ref_id) VALUES(NULL,1);
INSERT INTO test.test(name,ref_id) VALUES('',1);
INSERT INTO test.test(name,ref_id) VALUES('joe',1);
INSERT INTO test.test(name,ref_id) VALUES('mike',2);
/*
you can now catch the error in a client program for example (java)
...
try {
stmt.execute("INSERT INTO test.test(name,ref_id) VALUES('blabla',23)");
}
catch (SQLException e) {
if (e.SQLState.equals("P0001")) return parseError(e.SWLErrorMessage);
.. otherwise
}
*/
Hello, sorry for answering so late, ... > I do not know if there is an specific error handler built in postgresql (besides error-codes). > > If you break the unique constraint an error will be thrown. A > workaround may be to launch a trigger BEFORE the insertion and do whatever is necessary within the trigger. > This is possibillity I was thinking of, too. But if there are many inserts and (probably) only a few of them would break the unique constraint, there would be a great overhead. This was what I'd like to avoid with a custom error handler. Regards, Verena
On 5/10/06 9:35 AM, "Verena Ruff" <lists@triosolutions.at> wrote: > Hello, > > sorry for answering so late, ... >> I do not know if there is an specific error handler built in postgresql >> (besides error-codes). >> >> If you break the unique constraint an error will be thrown. A >> workaround may be to launch a trigger BEFORE the insertion and do whatever is >> necessary within the trigger. >> > This is possibillity I was thinking of, too. But if there are many > inserts and (probably) only a few of them would break the unique > constraint, there would be a great overhead. This was what I'd like to > avoid with a custom error handler. Probably the simplest way to do this is to load the data into a temporary table without the unique constraint then use SQL to insert a "clean" version into the new table. Alternatively, you could use savepoints. If an insert fails, just rollback to that savepoint. If it succeeds, commit that savepoint. Sean
Hi, Sean Davis schrieb: > Probably the simplest way to do this is to load the data into a temporary > table without the unique constraint then use SQL to insert a "clean" version > into the new table. Alternatively, you could use savepoints. If an insert > fails, just rollback to that savepoint. If it succeeds, commit that > savepoint. > But isn't that more overhead than using the trigger? And while inserting into the clean table, I would have to test if the value is existing or not. I don't see the advantage of this compared to the script which was posted a few weeks ago. Regards, Verena
On 5/10/06 9:09 AM, "Verena Ruff" <lists@triosolutions.at> wrote: > Hi, > > Sean Davis schrieb: >> Probably the simplest way to do this is to load the data into a temporary >> table without the unique constraint then use SQL to insert a "clean" version >> into the new table. Alternatively, you could use savepoints. If an insert >> fails, just rollback to that savepoint. If it succeeds, commit that >> savepoint. >> > But isn't that more overhead than using the trigger? And while inserting > into the clean table, I would have to test if the value is existing or > not. If you are copying bulk data into the table only once, then cleaning the data up front will not impact your actual use down the road. If you are saying that you will be inserting non-unique values and need to catch that, a trigger is the better way to go. As for testing if the value is existing or not, you can avoid that by using SQL functions (like the postgresql specific distinct on) to select from the temporary table only those values that are unique. See here in the docs: http://www.postgresql.org/docs/8.1/interactive/sql-select.html#SQL-DISTINCT > I don't see the advantage of this compared to the script which was > posted a few weeks ago. I should have pointed out that the solution depends on your needs. If you don't see an advantage, it is likely because there isn't one for your particular needs, so feel free to use some other option. Sean
Hi, Sean Davis schrieb: >> If you are copying bulk data into the table only once, then cleaning the >> data up front will not impact your actual use down the road. If you are >> saying that you will be inserting non-unique values and need to catch that, >> a trigger is the better way to go. This is what I need to do. >> As for testing if the value is existing >> or not, you can avoid that by using SQL functions (like the postgresql >> specific distinct on) to select from the temporary table only those values >> that are unique. See here in the docs: >> >> http://www.postgresql.org/docs/8.1/interactive/sql-select.html#SQL-DISTINCT >> OK, I forgot about DISTINCT. > I should have pointed out that the solution depends on your needs. If you > don't see an advantage, it is likely because there isn't one for your > particular needs, so feel free to use some other option Thanks for your hints. In my situation (many inserts and only a few would break the unique clause) I think using a trigger is the way to get a better performance. Regards, Verena
On 5/10/06 8:51 AM, "Verena Ruff" <lists@triosolutions.at> wrote: > Hi, > > Sean Davis schrieb: > >>> If you are copying bulk data into the table only once, then cleaning the >>> data up front will not impact your actual use down the road. If you are >>> saying that you will be inserting non-unique values and need to catch that, >>> a trigger is the better way to go. > This is what I need to do. >>> As for testing if the value is existing >>> or not, you can avoid that by using SQL functions (like the postgresql >>> specific distinct on) to select from the temporary table only those values >>> that are unique. See here in the docs: >>> >>> http://www.postgresql.org/docs/8.1/interactive/sql-select.html#SQL-DISTINCT >>> > OK, I forgot about DISTINCT. >> I should have pointed out that the solution depends on your needs. If you >> don't see an advantage, it is likely because there isn't one for your >> particular needs, so feel free to use some other option > Thanks for your hints. In my situation (many inserts and only a few > would break the unique clause) I think using a trigger is the way to get > a better performance. Just keep in mind that the trigger runs on EVERY insert, even those for which the unique clause is not violated. If that is the behavior you need, then use the trigger. However, if you know that after you have clean data in the table, you will not be inserting "duplicates" (I think this is the typical case), then a trigger may not be the way to go. Sean
Sean Davis schrieb: > Just keep in mind that the trigger runs on EVERY insert, even those for > which the unique clause is not violated. If that is the behavior you need, > then use the trigger. However, if you know that after you have clean data > in the table, you will not be inserting "duplicates" (I think this is the > typical case), then a trigger may not be the way to go. > These were my concerns in the first place I was thinking about using triggers. Maybe I haven't really understood your suggestion right. When would the temporary table be created? Isn't it neccessary to create it on every insert, too? With wich statement could I do this without using a trigger which is fired on every insert? Thanks for your patience. Regards, Verena
Verena Ruff wrote: > Sean Davis schrieb: > >> Just keep in mind that the trigger runs on EVERY insert, even those for >> which the unique clause is not violated. If that is the behavior you >> need, >> then use the trigger. However, if you know that after you have clean >> data >> in the table, you will not be inserting "duplicates" (I think this is the >> typical case), then a trigger may not be the way to go. >> > > These were my concerns in the first place I was thinking about using > triggers. Maybe I haven't really understood your suggestion right. When > would the temporary table be created? Isn't it neccessary to create it > on every insert, too? With wich statement could I do this without using > a trigger which is fired on every insert? > > Thanks for your patience. No problem. I was thinking that you had a bunch of data that you wanted to load ONCE, clean up, and then you would NOT be inserting duplicated values. If you are going to be inserting duplicates potentially with every insert, I think that a trigger is the only way to go on the DB side of things. Of course, you could do things on the client side, as well (do a lookup, find nothing--do insert, find something--do nothing or do update). Sean
Hi, Sean Davis schrieb: > No problem. I was thinking that you had a bunch of data that you > wanted to load ONCE, clean up, and then you would NOT be inserting > duplicated values. If you are going to be inserting duplicates > potentially with every insert, I think that a trigger is the only way > to go on the DB side of things. Of course, you could do things on the > client side, as well (do a lookup, find nothing--do insert, find > something--do nothing or do update). Thanks for your opinion. Then I'll go with a trigger. Regards, Verena