Hans et al:
People asked me to put a simple extension for PostgreSQL Open Source.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.)
The attached package contains a simple functions whichs tells a remote TCP socket that somebody is about to modify a certain table.
[ ... 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.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.
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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
