On Feb 19, 2008 12:12 AM, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Mikko Partio escribió: > > > 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. Regards Mikko
