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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-interfaces by date

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