[Zope-DB] Trying to catching ZPsycopg exceptions aborts the transaction

[Josef Meile]

Hi,

I know it may sound strange, but I don't want the DA to abort the
transaction when trying to insert a duplicate record. The reason of
doing this is that before trying to insert that duplicate, I have
already done other inserts, so, they will be rollebacked as well.

I have already posted this question to the psycopg mailing list (down at
the moment, so, I can't give you a reference to my message), but there I
was told that this suits better on a zope related list. The scenario is
the following:

I am implementing a reservation system for diferent kinds of resources:
rooms, beamers, and maybe others. The users can reserve those items for
a specific period of time, so, there is a start and end time (timestamps
rows). My problem is with the overlapped reservations, ie: user A tries
to reserve room R from 29.11.2004 07:00 to 16:00, at the same time, user
B tries to reserve the same room from 29.11.2004 14:00 to 22:00, so,
there is an overlapping period from 14:00 to 16:00. Let's supose that
user B completes its reservation before user A. I would like at this
point that user A could make partially the reservation till 14:00, then
the system will inform him that the resource couldn't be reserved after
14:00 because of another reservation. First I thaugt about this scheme:

Table: Reservation
+-------------+---------------+------+-----+
| Field       | Type          | Null | Key |
+-------------+---------------+------+-----+
| resourceId  | bigint(20)    |      | PRI |
| contactName | varchar(60)   |      |     |
| userName    | varchar(10)   |      | MUL |
| email       | varchar(60)   |      |     |
| startTime   | timestamp(14) | YES  | PRI |
| endTime     | timestamp(14) | YES  | MUL |
| comment     | text          | YES  |     |
| attenders   | text          | YES  |     |
+-------------+---------------+------+-----+

I also add a constraing to make  the pair (resourceId, endTime) to be
unique. But this alone won't be enough to avoid overlapping periods
between startTime and endTime, so, I created a second table for each
time period:

Table: ReservationTime
+------------+---------------+------+-----+
| Field      | Type          | Null | Key |
+------------+---------------+------+-----+
| resourceId | bigint(20)    |      | PRI |
| startTime  | timestamp(14) | YES  | PRI |
+------------+---------------+------+-----+

To make a reservation, first the users will try to insert each
time slot till there is a duplicate exception or till the end. Then,
here is safe to insert a Reservation record till the last inserted time
lot because it means that there wasn't a reservation on that period of
time.

Here is my code to make a reservation:

def newReservation(self,container,realName,email,
                    comment,attenders,startTime,
                    endTime):
   """Creates a new reservation"""

   msgid="The reservation was successfuly done"
   currentUser=Misc.getUserName()
   index=0
   exit=0
   numPeriods=endTime.hour()-startTime.hour()
   while (index<numPeriods) and (not exit):
     hour=index+startTime.hour()
     date=dateUtils.convertToZopeDate("%s %s:00" % \
                               (startTime.Date(),hour))
     try:
       container.sqlInsertReservationTime(resourceId=self.oid,
                                          userName=currentUser,
                                          startTime=date)
     except:
       errno, errorstr = sys.exc_info()[:2]
       if type(errorstr)!=type(''):
         errorstr=str(errorstr)
       errorstr=errorstr.lower()
       if (errorstr.find('duplicate')<0):
         #Unfortunatelly, there isn't an standard exception
         #for duplicate records on ZMysql and ZPsycopg, so,
         #the only way is to scan the errorstr for the word
         #duplicate.
         raise

       if (index==0):
         #This means that the reservation couldn't be done
         msgid='Somebody else has already done a reservation at this time.'
       else:
         msgid='It wasn\'t possible to make a reservation after: %i:00' % \
                (hour-1)
       exit=1
     index+=1

   if (hour!=startTime.hour()) or (not exit):
     if exit:
       #This means that the reservation could
       #be partially done. The last ReservationTime
       #wasn't inserted, so, we decrement the value
       hour-=1

     #Here is safe to insert a reservation on the
     #Reservation table because it means that all
     #the times where safetely inserted on the
     #ReservationTime table
     hour=hour+1
     date=dateUtils.convertToZopeDate("%s %s:00" % \
                            (startTime.Date(),hour))
     endTime=date
     container.sqlInsertReservation(resourceId=self.oid,
                                    contactName=realName,
                                    userName=currentUser,
                                    email=email,
                                    startTime=startTime,
                                    endTime=endTime,
                                    comment=comment,
                                    attenders=attenders)
   return msgid

sqlInsertReservation and sqlInsertReservationTime are two ZSqlMethods
that are stored on the container folder of my object and they contain
the standard INSERT sql queries. Both of them are related to a psycopg
database connection.

The problem here is that ZPsycopg aborts the database transaction when
trying to insert the duplicate, so, the previously inserted time slots
are gone. Finally, a new transaction is created when inserting the
reservation record. Here there is already an inconsistency because a
record on the Reservation table was inserted, but the related time slots
don't. So, anoter user will be able to do a reservation with overlapping
periods of time.

On the contrary, ZMysql + InnoDB tables doesn't abort the transaction,
so, the time slots are inserted till the duplicate and the reservation
record too. According to the people on the psycopg list, this is the
expected behaviour and perhaps there is an implementation error on
ZMysql. They also meant that this is a problem related to zope and not
to the DA itself. So, my question is how can this solved? I have already
thaugt about other solutions, but they have all some problems:

1) Try to check if a range overlaps an existing reservation: Two users
can come at the same time and trying to reserve an available resource at
the same time or at overlaping periods of time, so, the select query
will return no overlaping reservations for both of them. Other
disadvantage is that it will be last some time to do this check.

2) Do a table lock while doing the reservation. This approach will work,
but the only disadvantage is that only one user can reserve and the
others will have to wait till the table is unlocked. It will be also
have a big impact because the reservation table has records related to
more than one resource.

3) Create all the time slots before going online, so that each user can
do a row lock on the specified periods of time. Here a lot of space will
be wasted.

4) Working with the "autocommit" method of the psycopg cursors. This is
almost the solution. The only problem here is that each insert will be
considered as an individual transaction, so, if for some reason the
server crashes, the previously inserted records won't be rollbacked and
there will be some inconsistencies. Other disadvantage is that doing
this wouldn't be standard because it is psycopg specific.

Has somebody else a clue about how to handle with the database
exceptions?

Thanks in advanced,
Josef