Re: reliable lock inside stored procedure - Mailing list pgsql-sql

From Richard Huxton
Subject Re: reliable lock inside stored procedure
Date
Msg-id 490EDB4A.90204@archonet.com
Whole thread Raw
In response to reliable lock inside stored procedure  (Sebastian Böhm <psql@seb.exse.net>)
Responses Re: reliable lock inside stored procedure (SOLVED)
List pgsql-sql
Sebastian Böhm wrote:
> Hi,
>
> I have a stored procedure and I need a SHARE ROW EXCLUSIVE lock in this
> procedure (otherwise data will get corrupted).

OK. PostgreSQL doesn't have "stored procedures" so I guess you're
talking about a function.

> 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)

I'm not sure what you mean here, and I don't think you've understood the
documentation. It's not possible to have a LOCK statement outside of a
transaction. It's just not meaningful to have a transaction that only
has a LOCK statement in it.

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

All functions execute within a transaction. As do all other SELECT,
UPDATE, INSERT, DELETE, CREATE TABLE etc. If you (or your client
library) do not supply a BEGIN then the command is treated as though
BEGIN...COMMIT surrounded it. You can't have nested transactions because
the COMMIT of a subtransaction wouldn't be meaningful. You can use
SAVEPOINTS to roll back to a known point.

> 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.

You can't execute outside of a transaction. It's not possible.

> 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?)

I'm not sure I understand what you mean here.

--  Richard Huxton Archonet Ltd


pgsql-sql by date:

Previous
From: Sebastian Böhm
Date:
Subject: reliable lock inside stored procedure
Next
From: Sebastian Böhm
Date:
Subject: Re: reliable lock inside stored procedure (SOLVED)