What I would like to do, and please let me know if there is a better> way to do this, is to have an after event statement trigger run in a> separate process so the function call ends when it ends and the> statement trigger gets executed on its own time.>

The use that I have for this at the moment, and I can think of many> other uses, is that I want to populate a statistics table each time> that a table is updated. But the code to populate the table takes 10> seconds to run. I don't want the user to have to wait 10 seconds to> add a record.

This seems a case for using NOTIFY/LISTEN.>

- You have a process connected to the database that runs LISTEN,> causing it to listen for a particular message.>

LISTEN regen_statistics;>

- Your trigger submits a notification:>

NOTIFY regen_statistics;

So if I may re-phrase the question: "is there a way to have a trigger,>that, when launched, can check if it's already running in backgroung for>some other INSERT, return imediately if so, but turn into background for>a long-lasting job if not".>

Some time ago I fell into quite similair problem. But LISTEN/NOTIFY was> not a solution.>

In that case I needed to *disconnect* and never bother about the outcome> of a long running background trigger.

So if I may re-phrase the question: "is there a way to have a trigger,> that, when launched, can check if it's already running in backgroung for> some other INSERT, return imediately if so, but turn into background for> a long-lasting job if not".

What I would like to do, and please let me know if there is a better way > to do this, is to have an after event statement trigger run in a > separate process so the function call ends when it ends and the > statement trigger gets executed on its own time.

Some time ago I fell into quite similair problem. But LISTEN/NOTIFY was> not a solution.>

In that case I needed to *disconnect* and never bother about the outcome> of a long running background trigger.

So if I may re-phrase the question: "is there a way to have a trigger,> that, when launched, can check if it's already running in backgroung for> some other INSERT, return imediately if so, but turn into background for> a long-lasting job if not".

The functionality required is "lazy" update of agregate tables, which> need not be 100% acurate, but should be kept in-sync whenever possible.

Rafal, I'm wondering why you want to do this. You may be "fighting the > framework".

If you are trying to do something that is totally unsupported, it is > probably for a pretty good reason, usually dealing with security or data > loss. You can probably get what you want by supported methods, but it > may require looking at the problem in a different way.>

What is it you are trying to accomplish? Is it just performance?

I may not have understood exactly what you are trying to do, but from > what I understood, this will solve your problem.

Yes, most probably. I'm afraid of that ­>

Still...>

OK. here is a 'real life' example. It works more like a post office. Now> and then, there arrive a 'delivery man' with a bunch of post to deliver.> Post office takes the batch, checks and stamps each and every item, and> hands over a receipt. But the actual bin-ing (into delivery channels)> and routing (dispatch) is handled without the delivery man standing and> waiting for the process to end.>

In my *real*life* case I have a file with hundreds of tousends of> 'transactions' uploaded by operator (several times times a day, and in> fact, by 10-30 operators) - those are checked at INSERT time. But after> that check and INSERT, I'd like to say to the operator: "OK, jour job is> done, don't warry about the rest". >

But there is more work to do with the batch. >

I have to 'route the transactions' to their relevant accounts, and see> how those change the 'status' of those accounts, consequently, store the> updated status within the account itself. This is tedious and time> consuming. But it have to be done, because 'customers' query account> status for those 'agregate status information' and it would be *very*> haevy for the database if those queries required browsing of the entire> 'transaction log'. Number of 'Transactions' to number of accounts is> like milions to thousends.>

A plain INSERT of batch takes 5-10minutes on desktop postgresql (800MHz> machine, ATA disks). When I attach trigger (*Very* simple funciton) to> update the accounts, the INSERT take hours (2-4). But when I make just> one single update of all accounts at the end of the batch insert, it> takes 20-30min.>

So my solution was 1) to have an additional table "TABLE dirty_log(tiem> timestamp, who text)", which gets inserted a row *after* a batch of> INSERTS. 2) and a 'server side process', run every 5-10 minutes, which> makes accounts update, and which: A) does NOT launche when another such> process is currently running; B) purges DIRTY_LOG table after it's done.> This is quite obvoisly a 'user space implementation' of the 'background> triggers'. I needed that.>

Natuaraly, having this 'bacground trigger' I loose acuracy of the> account information. But I gain on system efficiency - this is> engineering decision. When we have to take those (like the> implementation above), it's good to have 'system tools' (like> 'background triggers') that support us.>

But of cource I may be wrong all togather. I'd really like to know the> techniq, which is 'along the line' of RDBM systems design, which serves> that same purpose. >

Is there a better solution?>

NB: the 'batch INSERT' I mentioned above is done by www server. It's> quite vital to have the the server process terminated (meaning: not keep> it waiting for LISTEN/NOPTIFY event - meaning not use LISTEN/NOTIFY), as> apache will keep the connection opened until the process ends.>

In 'real life', this scenario is applicable also to 'service network':> 1. Say, you have 100_000 - 1000_000 vending machines (VM) network.> 2. each is is loaded with c.a. 100 item types (300 types in the entire> network).> 3. each VM dispatches an item every 2-3 minutes. which make overall> 'transaction traffic' at the level of over hundreds per second.> 4. assume, that for 'customer management', you need to store quite a bit> of data with each item-dispense 'transaction'. Meaning: transaction are> not very light, and their details have to be kept for long time.> 5. obviously, you need to manage your stock (each of the 300> item-types): you keep VM loaded and keep some stock at central store.>

(ATMs are a good example of such netowrk)>

So:> 1. 'transaction traffic' is so signifficant, that you really have to> 'optimise for that'> 2. you don't really have to know *exactly* when you run out of stock,> because each VM has signifficant local item store, so if you get> notified, that a particular VM gets close to the bottom with particular> item, you may dispatch a reload in 10min, but it's also OK to dispatch> that in 2hours - meaning, the 'acocunt information' does not have to be> 'immediately acurate'. Far more important is 'dispatch transaction'> performance.> 3. normally, you 'keep an eye' on you VM network - meaning, you issue a> 'statistics' query quite frequently. If that was a 'haevy query' it> would degrade your database performance quite signifficantly - we really> need the 'agregate information' stored within 'item-accounts'.>

Is there a clean, 'along the framework' design that serves this reality?>

-R>

-------------------­--------(end of broadcast)---------­------------------> TIP 6: explain analyze is your friend>

A plain INSERT of batch takes 5-10minutes on desktop postgresql (800MHz>machine, ATA disks). When I attach trigger (*Very* simple funciton) to>update the accounts, the INSERT take hours (2-4). But when I make just>one single update of all accounts at the end of the batch insert, it>takes 20-30min.>

Why not have the INSERT go to an "inbox" table, a table whose only job > is to receive the data for future processing.

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.

Yes. But In my particular case (and I presume, the intention of> 'bacground triggers' is that) a programmer (like myself) is not really> interested in the outcome of thet trigger process. So there is no> 'technical' need for him/her to create the server side proces *provided*> hi/she can setup a job *within* the database server itself, and just go> away.>

That's the idea of 'background triggers'.>

Surely, there are work arounds. Like the LISTEN/NOTIFY server (not> datagase server, but system server) daemon that takes database server> notiffications. And even a system server daemon, that simply uses> synchronous database communication (like I did in my case). The problem> is, that I have this 'eatching desire', to have such technical issues> supported 'withing the framework of RDBM architecture'.>

That's why I keep thinking, that the solution I used is actually> 'bitting the fances', while gurus do it some other, *better* way. But if> not, a think that 'bakground triggers' could help here.>

I think you did. I just feel that 'background triggers' is 'real life'> engineering issue, so it should get some backing from RDBMS.>

just my 2c.>

-R>

-------------------­--------(end of broadcast)---------­------------------> TIP 2: Don't 'kill -9' the postmaster>

The problem with client code processing a function is that unless you > are using threads (my client application is not in a multi-threaded > environment), the client has to wait for the server to return from the > end of the function. I don't want the client to wait and the result > doesn't affect the user at all, so there is no reason why he should wait.

Kenneth Downs wrote:>

-------------------­--------(end of broadcast)---------­------------------> TIP 9: In versions below 8.0, the planner will ignore your desire to> choose an index scan if your joining column's datatypes do not> match

Actually, it 'sort of' works that way.>

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?

Why not have the INSERT go to an "inbox" table, a table whose only job > is to receive the data for future processing.>

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.>

IOW, have your "background trigger" be a stored procedure that is > invoked by the client, instead of trying to get the server to do it.>

-------------------­--------(end of broadcast)---------­------------------> TIP 9: In versions below 8.0, the planner will ignore your desire to> choose an index scan if your joining column's datatypes do not> match

No reason for him to wait. Even Windows supports background processing > (geez I think so anyway).>

If you are on Linux spawning a background process is a no-brainer, > there's plenty of ways to do it. There ought to be a way on windows I > would think.>>

-------------------­--------(end of broadcast)---------­------------------> TIP 4: Have you searched our list archives?>

http://archives.pos­tgresql.org

What web server and OS are you using? In linux/apache you can fork> off a process that runs the SP and then detach from it.

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...

It depends on something else...>

Is the process that is starting up the "background batch process"> making other changes to the data?

From the perspective of my earlier applications, it would be desired to

If the database supported background triggers, it might be implemented > by a daemon or by the Listen/Notify framework, but I really couldn't > care less. It is part of the database. I only need to write the function > and the trigger code with an option to run this in the background. No > matter what I do now, or how many environments I have, the database > handles it all. If I backup my database and install it on another > server, my process is still intact.>

The reason why this should be part of the database engine and not > another client application is because what you want is a trigger. All > you want is when a table is updated that another function should run, > you don't need any outside processing. The Listen/Notify framework is > needed for a case where you would like non-database actions to take > place. Your client application can then go and do what it needs to and > then comes back and can tell the database that it is done.>

Chris Browne wrote:>

-------------------­--------(end of broadcast)---------­------------------> TIP 4: Have you searched our list archives?>

http://archives.pos­tgresql.org

Well, you *clearly* need to have some sort of "daemon" running in> order to do this.>

I expect it will in effect be a LISTEN process that waits for clients> to submit NOTIFY requests.>

Even if you don't actually choose to use NOTIFY/LISTEN, per se, you'll> doubtless wind up creating an ad hoc, informally-specifie­d> implementation of part of it...

Triggers normally execute inside of a transaction.>

A COMMIT within a trigger could mean: "do a fork: fork-1) return to the> main and schedule COMMIT there, fork-2) continue in bacground".

Triggers normally execute inside of a transaction. >

A COMMIT within a trigger could mean: "do a fork: fork-1) return to the> main and schedule COMMIT there, fork-2) continue in bacground".>

And what if fork-1) returns to the main, attempts the COMMIT but instead and rolls back due > to a violated constraint? Where does that leave fork-2?>

Regards,> Thomas Hallgren

I don't think fork(2)ing a running backed is a good idea, probably it would> end up with major data corruption. You want to call fork(2) in your> application. Something like: "if (fork()==0) { reestablish connection, issue> trigger-code on the database } else { gracefully return };"

Hmm, I've got a feeling its something like "I don't feel like coding it in> application, so it would be better if community changed the backend> to do it". ­ However what you propose i 1,2,3,4 points is somewhat

similar to already existing 2PC (2-phase commit), which PostgreSQL> implements. Probably not what you want, but should be valuable to> know, I guess.

If your trigger would call COMMIT, the referential integrity triggers would

So... let's assume the "commit" whould not actually commit, but rather> start another backend and do the work [1]. The problem is that newly> started backed would not see the work until the old backend actually> COMMIT;

[1]: If you really insist on doing it this way, of course you may! Here is> a fishing rod:> write a trigger in PL/perlU, which will fork(); The newly started child will> use DBI to connect to database, and issue your query, and then call> exit(0) to be sure you don't return to backend. You might want to call> exec() with a pre-prepared script doing above work.>

From the perspective of the "main" backend, the trigger will call fork(),> and finish. And your application will commit.>

That's everything you need to do it the way you want it. Have fun!

No problem at all (at least in particular case of an application I have> in mind ­. The precedure that remains within fork-2 just does a time> consuming housekeeping. Like a cleanup - always succeeds, even if> sometimes is not really necesary (like in case of main rolling-back). >

And that's exacly why I thing that it should b