Re: signals in ODBC?

Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu> writes:
>> Browsing the documentation I found NOTIFY and LISTEN. Can I use them
>> within an ODBC connection? Is it possible to catch a NOTIFY signal this way?

> LISTEN happens on a table (or actually any arbitrary name; it doesn't
> need an underlying object). But afaik you have to do a query to get your
> LISTEN to fire on the client, since none of our clients are
> multithreaded.

I don't know whether the ODBC driver, or even the ODBC API, allows for
asynchronous NOTIFY, but it works just fine in plain libpq. My former
company used to rely on NOTIFY all the time for cross-client signaling.
It hasn't been necessary to issue dummy queries since 6.4 or so.

>> As I realized, the NOTIFY statement doesn't have a second parameter,
>> i.e. I cannot give any other signal to an other connection than
>> "Yes" or "No". What about future plans?

> No future plans afaik.

The normal usage is simply to inform other clients that you've changed
something, and then they go do SELECT queries to find out what. The
typical case is you NOTIFY on a table name to indicate the fact that
you changed that table, but you could devise more complicated NOTIFY
name conventions to give a better idea what to look for.

If you want to pass messages per se, you could do something like this:

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;

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;

(The point of the LOCK for message senders is to ensure that message
sender transactions commit in the exact order that they obtain message
IDs in. Otherwise there's a race condition where senders may commit
out of sequence-number order, possibly allowing receivers to miss some
messages. Alternatively you could use more complex bookkeeping in the
receivers so they can cope with out-of-order message IDs, but for our
purposes the LOCK was a painless solution.)

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.

regards, tom lane