Re: reliable lock inside stored procedure

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