Thread: Socket communication for contrib
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
Attachment
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
"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.
*************************************************************************
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
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
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.
*************************************************************************
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
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
>
> 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
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
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
> > 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
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/
> 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
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
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
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
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 #
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
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
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
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