reliable lock inside stored procedure

From: Sebastian Böhm <psql(at)seb(dot)exse(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: reliable lock inside stored procedure
Date: 2008-11-03 09:29:55
Message-ID: 27682F23-349D-4728-AE95-68FB6D6FE881@seb.exse.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-sql

Hi,

I have a stored procedure and I need a SHARE ROW EXCLUSIVE lock in
this procedure (otherwise data will get corrupted).

According to the documentation the LOCK statement is useless and will
silently fail if not executed inside a transaction. (btw: this sounds
dangerous to me)

Also it is not possible to start a transaction inside a stored
procedure.

How can I make the code of this stored procedure safe?

is there a way to let the procedure fail with an error if not executed
within a transaction.

does adding a SAVEPOINT the trick?
documentation says that savepoints can only be established inside
transactions, but does it fail fatal enough so that the procedure
getss aborted? (more fatal than LOCK does?)

thank you very much.
/sebastian

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jens-Wolfhard Schicke 2008-11-03 09:32:07 Re: Where to point CommitFestOpen?
Previous Message Kurt Harriman 2008-11-03 09:28:55 pg_typeof() patch review

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Huxton 2008-11-03 11:06:50 Re: reliable lock inside stored procedure
Previous Message Bryce Nesbitt 2008-11-02 20:14:43 Re: Seq scan on join, not on subselect? analyze this