Re: [RFC] CREATE QUEUE (log-only table) for londiste/pgQ ccompatibility - Mailing list pgsql-hackers

On 10/17/2012 12:03 AM, Josh Berkus wrote:
> Hannu,
>
> Can you explain in more detail how this would be used on the receiving
> side?  I'm unable to picture it from your description.
It would be used similar to how the event tables in pgQ (from skytools)
is used - as a source of "events" to be replied on the subscriber side.

(For discussion sake let's just call this LOGGED ONLY TABLE, as opposed
to UNLOGGED TABLE we already have)

The simplest usage would be implementing "remote log tables" that is
tables, where you do INSERT on the master side, but it "inserts" only
a logical WAL record and nothing else.

On subscriber side your replay process reads this WAL record as an
"insert event" and if the table is declared as an ordinary table on
subscriber, it performs an insert there.

This would make it trivial to implement a persistent remote log table
with minimal required amount of writing on the master side.

We could even implement a log table which captures also log entries
from aborted transactions by treating ROLLBACK as COMMIT for this
table.

But the subscriber side could also do other things instead (or in
addition to) filling a log table. For example, it could create a 
partitioned
table instead of a plain table defined on the provider side.

There is support and several example replay agents in skytools package
which do this based on pgQ

Or you could do computations/materialised views based on "events" from 
the table.

Or you could use the "insert events"/wal records as a base for some
other remote processing, like sending out e-mails .

There is also support for these kinds of things in skytools.

> I'm also a bit reluctant to call this a "message queue", since it lacks
> the features required for it to be used as an application-level queue.
> "REPLICATION MESSAGE", maybe?
>
Initially I'd just stick with LOG ONLY TABLE or QUEUE based on what it does, not on how it could be used.

LOGGED ONLY TABLE is very technical description of realisation - I'd
prefer it to work as mush like a table as possible, similar to how VIEW
currently works - for all usages that make sense, you can simply
substitute it for a TABLE

QUEUE emphasizes the aspect of logged only table that it accepts
"records" in a certain order, persists these and then quarantees
that they can be read out in exact the same order - all this being
guaranteed by existing WAL mechanisms.

It is not meant to be a full implementation of application level queuing
system though but just the capture, persisting and distribution parts

Using this as an "application level queue" needs a set of interface
functions to extract the events and also to keep track of the processed
events. As there is no general consensus what these shoul be (like if
processing same event twice is allowed) this part is left for specific
queue consumer implementations.

--------------------
Hannu Krosing