Hello DRH,
On Wed, March 1, 2006 16:53, [EMAIL PROTECTED] wrote:
...
> If you use READ COMMITTED isolation (the default in PostgreSQL)
> then your writes are not atomic as seen by the reader. In other
...
> then you need to select SERIALIZABLE isolation in PostgreSQL
> in which case the MVCC is not going to give you any advantage
> over SQLite.
indeed. Another trick which may be useful and that we often used in our
applications, which sometimes have similar needs: use an explicity "status"
field to mark the record situation.
Insert records as "processing by writer", update them to "ready to be
processed" with a single atomic update after a burst of inserts, update the
status of all "ready to be processed" records to the "to be processed by
reader" status with another single atomic update in the reader, process all
the "to be processed by reader" records, mark all the "to be processed by
reader" records as "processed" again with a single atomic update when
finished, if needed delete "processed" records.
This kind of approach requires just an index on the status field and is also
really useful when something goes wrong (application bug, power outage and so
on) because it becomes pretty easy to reprocess all the unprocessed records
just by looking at the status. The end results should be pretty similar to the
use of temporary tables, but without the need of additional tables.
Bye,
--
Dr. Denis Sbragion
InfoTecna
Tel: +39 0362 805396, Fax: +39 0362 805404
URL: http://www.infotecna.it
