Skip site navigation (1) Skip section navigation (2)

Re: Pet Peeves?

From: "A(dot)M(dot)" <agentm(at)themactionfaction(dot)com>
To: pgsql <pgsql-general(at)postgresql(dot)org>
Subject: Re: Pet Peeves?
Date: 2009-02-05 15:45:12
Message-ID: 3F93FE44-21B0-4A92-AF87-D9F9FCF5BA87@themactionfaction.com (view raw or flat)
Thread:
Lists: pgsql-general
On Feb 5, 2009, at 6:08 AM, Greg Stark wrote:

> On Wed, Feb 4, 2009 at 6:42 PM, Simon Riggs <simon(at)2ndquadrant(dot)com>  
> wrote:
>>
>> As A.M. says elsewhere, it would be good to have a trigger that  
>> fired a
>> NOTIFY that was picked up by a scheduled job that LISTENs every 10
>> minutes for certain events.
>>
>> We need a place for code that is *not* directly initiated by a user's
>> actions, yet works as part of a closed loop system.
>
>
> Can't you do this today in about three lines of your favourite
> scripting language?
>
> I used to do this in perl on Oracle and that was without anything like
> LISTEN/NOTIFY to optimize it. Web pages just inserted a record and
> went on about their business while a worker daemon scanned for new
> records and generated notification mails.
>
> The problem with trying to push everything into the database is that
> it ends up sucking your entire application into the database. That
> limits your choice of languages and tools, and also creates a huge
> bottleneck.

In addition to the other response, one should also take security  
scenarios into consideration. If role X installs an event handler  
(timed or via notify), I would expect the callback to be run as that  
role X. This is currently impossible to safely do from outside the  
database because SET SESSION AUTHORIZATION can be trivially revoked  
with RESET SESSION AUTHORIZATION. Also, LISTEN/NOTIFY really need to  
allow for additional user info to be attached (it's on the TODO list)  
and they have further security problems because they are global  
broadcasts. I would expect an integrated event dispatch mechanism to  
handle the complexity of security as well as what to do in case of  
rollback.

So, no, this is not something can be slapped together from outside the  
db.

Cheers,
M

In response to

pgsql-general by date

Next:From: Dimitri FontaineDate: 2009-02-05 17:08:39
Subject: Re: Pet Peeves?
Previous:From: Filip RembiałkowskiDate: 2009-02-05 15:18:31
Subject: Re: Array, bytea and large objects

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group