Re: Socket communication for contrib

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