Thread: SPI-functions and transaction control

Hello list

I am trying to write a function in c that would 'merge' two tables together.

The idea is that we insert rows from one table to another, and if there is a constraint violation, update the old row with the new row. I have done this succesfully with plpgsql, but alas, the tables are so big that it takes quite some time for the function to finish.

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 :)

Regards

Mikko

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