Hi,
I have this mammoth stored procedure that can be called by our
application. The aim of it is to perform maintenance operations on user
information and it SHOULDN'T be called too often... but it may be under
certain conditions.
I was load testing our application and came across deadlocks being
generated by this stored procedure. I then ran some load tests against
the database/stored procedure itself and got even more.
From what I've read, plpgsql stored procedures do not have transaction
control, or rather run in a single transaction. Is there anyway to fine
grain this control a little? I'd prefer to keep all the logic of the
operation on the database and only have our application run a single
database call rather than multiple needless (but possibly transaction
controlled) calls.
So, in brief:
- Is plpgsql the best procedural language to use for large/long database
operations or is there a better alternative?
- Can plpgsql procedures be split up (transaction wise)?
- Any good documentation about advanced stored procedures?
Currently using 7.4.
Many thanks and sorry for being a bit vague.
;-)
Tim