Hi Oliver,

Thanks, for your reply.
Even I was thinking, VACCUM ANALYSE <table-name> is jes enough but following discussion on forum forced me to add reindexing as I also saw similar error msgs from my postgres server.

Also Can Some one help me understand the overhead involved in DROPing and CREATing the indexes? How often this activity should be performed?
This is really URGENT guys.. So do give your feedbacks.

Thanks,
Rajan Bhide


-----Original Message-----
From: Oliver Fromme [mailtolli (AT) lurza (DOT) secnetix.de]
Sent: Friday, May 14, 2004 6:16 PM
To: Rajan Bhide
Cc: pgsql-novice (AT) postgresql (DOT) org
Subject: Re: [NOVICE] Should I keep INDEX on the table?



Hello,

Rajan Bhide wrote:
I think that is redundant. You can easily verify that by dropping the index and then check with EXPLAIN if anything has changed with regards to indexusage.

Uhm, maybe I'm completely misunderstanding you here, but
I think that's the job of VACUUM. There should not be a
need to drop and re-create the index for that purpose.

Best regards
Oliver Fromme

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München Any opinions expressed in this message may be personal to the author and may not necessarily reflect the opinions of secnetix in any way.

"In My Egoistical Opinion, most people's C programs should be indented six feet downward and covered with dirt."
-- Blair P. Houghton


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Rajan Bhide wrote:
I can't believe that's true. That would be a serious show-
stopper bug in PostgreSQL, in my opinion. The documentation
does not mention anywhere that periodically dropping and re-
creating indices might be necessary. In fact, in chapter
11.8 it specifically says "indexes in PostgreSQL do not need
maintenance and tuning".

(Can anyone of the developers confirm or deny that?)

That article is two years old, and describes different symp-
toms than what you described. So I think it does not apply
to your case.

I seriously hope it should not have be performed at all.
If you run a database cluster with dozens or even hundreds
of customers on it, it appears pretty ridculous to me to
have to rebuild all of their indices every night. I don't
even want to know what indices they have -- it's the job
of the database to maintain that and care for it.

Frankly, I think VACUUMimg the databases (possibly with the
"FULL" option) should be enough for normal maintenance.
If it's not, I would consider that a bug, or at least a
serious PITA that deservers to be removed ASAP. ;-)

(Just my 0.02 Euro.)

Best regards
Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

"That's what I love about GUIs: They make simple tasks easier,
and complex tasks impossible."
-- John William Chambless

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Mon, May 17, 2004 at 10:09:32 +0200,
Oliver Fromme <olli (AT) lurza (DOT) secnetix.de> wrote:
There was a problem with reclaming dead space in btrees before 7.4.
If old data was deleted and new data added with the key for the new
date always increasing (or always decreasing), then the freed up
pages would never get reused. Changes were mage in 7.4 to alleviate
this problem. The developers weren't sure if there were still some
usage patterns that could result in a lot of wasted space. My memory
is that there was an O(n) upper bound on the space used now, but I
might be wrong about that.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Hello All,

Can anybody from the forum (developer) confirm whether external
reindexing is not required with Postgres 7.4.
I added external reindexing after searching forum when my App which uses
Postgres (7.4.1) started giving error msgs like:

1)message type 0x44 arrived from server while idle.
http://archives.postgresql.org/pgsql...6/msg00813.php

2)WARNING: specified item offset is too large
PANIC: failed to add item to the page for "cdr_transid_key"
server closed the connection unexpectedly This probably means the server
terminated abnormally before or while processing the request.

After working for a week with external reindexing, Things are working
well.
But I am not sure whether its really the reindexing factor as some of
you have suggested that : "indexes in PostgreSQL do not need maintenance
and tuning".

Also Can anybody tell me what are the overheads involved with DROPing
and CREATing indexes and how often (frequency) this activity should be
perform (? If really required ?) on heavily loaded tables.
My tables have 2M transaction per 6 hours.
I am performing "VACCUM ANALYSE" every 10 mins.
I really require some help as I am doing all these changes with my
production build App.

Thanks,
Rajan.


-----Original Message-----
From: Bruno Wolff III [mailto:bruno (AT) wolff (DOT) to]
Sent: Monday, May 17, 2004 11:04 PM
To: Oliver Fromme
Cc: Rajan Bhide; pgsql-novice (AT) postgresql (DOT) org
Subject: Re: [NOVICE] !!URGENT!! Should I keep INDEX on the table?


On Mon, May 17, 2004 at 10:09:32 +0200,
Oliver Fromme <olli (AT) lurza (DOT) secnetix.de> wrote:
There was a problem with reclaming dead space in btrees before 7.4. If
old data was deleted and new data added with the key for the new date
always increasing (or always decreasing), then the freed up pages would
never get reused. Changes were mage in 7.4 to alleviate this problem.
The developers weren't sure if there were still some usage patterns that
could result in a lot of wasted space. My memory is that there was an
O(n) upper bound on the space used now, but I might be wrong about that.




---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

"Rajan Bhide" <rbhide (AT) nulinkinc (DOT) com> writes:
[blinks] Can you offer a reproducible test case for that one?

Your mail seems to mention a client bug (message ... while idle),
a server bug (the above), performance issues, and I'm not sure
what else. It would be well to try to subdivide your concerns.
I also wonder whether you might be suffering from flaky-hardware
problems, seeing that you are reporting at least two odd behaviors
that no one else has seen with PG 7.4.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

So concentrating on the client side issue first:

This was reported by the customer site.
The system info is as follows:
Sun Netra T4 system with 4G physical memory running Solaris 9 with
Postgres 7.4.2.
The table is accessed by two independent processes : One performs
Insertion and Other performs Select and Delete.
Table structure:
CREATE TABLE "cdr_archive" (
"accesstime" bigint,
"cgfaddr" bigint not NULL,
"cgfseqnum" integer default 0,
"reqindex" integer default 0,
"aaamgrid" smallint,
"cdrcount" integer,
"reqcount" integer,
"transid" bigint,
"reqinfo" bytea not NULL,
"status" smallint default 0,
UNIQUE ( accesstime, cgfaddr, transid, reqindex )
);


CREATE UNIQUE INDEX "access_time_req_idx"
ON "cdr_archive"("accesstime","reqindex","transid ");


The "reqinfo" field contains binary data stored using bytea type of
around 65K (*4 for bytea conversion).

The transaction rate (Insertion/Deletion) is around 2M per six hours and
As I mentioned the App is performing "VACCUM ANALYSE" on the table once
every 10 mins.
The error log file is not available as this was reported by customer
site.

I have introduced external reindexing mechanism using crob based job
which periodically DROP and CREATE "access_time_req_idx" index every 2
hours after which both the problems (Prob#2 also) are not seen.

Under what circumstances "message type 0x44 arrived from server while
idle." is reported to client?
Is this a problem with the transaction rate or the data type used?

Thanks,
Rajan Bhide



"Rajan Bhide" <rbhide (AT) nulinkinc (DOT) com> writes:
[blinks] Can you offer a reproducible test case for that one?

Your mail seems to mention a client bug (message ... while idle), a
server bug (the above), performance issues, and I'm not sure what else.
It would be well to try to subdivide your concerns. I also wonder
whether you might be suffering from flaky-hardware problems, seeing that
you are reporting at least two odd behaviors that no one else has seen
with PG 7.4.

regards, tom lane


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend