Deadlocks with plpgsql

From: Tim McAuley <tech-lists(at)mothy(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Deadlocks with plpgsql
Date: 2003-12-17 17:27:09
Message-ID: 3FE091ED.6030509@mothy.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Stephan Szabo 2003-12-17 17:27:26 Re: Need to understand PL/PGSQL, Foreign Key Deferable,
Previous Message Rich Hall 2003-12-17 16:52:33 Re: Any commercial shopping cart packages using