Functions and Transactions , need workaround

[amiracam]

Hi. Newbie and first post. We are evaluating porting to Postgres and have got past a lot of different nuances but are now stuck on what seems to be a show stopper.

A little context first. We by policy and by requirement by most of our customers can only update the database via stored procedures. Apparently, with PostgreSQL there is no way defining transaction boundaries pro grammatically in stored procedures i.e. functions unlike other rdms technologies. We can live with that for most things. Unfortunately, we have a tremendous number of batch programs written in C which engage stored procedures. Due to the nature of our batch processes we need to control the transaction boundaries i.e. explicitly mark where we are beginning work and explictly commit work i.e. we cannot have intermediate partial commits of the batch stream. Wither the batch stream totally fails and the backend remains unchanged or it is successful and the backend accordingly updated.

So either I need to somehow change this default policy of PostgreSQL functions being automatically wrapped in a transaction or possibly I need some other callable code module in Postgres that is not auto-wrapped in a transaction. I don't think such an animal exists.

Any help is greatly appreciated. thanks

Charles

 

[ceco]

maybe safe points can do the thing

 

[ConradIrwin]

Could you reimplement your batch files as internal functions? Thus calling one complicated function with arguments that returns the final results - do a google for "postgresql C function