Re: SPI-functions and transaction control

["Mikko Partio" <mpartio@xxxxxxxxx>]

On Feb 19, 2008 12:12 AM, Alvaro Herrera <alvherre@xxxxxxxxxxxxxxxxx> 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