Skip site navigation (1) Skip section navigation (2)

Re: index item size 4496 exceeds maximum 2713

From: Jon Hassen <jhassen(at)azstarnet(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: index item size 4496 exceeds maximum 2713
Date: 2002-03-07 19:37:37
Message-ID: l03130302b8ad6998ba54@[] (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
Hello all,

Thank you all very much for the input. I appreciate it. It turns out that
indeed, the biggest thing that can be indexed using btree is some division
(by 3 or 4, it seems to differ) of the size of the tuple size. (Thanks Tom
Lane and Andrew McMillan for pointing me in that direction)

I ended up changing the backend to a 32768 size block, (by using
BLCKSZ=32678 in the file, then running:
configure/gmake/gmake install) which gave me the 8192 (32,768/4) that I had
been looking for.

Be careful if you do this! Make a pg_dump of your current database first!

For those interested, (and since how to do this doesn't seem to exist
anywhere, or it is VERY hard to find anyway) here is how I did it:

Be careful. please.

1. pd_dumpall > outputfile  	/ This makes a backup of your current db.
2. pg_ctl stop -D /usr/local/pgsql/data 	/stop the pg process
3. Change the file /usr/local/src/postgresql-X.X/src/include/
to reflect the new blocksize that you desire. BLCKSZ = 32768 The 32k number
is the max that you can use, and I would stick with either 8192 or 16384 or
4. Re-run the configure, then the gmake, then the gmake install.
5. Move the /usr/local/pgsql/data directory or rename it. Try:
 mv /usr/local/pgsql/data /usr/local/pgsql/data.old or something like that.
When this is over, you can delete this, but in case you need to go back,
hang on to it. It is your old database(s).
6. Make a new data dir.
(root)# mkdir /usr/local/pgsql/data
(root)# chown postgres /usr/local/pgsql/data
(root)# initlocation -D /usr/local/pgsql/data
(root)# initdb -D /usr/local/pgsql/data
(root)# su postgres
(postgres)# postmaster -D /usr/local/pgsql/data
(postgres)# /usr/local/pgsql/bin/psql -d template1 -f outputfile
(where outputfile is the one you created in step 1.)

That's it. You now have a 32k block size.

btw, I will be trying to implememnt tsearch on this db too. I want it
blazing fast!! faster! Faster! FASTER!!

Jon Hassen
Systems Manager
Arizona Daily Star

In response to

pgsql-novice by date

Next:From: Patrick HatcherDate: 2002-03-07 20:01:12
Subject: SQL error 'Unable to identify an operator = for type numeric?'
Previous:From: Oliver ElphickDate: 2002-03-07 18:57:27
Subject: Re: wierd error

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group