Re: background triggers? - Mailing list pgsql-general

From Christopher Browne
Subject Re: background triggers?
Date
Msg-id 87r72jlelr.fsf@wolfe.cbbrowne.com
Whole thread Raw
In response to background triggers?  (Sim Zacks <sim@compulab.co.il>)
Responses Re: background triggers?  (Sim Zacks <sim@compulab.co.il>)
Re: background triggers?  (Rafal Pietrak <rafal@zorro.isa-geek.com>)
List pgsql-general
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...
--
let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];;
http://linuxdatabases.info/info/x.html
You know  how most packages say  "Open here". What is  the protocol if
the package says, "Open somewhere else"?

pgsql-general by date:

Previous
From: Benno Pütz
Date:
Subject: Incomplete dump?
Next
From: Sim Zacks
Date:
Subject: Re: background triggers?