create index - invalid memory alloc request size

Question

i get this error "invalid memory alloc request size 18446744072635840654. SQL Status:XX000" when i want to create an index.

The Table looks like this:

CREATE TABLE schema.tblname
(
  id bigint NOT NULL DEFAULT nextval(('schema.tblname_id_seq'::text)::regclass),
  column_1 integer,
  column_2 character varying(20),
  ... 8 other columns ...,
  CONSTRAINT tblname_pkey PRIMARY KEY (id),
  CONSTRAINT tlbname_fk FOREIGN KEY (column_1)
      REFERENCES schema.tblname2 (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

Server version:

PostgreSQL 8.3.8 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3)

Hardware (Virtual Machine):

4 Cores, 16GB RAM, 700GB SAN HDD

ulimit -a:

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 139264
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 139264
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

The History: We had a crash of the Raid-Controller in the SAN. The redundant Server/SAN prevented a failure of the DB but we had corrupted data in the table. Then i have searched the table for corrupted data and deleted the rows from the table. Now i want to recreate the index to be sure to have a correct index.

I wanted to create following index:

CREATE INDEX tlbname_column1_column2x
  ON schema.tlbname
  USING btree
  (column_1, column_2);

This was not possible because the following error occurred:

index row requires 2286728 bytes, maximum size is 8191

So i made an index on column_1, this worked fine and deleted it after that. Then i tried to create an index on column_2 and the error "index row requires 2286728 bytes, maximum size is 8191" occurred. I searched the table for rows where the octet_length was greater then 20 (because of charvar 20 on column_2) and deletet the lines (approx. 40k rows).

Now when i try to create the index on column_2 it fails with the error "invalid memory alloc request size 18446744072635840654. SQL Status:XX000"

Table info:

rows in the table: aprrox. 1.841.170.000
tablesize: 197GB

The index

CREATE INDEX tlbname_column1_column2x
  ON schema.tlbname
  USING btree
  (column_1, column_2);

already exists since the creation of the table and it worked without any problems. But the recreation does not work.

Any suggestions what i can try or do? Thanks.

Answer 1

This sort of problem almost certainly means damaged table information, either regarding table headers, page headers, or tuple headers. Your best option is to dump and restore, or to restore from backup before the crash.

Wish I had a better answer for you but you have to consider that you have fixed some problems and now there are others. You have to assume that there will be others still.