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

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
>