How to insert a row with autoincrement id in a multi-primary-key table?

Question

I am writing a turbogears2 application. I have a table like this:

class Order(DeclarativeBase):
    __tablename__ = 'order'

    # id of order
    id = Column(Integer, autoincrement=True, primary_key=True)

    # buyer's id
    buyer_id = Column(Integer, ForeignKey('user.user_id',
        onupdate="CASCADE", ondelete="CASCADE"), primary_key=True)

I want to insert a new row into this table, but I got a "Field 'order_id' doesn't have a default value" error. It seems that I have to set the id of order manually, because I got two primary-key. My question is, how can I insert a row that generate new ID automatically?

If I generate id manually, I got some problem. For example:

maxId = DBSession.query(func.max(Order)).one()[0]
newOrder = Order(id=maxId + 1, buyer_id=xxx)
DBSession.add(newOrder)

Add a new order in this way seems ok, but however, we got some problem if two request run these code in almost same time.

If there is request a and b run this code in following order:

a.maxId = DBSession.query(func.max(Order)).one()[0]
b.maxId = DBSession.query(func.max(Order)).one()[0]
b.newOrder = Order(id=maxId + 1, buyer_id=xxx)
b.DBSession.add(newOrder)
a.newOrder = Order(id=maxId + 1, buyer_id=xxx)
a.DBSession.add(newOrder)

Then the request a might failed, because there is already an order with same id in table. I can catch the exception and try again. But I am wondering, is there any better way to do?

Sometimes, the id is not simple integer, we might need order id like this:

2009090133 standards for 33rd order at 2009-09-01

In these case, autoincrement is not usable. So I have no choice, manualy assign id for order. So my another question is, is there any better way than catch exception and retry to insert a row with id.

Answer 1

If you want sequential numbers per buyer for your orders then you'll have to serialize the transactions inserting to one buyer. You can do that by acquiring exclusive lock on the buyer row:

sess.query(Buyer.id).with_lockmode('update').get(xxx)
order_id = sess.query(func.max(Order.id)+1).filter_by(buyer_id=xxx).scalar() or 1
sess.add(Order(id=order_id, buyer_id=xxx))

Using this pattern when two transactions try to insert order for one buyer in parallel one of them will block on the first line until the other transaction completes or fails.

Answer 2

You should be using a default on your column definitions

id = Column(Integer, default = sqlexpression)

Where sqlexpression can be a sql expression. Here is the documentation. For autoincrement you should use the sql expression coalesce(select max(order.id) from order,0) + 1. For ease you could import sqlalchemy.sql.text so the id column could look something like

id = Column(Integer, default = text("coalesce(select max(order.id) from order,0) + 1"))