Re: Using Postgresql as application server

On 08/16/2011 07:04 AM, Darren Duncan wrote:
> Chris Travers wrote:
>> On Mon, Aug 15, 2011 at 3:47 PM, Darren Duncan
>> <darren@darrenduncan.net> wrote:
>>> I believe we basically have all the foundation already, with maybe
>>> procedures executable outside transactions being the last major part.
>>>
>> Why is this desirable? Why is it more desirable than actually using
>> the listen/notify infrastructure that exists already?
>
> Maybe listen/notify is sufficient by itself. I withdraw my "procedures
> executable outside transactions" comment for now, and just bring it up
> later if I can think of a specific use case that other mechanisms don't
> satisfy. -- Darren Duncan
>
LISTEN/NOTIFY is good if you want to call an outside application,
however, if you want to call another database function, then it is just
plain wrong. I need a daemon that will listen to for notify calls so
that it can open a new database session and call the function. With an
asynchronous function, my function would be able to spawn a new session
and run in its own transaction while the calling function would be able
to complete and go away.
See http://archives.postgresql.org/pgsql-hackers/2011-04/msg01503.php
for my proposal on this issue.

One problem we have with LISTEN/NOTIFY (and I haven't found the cause
for this yet) is every once in a while my daemon stops listening. It may
be after a month of use or longer, and may be caused by the database
being restarted or something similar. When the daemon stops listening,
it doesn't give any errors or indication that it isn't working anymore.
It is only after a user complains that something hasn't been updated in
a day or so that we manually restart it. It doesn't happen very often,
but it does happen on occasion.