Re: background triggers? - Mailing list pgsql-general

Rafal Pietrak wrote:
> On Wed, 2006-05-24 at 13:36 +0200, Sim Zacks wrote:
>> My understanding of Listen/Notify is that it is a completely
>> disconnected process running on the database server.
>
> 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.
Well, exactly "not being interested in the outcome" is IMHO the reason
why your demands clash with archtecture of a RDBMS. Most RDBMS go a long
way to ensure consistency and safety of your data, once they promised
(usually by not raising an error at least on "commit") that they stored
them. This doesn't match very well with asynchronous processes for which
"nobody cares" (In the sense that there is nobody to reports errors to).

> 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'.
I wouldn't call this a workaround. It's a sane solution to your problem.
The only real downside I can see is that it requires you to implement
that daemon - but that can be solved once and for all - see below.

> 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 may not have understood exactly what you are trying to do, but from
>> what I understood, this will solve your problem.
>
> I think you did. I just feel that 'background triggers' is 'real life'
> engineering issue, so it should get some backing from RDBMS.
I don't think the RDBMS can do much more than provide a notification
framework - which postgres does (LISTEN / NOTIFY).

But what would probably ease your 'etching desire' is if there was a
general-purpose daemon that could be told the execute a given statement
either at predefined times, or when a notification arrives, or both.

Newer pgadmin3 versions include pgAgent, which is kind of a
postgres-aware cron daemon, as far as I know. I don't think that pgAgent
currently supports LISTEN/NOTIFY, but you could talk to the pgadmin
maintainers about adding support for that.

Then you wouldn't need to write the daemon yourself, and it would even
be "part of the framework" as long as you consider pgadmin to be "part
of the framework".

greetings, Florian Pflug



>
> just my 2c.
>
> -R
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>