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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
