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.
