[GENERAL] Running OS-level programs from Postgres?
Maybe this is a well duh kind of question, or maybe there's no straightforward way to do it, but is there any way that I could have a pg function initiate a process on the host system? Specifically I'd like to script an email to send off on an insert trigger, but the ability to initiate system processes in general seems like it would come in handy. My present need is for email notification; if there's a pg function or module that would handle this (I haven't turned up anything in my searches, but maybe I'm using the wrong search terms in the wrong places) I'd be OK for now, but I'd rather have the flexibility of starting whatever process a given circumstance calls for. Thanks. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Idle session timeout?
I've got a bunch of users on VB applications connecting to PG 8.2.3 via psqlodbc with SSL. For a variety of reasons, some good, some probably bad, I have the app open a connection and leave it open, using it as needed to run queries back and forth. If I look in my logs, I see loans of little clusters of entries like this: 2007-05-08 08:44:57 [EMAIL PROTECTED] LOG: SSL SYSCALL error: Connection reset by peer 2007-05-08 08:44:57 [EMAIL PROTECTED] LOG: could not receive data from client: Connection reset by peer 2007-05-08 08:44:57 [EMAIL PROTECTED] LOG: unexpected EOF on client connection These seem to correspond to dropped connections for my users, generally if a user with an open app that hasn't used it for a while attempts to use it. I've put some code in to re-establish the connection to mitigate user frustration, but it doesn't do much to mitigate my frustration. I'm WAY out of my depth here, but my impression, based on the circumstances, is that there is some sort of an idle session timeout kicking in (most likely on the client side) and dropping the connection. Judging by the error messages I'd be inclined to say that it's happening in openSSL, not Postgres, but I don't know. I've searched documentation and newsgroups for Postgres, psqlodbc, and openSSL, and haven't found any user-settable timeout parameters. The one thing I did come across that seemed to my clueless eyes to be relevant was an OpenSSL API call: SSL_CTX_set_timeout(). Even assuming this is relevant, wouldn't this be the kind of thing that would need to be called by Postgres code at the point that SSL is integrated? Anyway, I appreciate any help or guidance anyone might be able to give. It would be awesome if there were a user parameter somewhere on the server or client that someone could point out to me to take care of this. If this is a bug, please help me figure out who it belongs to. If this is a feature, please let me know so I can properly account for it in my code. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Idle session timeout?
Tom Lane wrote: > Sean Murphy <[EMAIL PROTECTED]> writes: >> I'm WAY out of my depth here, but my impression, based on the >> circumstances, is that there is some sort of an idle session timeout >> kicking in (most likely on the client side) and dropping the connection. > > There's no such timeout in the Postgres server, for sure. I would > actually bet that your problem is in some router between the client and > the server. In particular, routers that do NAT address mapping > typically have a timeout after which they will forget the mapping for an > idle connection. If you've got one of those, see if it'll let you > change the timeout. > > If you can't do that, you might think about teaching your client-side > code to send dummy queries every so often. > > regards, tom lane > I've already maxed out the connection timeout at the firewall... and I've been using dummy queries every five minutes, but it just feels like a crutch to do so. Sean ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Idle session timeout?
Scott Marlowe wrote: > On Tue, 2007-05-08 at 12:19, Sean Murphy wrote: >> Tom Lane wrote: >>> Sean Murphy <[EMAIL PROTECTED]> writes: >>>> I'm WAY out of my depth here, but my impression, based on the >>>> circumstances, is that there is some sort of an idle session timeout >>>> kicking in (most likely on the client side) and dropping the connection. >>> There's no such timeout in the Postgres server, for sure. I would >>> actually bet that your problem is in some router between the client and >>> the server. In particular, routers that do NAT address mapping >>> typically have a timeout after which they will forget the mapping for an >>> idle connection. If you've got one of those, see if it'll let you >>> change the timeout. >>> >>> If you can't do that, you might think about teaching your client-side >>> code to send dummy queries every so often. >>> >>> regards, tom lane >>> >> I've already maxed out the connection timeout at the firewall... and >> I've been using dummy queries every five minutes, but it just feels like >> a crutch to do so. > > Have you looked into tcp keepalive settings? > > net.ipv4.tcp_keepalive_intvl = 75 > net.ipv4.tcp_keepalive_probes = 9 > net.ipv4.tcp_keepalive_time = 500 > > Not sure if those settings will help with an NAT router or not but it's > worth a try. Pgsql 8.2 can set those for you. > I may be celebrating prematurely, but resetting the tcp_keepalive parameters seems to have done the trick - I left a pgAdmin connection that *always* drops after inactivity up while I went to lunch and it was still alive when I got back. Is there a way to alter the tcp_keepalive settings on an app-by-app basis rather than for the whole system? Thanks! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Idle session timeout?
Scott Marlowe wrote: > On Tue, 2007-05-08 at 15:59, Sean Murphy wrote: >> Scott Marlowe wrote: >>> On Tue, 2007-05-08 at 12:19, Sean Murphy wrote: >>>> Tom Lane wrote: >>>>> Sean Murphy <[EMAIL PROTECTED]> writes: >>>>>> I'm WAY out of my depth here, but my impression, based on the >>>>>> circumstances, is that there is some sort of an idle session timeout >>>>>> kicking in (most likely on the client side) and dropping the connection. >>>>> There's no such timeout in the Postgres server, for sure. I would >>>>> actually bet that your problem is in some router between the client and >>>>> the server. In particular, routers that do NAT address mapping >>>>> typically have a timeout after which they will forget the mapping for an >>>>> idle connection. If you've got one of those, see if it'll let you >>>>> change the timeout. >>>>> >>>>> If you can't do that, you might think about teaching your client-side >>>>> code to send dummy queries every so often. >>>>> >>>>> regards, tom lane >>>>> >>>> I've already maxed out the connection timeout at the firewall... and >>>> I've been using dummy queries every five minutes, but it just feels like >>>> a crutch to do so. >>> Have you looked into tcp keepalive settings? >>> >>> net.ipv4.tcp_keepalive_intvl = 75 >>> net.ipv4.tcp_keepalive_probes = 9 >>> net.ipv4.tcp_keepalive_time = 500 >>> >>> Not sure if those settings will help with an NAT router or not but it's >>> worth a try. Pgsql 8.2 can set those for you. >>> >> I may be celebrating prematurely, > > Never stopped me :) > >> but resetting the tcp_keepalive >> parameters seems to have done the trick - I left a pgAdmin connection >> that *always* drops after inactivity up while I went to lunch and it was >> still alive when I got back. >> >> Is there a way to alter the tcp_keepalive settings on an app-by-app >> basis rather than for the whole system? > > Well, you could set it on individual workstations instead of on the > server. I.e. if you set tcp_keepalive on your workstation to 500, but > leave Wally and Dilbert set at the default 7200 then they'd still > timeout and you wouldn't. > Unfortunately, my individual workstations are all running Windows...:( any idea if/where this could be set in MS-land? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
