Re: Problem with async notifications of table updates

On Mar 19, 2008, at 2:35 AM, Tyler, Mark wrote:

> Alban Hertroys wrote:
>> On Mar 18, 2008, at 3:58 AM, Tyler, Mark wrote:
>>> I suggest rethinking your dislike of NOTIFY.
>>>
>>> I have thought very hard about using NOTIFY for this but it has two
>>> large problems (from my point of view). The first is that it forces
> me
>>> to put far more smarts and state into the subscriber applications.
>>> This
>>> is because I cannot pass any information with the NOTIFY apart from
>>> the fact that "something happened". Due to this restriction my
>>> subscriber apps would have to go and look up some secondary table to
>>> get sufficient information to construct the real query. That is just
>>> plain ugly in my view.
>>
>> You will have the same problem if you want to send a message about a
>> record change in combination with transactions. You can either send
>> a message about an /uncommitted/ transaction and include what record
>> changed, /or/ you send a message about a /committed/ transaction
>> which
>
>> possibly changed multiple of those records - in which case there's no
>> possibility to send a single id along with your message.
>
> I think you are suggesting that instead of having the trigger function
> FOR EACH ROW it should be FOR EACH STATEMENT. In the case of an AFTER
> trigger using a FOR EACH STATEMENT clause; does it get fired /
> after/ the
> commit? (the documentation for CREATE TRIGGER does not really specify)

I think FOR EACH ROW or FOR EACH STATEMENT will make little or no
difference here, unless you can guarantee that you'll never change
more than one row in a transaction. Rows changed in a transaction are
usually in different tables, that's what transactions are for after
all - "Transaction" is a good name for what it does.

As an example in an employee database, if you want to send a NOTIFY
when an employee's salary gets INSERTed, the transaction will usually
also contain the INSERT of the employee record. You want those two
statements together in a transaction, or you risk creating an
employee without a salary (because the application crashed, the
connection was lost, the office got flooded, etc) - and be sure that
employee will be grumpy about that!

For the difference between ON EACH ROW and ON EACH STATEMENT in
triggers, that's best illustrated by queries such as INSERT INTO
table1 (column1, column2, fkey1) SELECT 'value1', 'value2', id FROM
table2 WHERE column1 = 'value3';
This single statement could result in multiple rows being inserted,
and so you can either choose to fire a trigger for each inserted row
or for the entire statement at once.

That said, if you'll need to batch your statements for a client to be
notified at the end of the transaction, sending a notify with an ON
EACH STATEMENT trigger will yield NOTIFY's at the same moment in time
(at COMMIT).

> That may work for me if I can guarantee that the publisher only ever
> changes single rows for notifiable tables in a transaction.

That depends on the importance of your data's integrity in relation
to transactions, as per the employee example above. I can't say
anything about that.

>> You could try sending a set after commit, equivalent to how INSERT
> RETURNING
>> works, but you'll have to marshall those id's into your message
> yourself. And
>> that's pretty similar to putting those id's in a table and fetch them
> from your
>> application - it's just moving the work around.
>
> I prefer to put as much of the knowledge about the technicalities
> of the
> process into the publishing side of the system keeping the
> interface the
> subscriber apps have to deal with relatively simple. Anything I can
> do,
> within reason, to help this is good.

Regards,
Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,47e0a7459781583513226!