Re: [GENERAL] Listen/notify, equivalents on other DBs
Thomas Kellerer wrote: Mark Morgan Lloyd schrieb am 25.09.2015 um 23:41: I'm trying to get support for PostgreSQL's listen/notify into a development environment, but since it supports multiple database backends: can anybody comment on how many other servers have a comparable facility? Minimal research has allowed me to code something for Firebird/Interbase, but I'm not in a position to investigate Oracle, IBM, MS and the rest. I'd appreciate any general comments from somebody who has broad SQL experience, I'm not asking for example code. For Oracle this would be the "change notification" feature which goes beyond what Postgres can do if I'm not mistaken as you can register a query and the client is notified if the result of that query would change: http://docs.oracle.com/database/121/JJDBC/dbchgnf.htm#JJDBC28815 Thanks for that, very useful. There's minimal info on the Firebird/Interbase equivalent at http://www.firebirdsql.org/file/documentation/papers_presentations/Power_Firebird_events.pdf but my recollection is that it's very lightweight- no useful payload. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Listen/notify, equivalents on other DBs
Mark Morgan Lloyd schrieb am 25.09.2015 um 23:41: I'm trying to get support for PostgreSQL's listen/notify into a development environment, but since it supports multiple database backends: can anybody comment on how many other servers have a comparable facility? Minimal research has allowed me to code something for Firebird/Interbase, but I'm not in a position to investigate Oracle, IBM, MS and the rest. I'd appreciate any general comments from somebody who has broad SQL experience, I'm not asking for example code. For Oracle this would be the "change notification" feature which goes beyond what Postgres can do if I'm not mistaken as you can register a query and the client is notified if the result of that query would change: http://docs.oracle.com/database/121/JJDBC/dbchgnf.htm#JJDBC28815 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Listen/notify, equivalents on other DBs
I'm trying to get support for PostgreSQL's listen/notify into a development environment, but since it supports multiple database backends: can anybody comment on how many other servers have a comparable facility? Minimal research has allowed me to code something for Firebird/Interbase, but I'm not in a position to investigate Oracle, IBM, MS and the rest. I'd appreciate any general comments from somebody who has broad SQL experience, I'm not asking for example code. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Listen/notify and ODBC
In early 2009 I asked whether there was a hack to allow PostgreSQL's listen/notify commands via ODBC, some while later I believe that somebody sent me example code encoded as base64 which erroneously got deleted as spam. Google suggests that several other people have asked about this over the years: if anybody still has this example do you think you could post a URL where it might be found? My apologies for posting this here as well as in the ODBC ML. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] LISTEN/NOTIFY problem
Thank you guys for your replies!
It is definitely something wrong with my code, here is the thread which does
the listening:
http://mitek.id.au/flex/DBListener.cpp
The rest of application is fairly big and does not relate to the database.
For libpq calls I am using the SPTK (http://sptk.net) library. I've added
additional wrappers for PGsocket, PQfreemem.
So the current code works without any problem, but as soon as I remove
subcribe() function from the loop and call it just once, daemon eventually
will stop getting notifications.
There is nothing wrong with the socket or db connections - all possible
error will be logged.
Cheers,
Dmitri.
On Mon, Mar 23, 2009 at 10:00 PM, Dmitri Girski mi...@lizardsw.com wrote:
Hi everybody,
I've got a weird problem with LISTEN/NOTIFY.
My C++ app subscribes for the notifications, just like in libpq examples:
http://www.postgresql.org/docs/8.3/static/libpq-example.html
The only difference, that I am setting the timeout on select just to check
if application wants to exit.
//open session
//subscribe
while(!exit)
{
sock = PGsocket(conn)
res = select(sock);
if (res)
//check if it timeout
//or if there was a notification
}
The problem that I am facing is that after some time notifications stop
coming.
select() returns on timeout and nothing else.
Just as a test a included UNLISTEN/LISTEN sequence into the loop, so after
timeout or event application re-subscribes. And this helps.
The question is, what I am doing wrong with the code in the first place?
Any help is appreciated.
Cheers,
Dmitri.
--
@Gmail
--
@Gmail
[GENERAL] LISTEN/NOTIFY problem
Hi everybody,
I've got a weird problem with LISTEN/NOTIFY.
My C++ app subscribes for the notifications, just like in libpq examples:
http://www.postgresql.org/docs/8.3/static/libpq-example.html
The only difference, that I am setting the timeout on select just to check
if application wants to exit.
//open session
//subscribe
while(!exit)
{
sock = PGsocket(conn)
res = select(sock);
if (res)
//check if it timeout
//or if there was a notification
}
The problem that I am facing is that after some time notifications stop
coming.
select() returns on timeout and nothing else.
Just as a test a included UNLISTEN/LISTEN sequence into the loop, so after
timeout or event application re-subscribes. And this helps.
The question is, what I am doing wrong with the code in the first place?
Any help is appreciated.
Cheers,
Dmitri.
--
@Gmail
Re: [GENERAL] LISTEN/NOTIFY problem
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org]on Behalf Of Dmitri Girski
Sent: Monday, 23 March 2009 10:00 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] LISTEN/NOTIFY problem
Hi everybody,
I've got a weird problem with LISTEN/NOTIFY.
My C++ app subscribes for the notifications, just like in libpq examples:
http://www.postgresql.org/docs/8.3/static/libpq-example.html
The only difference, that I am setting the timeout on select just to check if
application wants to exit.
//open session
//subscribe
while(!exit)
{
sock = PGsocket(conn)
res = select(sock);
if (res)
//check if it timeout
//or if there was a notification
}
The problem that I am facing is that after some time notifications stop coming.
select() returns on timeout and nothing else.
Just as a test a included UNLISTEN/LISTEN sequence into the loop, so after
timeout or event application re-subscribes. And this helps.
The question is, what I am doing wrong with the code in the first place?
Any help is appreciated.
Cheers,
Dmitri.
--
@Gmail
Cannot tell what is wrong with you piece of code.
But below is a routine I use for notifications.
No problems with it so far.
Hope it helps.
Allan
/*
** Structure to hold the connection data
*/
typedef struct _condetails
{
char *pghost;
char *pgport;
char *pgoptions;
char *pgtty;
char *dbname;
char *pguser;
char *pgpswd;
PGconn *conn;
int bpid;
} CONDETAILS;
/*
** Register for a database notification
*/
int reg_notification( CONDETAILS *cd, const char *notif )
{
char *sqlnotify, sql[128];
PGconn* conn;
PGresult* res;
conn = cd-conn;
sqlnotify = listen %s;
/*
** check to see that the backend connection was successfully made
*/
if ( PQstatus( conn ) == CONNECTION_BAD )
{
sysErr( reg_notification(). %s, PQerrorMessage( conn ) );
PQfinish( conn );
condetails( cd );
return -1;
}
/*
** Register
*/
sprintf( sql, sqlnotify, notif );
res = PQexec( conn, sql );
if ( PQresultStatus( res ) != PGRES_COMMAND_OK )
{
sysErr( reg_notification(). listen command failed. %s,
PQerrorMessage( conn ) );
PQclear( res );
return -1;
}
PQclear( res );
return 0;
}
/*
** Wait for a database notification or time out
** This is an async method.
**
** Return only the first notification not generated by me
** and flush the rest.
**
** Probly should do something smarter.
*/
int wait_db_notification( CONDETAILS *cd, char *notif, const long sec, const
long usec )
{
int sock, ret;
fd_set input_mask;
PGconn* conn;
PGnotify *notify;
struct timeval timeout;
conn = cd-conn;
/*
** check to see that the backend connection was successfully made
*/
if ( PQstatus( conn ) == CONNECTION_BAD )
{
sysErr( wait_db_notification(). %s, PQerrorMessage( conn ) );
PQfinish( conn );
condetails( cd );
return -1;
}
sock = PQsocket( conn );
if ( sock 0 )
{
sysErr( wait_db_notification(). Could not get socket
descriptor. %s, PQerrorMessage( conn ) );
return -1;
}
FD_ZERO( input_mask );
FD_SET( sock, input_mask );
timeout.tv_sec = sec;
timeout.tv_usec = usec;
ret = select( sock + 1, input_mask, NULL, NULL, timeout );
if ( ret 0 )
{
sysErr( %d: %s, __LINE__, wait_db_notification(). Select on
db socket failed );
if ( errno != EINTR )
exit( 1 );
/*
** Been interrupted by a trappable signal
** Force going to the top of the loop to handle it.
-*/
return -1;
}
if ( ret == 0 )
{
/*
** Time out occurred.
*/
return 0;
}
/*
** Some activity on the db
*/
ret = -1;
PQconsumeInput( conn );
do
{
notify = PQnotifies( conn );
if ( notify != NULL )
{
if ( cd-bpid != notify-be_pid )
{
strcpy( notif, notify-relname );
ret = 1
[GENERAL] LISTEN/NOTIFY and python
Hi - Does anyone have any suggestions for how to write client python code that uses LISTEN? The only thing I can find is the older PyGreSQL module (import pg), which has getnotify(), but this seems to require busy-waiting. Is there any way to do this that will use select() or something under the covers, so my client doesn't spin up the cpu? (Yes, I know I could poll-sleep-poll-sleep - that's what I'll do if there's nothing cleaner.) - John D. Burger MITRE ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] LISTEN/NOTIFY and python
John D. Burger [EMAIL PROTECTED] writes: Does anyone have any suggestions for how to write client python code that uses LISTEN? The only thing I can find is the older PyGreSQL module (import pg), which has getnotify(), but this seems to require busy-waiting. Is there any way to do this that will use select() or something under the covers, so my client doesn't spin up the cpu? (Yes, I know I could poll-sleep-poll-sleep - that's what I'll do if there's nothing cleaner.) The standard approach when using libpq directly is to get the file descriptor number of the backend connection with PQsocket(), then include that in the set of FDs that the client app's idle loop select()s or poll()s on. Read-ready on the socket doesn't necessarily mean that a NOTIFY has arrived, but it's at least sufficient info to justify waking up and checking libpq's status. Dunno if you can translate that into python readily ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] LISTEN/NOTIFY and python
Tom Lane wrote: The standard approach when using libpq directly is to get the file descriptor number of the backend connection with PQsocket(), then include that in the set of FDs that the client app's idle loop select()s or poll()s on. And Tino Wildenhain, in off-list mail, described getting the socket- fd from the PyGreSQL connection object and doing something analogous. It turns out that Python's listen() takes ints =or= objects with a fileno() method, whence it gets the int, and PyGreSQL's connection objects qualify. So I can do this: import pg, select con = pg.connect(...) con.query(listen foo) while True: select.select([con], [], []) # Wait for it ... print con.getnotify() I wish I could do this with the more standard pgdb module, but, then again, LISTEN/NOTIFY aren't standard. Thanks, Tino and Tom, for the pointers toward this solution. - John D. Burger MITRE ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] LISTEN/NOTIFY and python
John D. Burger schrieb: Tom Lane wrote: The standard approach when using libpq directly is to get the file descriptor number of the backend connection with PQsocket(), then include that in the set of FDs that the client app's idle loop select()s or poll()s on. And Tino Wildenhain, in off-list mail, described getting the socket-fd from the PyGreSQL connection object and doing something analogous. It turns out that Python's listen() takes ints =or= objects with a fileno() method, whence it gets the int, and PyGreSQL's connection Well actually fileno() just returns an int and thats what the syscalls expect as filehandle. Sockets have that method too. And the PyGreSQL connection has a (tcp-) socket under the hood. objects qualify. So I can do this: import pg, select con = pg.connect(...) con.query(listen foo) while True: select.select([con], [], []) # Wait for it ... print con.getnotify() I wish I could do this with the more standard pgdb module, but, then again, LISTEN/NOTIFY aren't standard. Thanks, Tino and Tom, for the pointers toward this solution. Ah, here is the summery of my solution as promised: import select from pyPgSQL import PgSQL db=PgSQL.connect( ... ) cur=db.cursor() cur.execute(LISTEN baskets) # if baskets is the table name db.commit() while True: rlist,wlist,xlist=select.select([db.conn.socket],[],[],20*60) if rlist: if db.conn.socket in rlist: db.conn.consumeInput() # - thats the important bit n=db.conn.notifies() if n: print Backend with pid %s asks for us. % n.be_pid ... do something usefull ... HTH. Tino ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] LISTEN / NOTIFY
Is there a way I can have notifications to be streamed to the listener, so I don't need to poll with LISTEN? LISTEN foo; LISTEN NOTIFY foo; NOTIFY Asynchronous notification foo received from server process with PID 3593. This does work for the same backend, but not for notifications issued from another one. Thanks! -- Regards, Hannes Dorbath ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] LISTEN / NOTIFY
On Fri, Dec 08, 2006 at 03:38:49PM +0100, Hannes Dorbath wrote: Is there a way I can have notifications to be streamed to the listener, so I don't need to poll with LISTEN? LISTEN foo; LISTEN NOTIFY foo; NOTIFY Asynchronous notification foo received from server process with PID 3593. This does work for the same backend, but not for notifications issued from another one. AIUI they are, it's just that the client needs to be looking for incoming data. If you're using psql for example, I don't beleive it checks for incoming data until you send a command. How you actually acheive that in your code is a seperate question and depends on the language you're using. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] LISTEN/NOTIFY for lightweight replication
Ted Shab wrote: Hi, I'm trying to come up with a relatively simple multi-master replication solution. This is for multiple databases that need to be discreet, and change relatively infrequently (10-30 updates an hour), and almost never update each others data (less than once a day). The TCL-based replication project for multi-master is troublesome to configure and seems to really impact performance. It can be assumed that the master-slave setup will not work for me, nor do we want to purchase a commercial soluton, nor can we run this all from one central database. e. If there is a field level conflict, raise an exception (TBD). Exception handling and failure recovery are what makes for all the work in replication. I don't think a pure listen/notify setup will be enough because iirc the system doesn't guarantee delivery of multiple notifications if 1 are queued. Have you looked into the possibility of using dblink to handle updates of each others' data? That would mean your problem reverting to one of single-master replication. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] LISTEN/NOTIFY for lightweight replication
Richard, Thanks for the response. I'll look into both the dblink and iirc. Do you know of any extended examples of either? --Ted --- Richard Huxton [EMAIL PROTECTED] wrote: Ted Shab wrote: Hi, I'm trying to come up with a relatively simple multi-master replication solution. This is for multiple databases that need to be discreet, and change relatively infrequently (10-30 updates an hour), and almost never update each others data (less than once a day). The TCL-based replication project for multi-master is troublesome to configure and seems to really impact performance. It can be assumed that the master-slave setup will not work for me, nor do we want to purchase a commercial soluton, nor can we run this all from one central database. e. If there is a field level conflict, raise an exception (TBD). Exception handling and failure recovery are what makes for all the work in replication. I don't think a pure listen/notify setup will be enough because iirc the system doesn't guarantee delivery of multiple notifications if 1 are queued. Have you looked into the possibility of using dblink to handle updates of each others' data? That would mean your problem reverting to one of single-master replication. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] LISTEN/NOTIFY for lightweight replication
On Wed, Oct 13, 2004 at 08:32:04AM -0700, Ted Shab wrote: Thanks for the response. I'll look into both the dblink and iirc. That's actually only dblink. IIRC is an acronym, meaning if I recall correctly, IIRC. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) The West won the world not by the superiority of its ideas or values or religion but rather by its superiority in applying organized violence. Westerners often forget this fact, non-Westerners never do. (Samuel P. Huntington) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] LISTEN/NOTIFY for lightweight replication
Ted Shab wrote: Richard, Thanks for the response. I'll look into both the dblink and iirc. Do you know of any extended examples of either? dblink is in the contrib/ folder of the source distribution and possibly your packaged version if you use such a thing. Never needed it myself, but the documentation looks clear enough. As for listen/notify possibly dropping duplicate notifications... Ah! it's in the SQL COMMANDS reference part of the manuals NOTIFY behaves like Unix signals in one important respect: if the same condition name is signaled multiple times in quick succession, recipients may get only one notify event for several executions of NOTIFY. So it is a bad idea to depend on the number of notifies received. Instead, 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. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] LISTEN/NOTIFY for lightweight replication
Thanks. I was thinking iirc was the transport protocol :-) Looks like dblink is the best bet here. --Ted --- Richard Huxton [EMAIL PROTECTED] wrote: Ted Shab wrote: Richard, Thanks for the response. I'll look into both the dblink and iirc. Do you know of any extended examples of either? dblink is in the contrib/ folder of the source distribution and possibly your packaged version if you use such a thing. Never needed it myself, but the documentation looks clear enough. As for listen/notify possibly dropping duplicate notifications... Ah! it's in the SQL COMMANDS reference part of the manuals NOTIFY behaves like Unix signals in one important respect: if the same condition name is signaled multiple times in quick succession, recipients may get only one notify event for several executions of NOTIFY. So it is a bad idea to depend on the number of notifies received. Instead, 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. -- Richard Huxton Archonet Ltd ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] LISTEN/NOTIFY for lightweight replication
Hi, I'm trying to come up with a relatively simple multi-master replication solution. This is for multiple databases that need to be discreet, and change relatively infrequently (10-30 updates an hour), and almost never update each others data (less than once a day). The TCL-based replication project for multi-master is troublesome to configure and seems to really impact performance. It can be assumed that the master-slave setup will not work for me, nor do we want to purchase a commercial soluton, nor can we run this all from one central database. I'm considering the following, and am requesting advice and any suggestions: a. Use listen/notify to develop a notification when changes are made. This could be done between each node, or not (i.e. it could be a chain instead). b1. All of the add events are using sequences so each node has a unique set of new records. b2. When an add, update or delete is recorded, DDL of this is passed via the notify. c. If no local event happened prior during this event envelope (i.e. since the last update notification but before the new event completed), perform the event. d. If there is a record level conflict but no field level one, perform the event. e. If there is a field level conflict, raise an exception (TBD). There are plenty of things that might not work here, but I'm particularly interested in: 1. If this is brain-dead because of performance issues, I'd like to know upfront. 2. Is there a way to get at the system tables that would contain overall change events? Otherwise, is this information available in some log event? 3. Can this be readily translated to DDL? 4. Does anyone have some extended examples of using listen/notify, especially in any kind of distributed transaction capability? Thanks! __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] LISTEN/NOTIFY with JDBC
On Wed, 12 May 2004, Glenn Sullivan wrote: Thanks for the response. I have been using Sun's JDBC. Do I take it that I need to create and use the Postgres JDBC to get the ability to do NOTIFY/LISTEN? You have been using the JDBC API provided by Sun as a number of interfaces (java.sql.*) which are implemented behind the scenes by the postgresql JDBC driver in concrete classes. Listen/Notify is a postgresql extension not covered in the standard java.sql.* API, so must cast the standard interfaces to postgresql implementations so that you can access the additional functionality provided. For example when using the postgresql JDBC driver java.sql.Connection is actually an instance of org.postgresql.PGConnection, so you can cast your Connection to a PGConnection to use the notification API. Unfortunately the PGConnection and other extensions are either poorly documented or completely undocumented. Combining my example with a brief look at the source code for PGConnection and PGNotification should hopefully get you going. Kris Jurka ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] LISTEN/NOTIFY with JDBC
Kris,
Thanks for the response. I have been using Sun's JDBC.
Do I take it that I need to create and use the Postgres
JDBC to get the ability to do NOTIFY/LISTEN?
Glenn
Kris Jurka wrote:
On Tue, 11 May 2004, Glenn Sullivan wrote:
Hi,
I have been trying to get LISTEN/NOTIFY working in with JDBC. I cannot seem
to get notified. I looked in the e-mail archive and saw a lot of similiar
questions a couple of years ago. I never could find any answers in the
e-mail nor in the documentation. Perhaps I just missed it.
I have tried the following code snipit:
Connection db = DriverManager.getConnection(url, user, passwd);
Statement sql = db.createStatement();
sql.execute(LISTEN mytest);
db.clearWarnings();
for(int i=0; i 10 ; i ++) {
Thread.sleep(3000);
SQLWarning warn = db.getWarnings();
if(warn != null)
System.out.println(warn: + warn.getMessage());
else
System.out.println(warning null);
}
During the running of this loop, I run psql on the same database
and manually execute NOTIFY mytest;.
Notifications don't come back as warning's, but are implemented using pg
specific java code. Further, there is no asynchronous notification
support in the JDBC driver, so you can't just wait for them to show up.
Instead you must send a backend command every so often to see if a
notification is ready.
Code more like the below should work:
import org.postgresql.PGConnection;
import org.postgresql.PGNotification;
Connection conn = ... // get connection somehow
Statement stmt = conn.createStatement();
while (1) {
ResultSet rs = stmt.executeQuery(SELECT 1);
rs.close();
PGConnection pgconn = (PGConnection)conn;
PGNotification notif[] = conn.getNotifications();
for (int i=0; inotif.length; i++) {
System.out.println(notif[i].getName());
}
Thread.sleep(3000);
}
Kris Jurka
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] LISTEN/NOTIFY with JDBC
On Tue, 11 May 2004, Glenn Sullivan wrote:
Hi,
I have been trying to get LISTEN/NOTIFY working in with JDBC. I cannot seem
to get notified. I looked in the e-mail archive and saw a lot of similiar
questions a couple of years ago. I never could find any answers in the
e-mail nor in the documentation. Perhaps I just missed it.
I have tried the following code snipit:
Connection db = DriverManager.getConnection(url, user, passwd);
Statement sql = db.createStatement();
sql.execute(LISTEN mytest);
db.clearWarnings();
for(int i=0; i 10 ; i ++) {
Thread.sleep(3000);
SQLWarning warn = db.getWarnings();
if(warn != null)
System.out.println(warn: + warn.getMessage());
else
System.out.println(warning null);
}
During the running of this loop, I run psql on the same database
and manually execute NOTIFY mytest;.
Notifications don't come back as warning's, but are implemented using pg
specific java code. Further, there is no asynchronous notification
support in the JDBC driver, so you can't just wait for them to show up.
Instead you must send a backend command every so often to see if a
notification is ready.
Code more like the below should work:
import org.postgresql.PGConnection;
import org.postgresql.PGNotification;
Connection conn = ... // get connection somehow
Statement stmt = conn.createStatement();
while (1) {
ResultSet rs = stmt.executeQuery(SELECT 1);
rs.close();
PGConnection pgconn = (PGConnection)conn;
PGNotification notif[] = conn.getNotifications();
for (int i=0; inotif.length; i++) {
System.out.println(notif[i].getName());
}
Thread.sleep(3000);
}
Kris Jurka
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] listen/notify
Hi Could someone, please give me a little example on how to use listen/notify mechanism. Or at least point me to some more legible documentation. What I particulary need is that my frontend application is notified on update/insert in a table. Thanx a lot. Tomas Winkler The System Group CSE HUJI
Re: [GENERAL] listen/notify
"Tomas B. Winkler" [EMAIL PROTECTED] writes: Could someone, please give me a little example on how to use listen/notify mechanism. Or at least point me to some more legible documentation. What I particulary need is that my frontend application is notified on update/insert in a table. Make an ON INSERT OR UPDATE trigger that executes the NOTIFY. regards, tom lane
