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

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

------------------------------------------------------------------------------------------------------------------------------|

=?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

---------------------------(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 contain proprietary, 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, please notify the sender immediately by return e-mail, delete this communication and destroy all copies.
*************************************************************************

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

=?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

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

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:

=?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

*************************************************************************
PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, 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, please notify the sender immediately by return e-mail, delete this communication and destroy all copies.
*************************************************************************

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

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, Pennsylvania 19073

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&gt;.&quot;

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&gt;.&quot;

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

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 Berkus
Aglio Database Solutions
San Francisco