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.