Обсуждение: Socket communication for contrib

Поиск
Список
Период
Сортировка

Socket communication for contrib

От
Hans-Jürgen Schönig
Дата:
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


Вложения

Re: Socket communication for contrib

От
Tom Lane
Дата:
Hans-Jürgen Schönig <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


Re: Socket communication for contrib

От
Bob.Henkel@hartfordlife.com
Дата:




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

Any postgresql C coders out there that can help us out with nested
transactions?

This pretty much comes down to having nested transactions for this to be of
real use.  For example.
BEGIN

update table;
commit;
send remote message that table was updated;

when ANY exception
do error logic

END

Otherwise it doesn't really tell you anything of much value.  Though I
imagine in some rare/special case it could be of use as it stands.

Bob Henkel          651-738-5085
Mutual Funds I/T Woodbury
Hartford Life
500 Bielenberg Drive
Woodbury, MN 55125


|---------+---------------------------------->
|         |           Tom Lane               |
|         |           <tgl@sss.pgh.pa.us>    |
|         |           Sent by:               |
|         |           pgsql-hackers-owner@pos|
|         |           tgresql.org            |
|         |                                  |
|         |                                  |
|         |           04/05/2004 09:57 AM    |
|         |                                  |
|---------+---------------------------------->
>------------------------------------------------------------------------------------------------------------------------------|
|
     | |       To:       Hans-Jürgen Schönig <postgres@cybertec.at>
             | |       cc:       pgsql-hackers@postgresql.org
                     | |       Subject:  Re: [HACKERS] Socket communication for contrib
                             |
>------------------------------------------------------------------------------------------------------------------------------|




Hans-Jürgen Schönig <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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faqs/FAQ.html






*************************************************************************
PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may
containproprietary, confidential and/or privileged information.  If you are not the intended recipient, any use,
copying,disclosure, dissemination or distribution is strictly prohibited.  If you are not the intended recipient,
pleasenotify the sender immediately by return e-mail, delete this communication and destroy all copies. 
*************************************************************************



Re: Socket communication for contrib

От
Hans-Jürgen Schönig
Дата:
Tom Lane wrote:
> Hans-Jürgen Schönig <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




Re: Socket communication for contrib

От
Tom Lane
Дата:
Hans-Jürgen Schönig <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


Re: Socket communication for contrib

От
Bob.Henkel@hartfordlife.com
Дата:




I would be FOR it if the README states the dangers



Bob Henkel          651-738-5085
Mutual Funds I/T Woodbury
Hartford Life
500 Bielenberg Drive
Woodbury, MN 55125


|---------+---------------------------->
|         |           Hans-Jürgen      |
|         |           Schönig          |
|         |           <postgres@cyberte|
|         |           c.at>            |
|         |                            |
|         |           04/05/2004 10:59 |
|         |           AM               |
|         |                            |
|---------+---------------------------->
>------------------------------------------------------------------------------------------------------------------------------|
|
     | |       To:       Tom Lane <tgl@sss.pgh.pa.us>
             | |       cc:       pgsql-hackers@postgresql.org, Bob.Henkel@hartfordlife.com
                     | |       Subject:  Re: [HACKERS] Socket communication for contrib
                             |
>------------------------------------------------------------------------------------------------------------------------------|




Tom Lane wrote:
> Hans-Jürgen Schönig <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








*************************************************************************
PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may
containproprietary, confidential and/or privileged information.  If you are not the intended recipient, any use,
copying,disclosure, dissemination or distribution is strictly prohibited.  If you are not the intended recipient,
pleasenotify the sender immediately by return e-mail, delete this communication and destroy all copies. 
*************************************************************************



Re: Socket communication for contrib

От
Hans-Jürgen Schönig
Дата:
Tom Lane wrote:
> Hans-Jürgen Schönig <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




Re: Socket communication for contrib

От
Bruce Momjian
Дата:
Hans-J�rgen Sch�nig wrote:
> Tom Lane wrote:
> > Hans-Jürgen Schönig <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,
Pennsylvania19073
 


Re: Socket communication for contrib

От
Hans-Jürgen Schönig
Дата:
> 
> 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




Re: Socket communication for contrib

От
Bruce Momjian
Дата:
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,
Pennsylvania19073
 


Re: Socket communication for contrib

От
"Dave Page"
Дата:
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




Re: Socket communication for contrib

От
Fabien COELHO
Дата:
> > 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


Re: Socket communication for contrib

От
Jeff
Дата:
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/



Re: Socket communication for contrib

От
Fabien COELHO
Дата:
> 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


Re: Socket communication for contrib

От
Hans-Jürgen Schönig
Дата:
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




Re: Socket communication for contrib

От
Paul Tillotson
Дата:
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


Re: Socket communication for contrib

От
Hans-Jürgen Schönig
Дата:
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



Re: Socket communication for contrib

От
Jan Wieck
Дата:
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 #



Re: Socket communication for contrib

От
Andrew Sullivan
Дата:
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


Re: Socket communication for contrib

От
Andrew Sullivan
Дата:
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


Re: Socket communication for contrib

От
Steve Atkins
Дата:
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
gborgtoo)
 

Cheers, Steve


Re: Socket communication for contrib

От
Josh Berkus
Дата:
Hans, Andrew,

> > I have learned (please correct me if I am wrong) that people tend to 
> > look in contrib before they look at gborg.

pgFoundry/project.postgresql.org will be up by next week, I promise.    
Working on it now.

This should increase the visibility of non-core components.

-- 
-Josh BerkusAglio Database SolutionsSan Francisco