Thread: signals in ODBC?
My goal is for a connection to receive signals from other connections. E.g., I would get the information about changing a row of a table by an other connection. This would be great having the information at once. An ugly and slow solution is to send queries to the server e.g. twice a minute or so---but is there a better way? 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? 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? Regards, Zoltan
> My goal is for a connection to receive signals from other connections.
> E.g., I would get the information about changing a row of a table by
> an other connection. This would be great having the information at once.
> An ugly and slow solution is to send queries to the server e.g. twice
> a minute or so---but is there a better way?
afaik we need multithreaded clients (and servers) to be able to do this.
It would be a nice capability, but asynchronous communication requires
that you set up a separate thread to receive a callback. I'm pretty sure
we have none of the required infrastructure in our existing
communication protocol.
> 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.
> 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. Commercial Ingres allowed a text string to be
associated with a NOTIFY message, which presumably would require a more
involved (and difficult to manage) underlying structure to be created.
- Thomas
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
> > 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. Commercial Ingres allowed a text string to be > associated with a NOTIFY message, which presumably would require a more > involved (and difficult to manage) underlying structure to be created. Huh? First of all, the documentation says that NOTIFY does take an argument. Secondly, I tried it in two psql's and it appears to work. I now understand what the other talk was about, LISTEN is only checked once after each query (correct?). Now, as far as slow and ugly, remember that the query could always just be something like select 1; -Cedar
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
> Huh? First of all, the documentation says that NOTIFY does take an
> argument. Secondly, I tried it in two psql's and it appears to work. I
> now understand what the other talk was about, LISTEN is only checked once
> after each query (correct?).
I was not clear on my nonsequiteur: Commercial Ingres allows you to post
a *message* with the notifying event, rather than just the event itself.
But it is not relevant to the current PostgreSQL implementation, so why
did I add to the confusion? I don't know ;)
- Thomas
At 14:01 25/10/00 -0400, Tom Lane wrote: >> 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. > ODBC 1 & 2 don't. I doubt 3 does either. What would be really kind of nice is if we had: LISTEN <name> [<port>] which would send notifications to the specified port on the client machine. Then with a small amount of effort, ODBC users could take advantage of notifications. Does this sound easy/hard and worthwhile? ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Philip Warner <pjw@rhyme.com.au> writes:
> ODBC 1 & 2 don't. I doubt 3 does either. What would be really kind of nice
> is if we had:
> LISTEN <name> [<port>]
> which would send notifications to the specified port on the client machine.
> Then with a small amount of effort, ODBC users could take advantage of
> notifications. Does this sound easy/hard and worthwhile?
Not sure I see the point. If there's to be a separate connection, you
might as well just fire up a libpq connection. Someone else already
pointed out that you could use ODBC for your main data transfer, if you
like ODBC, and use a second connection through libpq only to get
notifications.
AFAIK there's no fundamental reason that NOTIFY support couldn't be
added to our ODBC driver, it's just that it would fall outside the
ODBC API spec. But then a second connection through libpq isn't ODBC
compliant either.
My vote would be to spend time updating the driver, rather than adding
a wart onto the backend's LISTEN support.
regards, tom lane
At 10:43 26/10/00 -0400, Tom Lane wrote: > >Not sure I see the point. If there's to be a separate connection, you >might as well just fire up a libpq connection. Someone else already >pointed out that you could use ODBC for your main data transfer, if you >like ODBC, and use a second connection through libpq only to get >notifications. I hadn't realized libpq worked without Cygwin etc. >AFAIK there's no fundamental reason that NOTIFY support couldn't be >added to our ODBC driver, it's just that it would fall outside the >ODBC API spec. But then a second connection through libpq isn't ODBC >compliant either. I suspect that a libpq connection would be the go; there is no facility that I am aware of in ODBC for async callbacks, which is what you'd want for the NOTIFY stuff to work well. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/