Community,

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.

Why would anybody do that?
Currently PostgreSQL provides a nice LISTEN / NOTIFY mechanism. However, 
this mechanism is obsolete when the machine which should receive a 
message is not connected to PostgreSQL directly.
In this case it is possible to define a trigger on a table and let the 
database tell those machines via TCP that something interesting might 
have happened.
In our case this package has helped us to get rid of permanent, speed 
consuming polling.

Maybe some people might find it useful and want to see this feature in 
contrib.
Please let us know.

	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
Mon, 05 Apr 2004 13:06:18 +0200
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at>
Socket communication for contrib
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.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

Mon, 05 Apr 2004 10:57:27 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Socket communication for contrib 
Tom Lane wrote:
> =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.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



Mon, 05 Apr 2004 17:36:15 +0200
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at>
Re: Socket communication for contrib
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at> writes:
> 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.

Agreed.

> 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.

I won't take a position on whether it's useful enough to put in contrib,
but if people want it there, I'd just ask that the README be extended to
point out the transactional risks.

			regards, tom lane

Mon, 05 Apr 2004 11:50:56 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Socket communication for contrib 
Tom Lane wrote:
> =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at> writes:
> 
>>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.
> 
> 
> Agreed.
> 
> 
>>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.
> 
> 
> I won't take a position on whether it's useful enough to put in contrib,
> but if people want it there, I'd just ask that the README be extended to
> point out the transactional risks.

this should not be a problem.
I can intregrate all necessary information there.

folks, let's do a poll ...
who is for it - who is against it ...

	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



Mon, 05 Apr 2004 17:59:41 +0200
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at>
Re: Socket communication for contrib
Hans-J�rgen Sch�nig wrote:
> Tom Lane wrote:
> > =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at> writes:
> > 
> >>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.
> > 
> > 
> > Agreed.
> > 
> > 
> >>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.
> > 
> > 
> > I won't take a position on whether it's useful enough to put in contrib,
> > but if people want it there, I'd just ask that the README be extended to
> > point out the transactional risks.
> 
> this should not be a problem.
> I can intregrate all necessary information there.
> 
> folks, let's do a poll ...
> who is for it - who is against it ...

Is it better in /contrib or gborg?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 5 Apr 2004 12:20:28 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Socket communication for contrib
> 
> Is it better in /contrib or gborg?
> 


I have learned (please correct me if I am wrong) that people tend to 
look in contrib before they look at gborg.
Also, when people ask for training most of them ask for stuff in 
contrib. It is people's mind that contrib is somehow a source of 
additional, small software. Again, correct me if I am wrong.

	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



Mon, 05 Apr 2004 18:33:51 +0200
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at>
Re: Socket communication for contrib
Hans-J�rgen Sch�nig wrote:
> > 
> > Is it better in /contrib or gborg?
> > 
> 
> 
> I have learned (please correct me if I am wrong) that people tend to 
> look in contrib before they look at gborg.
> Also, when people ask for training most of them ask for stuff in 
> contrib. It is people's mind that contrib is somehow a source of 
> additional, small software. Again, correct me if I am wrong.

For me, /contrib is for things closely tied to the backend code, like
GIST stuff, and for key tools, like conversion programs.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 5 Apr 2004 12:35:52 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Socket communication for contrib
It's rumoured that Bruce Momjian once said:
>
> Is it better in /contrib or gborg?
>

Gborg imho. I thought we were trying to move all non-core code there now.

Isn't that why psqlodbc etc. were moved?

Regards, Dave



Mon, 5 Apr 2004 17:46:02 +0100 (BST)
"Dave Page" <dpage@vale-housing.co.uk>
Re: Socket communication for contrib

> > Is it better in /contrib or gborg?
>
> Gborg imho. I thought we were trying to move all non-core code there
> now. Isn't that why psqlodbc etc. were moved?

The argument was that it can be devopped and released independently?

Features in "contrib/" have a premium over external add-ons.

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Mon, 5 Apr 2004 18:52:16 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: Socket communication for contrib

On Apr 5, 2004, at 12:35 PM, Bruce Momjian wrote:
>
> For me, /contrib is for things closely tied to the backend code, like
> GIST stuff, and for key tools, like conversion programs.
>

something that would be useful (and perhaps may be part of that 
pgfoundry or whatever its called movement) would be making gborg's 
existance more clear.
Maybe putting a file in doc/ or contrib/ mentioning it or including an 
index of things on it (Or at least the more popular items).

Often when I talk to people at work about PG they ask about stuff and I 
say "Silly, thats on gborg!"  and they look at me strangely and have no 
idea about it.  You get less of it with contrib/ items..

just my $0.02


--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/


Mon, 5 Apr 2004 13:18:49 -0400
Jeff <threshar@torgo.978.org>
Re: Socket communication for contrib

> say "Silly, thats on gborg!"  and they look at me strangely

Sure. The "gborg" name does not strike as being related to postgresql.

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Mon, 5 Apr 2004 19:25:53 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: Socket communication for contrib
Jeff wrote:
> 
> On Apr 5, 2004, at 12:35 PM, Bruce Momjian wrote:
> 
>>
>> For me, /contrib is for things closely tied to the backend code, like
>> GIST stuff, and for key tools, like conversion programs.
>>
> 
> something that would be useful (and perhaps may be part of that 
> pgfoundry or whatever its called movement) would be making gborg's 
> existance more clear.
> Maybe putting a file in doc/ or contrib/ mentioning it or including an 
> index of things on it (Or at least the more popular items).
> 
> Often when I talk to people at work about PG they ask about stuff and I 
> say "Silly, thats on gborg!"  and they look at me strangely and have no 
> idea about it.  You get less of it with contrib/ items..
> 
> just my $0.02


You have hit an important point here: "What is gborg?". That's what 
people think.
Maybe we should have a contrib package (any better ideas?) or at least a 
pretty obvious place inside our .tar.gz files mentioning gborg.
If you want more -> gborg.
Otherwise people won't use gborg too much. I have seen that too often.
Maybe thinking about that makes sense about that's more an advocacy 
issue I think.

	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



Mon, 05 Apr 2004 19:53:11 +0200
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at>
Re: Socket communication for contrib
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

Mon, 05 Apr 2004 21:36:11 -0400
Paul Tillotson <pntil@shentel.net>
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


Tue, 06 Apr 2004 09:23:57 +0200
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at>
Re: Socket communication for contrib
Fabien COELHO wrote:

>> > Is it better in /contrib or gborg?
>>
>> Gborg imho. I thought we were trying to move all non-core code there
>> now. Isn't that why psqlodbc etc. were moved?
> 
> The argument was that it can be devopped and released independently?
> 
> Features in "contrib/" have a premium over external add-ons.
> 

That is probably the average users perception. But I hope that is not 
the reason why dblink, dbmirror and the "rserv prototype" are under 
contrib/ while projects like erserver and slony1 live on gborg - just to 
take this class of projects as an example.

I totally agree that gborg is the right place for this, as it is for 
another few things that still are in contrib/ and don't belong there.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Wed, 07 Apr 2004 09:39:15 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: Socket communication for contrib
On Mon, Apr 05, 2004 at 06:33:51PM +0200, Hans-J?rgen Sch?nig wrote:
> 
> I have learned (please correct me if I am wrong) that people tend to 
> look in contrib before they look at gborg.

This may be true, but if so, perhaps it's a reason to add a
contrib/gborg directory with just a README that says "For lots of
additional software which is designed to be part of your PostgreSQL
installation, go to <http://gborg.postgresql.org>."

A


-- 
Andrew Sullivan  | ajs@crankycanuck.ca
The plural of anecdote is not data.
		--Roger Brinner

Fri, 16 Apr 2004 08:10:20 -0400
Andrew Sullivan <ajs@crankycanuck.ca>
Re: Socket communication for contrib
On Wed, Apr 07, 2004 at 09:39:15AM -0400, Jan Wieck wrote:

> the reason why dblink, dbmirror and the "rserv prototype" are under 
> contrib/ while projects like erserver and slony1 live on gborg - just to 
> take this class of projects as an example.

Since the rserv prototype actually does not work any more (at least
according to the reports I've seen), it should probably be removed
anyway.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism. 
                --Brad Holland

Fri, 16 Apr 2004 08:11:58 -0400
Andrew Sullivan <ajs@crankycanuck.ca>
Re: Socket communication for contrib
On Fri, Apr 16, 2004 at 08:10:20AM -0400, Andrew Sullivan wrote:
> On Mon, Apr 05, 2004 at 06:33:51PM +0200, Hans-J?rgen Sch?nig wrote:
> > 
> > I have learned (please correct me if I am wrong) that people tend to 
> > look in contrib before they look at gborg.
> 
> This may be true, but if so, perhaps it's a reason to add a
> contrib/gborg directory with just a README that says "For lots of
> additional software which is designed to be part of your PostgreSQL
> installation, go to <http://gborg.postgresql.org>."

Or even a text dump of http://gborg.postgresql.org/project/projdisplaylist.php
(which would be more useful if all gborg projects had useful descriptions -
 but that would directly benefit users of gborg too)

Cheers,
  Steve

Fri, 16 Apr 2004 09:09:02 -0700
Steve Atkins <steve@blighty.com>
Re: Socket communication for contrib