From: | Alban Hertroys <alban(at)magproductions(dot)nl> |
---|---|
To: | Harpreet Dhaliwal <harpreet(dot)dhaliwal01(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Defining and Using variables in a postgres function |
Date: | 2007-02-02 16:18:39 |
Message-ID: | 45C3645F.6090601@magproductions.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Harpreet Dhaliwal wrote:
> About the concurrency control, if i have both Select Max(id) and insert
> (id)
You know, if you don't top-post you don't need to tell me what you're
talking about ;)
> 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.
I just realized you can do this in one statement - that wouldn't solve
the concurrency problem, though.
You can do this:
INSERT INTO tbl_email (option_public, agency, id)
SELECT $1, $2, MAX(id) + 1
FROM xyz;
> Also, should i go with a table level lock or a row level lock in this
> scenario?
I just realize you don't so much need a lock, you need a serialized
transaction. I can't say I know a lot about locks, I usually prevent
needing them.
Thinking of which... If there is _any_ relation between the MAX(id)
you're selecting from xyz and the record you insert into tbl_email, how
do you guarantee that you're actually looking at the correct record in xyz?
Basically the problem occurs when a new record was inserted and
committed into xyz while you were processing the insert of the previous
one. MAX(id) would return the id of the newly inserted record, _after_
you started processing.
I can't imagine what purpose the MAX(id) from a different table would
serve. If there isn't a relation between those fields, what benefit does
your method have over a sequence?
Regards,
--
Alban Hertroys
alban(at)magproductions(dot)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 //
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-02-02 16:23:17 | Re: I "might" have found a bug on 8.2.1 win32 |
Previous Message | Dave Page | 2007-02-02 16:15:43 | Re: I "might" have found a bug on 8.2.1 win32 |