Re: SPI-functions and transaction control

On Feb 19, 2008 12:12 AM, Alvaro Herrera <alvherre(at)commandprompt(dot)com> 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