Re: Socket communication for contrib - Mailing list pgsql-hackers

Paul Tillotson wrote:
> 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.


Yes, It might happen that you cannot see changes.


> 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


Well, sacrifycing one backend would not be a problem.
If you are using one connection to do the LISTEN / NOTIFY work (maybe 
including some configuration schema), you had a good chance to see the 
changes which have been made.
Basically this should not be a problem. However, my time is very limited 
at the moment. I hope that I will finde some spare time within the next 
few months to SELECT FOR UPDATE NOWAIT and you idea.
Regards,
    Hans

-- 
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/2952/30706 or +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at