Thread: SPI-functions and transaction control

SPI-functions and transaction control

From
"Mikko Partio"
Date:
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

Re: SPI-functions and transaction control

From
Alvaro Herrera
Date:
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

Re: SPI-functions and transaction control

From
"Mikko Partio"
Date:


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