About the concurrency control, if i have both Select Max(id) and insert (id) in the same function, then would it be
a nice idea to put both these statements in the same function or differenct functions and then put the insert in a transaction and lock the table for any further query till insert commits.
Also, should i go with a table level lock or a row level lock in this scenario?
Thanks
~Harpreet
On 2/2/07, Alban Hertroys < alban@magproductions.nl> wrote:Harpreet Dhaliwal wrote:
> I have a function like the follwoing:
>
> CREATE OR REPLACE FUNCTION sp_insert_raw_email(bool, text, text, text,
> int4,text,text,text,text,text,text,text,timestamp)
> RETURNS void AS
> $BODY$
> BEGIN
> -- SELECT STATEMENT GOES HERE--
> INSERT INTO tbl_email(option_public,
> agency , id)
> VALUES ($1,$2) ;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
>
> For inserting the id, i need to query a table xyz, fetch the maximum id in
> it, increment it by 1 and store it in tbl_email.
Shouldn't you circumvent the whole concurrency mess you're getting
yourself into by using a sequence?
You're in trouble if this function gets called concurrently from
different sessions, unless you lock the relevant records. They'll both
see the same MAX(id) and try to insert records with the same id values.
> How should i define this variable first and how to push the result of the
> query fired on table xyz.
Yes indeed, like this:
DECLARE
x int;
BEGIN
SELECT INTO x MAX(id) + 1 FROM xyz;
INSERT INTO tbl_email(option_public, agency , id)
VALUES ($1,$2, x) ;
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //