Re: SPI-functions and transaction control

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.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support