Re: Socket communication for contrib

Tom Lane wrote:
> =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres(at)cybertec(dot)at> writes:
>
>>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.
>
>
> 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.
>
> regards, tom lane

Yes, absolutely - it is damn hard to ROLLBACK a TCP connection.
Unfortunately there are no "ON COMMIT" triggers or something like that -
this would have been a better solution.
I am very well aware of this problem because I share your concerns.

However, sometimes it can be interesting to know if somebody ATTEMPTS to
modify the database.
Also, you can use it to send data in the database to somebody where. In
this case there are in most cases 1-line transactions:

eg. SELECT tellsomebody() WHERE id = someid;

In our special case it makes sense when various clients which are NOT
connected to the database (because they are somewhere else on this
planet) receive some sort of database driven notification in case of
some events. Depending on the return value a user can see whether a
message has been delivered or not.

Sending a message to many clients has always the same problem:
Unfortunately TCP does not support transactions the way people would use
it inside a database.

Nested transactions: I don't think nested transactions will really help
to resolve the core problem. Committing a subtransaction will most
likely not imply that a parent transaction can be committed as well.

As I said: Some people MIGHT find it useful in some special cases.
If the community decides that it does not enough sense to integrate it
into contrib I can live with that.

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