Re: Defining and Using variables in a postgres function

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 //