pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed[SQL NOTIFY Help] sending NOTIFY to the external program
8+ messages / 4 participants
[nested] [flat]
* [SQL NOTIFY Help] sending NOTIFY to the external program
@ 2007-01-03 09:35 ramachandra.bhaskaram@wipro.com
0 siblings, 1 reply; 8+ messages in thread
From: ramachandra.bhaskaram@wipro.com @ 2007-01-03 09:35 UTC (permalink / raw)
To: pgsql-sql
Hi,
In one of our project we are supposed to send the notifications
regarding any row modifications in a perticular table to the external
application which will be implementing the listen event for the same.
We are using the postgress version 8.0.3.
we have observed that pgNotify structure in "libpq-fe.h" file is
having the following structure;
typedef struct pgNotify
{
char *relname; /* notification condition name
*/
int be_pid; /* process ID of
notifying server process */
char *extra; /* notification
parameter */
/* Fields below here are private to libpq; apps should not use
'em */
struct pgNotify *next; /* list link */
} PGnotify;
But, by default only relname and be_pid are used. The technical
document also mentions that extra attribute is not used and by default
it will be null.
Can anybody clarify whether we can pass the primary key value of
the record modified in the extra attribute of this structure?
If not any help regarding the same will be appreciated.
Thanks in Advance,
Ramachandra B.S.
The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.
www.wipro.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL NOTIFY Help] sending NOTIFY to the external program
@ 2007-01-03 10:22 Richard Huxton <dev@archonet.com>
parent: ramachandra.bhaskaram@wipro.com
0 siblings, 0 replies; 8+ messages in thread
From: Richard Huxton @ 2007-01-03 10:22 UTC (permalink / raw)
To: ramachandra.bhaskaram@wipro.com; +Cc: pgsql-sql
ramachandra.bhaskaram@wipro.com wrote:
> Hi,
>
> In one of our project we are supposed to send the notifications
> regarding any row modifications in a perticular table to the external
> application which will be implementing the listen event for the same.
> We are using the postgress version 8.0.3.
> Can anybody clarify whether we can pass the primary key value of
> the record modified in the extra attribute of this structure?
Afraid not. You'll need to record the pkey in a "process_these" table
and have the application check there.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL NOTIFY Help] sending NOTIFY to the external program
@ 2007-01-03 13:59 ramachandra.bhaskaram@wipro.com
0 siblings, 1 reply; 8+ messages in thread
From: ramachandra.bhaskaram@wipro.com @ 2007-01-03 13:59 UTC (permalink / raw)
To: dev@archonet.com; +Cc: pgsql-sql
Hi Richard Huxton,
Thank you for your kind response.
Is there not any other way other than record the pkey in a
"process_these" table and have the application check there. We want
to minimise the database transactions to improve the DB performance.
Can we send the exact data which has been changed using
db-notify to the application? So that, the application can continue
processing the data using the Notify message from the DB.
Thanks in Advance,
Ramachandra B.S.
-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Wednesday, January 03, 2007 3:53 PM
To: Ramachandra Bhaskaram (WT01 - IP-Multimedia Carrier & Ent Networks)
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] [SQL NOTIFY Help] sending NOTIFY to the external
program
ramachandra.bhaskaram@wipro.com wrote:
> Hi,
>
> In one of our project we are supposed to send the notifications
> regarding any row modifications in a perticular table to the external
> application which will be implementing the listen event for the same.
> We are using the postgress version 8.0.3.
> Can anybody clarify whether we can pass the primary key value of
the
> record modified in the extra attribute of this structure?
Afraid not. You'll need to record the pkey in a "process_these" table
and have the application check there.
--
Richard Huxton
Archonet Ltd
The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.
www.wipro.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL NOTIFY Help] sending NOTIFY to the external program
@ 2007-01-03 14:09 Richard Huxton <dev@archonet.com>
parent: ramachandra.bhaskaram@wipro.com
0 siblings, 0 replies; 8+ messages in thread
From: Richard Huxton @ 2007-01-03 14:09 UTC (permalink / raw)
To: ramachandra.bhaskaram@wipro.com; +Cc: pgsql-sql
ramachandra.bhaskaram@wipro.com wrote:
> Hi Richard Huxton,
>
> Thank you for your kind response.
>
> Is there not any other way other than record the pkey in a
> "process_these" table and have the application check there. We want
> to minimise the database transactions to improve the DB performance.
>
> Can we send the exact data which has been changed using
> db-notify to the application? So that, the application can continue
> processing the data using the Notify message from the DB.
Not via your database connection.
With one of the untrusted languages (C, pl/perlu pl/pythonu etc) you
could do anything you could make a separate connection / write to a file
etc.
Does your application need to know *immediately* that a change in the
database has occurred?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL NOTIFY Help] sending NOTIFY to the external program
@ 2007-01-03 14:17 ramachandra.bhaskaram@wipro.com
0 siblings, 2 replies; 8+ messages in thread
From: ramachandra.bhaskaram@wipro.com @ 2007-01-03 14:17 UTC (permalink / raw)
To: dev@archonet.com; +Cc: pgsql-sql
Yes, our application is supposed to know *immediately* that a change in
the database has occurred since, based on this modified data it is doing
lot of other operations and also, the db transactions are heavy(expected
to be arround 300 tps(transactions per sec)).
I agree that this can be done using some untrusted languages, written to
a file and then the application can look into the file for the modified
date. But, as far as performance is concerned it will take time for the
application to get the response. The user on the application side might
not be kept in waiting state until this transaction happens.
Thanks in Advance,
Ramachandra B.S.
-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Wednesday, January 03, 2007 7:39 PM
To: Ramachandra Bhaskaram (WT01 - IP-Multimedia Carrier & Ent Networks)
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] [SQL NOTIFY Help] sending NOTIFY to the external
program
ramachandra.bhaskaram@wipro.com wrote:
> Hi Richard Huxton,
>
> Thank you for your kind response.
>
> Is there not any other way other than record the pkey in a
> "process_these" table and have the application check there. We want
> to minimise the database transactions to improve the DB performance.
>
> Can we send the exact data which has been changed using
> db-notify to the application? So that, the application can continue
> processing the data using the Notify message from the DB.
Not via your database connection.
With one of the untrusted languages (C, pl/perlu pl/pythonu etc) you
could do anything you could make a separate connection / write to a file
etc.
Does your application need to know *immediately* that a change in the
database has occurred?
--
Richard Huxton
Archonet Ltd
The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.
www.wipro.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL NOTIFY Help] sending NOTIFY to the external program
@ 2007-01-03 14:22 Richard Huxton <dev@archonet.com>
parent: ramachandra.bhaskaram@wipro.com
1 sibling, 0 replies; 8+ messages in thread
From: Richard Huxton @ 2007-01-03 14:22 UTC (permalink / raw)
To: ramachandra.bhaskaram@wipro.com; +Cc: pgsql-sql
ramachandra.bhaskaram@wipro.com wrote:
> Yes, our application is supposed to know *immediately* that a change in
> the database has occurred since, based on this modified data it is doing
> lot of other operations and also, the db transactions are heavy(expected
> to be arround 300 tps(transactions per sec)).
>
> I agree that this can be done using some untrusted languages, written to
> a file and then the application can look into the file for the modified
> date. But, as far as performance is concerned it will take time for the
> application to get the response. The user on the application side might
> not be kept in waiting state until this transaction happens.
Needn't take time, if the O.S. you're on can notify you of file writes,
but in any case I'd look at communicating over a socket (unix or tcp/ip)
if you want immediate notification. It's simple enough in perl or python
(or ruby, or any of those).
Oh, and you'll need to cope with the case:
1. Transaction containing the update is rolled back
2. Notification has already left the database
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL NOTIFY Help] sending NOTIFY to the external program
@ 2007-01-03 16:10 Andrew Sullivan <ajs@crankycanuck.ca>
parent: ramachandra.bhaskaram@wipro.com
1 sibling, 1 reply; 8+ messages in thread
From: Andrew Sullivan @ 2007-01-03 16:10 UTC (permalink / raw)
To: pgsql-sql
On Wed, Jan 03, 2007 at 07:47:34PM +0530, ramachandra.bhaskaram@wipro.com wrote:
>
> Yes, our application is supposed to know *immediately* that a change in
> the database has occurred since,
NOTIFY doesn't get you that anyway. It's _close_ to immediately, but
it's still asynchronous.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
A certain description of men are for getting out of debt, yet are
against all taxes for raising money to pay it off.
--Alexander Hamilton
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL NOTIFY Help] sending NOTIFY to the external program
@ 2007-01-03 16:20 Dave Steinberg <dave-pgsql@redterror.net>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 8+ messages in thread
From: Dave Steinberg @ 2007-01-03 16:20 UTC (permalink / raw)
To: ramachandra.bhaskaram@wipro.com; +Cc: pgsql-sql
> On Wed, Jan 03, 2007 at 07:47:34PM +0530, ramachandra.bhaskaram@wipro.com wrote:
>> Yes, our application is supposed to know *immediately* that a change in
>> the database has occurred since,
I'd recommend some sort of "whiteboard" layer or messaging fabric as an
option for this sort of thing. This offloads the notification element
from the database, and lets you pass along any data you want to the
listeners - even the complete record.
Also this sort of thing scales well to multiple listeners. Anyway, just
a thought...
Regards,
--
Dave Steinberg
http://www.geekisp.com/
http://www.steinbergcomputing.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-01-03 16:20 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-03 09:35 [SQL NOTIFY Help] sending NOTIFY to the external program ramachandra.bhaskaram@wipro.com
2007-01-03 10:22 ` Richard Huxton <dev@archonet.com>
2007-01-03 13:59 Re: [SQL NOTIFY Help] sending NOTIFY to the external program ramachandra.bhaskaram@wipro.com
2007-01-03 14:09 ` Richard Huxton <dev@archonet.com>
2007-01-03 14:17 Re: [SQL NOTIFY Help] sending NOTIFY to the external program ramachandra.bhaskaram@wipro.com
2007-01-03 14:22 ` Richard Huxton <dev@archonet.com>
2007-01-03 16:10 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-01-03 16:20 ` Dave Steinberg <dave-pgsql@redterror.net>
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