> Now, I was wondering if a c function would be faster, and with the help of > the manual I have written a function that can insert tuples from one table > to another. As the manual states ( > http://www.postgresql.org/docs/8.3/interactive/spi.html), there is no way to > catch the constraint violation error with SPI though. The manual still > mentions that there is an undocumented way of doing this, has anybody ever > done this? I was looking the 'exception' code at > src/pl/plpgsql/src/pl_exec.c but it's quite frightening since I really don't > have that much experience in c :)
I think you should try DefineSavepoint, RollbackToSavepoint and ReleaseSavepoint. Take a close look at plpgsql's callers for those functions, because I don't think it's all that straightforward. OTOH you also need some PG_TRY blocks. There are many fine details here, perhaps too many if you're not battered enough in C.
I think I would suggest trying to do it purely with SQL, temp tables, etc. If you can afford a table lock, it could be a lot easier and faster than setting a savepoint per tuple.
The import is done with a temp table, the bottleneck being the copying of rows from the temp table to the actual table.
I managed to solve the issue with PG_TRY blocks (idea copied from http://archives.postgresql.org/pgsql-hackers/2006-02/msg00836.php). As you said, I'm not battered with c so I guess not all the cornercases are handled but for now it seems to work ok. The c-version of the function is in fact 5 times faster than the original plpgsql version.
