On Tue, Aug 03, 2004 at 10:17:14AM -0400, Tom Lane wrote: > Gavin Sherry <[EMAIL PROTECTED]> writes: > > > BEGIN; > > SAVEPOINT start; > > INSERT INTO users VALUES(user || suffix); > > EXIT; > > EXCEPTION > > WHEN UNIQUE_VIOLATION THEN > > ROLLBACK TO start; > > suffix := suffix + 1; > > END; > > Right. Essentially, our implementation is supplying the SAVEPOINT and > ROLLBACK TO commands implicitly as part of any block with an EXCEPTION > clause. When we get around to updating the "Oracle porting" guide in > the plpgsql docs, this will need to be clearly explained. > > Depending on how tense you want to be about Oracle compatibility, we > could make people actually write their blocks as above --- that is, > the SAVEPOINT and ROLLBACK commands would be a required part of the > exception-block syntax. They wouldn't actually *do* anything, but > they would make the code look more like its Oracle equivalent. I'm not > for this, but maybe someone wants to make the case for it? > > regards, tom lane If it's not difficult it would probably be good to allow for handling the rollback yourself. In this example it wouldn't matter; the row triggering an error won't be inserted. But if you were inserting data from a multi-row source such as a temporary table it would make a difference.
By the way, while I know Oracle won't abort the transaction, they might rollback whatever work the command that failed had done; I'm not really sure how that's handled. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
