Thread: connection timeout?
Is there a connection timeout? I'm using DBD::Pg (1.47) in perl to access 8.2.1 database. I've got a rather long running script; it does a bit of database access, then did some other crunching for about 11-12 hours before making another pgsql call; at which point, I get this error: FATAL: This connection has been terminated by the administrator. I've restarted my script on a faster system and expect to get to the same point in about 4 hours this time. Hopefully timeout values won't get in the way this time?
Make sure there is no firewall setting in between the database server and application that might be closing the connections...
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com )
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com )
On 2/3/07, Frank Bax <fbax@sympatico.ca> wrote:
Is there a connection timeout? I'm using DBD::Pg (1.47) in perl to access
8.2.1 database.
I've got a rather long running script; it does a bit of database access,
then did some other crunching for about 11-12 hours before making another
pgsql call; at which point, I get this error:
FATAL: This connection has been terminated by the administrator.
I've restarted my script on a faster system and expect to get to the same
point in about 4 hours this time. Hopefully timeout values won't get in
the way this time?
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Script and database are both on same system. Faster system was able to get past next database access. So far so good, script continues to run. Still curious why it failed on slower system, though. Was this just a one-time "glitch"? Background info: This is a *very* long running script (could be days) that initially loads data from database, then periodically updates database to let know current status of otherwise cpu-intensive process. Running on a P4 1.80Ghz processor, script got to one of the "status update" points in the process in 11:14 (hrs:min); a Core2Duo 1.86Ghz system took 4:06 (quite close to prediction) to reach same point in process. Script does not exploit multi-processors. At 01:35 PM 2/3/07, Shoaib Mir wrote: Make sure there is no firewall setting in between the database server and application that might be closing the connections... -- Shoaib Mir EnterpriseDB (www.enterprisedb.com ) On 2/3/07, Frank Bax <fbax@sympatico.ca> wrote: Is there a connection timeout? I'm using DBD::Pg (1.47) in perl to access 8.2.1 database. I've got a rather long running script; it does a bit of database access, then did some other crunching for about 11-12 hours before making another pgsql call; at which point, I get this error: FATAL: This connection has been terminated by the administrator. I've restarted my script on a faster system and expect to get to the same point in about 4 hours this time. Hopefully timeout values won't get in the way this time? ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
I have the following in a plpgsql function:
DECLARE
rec RECORD;
BEGIN
FOR rec IN EXECUTE 'SELECT * FROM '||table[1]
LOOP
--use the results here
END LOOP;
How can I determine the actual field name and the field value for the record variable (rec) if I were to look in index 1 or 2 or 3 of the variable?
As you can see from the snippet of the function, the table that will be queried is dynamic and as such so are the names of the fields the rec variable will be returning.
Thank you,
Derrick
Derrick Betts <list@blueaxis.com> schrieb: > > I have the following in a plpgsql function: > > DECLARE > rec RECORD; > > BEGIN > FOR rec IN EXECUTE 'SELECT * FROM '||table[1] > LOOP > --use the results here > END LOOP; > > How can I determine the actual field name and the field value for the record > variable (rec) if I were to look in index 1 or 2 or 3 of the variable? Unfortunately you can't access to row-index, you need the name of the column. You can do a select on information_schema.columns to retrieve the column-names: select column_name from information_schema.columns where table_name = ... Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
In that case for the *slower* system I will recommend having a look at your database server logs and syslogs to see if it was a memory overcomitt that caused the disconnection. It might be an OOM killer here...
To my knowledge the multi-processing with PostgreSQL is dependent on how your OS schedules the processes across the multi-processer box. Which OS are you using? I guess 2.6 + Kernel can be a good option as I have seen it to perform good processes scheduling.
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
To my knowledge the multi-processing with PostgreSQL is dependent on how your OS schedules the processes across the multi-processer box. Which OS are you using? I guess 2.6 + Kernel can be a good option as I have seen it to perform good processes scheduling.
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 2/4/07, Frank Bax <fbax@sympatico.ca> wrote:
Script and database are both on same system. Faster system was able to get past next database access. So far so good, script continues to run. Still curious why it failed on slower system, though. Was this just a one-time "glitch"?
Background info: This is a *very* long running script (could be days) that initially loads data from database, then periodically updates database to let know current status of otherwise cpu-intensive process. Running on a P4 1.80Ghz processor, script got to one of the "status update" points in the process in 11:14 (hrs:min); a Core2Duo 1.86Ghz system took 4:06 (quite close to prediction) to reach same point in process. Script does not exploit multi-processors.
At 01:35 PM 2/3/07, Shoaib Mir wrote:Make sure there is no firewall setting in between the database server and application that might be closing the connections...
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com )
On 2/3/07, Frank Bax <fbax@sympatico.ca> wrote:Is there a connection timeout? I'm using DBD::Pg (1.47) in perl to access
8.2.1 database.
I've got a rather long running script; it does a bit of database access,
then did some other crunching for about 11-12 hours before making another
pgsql call; at which point, I get this error:
FATAL: This connection has been terminated by the administrator.
I've restarted my script on a faster system and expect to get to the same
point in about 4 hours this time. Hopefully timeout values won't get in
the way this time?
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Frank Bax <fbax@sympatico.ca> writes:
> Script and database are both on same system. Faster system was able
> to get past next database access. So far so good, script continues
> to run. Still curious why it failed on slower system, though.
There's no connection timeout built into PG itself. The message implies
that something sent the backend a SIGTERM signal, which makes me wonder
if you are running the postmaster under ulimit settings that include
a finite limit on process CPU or I/O expenditure. Some platforms have
special signals for that (SIGXCPU etc) but I think some just send
SIGTERM when a process goes over the limit.
regards, tom lane
Andreas Kretschmer <akretschmer@spamfence.net> writes:
> Derrick Betts <list@blueaxis.com> schrieb:
>> How can I determine the actual field name and the field value for the record
>> variable (rec) if I were to look in index 1 or 2 or 3 of the variable?
> Unfortunately you can't access to row-index, you need the name of the
> column.
plpgsql is not designed for that sort of thing; despite the existence of
"record" variables, it's really a strongly typed language and doesn't
cope well with fields of variable data types. Consider using one of the
other PLs instead (plperl, plpython, etc depending on which language you
like best). Most of them can handle this easily.
regards, tom lane
For some reason I cannot get an IF statement to work in PostGreSQL, even
though I am doing it in the format the manual tells me to...
Here is my query...
IF 1 = 1 THEN
update contacts set deleted = TRUE
END IF;
I get the error ERROR: syntax error at or near "IF" at character 1
I am using EMS's PostGreSQL Manager for windows.
Thanks
am Sun, dem 04.02.2007, um 20:15:33 +0200 mailte Greg Quinn folgendes: > For some reason I cannot get an IF statement to work in PostGreSQL, even > though I am doing it in the format the manual tells me to... > Here is my query... > > IF 1 = 1 THEN > update contacts set deleted = TRUE > END IF; IF is only possible inside plpgsql¹, not in plain SQL. And, your check is always true because 1 = 1 is always true. ¹ or an other language Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Ok, then how do I make sure my query uses plpsql?
I have tried
IF 1 = 1 THEN
update contacts set deleted = TRUE
END IF;
LANGUAGE 'plpgsql';
But I still get the same error.
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of A. Kretschmer
Sent: Sunday, February 04, 2007 8:25 PM
To: pgsql-novice@postgresql.org
Subject: SPAM-LOW: Re: [NOVICE] If Statement
IF is only possible inside plpgsql¹, not in plain SQL. And, your check is
always true because 1 = 1 is always true.
¹ or an other language
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
"Greg Quinn" <greg@officium.co.za> writes: > Ok, then how do I make sure my query uses plpsql? Queries, per se, don't use plpgsql. It's a language for writing functions in, so you have to create a function. Look at the examples in the manual, for instance on this page: http://www.postgresql.org/docs/8.2/static/plpgsql-porting.html regards, tom lane
Perhaps what you actually want is the CASE function - it performs what most
people with previous programming experience would expect from IF
Cheers,
-p
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Greg Quinn
Sent: Monday, 5 February 2007 05:16
To: pgsql-novice@postgresql.org
Subject: [NOVICE] If Statement
For some reason I cannot get an IF statement to work in PostGreSQL, even
though I am doing it in the format the manual tells me to...
Here is my query...
IF 1 = 1 THEN
update contacts set deleted = TRUE
END IF;
I get the error ERROR: syntax error at or near "IF" at character 1
I am using EMS's PostGreSQL Manager for windows.
Thanks
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
*******************Confidentiality and Privilege Notice*******************
The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.
Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments