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

On 10/16/2012 11:29 AM, Hannu Krosing wrote:
> On 10/16/2012 11:18 AM, Simon Riggs wrote:
>> On 16 October 2012 09:56, Hannu Krosing <hannu(at)2ndquadrant(dot)com> wrote:
>>> Hallo postgresql and replication hackers
>>>
>>> This mail is an additional RFC which proposes a simple way to extend
>>> the
>>> new logical replication feature so it can cover most usages of
>>> skytools/pgq/londiste
>>>
>>> While the current work for BDR/LCR (bi-directional replication/logical
>>> replication) using WAL is theoretically enought to cover
>>> _replication_ offered by
>>> Londiste it falls short in one important way - there is currently no
>>> support for pure
>>> queueing, that is for "streams" of data which does not need to be
>>> stored in the source
>>> database.
>>>
>>> Fortunately there is a simple solution - do not store it in the source
>>> database :)
>>>
>>> The only thing needed for adding this is to have a table type which
>>>
>>> a) generates a INSERT record in WAL
>>>
>>> and
>>>
>>> b) does not actually store the data in a local file
>>>
>>> If implemented in userspace it would be a VIEW (or table) with a
>>> before/instead
>>> trigger which logs the inserted data and then cancels the insert.
>>>
>>> I'm sure this thing could be implemented, but I leave the tech
>>> discussion to
>>> those who are currently deep in WAL generation/reconstruction .
>>>
>>> If we implement logged only tables / queues we would not only enable
>>> a more
>>> performant pgQ replacement for implementing full Londiste / skytools
>>> functionality
>>> but would also become a very strong player to be used as persistent
>>> basis
>>> for message queueing solutions like ActiveMQ, StorMQ, any Advanced
>>> Message
>>> Queuing Protocol (AMQP) and so on.
>>
>> Hmm, I was assuming that we'd be able to do that by just writing extra
>> WAL directly. But now you've made me think about it, that would be
>> very ugly.
>>
>> Doing it this was, as you suggest, would allow us to write WAL records
>> for queuing/replication to specific queue ids. It also allows us to
>> have privileges assigned. So this looks like a good idea and might
>> even be possible for 9.3.
>>
>> I've got a feeling we may want the word QUEUE again in the future, so
>> I think we should call this a MESSAGE QUEUE.
>>
>> CREATE MESSAGE QUEUE foo;
>> DROP MESSAGE QUEUE foo;
> I would like this to be very similar to a table, so it would be
>
> CREATE MESSAGE QUEUE(fieldname type, ...) foo;
>
> perhaps even allowing defaults and constraints. again, this
> depends on how complecxt the implementation would be.
>
> for the receiving side it would look like a table with only inserts,
> and in this case there could even be a possibility to use it as
> a remote log table.

To clarify - this is intended to be a mirror image of UNLOGGED table

That is , as much as possible a full table, except that no data gets
written, which means that

a) indexes do not make any sense
b) exclusion and unique constraints dont make any sense
c) select, update and delete always see an empty table

all these should probably throw and error, analogous to how VIEWs
currently work.

It could be also described as a write-only table, except that it is
possible to materialise it as a real table on the receiving side

>
>>
>> GRANT INSERT ON MESSAGE QUEUE foo TO ...;
>> REVOKE INSERT ON MESSAGE QUEUE foo TO ...;
>>
>> Rules wouldn't. DELETE and UPDATE wouldn't work, nor would SELECT.
>>
>> Things for next release: Triggers, SELECT sees a stream of changes,
>> CHECK clauses to constrain what can be written.
>>
>> One question: would we require the INSERT statement to parse against a
>> tupledesc, or would it be just a single blob of TEXT or can we send
>> any payload? I'd suggest just a single blob of TEXT, since that can be
>> XML or JSON etc easily enough.
>>
>
>
>