The problem with Listen/Notify is that you have to create another client
application that is not managed in the central database as a daemon to
call a function. If the server supported background triggers, then you
wouldn't need an extra client application to use the listen/notify
framework, you would just need to have a trigger with the background
option on and call the function.
That is a lot of extra overhead.
Christopher Browne wrote:
> The world rejoiced as rafal@zorro.isa-geek.com (Rafal Pietrak) wrote:
>> On Wed, 2006-05-24 at 07:41 -0400, Kenneth Downs wrote:
>>> Why not have the INSERT go to an "inbox" table, a table whose only job
>>> is to receive the data for future processing.
>> Actually, it 'sort of' works that way.
>>
>>> Your client code should mark all rows with a batch number as they go
>>> in. Then when the batch is loaded, simply invoke a stored procedure to
>>> process them. Pass the stored procedure the batch number.
>> If I have that stored procedure and if I issue command that would launch
>> such stored procedure from "psql>" prompt: how long will I have to wait
>> for another prompt? 1) until the procedure ends its job. 2) right away,
>> the procedure does its job unabidedly 'in the background'.
>>
>> My impression was, that I get the next prompt after the procedure
>> finishes, so it wouldn't be a solution. But if (2) applies, that is
>> really it.... Frankly, it would take me some time to get back to those
>> sources (and generate simulation data) - so anybody knows the answer?
>
> It depends on something else...
>
> Is the process that is starting up the "background batch process"
> making other changes to the data?
>
> If it is, then the "foreground" process MUST submit a COMMIT
> in order to finalize the batch before kicking off anything in the
> background. Otherwise, the background process won't see the batch in
> its "full glory." You can never read uncommitted data, after all.
>
> That being the case (that you MUST have a COMMIT), I don't see any
> reason to consider LISTEN/NOTIFY to be an insufficient mechanism.
>
> It would only be insufficient if you don't submit a COMMIT.
>
> But the background process won't see the right data unless you COMMIT.
> So you *MUST* submit a COMMIT, making LISTEN/NOTIFY quite adequate to
> the task...