How do you robustly implement a REST service that retrieves DB records then purges them before returning ?

Question

Scenario Imagine a REST service that returns a list of things (e.g. notifications)

Usage A client will continually poll the REST service. The REST service retrieves records from the database. If records are available, they are converted into JSON and returned to the client. And at the same time, the retrieved records are purged from the DB.

Problem How do you handle the problem if the REST endpoints encounters a problem writing the results back to the client ? By that time, the records have been deleted.

Answer 1

This could be a possible solution. The REST service can query the database for a list of notifications. It marks each item in the list ,by say setting a flag in the database. It then delivers all this notification records to the client.

If the server has successfully sent the results to the client, all marked records are deleted.

In case of failure, the marked records are unmarked,so that they get delivered during the subsequent polling interval.

I hope you got my point.

Answer 2

Deleting the records will always be a dangerous proposition. What you could do instead is include a timestamp column on the data. Then have your REST url include a "new since" timestamp. You return all records from that timestamp on.

If the notifications grow to be too large you can always setup an automated task to purge records more than an hour old - or whatever interval works well for you.

Answer 3

It sounds like a strange idea to delete DB records after reading access. Possible problems immediately leap into mind: Network trouble prevent the client reading the data, multiple clients cause each other to see incomplete lists, et.al.

The RESTful apporach might be like this:

• Give each notification a specific URI. Allow GET and DELETE on these URIs. The client may trigger the record deletion once it successfully received and processed the notification.
• Provide an URI to the collection of current notifications. Serve a list of notification data (ID, URI, timestamp, (optional:) content) upon GET request. Take a look at the Atom protocol for ideas. Optional: Allow POST to add a new notification.

With this approach all reading requests stay simple GETs. You may instrument the usual HTTP caching mechanisms on proxies and clients for performance improvement.

Anyway: Deleting a DB entry is a state change on the server. You must not do this upon a GET request. So POST will be you primary choice. But this does not help you much, since the communication might still not be reliable. And polling qith POSTs smells a lot more like Web-Services than REST.

Answer 4

We did this with special timestamp paramter.

Requests

1. Request with timestamp.min, this return all items, and server timestamp;
2. Request with timestamp from server, return items from hat time stamp, and delete prevoius, return server time stamp;

Please note that we did all this with post. means that virtually we sent command (not query get).