hi, can i use savepoints to realize something like "on error resume next"? i've got the following situation: begin; 1. create view user001.accounts as select * from base.accounts; 2. grant select on user001.accounts to loginuser001; commit; begin; 3. create view user002.accounts as select * from base.accounts; 4. grant select on user002.accounts to loginuser002; commit; my goal is to avoid execution stop, if one of the transactions fail. let's say line 1 throws an error it should go further to line 3. any ideas? thank you. jasmin
AFAIK it's not possible. A transaction is kind of a container with a positive or negative result. If one of the queries fails in between a transaction, it will be rolled back after a commit. What you can do with savepoints is the following:
usage=# CREATE TABLE test (id serial, content text); usage=# BEGIN; usage=# INSERT INTO test (content) VALUES ('first stuff'); usage=# SAVEPOINT s1; usage=# INSERT INTO test (content) VALUES (); ERROR: syntax error at or near ")" usage=# ROLLBACK TO SAVEPOINT s1; ROLLBACK usage=# SELECT * FROM test; id | content ----+-------------- 1 | first stuff (1 row)
usage=# COMMIT; COMMIT usage=# SELECT * FROM test; id | content ----+-------------- 1 | first stuff (1 row)
The second INSERT statement fails. If you would go further with insert statements and then fire a COMMIT at the end, nothing would be inserted into the table. But if you fire a ROLLBACK TO SAVEPOINT s1, at least the data of the first INSERT statement are written.
So maybe this is a start help for creating some logic to get something like 'on error resume next'.