Re: PG-MQ?

On 6/20/07, Jeroen T. Vermeulen <jtv(at)xs4all(dot)nl> wrote:
> On Wed, June 20, 2007 04:45, Chris Browne wrote:
> > - Sometimes you have the semantics where:
> > - messages need to be delivered at least once
> > - messages need to be delivered no more than once
> > - messages need to be delivered exactly once
>
> IMHO, if you're not doing "exactly once," or something very close to it,
> you might as well stay with ad-hoc code. You can ensure single delivery
> by having the sender re-send when in doubt, and keeping track of
> duplications in the recipient.

In case of PGQ, the "at least once" semantics is related to batch-based
processing it does - in case of failure, full batch is delivered again,
so if consumer had managed to process some of the items already, it gets
them double.

As it is responsponsible only for delivering events from database,
it has no way of guaranteeing "exactly once" behaviour, that needs
to be built on top of PGQ.

Simplest case would be if the events are processed in same database
that the queue resides. Then you can just fetch, process, close batch
in one transaction and immidiately you get "exactly once" behaviour.

To achieve "exactly once" behaviour with different databases, look
at the "pgq_ext" module for sample. Basically it just requires
storing batch_id/event_id on remote db and committing there first.
Later it can be checked if the batch/event is already processed.

It's tricky only if you want to achieve full transactionality for
event processing. As I understand, JMS does not have a concept
of transactions, probably also other solutions mentioned before,
so to use PgQ as backend for them should be much simpler...

To Chris: you should like PgQ, its just stored procs in database,
plus it's basically just generalized Slony-I, with some optimizations,
so should be familiar territory ;)

--
marko