psql: FATAL: the database system is in recovery mode

Bhushan Verma wrote:

Hi All,

I am trying to run as follows queries
----------
SELECT PageIndex,
(SUM(ConnectDoneTime - StartTime) * 100) / SUM(EndTime - StartTime)
AS "ConnectTimePct",
(SUM(WriteCompleTime - ConnectDoneTime) * 100) / SUM(EndTime -
StartTime) AS "RequestSentTimePct",
(SUM(FirstByteRcdTime - WriteCompleTime) * 100) / SUM(EndTime -
StartTime) AS "FirstByteTimePct",
(SUM(EndTime - FirstByteRcdTime) * 100) / SUM(EndTime - StartTime) AS
"DownloadTimePct"
FROM UrlRecord_$1
WHERE EndTime <> 0
AND ConnectDoneTime <> 0
AND WriteCompleTime <> 0
AND FirstByteRcdTime <> 0
GROUP By PageIndex
ORDER By PageIndex;

-----------
after running this query, I am getting as follows error
-----------
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

connection to server was lost
...
Is there any limitations or some database configuration problem.
Can anyone please help me in this regards.

A segmentation fault is certainly not normal, but it's hard to tell if
it's caused by a bug, misconfigured or buggy 3rd party modules, or
faulty hardware without some more information. Like:

- What version of PostgreSQL you are using?
- What does "UrlRecord_$1" table look like?
- What is your operating system?

A backtrace would help a lot to pinpoint the problem. Please run "ulimit
-c unlimited" or similar to enable core dumps, and then do:

gdb <path to postgres binary>/postgres $PGDATA/core

bt

and post the output.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Hi,

Sorry for the insufficient information.
As follows is the infomrations:
- What version of PostgreSQL you are using?
#rpm -qa|grep postgres
postgresql-server-8.0.3-1
postgresql-libs-8.0.3-1
postgresql-odbc-08.00.0100-1
postgresql-jdbc-8.0.3-1
postgresql-docs-8.0.3-1
postgresql-tcl-8.0.3-1
postgresql-test-8.0.3-1
postgresql-devel-8.0.3-1
postgresql-8.0.3-1
gnucash-backend-postgres-1.8.11-3
freeradius-postgresql-1.0.2-2
postgresql-python-8.0.3-1
postgresql-contrib-8.0.3-1
postgresql-pl-8.0.3-1

- What does "UrlRecord_$1" table look like?
#select * from UrlRecord_9492 ;

testindex | urlindex | sessionindex | sessioninstance | transactionindex |
pageindex | childindex | txinstance | pageinstance | fetchedfrom |
httsreqreused | conreused | urltype | starttime | dnsstarttime |
dnsendtime | connectstarttime | connectdonetime | sslhandshakedone |
writecompletime | firstbytercdtime
| requestcompletedtime | renderingtime | endtime | httpresponsecode |
httppayloadbytessent | appbytessent | ethbytessent | httppayloadbytesrcd |
appbytesrcd | ethbytesrcd | completionmode | status | connectionnumber |
connectiontype | retries
-----------+----------+--------------+-----------------+------------------+-----------+------------+------------+--------------+-------------+---------------+-----------+---------+-----------+--------------+------------+------------------+-----------------+------------------+-----------------+------------------+----------------------+---------------+---------+------------------+----------------------+--------------+--------------+---------------------+-------------+-------------+----------------+--------+------------------+----------------+---------
9492 | 0 | 0 | 0 |
| 0 | 0 |
0 | 0 | 0 | f | f | 1
| 2719 | 0 |
0 | 2719 | 2720 | 2720
| 2720 | 2733
| 2733 | 0 | 2733 | 200
| 0 | 453 | 0 | 2975
| 3065 | 0 | 0 | 0 |
0 | 1 | 0

- What is your operating system
OS is Fedora core-9[FC9].

I have already done ulimit -c unlimited, but not able to generate core file
in $PGDATA ie
/var/lib/pgsql/data.

On 8/31/09, Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote:

Bhushan Verma wrote:

Hi All,

I am trying to run as follows queries
----------
SELECT PageIndex,
(SUM(ConnectDoneTime - StartTime) * 100) / SUM(EndTime -

StartTime)

AS "ConnectTimePct",
(SUM(WriteCompleTime - ConnectDoneTime) * 100) / SUM(EndTime -
StartTime) AS "RequestSentTimePct",
(SUM(FirstByteRcdTime - WriteCompleTime) * 100) / SUM(EndTime -
StartTime) AS "FirstByteTimePct",
(SUM(EndTime - FirstByteRcdTime) * 100) / SUM(EndTime - StartTime)

AS

"DownloadTimePct"
FROM UrlRecord_$1
WHERE EndTime <> 0
AND ConnectDoneTime <> 0
AND WriteCompleTime <> 0
AND FirstByteRcdTime <> 0
GROUP By PageIndex
ORDER By PageIndex;

-----------
after running this query, I am getting as follows error
-----------
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

connection to server was lost

...

Is there any limitations or some database configuration problem.
Can anyone please help me in this regards.

A segmentation fault is certainly not normal, but it's hard to tell if
it's caused by a bug, misconfigured or buggy 3rd party modules, or
faulty hardware without some more information. Like:

- What version of PostgreSQL you are using?
- What does "UrlRecord_$1" table look like?
- What is your operating system?

A backtrace would help a lot to pinpoint the problem. Please run "ulimit
-c unlimited" or similar to enable core dumps, and then do:

gdb <path to postgres binary>/postgres $PGDATA/core

bt

and post the output.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

--
With Regards,
Bhushan

Bhushan Verma <verma.bhushan@gmail.com> writes:

- What version of PostgreSQL you are using?
#rpm -qa|grep postgres
postgresql-server-8.0.3-1

8.0.3 is four years obsolete. If you can still reproduce the crash
after updating to some recent 8.0.x version, it'd be worth looking
closer.

regards, tom lane

Hi,
Sorry that was for my fedora 4 machine.

But my fedora 9 machine have as follows postgres version.
rpm -qa|grep postgres
postgresql-server-8.3.1-1.fc9.i386
postgresql-devel-8.3.1-1.fc9.i386
postgresql-python-8.3.1-1.fc9.i386
postgresql-8.3.1-1.fc9.i386
postgresql-libs-8.3.1-1.fc9.i386

I am getting the problem on Fedora 9 not on fedora 4.

On 9/1/09, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Bhushan Verma <verma.bhushan@gmail.com> writes:

- What version of PostgreSQL you are using?
#rpm -qa|grep postgres
postgresql-server-8.0.3-1

8.0.3 is four years obsolete. If you can still reproduce the crash
after updating to some recent 8.0.x version, it'd be worth looking
closer.

regards, tom lane

--
With Regards,
Bhushan

On Tue, Sep 1, 2009 at 8:47 AM, Bhushan Verma<verma.bhushan@gmail.com> wrote:

But my fedora 9 machine have as follows postgres version.
rpm -qa|grep postgres
postgresql-server-8.3.1-1.fc9.i386
postgresql-devel-8.3.1-1.fc9.i386
postgresql-python-8.3.1-1.fc9.i386
postgresql-8.3.1-1.fc9.i386
postgresql-libs-8.3.1-1.fc9.i386

Same here, please upgrade to 8.3.7 and see if you can reproduce the problem.

If so, please provide more information: a self contained test case
would be nice if you can build one or a backtrace as Heikki suggested
it.

If you can't upgrade for a good reason, please provide the self
contained test case so that we can check ourselves if it's still
reproducible with 8.3.7.

Thanks.

--
Guillaume

On Tue, 2009-09-01 at 12:17 +0530, Bhushan Verma wrote:

But my fedora 9 machine have as follows postgres version.
rpm -qa|grep postgres
postgresql-server-8.3.1-1.fc9.i386

Please note that you will need to follow this procedure before updating
PostgreSQL on Fedora 9:

https://fedoraproject.org/wiki/Enabling_new_signing_key#Fedora_9

Otherwise it will stay on 8.3.1 .

--
Devrim GÜNDÜZ, RHCE
Command Prompt - http://www.CommandPrompt.com
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org

Thanks for this information.
By the way my same queries is workig fine on as follows version:

Machine: Fedora core 4[FC4]
PostGresVersion:
$ rpm -qa|grep postgres
postgresql-server-8.0.3-1
postgresql-libs-8.0.3-1
postgresql-odbc-08.00.0100-1
postgresql-jdbc-8.0.3-1
postgresql-docs-8.0.3-1
postgresql-tcl-8.0.3-1
postgresql-test-8.0.3-1
postgresql-devel-8.0.3-1
postgresql-8.0.3-1
gnucash-backend-postgres-1.8.11-3
freeradius-postgresql-1.0.2-2
postgresql-python-8.0.3-1
postgresql-contrib-8.0.3-1
postgresql-pl-8.0.3-1

with regards,
Bhushan

On 9/3/09, Devrim GÜNDÜZ <devrim@gunduz.org> wrote:

On Tue, 2009-09-01 at 12:17 +0530, Bhushan Verma wrote:

But my fedora 9 machine have as follows postgres version.
rpm -qa|grep postgres
postgresql-server-8.3.1-1.fc9.i386

Please note that you will need to follow this procedure before updating
PostgreSQL on Fedora 9:

https://fedoraproject.org/wiki/Enabling_new_signing_key#Fedora_9

Otherwise it will stay on 8.3.1 .

--
Devrim GÜNDÜZ, RHCE
Command Prompt - http://www.CommandPrompt.com
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org

--
With Regards,
Bhushan