Thread: Regarding NOTIFY

Regarding NOTIFY

From
Bret Stern
Date:
We have a concrete batching application composed of two parts.
1. The Monitor.
The Monitor cycles every 60 seconds, and looks into a Postgresql table
for jobs to run. Primarily these jobs update Postgresql tables with
data from external applications.

2. The Client.
The client schedules orders etc.

When a new product or customer is added to the Accounting or Batching
Controller (both external applications; and databases) the Client user
clicks a button and adds a job to run on the Monitor.

Is it possible use the NOTIFY event to serve more like an interrupt,
and trigger the Monitor to run immediately.

Can it be used with VB?
or
Should I use LibPQ?

Any suggestions welcome.


If this is the wrong list for these questions, let me know?

Bret Stern







Re: Regarding NOTIFY

From
Chris Travers
Date:
On Fri, Mar 9, 2012 at 8:53 AM, Bret Stern
<bret_stern@machinemanagement.com> wrote:
> We have a concrete batching application composed of two parts.
> 1. The Monitor.
> The Monitor cycles every 60 seconds, and looks into a Postgresql table
> for jobs to run. Primarily these jobs update Postgresql tables with
> data from external applications.
>
> 2. The Client.
> The client schedules orders etc.
>
> When a new product or customer is added to the Accounting or Batching
> Controller (both external applications; and databases) the Client user
> clicks a button and adds a job to run on the Monitor.
>
> Is it possible use the NOTIFY event to serve more like an interrupt,
> and trigger the Monitor to run immediately.

Yes.  Note that Notify fires when the transaction is committed, and
you have to poll the client library to see if one was received.

>
> Can it be used with VB?
> or
> Should I use LibPQ?

I believe the .net provider for PostgreSQL supports this.  I know
libpq does.  Maybe others here know better than I.

Best Wishes
Chris Travers

Re: Regarding NOTIFY

From
Kiriakos Georgiou
Date:
Yes, can do.  Just have an insert trigger on the jobs table that notifies the monitor, something like:

CREATE OR REPLACE FUNCTION notify_monitor()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
    BEGIN
        NOTIFY monitor;
        RETURN NULL;
    END
$$

CREATE TRIGGER jobs_trigger
AFTER INSERT
ON jobs
FOR EACH STATEMENT
EXECUTE PROCEDURE notify_monitor();

Then all the monitor has to do is wait for notifications.  For C, see
http://www.postgresql.org/docs/9.1/static/libpq-notify.html
ruby-pg (the official ruby api to postgresql) has wait_for_notify(), which pretty much implements what the postgresql
docssuggest. 

Kiriakos
http://www.mockbites.com



On Mar 9, 2012, at 11:53 AM, Bret Stern wrote:

> We have a concrete batching application composed of two parts.
> 1. The Monitor.
> The Monitor cycles every 60 seconds, and looks into a Postgresql table
> for jobs to run. Primarily these jobs update Postgresql tables with
> data from external applications.
>
> 2. The Client.
> The client schedules orders etc.
>
> When a new product or customer is added to the Accounting or Batching
> Controller (both external applications; and databases) the Client user
> clicks a button and adds a job to run on the Monitor.
>
> Is it possible use the NOTIFY event to serve more like an interrupt,
> and trigger the Monitor to run immediately.
>
> Can it be used with VB?
> or
> Should I use LibPQ?
>
> Any suggestions welcome.
>
>
> If this is the wrong list for these questions, let me know?
>
> Bret Stern
>
>
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


Re: Regarding NOTIFY

From
Jasen Betts
Date:
On 2012-03-09, Bret Stern <bret_stern@machinemanagement.com> wrote:
> We have a concrete batching application composed of two parts.
> 1. The Monitor.
> The Monitor cycles every 60 seconds, and looks into a Postgresql table
> for jobs to run. Primarily these jobs update Postgresql tables with
> data from external applications.
>
> 2. The Client.
> The client schedules orders etc.
>
> When a new product or customer is added to the Accounting or Batching
> Controller (both external applications; and databases) the Client user
> clicks a button and adds a job to run on the Monitor.
>
> Is it possible use the NOTIFY event to serve more like an interrupt,
> and trigger the Monitor to run immediately.

Reasonably immediately.

> Can it be used with VB? or Should I use LibPQ?

I used libpq with VB6 when I needed this feature 3 years ago.

I had to write a little DLL to wrap the libpq calls so that VB could
call them (actually I repackaged the wrapper that the main application uses)

> If this is the wrong list for these questions, let me know?

what language are you really using? VB is kind of vague it could mean
.net (lots of people say VB when they mean .net, makes it hard to get
questions about VB answered)

--
⚂⚃ 100% natural

Re: Regarding NOTIFY

From
Bret Stern
Date:
On Mon, 2012-04-09 at 09:03 +0000, Jasen Betts wrote:
> On 2012-03-09, Bret Stern <bret_stern@machinemanagement.com> wrote:
> > We have a concrete batching application composed of two parts.
> > 1. The Monitor.
> > The Monitor cycles every 60 seconds, and looks into a Postgresql table
> > for jobs to run. Primarily these jobs update Postgresql tables with
> > data from external applications.
> >
> > 2. The Client.
> > The client schedules orders etc.
> >
> > When a new product or customer is added to the Accounting or Batching
> > Controller (both external applications; and databases) the Client user
> > clicks a button and adds a job to run on the Monitor.
> >
> > Is it possible use the NOTIFY event to serve more like an interrupt,
> > and trigger the Monitor to run immediately.
>
> Reasonably immediately.
>
> > Can it be used with VB? or Should I use LibPQ?
>
> I used libpq with VB6 when I needed this feature 3 years ago.
>
> I had to write a little DLL to wrap the libpq calls so that VB could
> call them (actually I repackaged the wrapper that the main application uses)
>
> > If this is the wrong list for these questions, let me know?
>
> what language are you really using? VB is kind of vague it could mean
> .net (lots of people say VB when they mean .net, makes it hard to get
> questions about VB answered)
>
Was referring VB6 (I still prefer the IDE), but I can write in most
languages; (codeblocks for the libpq project).

> --
> ⚂⚃ 100% natural
>
>

Thanks for the comments.