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@xxxxxxxxxxxxxxxxxx (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...
