Thread: Asynchronous queries - processing listen (notify) in a procedural language

Hi there,

I'd like to make a real-time enabled database and I need to process data
asynchronously. Usually, there are many quick inserts sometimes causing
vast updates. I can't use triggers and rules because the transactions
are really long when there is the update (upgrade) needed.

Is there a way how to listen and trigger the notify messages in the
database (+-)immediately and/or to execute additional (trigger) queries
in other transactions?

In Oracle there is ON COMMIT trigger and an anonymous transaction that
allows commit inside triggers. However this is not possible in Postgres.
Moreover I can't know the check interval of the listening procedures in
an external application (can be anything from millis to days). I was so
desperate that I was thinking about own logging function.

Many thanks, Petr

Hello

look on orafce

http://www.postgres.cz/index.php/Oracle_functionality_%28en%29

Regards
Pavel Stehule


2010/2/21 Petr Chmelar <chmelarp@fit.vutbr.cz>:
> Hi there,
>
> I'd like to make a real-time enabled database and I need to process data
> asynchronously. Usually, there are many quick inserts sometimes causing
> vast updates. I can't use triggers and rules because the transactions
> are really long when there is the update (upgrade) needed.
>
> Is there a way how to listen and trigger the notify messages in the
> database (+-)immediately and/or to execute additional (trigger) queries
> in other transactions?
>
> In Oracle there is ON COMMIT trigger and an anonymous transaction that
> allows commit inside triggers. However this is not possible in Postgres.
> Moreover I can't know the check interval of the listening procedures in
> an external application (can be anything from millis to days). I was so
> desperate that I was thinking about own logging function.
>
> Many thanks, Petr
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On Sat, Feb 20, 2010 at 9:38 PM, Petr Chmelar <chmelarp@fit.vutbr.cz> wrote:
> Hi there,
>
> I'd like to make a real-time enabled database and I need to process data
> asynchronously. Usually, there are many quick inserts sometimes causing
> vast updates. I can't use triggers and rules because the transactions
> are really long when there is the update (upgrade) needed.
>
> Is there a way how to listen and trigger the notify messages in the
> database (+-)immediately and/or to execute additional (trigger) queries
> in other transactions?
>
> In Oracle there is ON COMMIT trigger and an anonymous transaction that
> allows commit inside triggers. However this is not possible in Postgres.
> Moreover I can't know the check interval of the listening procedures in
> an external application (can be anything from millis to days). I was so
> desperate that I was thinking about own logging function.

The only way that I know of to send notify 'in-transaction' is via
dblink...you just send 'notify x' as the query which commits and fires
the action.  It doesn't make sense to do this if your outer
transaction is very short in duration.

merlin

Merlin Moncure <mmoncure@gmail.com> writes:
> On Sat, Feb 20, 2010 at 9:38 PM, Petr Chmelar <chmelarp@fit.vutbr.cz> wrote:
>> Is there a way how to listen and trigger the notify messages in the
>> database (+-)immediately and/or to execute additional (trigger) queries
>> in other transactions?

> The only way that I know of to send notify 'in-transaction' is via
> dblink...you just send 'notify x' as the query which commits and fires
> the action.  It doesn't make sense to do this if your outer
> transaction is very short in duration.

It's not clear that it makes sense to do that in a long transaction,
either.  What are you notifying other sessions *about*?  Not your own
changes --- they won't be able to see those till you commit.  There's
a reason why NOTIFY is delayed till commit ...

            regards, tom lane

On Sun, Feb 21, 2010 at 9:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Merlin Moncure <mmoncure@gmail.com> writes:
>> On Sat, Feb 20, 2010 at 9:38 PM, Petr Chmelar <chmelarp@fit.vutbr.cz> wrote:
>>> Is there a way how to listen and trigger the notify messages in the
>>> database (+-)immediately and/or to execute additional (trigger) queries
>>> in other transactions?
>
>> The only way that I know of to send notify 'in-transaction' is via
>> dblink...you just send 'notify x' as the query which commits and fires
>> the action.  It doesn't make sense to do this if your outer
>> transaction is very short in duration.
>
> It's not clear that it makes sense to do that in a long transaction,
> either.  What are you notifying other sessions *about*?  Not your own
> changes --- they won't be able to see those till you commit.  There's
> a reason why NOTIFY is delayed till commit ...

Heh...I almost mentioned this on the listen/notify thread.  There is
actually a case for mid transaction notify that I rely on quite a bit:
when you need to request information from some client that is attached
to your database.  The database needs to signal the client and go get
the information and return it, preferably _inside_ the notifying
transaction so that you can have the information come back as a result
to the function that set up the notification.  The way I currently do
this currently is via dblink establish a receiving record that the
client stores it's response data with and block for it in the
transaction that set up the dblink,  Since it's read committed I can
block and wait for the data or a timeout.

With immediate notification and payloads, the dblink approach wouldn't
be needed.  I could establish the receiving record, and notify the
client with the id of the record I want the response data in as a
payload.  It's mainly a parlor trick, but I like being able fetch data
from a client in a single transaction based on an event.  So, I have
to basically state that while I can work around the current state
affairs quite nicely, I think that the assertion that you have to
necessarily wait for the txn to end before dispatching notify is
only_mostly_ true.  I'm pretty happy with the way things work now
though...the new notification system is awesome.

merlin

On Sun, Feb 21, 2010 at 10:32 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Sun, Feb 21, 2010 at 9:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Merlin Moncure <mmoncure@gmail.com> writes:
>>> On Sat, Feb 20, 2010 at 9:38 PM, Petr Chmelar <chmelarp@fit.vutbr.cz> wrote:
>>>> Is there a way how to listen and trigger the notify messages in the
>>>> database (+-)immediately and/or to execute additional (trigger) queries
>>>> in other transactions?
>>
>>> The only way that I know of to send notify 'in-transaction' is via
>>> dblink...you just send 'notify x' as the query which commits and fires
>>> the action.  It doesn't make sense to do this if your outer
>>> transaction is very short in duration.
>>
>> It's not clear that it makes sense to do that in a long transaction,
>> either.  What are you notifying other sessions *about*?  Not your own
>> changes --- they won't be able to see those till you commit.  There's
>> a reason why NOTIFY is delayed till commit ...
>
> Heh...I almost mentioned this on the listen/notify thread.  There is
> actually a case for mid transaction notify that I rely on quite a bit:
> when you need to request information from some client that is attached
> to your database.  The database needs to signal the client and go get
> the information and return it, preferably _inside_ the notifying
> transaction so that you can have the information come back as a result
> to the function that set up the notification.  The way I currently do
> this currently is via dblink establish a receiving record that the
> client stores it's response data with and block for it in the
> transaction that set up the dblink,  Since it's read committed I can
> block and wait for the data or a timeout.
>
> With immediate notification and payloads, the dblink approach wouldn't
> be needed.  I could establish the receiving record, and notify the

small correction here.  I would have to have the client establish the
record at the ID of the notifier's choosing (probably nextval() on a
sequence) and then wait for it to be inserted.  Obviously, you can't
generate a record and wait around for it to be updated in the same
transaction.

merlin