[Buildbot-devel] IntegrityError on postgresql, "claimed_at" violates not-null constraint

[Dustin J. Mitchell]

On Fri, Sep 21, 2012 at 10:37 AM, Benoît Allard <benoit at aeteurope.nl> wrote:
> I've seen those lines, yet, I've seen my logs ... Some preconditions over here are not meet ... I could add some logging (and I will), I'm just not sure where ...
>
> We could probably figure out why this claimed_at is 'null' (not None, 'null'), and prevent it to happen again, but yet, that's just asking for the next integrity error to show ...

The Python DBAPI translates None to NULL.

> I imagine querying before inserting (to check if the request is not already claimed) is not an option, right ? What if we do that inside a transaction ? Oh wait, the transaction is already there ... What's the problem then ?

Transactions don't assure read-before-write atomicity in any of the
databases we support.  That would be "serializable" isolation
  http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Isolation_levels
which basically means that if any of the data returned by a SELECT
during the transaction has changed when the transaction is committed,
then the commit fails.

I think postgres *can* support this, but SQLite can't, and MySQL can't, AFAIK.

Anyway, even if we did have this, we'd be at the same place - the
transaction would fail and need to be retried.

> I don't think this will help either ...

Now that I look at the queries again, you're right:
  INSERT INTO buildrequest_claims DEFAULT VALUES

That's not the query that this code should produce:

111             try:
112                 q = tbl.insert()
113                 conn.execute(q, [ dict(brid=id, objectid=_master_objectid,
114                                     claimed_at=claimed_at)
115                                   for id in brids ])
116             except (sa.exc.IntegrityError, sa.exc.ProgrammingError):
117                 transaction.rollback()
118                 raise AlreadyClaimedError

The only place I see that string in sqlalchemy is (aside from in the
MSSQL dialect):

sqlalchemy/sql/compiler.py
1038     def visit_insert(self, insert_stmt):
1039         self.isinsert = True
1040         colparams = self._get_colparams(insert_stmt)
...
1086         if not colparams and supports_default_values:
1087             text += " DEFAULT VALUES"
1088         else:
1089             text += " VALUES (%s)" % \
1090                      ', '.join([c[1] for c in colparams])

>From re-reading the docs, it looks like conn.execute should take each
dict as a positional argument, rather than a list, e.g.,

113                 conn.execute(q, *[ dict(brid=id, objectid=_master_objectid,
114                                     claimed_at=claimed_at)
115                                   for id in brids ])

can you try that and see if it works better?  What version of
sqlalchemy are you running?

Dustin