Hi Richard,
thank you for your answer!
Am 03.11.2008 um 12:06 schrieb Richard Huxton:
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.
yes
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.
as postgres does not warn you about this, this may lead to not so easy
to spot bugs.
If you forget to start a transaction and assume that you got a lock
while modifieing a table, you can corrupt data.
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.
so a call to a function is ALLWAYS a transaction ? good then I have no
problem...
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.
I assumed that a function can be executed without a transaction,
means: every statement in the function is its own transaction. I
understood that this is not the case.
As SAVEPOINTS failes outside of a transaction I could then be used to
detect wether there is a transaction already started or not.
Imagine that you have a function in your code (not a postgres-
function, but a C function) and this functions issues some statements
that may corrupt data if no transaction is already created.
You may then use SAVEPOINT in that c-function to make sure that there
is a transaction started, as SAVEPOINT creates an exception if no
transaction was started.
/sebastian
--
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
