Re: Socket communication for contrib

Hans et al:

>People asked me to put a simple extension for PostgreSQL Open Source.
>The attached package contains a simple functions whichs tells a remote 
>TCP socket that somebody is about to modify a certain table.
>  
>
I would very much appreciate being able to receive notifications over 
the network.  Besides helping machines which are not directly connected 
to the database, this is very useful when one is using a deficient 
API/wrapper which does not provide a "block until a notify arrives."  
(Such as the pg_xxxxxx functions in PHP.)

>Doesn't this encourage violation of the basic notion of a transaction?
>The message will be sent immediately, whether or not the sending
>transaction actually commits.
>
>  
>
[ ... thinks ... ]  Good point, but I think I see another problem with 
it--changes to a table are not visible until a transaction commits.  
Depending on the speed of your network, you might often get the 
notification BEFORE the transaction commits, and so your SELECT new rows 
SQL statement might miss the very change that it was notified of.  The 
only way to tell would be to wait for a "reasonable" amount of time and 
try again.  (And of course, if the change were rolled back then you 
would never see a changed row.)  It seems that one would be almost 
reduced to polling again.

Instead of this, what do the hackers think of a NOTIFY forwarder?  One 
could make a small C program which connects to the database, executes 
LISTEN for the proper notifies, goes to sleep using select(), and then 
forwards each notify received over the network to the proper hosts?  It 
seems that this would accomplish the same result while not violating the 
basic notion of a transaction. 

It would permanently tie up one backend, though.   : (

Could your extension be modified to work this way, Hans?

Paul Tillotson