Skip site navigation (1) Skip section navigation (2)

Re: signals in ODBC?

From: Tibor Laszlo <ltibor(at)mail(dot)tiszanet(dot)hu>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-interfaces(at)postgresql(dot)org
Subject: Re: signals in ODBC?
Date: 2000-10-26 10:28:55
Message-ID: 00102613044103.00856@tir (view raw or flat)
Thread:
Lists: pgsql-interfaces
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(at)mail(dot)tiszanet(dot)hu

In response to

pgsql-interfaces by date

Next:From: Алексей ГиневскийDate: 2000-10-26 12:08:46
Subject: PsqlKoi8 Translation Dll for Win32
Previous:From: Enrico CominiDate: 2000-10-26 10:26:42
Subject: JDBC DRIVER

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group