Message-ID: <4E4B6828.1050506@compulab.co.il>
Date: Wed, 17 Aug 2011 10:05:12 +0300
From: Sim Zacks <sim@compulab.co.il>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.18) Gecko/20110617 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
To: pgsql-general@postgresql.org
Subject: Re: Using Postgresql as application server
References: 
 <CAN2Y=uPjF14F5A9JXj02_vaPw4TMR0WGQid_w80xt4sC8o4qGg@mail.gmail.com>
	<4E498536.9080109@darrenduncan.net>
	<008301cc5b95$e287b120$a7971360$@yahoo.com>
	<4E49A208.5070604@darrenduncan.net>
	<CAKt_ZftgpuGNbyxvQ_Bf0QrjahY5=HbxcoUUuxUYCdKbY=rp0g@mail.gmail.com>
	<4E49EC4D.1090707@darrenduncan.net>
In-Reply-To: <4E49EC4D.1090707@darrenduncan.net>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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.