Thread: Beating Oracle
I posted a question a while back but got no response :-( Due to network glitch, a PostgreSQL connection over IP is being dropped. In the same situation, Oracle can keep it's connections going. Is there any way to keep PostgreSQL connections alive across glitches, or perhaps re-connect to an existing backend connection? I have people using a mix of PostgreSQL and Oracle at the moment. I suspect they will drop PostgreSQL if there is no answer to this issue. BTW I am the author of the Smalltalk PostgreSQL library (http://sourceforge.net/projects/st-postgresql) Thanks.
Bruce Badger wrote: > I posted a question a while back but got no response :-( > > Due to network glitch, a PostgreSQL connection over IP is being dropped. > > In the same situation, Oracle can keep it's connections going. > > Is there any way to keep PostgreSQL connections alive across glitches, > or perhaps re-connect to an existing backend connection? > > I have people using a mix of PostgreSQL and Oracle at the moment. I > suspect they will drop PostgreSQL if there is no answer to this issue. > > BTW I am the author of the Smalltalk PostgreSQL library > (http://sourceforge.net/projects/st-postgresql) Wow, the TCP connection gets dropped and Oracle somehow reestablishes the connection? That is a nice feature. TCP has it's own restart logic that will handle the dropping of some packets, but if the TCP connection itself shuts down, PostgreSQL has no way of reestablishing it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Badger <bbadger@BadgerSE.com> writes:
> Due to network glitch, a PostgreSQL connection over IP is being dropped.
Perhaps there is something wrong with your TCP stacks. I do not think
it's Postgres' responsibility to second-guess the network transport
code...
regards, tom lane
Tom Lane wrote: >Bruce Badger <bbadger@BadgerSE.com> writes: > >>Due to network glitch, a PostgreSQL connection over IP is being dropped. >> > >Perhaps there is something wrong with your TCP stacks. I do not think >it's Postgres' responsibility to second-guess the network transport >code... > > regards, tom lane > Not *my* stack, BTW, but that aside I agree with you. The people involved suspect that a firewall is causing the problem. The thing is that from their point of view, Oracle keeps going where PostgreSQL does not. Given that both are running over the same network, the response of these people could well be to simply move everything to Oracle. The question, then, is: what can we PostgreSQL people can do to match this Oracle ability??
Bruce Badger wrote: > Tom Lane wrote: > > >Bruce Badger <bbadger@BadgerSE.com> writes: > > > >>Due to network glitch, a PostgreSQL connection over IP is being dropped. > >> > > > >Perhaps there is something wrong with your TCP stacks. I do not think > >it's Postgres' responsibility to second-guess the network transport > >code... > > > > regards, tom lane > > > Not *my* stack, BTW, but that aside I agree with you. The people > involved suspect that a firewall is causing the problem. > > The thing is that from their point of view, Oracle keeps going where > PostgreSQL does not. Given that both are running over the same network, > the response of these people could well be to simply move everything to > Oracle. > > The question, then, is: what can we PostgreSQL people can do to match > this Oracle ability?? Unless there is more information, nothing. This is the first time I have heard of this in my 6 years with PostgreSQL. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian wrote: >Bruce Badger wrote: > >>Tom Lane wrote: >> >>>Bruce Badger <bbadger@BadgerSE.com> writes: >>> >>>>Due to network glitch, a PostgreSQL connection over IP is being dropped. >>>> >>>Perhaps there is something wrong with your TCP stacks. I do not think >>>it's Postgres' responsibility to second-guess the network transport >>>code... >>> >>> regards, tom lane >>> >>Not *my* stack, BTW, but that aside I agree with you. The people >>involved suspect that a firewall is causing the problem. >> >>The thing is that from their point of view, Oracle keeps going where >>PostgreSQL does not. Given that both are running over the same network, >>the response of these people could well be to simply move everything to >>Oracle. >> >>The question, then, is: what can we PostgreSQL people can do to match >>this Oracle ability?? >> > >Unless there is more information, nothing. This is the first time I >have heard of this in my 6 years with PostgreSQL. > OK - now I have spoken with you sages, I will indeed go back and see if these people have any more information. Many thanks.
Hello Bruce. I'd be willing to bet that if a firewall is involved, it's been especially tuned for Oracle, whereas special rules weren't set for Postgres. As soon as those rules get put in, things will probably be very equivalent. -Brian On Mar 2, 10:15am, Bruce Badger wrote: } Subject: Re: Beating Oracle } Bruce Momjian wrote: } } >Bruce Badger wrote: } > } >>Tom Lane wrote: } >> } >>>Bruce Badger <bbadger@BadgerSE.com> writes: } >>> } >>>>Due to network glitch, a PostgreSQL connection over IP is being dropped. } >>>> } >>>Perhaps there is something wrong with your TCP stacks. I do not think } >>>it's Postgres' responsibility to second-guess the network transport } >>>code... } >>> } >>> regards, tom lane } >>> } >>Not *my* stack, BTW, but that aside I agree with you. The people } >>involved suspect that a firewall is causing the problem. } >> } >>The thing is that from their point of view, Oracle keeps going where } >>PostgreSQL does not. Given that both are running over the same network, } >>the response of these people could well be to simply move everything to } >>Oracle. } >> } >>The question, then, is: what can we PostgreSQL people can do to match } >>this Oracle ability?? } >> } > } >Unless there is more information, nothing. This is the first time I } >have heard of this in my 6 years with PostgreSQL. } > } OK - now I have spoken with you sages, I will indeed go back and see if } these people have any more information. } } Many thanks. } } } } ---------------------------(end of broadcast)--------------------------- } TIP 4: Don't 'kill -9' the postmaster >-- End of excerpt from Bruce Badger
Bruce Badger <bbadger@BadgerSE.com> writes:
> The thing is that from their point of view, Oracle keeps going where
> PostgreSQL does not. Given that both are running over the same network,
> the response of these people could well be to simply move everything to
> Oracle.
<shrug> If that feature makes Oracle worth its price to them, then
no doubt that's what they'll do. Postgres doesn't have 6000 full-time
developers engaged in inventing bizarre features (and a substitute for
something that should be done at the network level is bizarre in my
book).
The reason I'm so unenthused about this is that I think it'd be quite
difficult to do transparently at the database level. (1) How can you
know where communication was lost, from the other end's point of view?
(eg, client has no idea whether database completed the last request
sent.) I think you'd wind up reimplementing TCP, or something much like
it, just to deal with that aspect of the problem. (2) How can you even
be sure that you're reconnecting to the same other-end session?
Opportunities for security holes seem rampant. (3) Should the database
hold sessions open indefinitely, waiting to see if the client can be
recontacted? Won't take too long until your session limit is entirely
filled by dead connections.
Non-broken TCP stacks are normally quite tenacious about surviving
transmission glitches in already-open connections. I think you ought
to be looking for solutions at that level, not trying to tell us that
it's our problem.
regards, tom lane
Tom Lane wrote: >Bruce Badger <bbadger@BadgerSE.com> writes: > >>The thing is that from their point of view, Oracle keeps going where >>PostgreSQL does not. Given that both are running over the same network, >>the response of these people could well be to simply move everything to >>Oracle. >> > ><shrug> If that feature makes Oracle worth its price to them, then >no doubt that's what they'll do. Postgres doesn't have 6000 full-time >developers engaged in inventing bizarre features (and a substitute for >something that should be done at the network level is bizarre in my >book). > >The reason I'm so unenthused about this is that I think it'd be quite >difficult to do transparently at the database level. (1) How can you >know where communication was lost, from the other end's point of view? >(eg, client has no idea whether database completed the last request >sent.) I think you'd wind up reimplementing TCP, or something much like >it, just to deal with that aspect of the problem. (2) How can you even >be sure that you're reconnecting to the same other-end session? >Opportunities for security holes seem rampant. (3) Should the database >hold sessions open indefinitely, waiting to see if the client can be >recontacted? Won't take too long until your session limit is entirely >filled by dead connections. > >Non-broken TCP stacks are normally quite tenacious about surviving >transmission glitches in already-open connections. I think you ought >to be looking for solutions at that level, not trying to tell us that >it's our problem. > > regards, tom lane > Ah, Tom. I'm not demanding anything, nor saying this is any individual's problem. I was merely seeking the wisdom of others, and I am grateful for your feedback (apart from the bits that slap me for something I have not done ;-) ). FWIW, I too have invested a great deal of my own time in making PostgreSQL available to others, having built and supported the Smalltalk PostgreSQL library.
> The reason I'm so unenthused about this is that I think it'd be quite > difficult to do transparently at the database level. (1) How can you > know where communication was lost, from the other end's point of view? Actually, if we're talking about transient TCP failures here (ie. one in X connections dies halfway through a transaction), then [1]libpqxx does have the logic built-in for semi-transparent connection recovery. I'm not sure this is quite what is needed here, but what it does right now is reset the backend connection (PQreset() I think the function was), forget about the current attempt at performing the transaction, and re- running the client code that makes up the transaction. So we're not talking about a simple replay of old SQL, but a simple framework for writing restartable code that "plugs into" a connection. Look for the Transactor base class. If PQreset() is not sufficient, I see no fundamental problem in doing an entire new connect instead. The one underlying assumption of course is that the client side will know if the connection was lost. If TCP and libpq guarantee that much, then the libpqxx mechanism should solve the problem perfectly. All we need now is two-phase commit. :-) > (eg, client has no idea whether database completed the last request > sent.) I think you'd wind up reimplementing TCP, or something much > like it, just to deal with that aspect of the problem. (2) How can you > even be sure that you're reconnecting to the same other-end session? > Opportunities for security holes seem rampant. (3) Should the database > hold sessions open indefinitely, waiting to see if the client can be > recontacted? Won't take too long until your session limit is entirely > filled by dead connections. Basically, libpqxx solves these problems at the client side--it doesn't try to reconnect to an older session, but simply leaves the old session to wither and die while it starts up a new one to perform the same task. This takes very little in the way of state-keeping on either side. Jeroen [1] http://members.ams.chello.nl/j.vermeulen31/proj-libpqxx.html
Bruce Momjian wrote: > TCP has it's own restart logic that will handle the dropping of some > packets, but if the TCP connection itself shuts down, PostgreSQL has no > way of reestablishing it. Yupp, and it already has spent some time trying to retransmit lost packets in order to keep this connection alive, before telling "sorry, lost contact". Do we want to decide dynamically, maybe based on the number of row locks the current transaction holds, what a good amount of time for trying to reconnect is? I mean, someone else might wait for these rows to be available again. Maybe even the same user, because his client library decided earlier that the connection is lost, unfortunately some "reestablish packets" drowned in the net too, but his app finally succeeded in connectingto a new backend. If you have connection aborts, the client and application code is the wrong place to fix. In the "golden era",95% of all connectivity problems used to be cables or plugs. These days, firewalls take care that increased reliability of cables and plugs doesn't increase overall network reliability. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
<jtv@xs4all.nl> writes:
> Basically, libpqxx solves these problems at the client side--it doesn't try
> to reconnect to an older session, but simply leaves the old session to
> wither and die while it starts up a new one to perform the same task. This
> takes very little in the way of state-keeping on either side.
This scares the hell out of me. How do you know whether the old session
committed your transaction, but the connection died just before it could
tell you so? libpqxx is the very LAST place in the chain that is
qualified to undertake the task of error recovery.
There are cases where the best way to design the client code is to make
it automatically rollback and then retry whole transactions. (In
particular, if you use SERIALIZABLE transaction mode then this is more
or less essential to cope with concurrency violations.) If you've got a
client that's built that way, then it's a pretty simple matter to extend
the client to do the same sort of retry for connection-level failures,
too. But I'd still be worried about the prospect of double execution of
a transaction, if the link was lost just after you issued a COMMIT and
just before the backend could tell you it had committed. So you'd also
need to add code to verify you hadn't already executed the transaction.
In any case, I do not think that libpq or libpqxx can or should try to
hide this problem from the client.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Postgres doesn't have 6000 full-time developers engaged in inventing
> bizarre features (and a substitute for something that should be done
> at the network level is bizarre in my book).
May be, though, it's just a matter of not setting SO_KEEPALIVE? In my
experience, the difference between always keeping and spontaneously
losing a connection during a network outage is often this option. In
src/backend/libpq/pqcomm.c, we find (reformatted slightly here):
/* select NODELAY and KEEPALIVE options if it's a TCP connection */ if (port->laddr.sa.sa_family == AF_INET) {
inton = 1; if (setsockopt(port->sock, IPPROTO_TCP, TCP_NODELAY, (char *) &on, sizeof(on)) < 0) {
perror("postmaster: StreamConnection: setsockopt(TCP_NODELAY)"); return STATUS_ERROR; } if
(setsockopt(port->sock,SOL_SOCKET, SO_KEEPALIVE, (char *) &on, sizeof(on)) < 0) {
perror("postmaster:StreamConnection: setsockopt(SO_KEEPALIVE)"); return STATUS_ERROR; } }
And in my nearest manual entry on setsockopt(), I find:
SO_KEEPALIVE enables the periodic transmission of messages on a connected socket. Should the connected party fail
torespond to these messages, the connection is considered broken and processes using the socket are notified via a
SIGPIPEsignal when attempting to send data.
Betcha if Bruce commented out that second setsockopt() block, he'd
suddenly see his PostgreSQL connections stay up, just like the Oracle
ones! :-)
> Non-broken TCP stacks are normally quite tenacious about surviving
> transmission glitches in already-open connections.
...but you can turn part of that off by turning on SO_KEEPALIVE.
-tih
--
Puritanism -- the haunting fear that someone, somewhere, may be happy.
Bruce Badger <bbadger@BadgerSE.com> writes: > Due to network glitch, a PostgreSQL connection over IP is being dropped. > > In the same situation, Oracle can keep it's connections going. I mentioned the SO_KEEPALIVE socket option in another email, but the other possibility here is, of course, that the PostgreSQL connection might be receiving an ICMP error message that Oracle isn't. This would be due to different handling of the two protocols in a firewall or something -- and whether it happens or not would be something you could find out using tcpdump. -tih -- Puritanism -- the haunting fear that someone, somewhere, may be happy.
Tom Ivar Helbekkmo <tih@kpnQwest.no> writes:
>> Non-broken TCP stacks are normally quite tenacious about surviving
>> transmission glitches in already-open connections.
> ...but you can turn part of that off by turning on SO_KEEPALIVE.
In an RFC-compliant stack, the outage interval required before KEEPALIVE
will kill the connection is of the order of hours. RFC 1122 specifies
that the minimum interval before the first probe is even sent is 2 hours
(since last activity on connection), and that a single failed probe is
not sufficient reason to drop the connection.
RFC 2525 does note that excessively short keepalive timeout is a common
form of TCP-stack bug.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > In an RFC-compliant stack, the outage interval required before KEEPALIVE > will kill the connection is of the order of hours. RFC 1122 specifies > that the minimum interval before the first probe is even sent is 2 hours > (since last activity on connection), and that a single failed probe is > not sufficient reason to drop the connection. Ah, yes, I see that now. On NetBSD, it's four hours until the first keepalive, then eight missed ones at 150 second intervals (totalling 20 minutes) are required before the connection is considered dead. > RFC 2525 does note that excessively short keepalive timeout is a common > form of TCP-stack bug. So, Bruce might still be bothered with something like that, and/or (for all he's given us of details) he might actually be talking about a situation where Oracle will wait through severely prolonged outages where PostgreSQL won't. Details, Bruce? -tih -- Puritanism -- the haunting fear that someone, somewhere, may be happy.
Tom Ivar Helbekkmo <tih@kpnQwest.no> writes:
> So, Bruce might still be bothered with something like that, and/or
> (for all he's given us of details) he might actually be talking about
> a situation where Oracle will wait through severely prolonged outages
> where PostgreSQL won't.
I believe that Oracle uses its own networking code (SQLNet) which might
well have different error response behavior than TCP does. Still, it's
hard to believe that even a broken TCP stack will not hold connections
through short- or moderate-duration network glitches; and Bruce did not
say that he was trying to deal with multiple-hour outages.
I have been wondering about whether Bruce's problem is firewall
misbehavior. In particular, if there's a NAT translation happening
anywhere between his client and his server, then the firewall could
break the connection by dropping that particular port mapping, which
perhaps it might do if there's no activity for awhile. In this case,
it might actually be that the default KEEPALIVE timeout of 2 hours is
too long for us :-(. (RFC 1122 says that the timeout shall be
configurable, but this requirement seems to be widely ignored.)
As for why Oracle doesn't suffer the same problem, someone suggested
that the firewall might be specially configured not to drop Oracle
connections (or perhaps to pass them through without NAT mapping).
I don't know enough about SQLNet to know what to look for.
regards, tom lane
Tom Ivar Helbekkmo wrote: > Tom Lane <tgl@sss.pgh.pa.us> writes: > > > In an RFC-compliant stack, the outage interval required before KEEPALIVE > > will kill the connection is of the order of hours. RFC 1122 specifies > > that the minimum interval before the first probe is even sent is 2 hours > > (since last activity on connection), and that a single failed probe is > > not sufficient reason to drop the connection. > > Ah, yes, I see that now. On NetBSD, it's four hours until the first > keepalive, then eight missed ones at 150 second intervals (totalling > 20 minutes) are required before the connection is considered dead. > > > RFC 2525 does note that excessively short keepalive timeout is a common > > form of TCP-stack bug. > > So, Bruce might still be bothered with something like that, and/or > (for all he's given us of details) he might actually be talking about > a situation where Oracle will wait through severely prolonged outages > where PostgreSQL won't. The question is "what exactly is the network glitch"? Firewalls doing NAT frequently cleanup a little too much, namely connections that just have been idle for some time. Maybe Oracle has it's private li'l keepalive ping to avoid that? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
On Fri, Mar 01, 2002 at 02:40:06PM -0500, Tom Lane wrote: > > I have been wondering about whether Bruce's problem is firewall > misbehavior. In particular, if there's a NAT translation happening > anywhere between his client and his server, then the firewall could > break the connection by dropping that particular port mapping, which > perhaps it might do if there's no activity for awhile. In this case, > it might actually be that the default KEEPALIVE timeout of 2 hours is > too long for us :-(. (RFC 1122 says that the timeout shall be > configurable, but this requirement seems to be widely ignored.) If that turns out to be the case, sending null queries periodically should help some. Jeroen
what is your tcpdump looks like? I have been using Pgsql for about five years. I have not seen something like that. If you do have stateful firewall, may be it does not keep state for port 5432.I could be wrong. just a thought. anil anil Tom Lane wrote: > Bruce Badger <bbadger@BadgerSE.com> writes: > > The thing is that from their point of view, Oracle keeps going where > > PostgreSQL does not. Given that both are running over the same network, > > the response of these people could well be to simply move everything to > > Oracle. >
Thanks for that. I have passed the all the comments from the list back to the people with the problem. I'll pass your tcpdump suggestion on too. I'll post the outcome when I hear what it is. Thanks to you all. anil wrote: >what is your tcpdump looks like? I have been using Pgsql for about five years. >I have not seen something like that. If you do have stateful firewall, may be >it does not keep state for port 5432. > I could be wrong. just a thought. > >anil > >anil > >Tom Lane wrote: > >>Bruce Badger <bbadger@BadgerSE.com> writes: >> >>>The thing is that from their point of view, Oracle keeps going where >>>PostgreSQL does not. Given that both are running over the same network, >>>the response of these people could well be to simply move everything to >>>Oracle. >>> > > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
Hello all Please how can I modify - insert, update and delete -records using libpq interfaces. I can use it to SELECT but cannot modify records. Thanks Arnold __________________________________________________ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/