[GENERAL] Locking rows

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

[GENERAL] Locking rows

gabriele zelasco
Hi.I'm using postgresql 8.0.3 under win2000 and developing with VS2003 (npgsql net provider).
I would like to start a transaction with a sql function.
When user press "edit" button on my form, i would lock the current row.
After user has modified data on form, pressing "save" button I would save the modified row by sql update function and so commit.
Well.The update function works fine.
My problem is to lock the current row.
My code is something like:

CREATE OR REPLACE FUNCTION "public"."new_function" () RETURNS SETOF "public"."table" AS
$body$
begin;
select * from table where field = 'value' for update;
$body$
LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;


But I get :

ERROR , BEGIN IS NOT ALLOWED IN SQL FUNCTION

What's wrong?
Please tell me if it's a syntax problem
maybe have I to use pgsql ?
How can I lock row in pgsql ?
Thanks in advance.

Internal Virus Database is out-of-date.
Checked by AVG Anti-Virus.
Version: 7.0.289 / Virus Database: 266.11.11 - Release Date: 16/05/2005


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] Locking rows

Tom Lane-2
"gabriele zelasco" <[hidden email]> writes:
> I would like to start a transaction with a sql function.
> When user press "edit" button on my form, i would lock the current row.
> After user has modified data on form, pressing "save" button I would save t=
> he modified row by sql update function and so commit.

This is widely considered a very bad way to design an application.
Consider what happens when the user leaves for lunch, or otherwise
lets the app sit for a long time.  See the list archives for prior
discussions of the issue.

But in any case, the answer to your question is to use "SELECT FOR
UPDATE" to retrieve the row.  And you can't start a transaction
inside a function, because by definition you'll already be in one.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match