From: Tom Lane <tgl@sss.pgh.pa.us>
To: Thomas Lockhart <lockhart@alumni.caltech.edu>
Cc: Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
Cc: pgsql-interfaces@postgreSQL.org
Subject: Re: signals in ODBC?
Date: Wed, 25 Oct 2000 14:01:57 -0400
Message-ID: <29081.972496917@sss.pgh.pa.us> (raw)
In-Reply-To: <39F6F11D.79C5800B@alumni.caltech.edu>
References: <Pine.LNX.4.05.10010251411460.30412-100000@pc10.radnoti-szeged.sulinet.hu>
<39F6F11D.79C5800B@alumni.caltech.edu>
Thomas Lockhart <lockhart@alumni.caltech.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: tgl@sss.pgh.pa.us, lockhart@alumni.caltech.edu, tip@pc10.radnoti-szeged.sulinet.hu
Subject: Re: signals in ODBC?
In-Reply-To: <29081.972496917@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox