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

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

=?ISO-8859-1?Q?Sebastian_B=F6hm?= <psql@seb.exse.net> writes:

Am 03.11.2008 um 12:06 schrieb Richard Huxton:

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.

That's a good point. We throw error for DECLARE CURSOR outside a
transaction block, since it's obviously a mistake. I wonder whether
we shouldn't equally throw error for LOCK outside a transaction block.

I can sort of imagine some corner cases where
lock-and-immediately-release would be the intended behavior, but that
sure seems a whole lot less probable than it being user error.
And you could always throw BEGIN/COMMIT into the command if that
really was what you wanted.

Objections anyone?

regards, tom lane

That's a good point. We throw error for DECLARE CURSOR outside a
transaction block, since it's obviously a mistake. I wonder whether
we shouldn't equally throw error for LOCK outside a transaction block.

I can sort of imagine some corner cases where
lock-and-immediately-release would be the intended behavior, but that
sure seems a whole lot less probable than it being user error.
And you could always throw BEGIN/COMMIT into the command if that
really was what you wanted.

Objections anyone?

No, I've been bitten by this myself.

...Robert

"Robert Haas" <robertmhaas@gmail.com> writes:

That's a good point. We throw error for DECLARE CURSOR outside a
transaction block, since it's obviously a mistake. I wonder whether
we shouldn't equally throw error for LOCK outside a transaction block.

Objections anyone?

No, I've been bitten by this myself.

OK, done in CVS HEAD.

regards, tom lane