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