Re: !!URGENT!! Should I keep INDEX on the table? - Mailing list pgsql-novice

From Rajan Bhide
Subject Re: !!URGENT!! Should I keep INDEX on the table?
Date
Msg-id FF851C7EEB75954F9BCFB5CA117AB1EC6742D1@delta.nulinkinc.com
Whole thread Raw
List pgsql-novice
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@nulinkinc.com> writes:
> 2)WARNING:  specified item offset is too large
> PANIC:  failed to add item to the page for "cdr_transid_key"

[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


pgsql-novice by date:

Previous
From: Tom Lane
Date:
Subject: Re: !!URGENT!! Should I keep INDEX on the table?
Next
From: Eduardo Vázquez Rodríguez
Date:
Subject: Copy From problem