From: Tibor Laszlo <ltibor@mail.tiszanet.hu>
To: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: signals in ODBC?
Date: Thu, 26 Oct 2000 12:28:55 +0200
Content-Type: text/plain
References: 
 <Pine.LNX.4.05.10010251411460.30412-100000@pc10.radnoti-szeged.sulinet.hu>
	<39F6F11D.79C5800B@alumni.caltech.edu>
	<29081.972496917@sss.pgh.pa.us>
In-Reply-To: <29081.972496917@sss.pgh.pa.us>
MIME-Version: 1.0
Message-Id: <00102613044103.00856@tir>
Content-Transfer-Encoding: 8bit
Cc: pgsql-interfaces@postgresql.org

Tom Lane wrote:
>CREATE TABLE message_table (msg text, id serial primary key);
>
>Message senders do:
> BEGIN;
> LOCK TABLE message_table;
> INSERT INTO message_table ('message here');
> NOTIFY message_table;
> END;

As the documentation says, it could be part of a trigger or a rule on the
table. (AFAIK in this case the BEGIN and END are needless.)

> Message receivers start out with LISTEN message_table.  They must
> keep track of the highest message ID they've seen, and upon getting
> a notification they do
> 
> 	SELECT * FROM message_table WHERE id > highest_already_seen;

> You can extend this basic idea by adding a timestamp column, multiple
> data columns, etc etc.  It's a lot more flexible than any built-in
> notion of a data payload for a NOTIFY message could be.  Plus, old
> messages are still in the message table, which is of great use for
> freshly started clients (they can consult the table to see what's
> happened recently) not to mention amazingly helpful for debugging
> cross-client interactions.
> 
> In our case we had a nightly cron job that cleaned out
> more-than-a-day-old messages just before doing the nightly vacuum,
> but you could adjust the length of retention of old messages to suit
> your needs.

Thank you Tom! Its a simple solution.

Could this example be part of the documentation? It says more then the existing
comment in the NOTIFY Description:

"Higher-level mechanisms can be built by using tables in the database to pass
additional data (beyond a mere condition name) from notifier to listener(s)."
and
"...use NOTIFY to wake up applications that need to pay attention to something,
and use a database object (such as a sequence) to keep track of what happened
or how many times it happened."

The only problem is: how we catch the NOTIFY events in an ODBC based C++Builder
app? Can I share a connection with an ODBC hDbc? (I will have one only for
reading the changing data.) Or I have to get a new connection. Should it be in
an other thread? As I see, the libpq SampleProgram2 gives us a starting point.

-- 
Tibor Laszlo
ltibor@mail.tiszanet.hu