Thanks for your reply.
I can't lock the table as that would effectively serialize the db access
in my case so I guess retrying the transaction is my best option.
.oO V Oo.
On 06/07/2011 05:11 PM, A.M. wrote:
On Jun 7, 2011, at 7:17 AM, Vlad K. wrote:
Hi all!
I have a daily stats table with the date as primary key. Currently the date is
string in the YYYYMMDD format, but I'll probably port it to a Date type for
easier statistical analysis. Other cols are various counters that get
incremented in various situations.
My problem is that I need to write code that either updates if row with that
date exists, or inserts as new. I know SQLAlchemy does that by itself
(session.merge()), but the problem here is that you can't lock a row that does
not exist yet, and with concurrency in mind, what will happen if two parallel
processes happen to insert same date row at the same time.
One solution would be to catch Integrity error and simply try again (with
second attempt finding the pkey and thus updating instead of insert). But since
I'm new to SQLAlchemy, I don't know if there is any better method.
The DB backend is Postgres.
This is a common PostgreSQL question which can be addressed with rules or
triggers or just retrying the transaction, as you suggest. However, the easiest
solution is to lock the table for the duration of the transaction which may be
option to you if the transactions are short-lived and not performance-critical.
Cheers,
M
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.