On Tue, Mar 14, 2000 at 05:05:26PM +0100, Wim Aarts wrote:
> I'm getting futher in focussing on the problem. I can reproduce these
> messages I get when vacuuming a database.
> And by reproduce I mean on a different system a clean installed database It
> takes me less then a minute....
>
> NOTICE: Rel pg_class: TID 294/3: InsertTransactionInProgress 20065 - can't
> shrink relation
> NOTICE: Rel pg_class: TID 294/4: InsertTransactionInProgress 20065 - can't
> shrink relation
> NOTICE: Index pg_class_relname_index: NUMBER OF INDEX' TUPLES (1615) IS NOT
> THE SAME AS HEAP' (1587)
> NOTICE: Index pg_class_oid_index: NUMBER OF INDEX' TUPLES (1615) IS NOT THE
> SAME AS HEAP' (1587)
> VACUUM
O.K. - something is messing with pg_class (the system table that defines the
system's tables ;-) and not updating the indices. Looks like tuples are
disappearing. Are you losing tables?
>
> The message below is also one of the messages I get when restarting the
> database after a crash.
>
> Starting postgresql service: IpcMemoryCreate: shmget failed (Identifier
> removed) key=5432010, size=120, permission=700
> IpcMemoryIdGet: shmget failed (Identifier removed) key=5432010, size=120,
> permission=0
> IpcMemoryAttach: shmat failed (Invalid argument) id=-2
> FATAL 1: AttachSLockMemory: could not attach segment
This is a side effect of the crashed server not releasing it's shared memory,
and you therefore running out. Try running ipcclean to remove them before
restarting the server.
>
> I get these messages by doing many revokes (> 1000)
>
Ah, I think I see where this is coming from:
REVOKE changes the tuples in system relation pg_class by means direct
access methods, bypassing the syscache, then manually updates the indices.
Sounds like having several hundred pending during a transaction is
confusing vacuum. Someone with more knowledge about the guts of vacuum
than I may be able to figure out exactly why.
> psql -e template1 < /tmp/test-revokes
> where test-revokes looks like:
> \connect foodb
> begin;
> alter user hr270 nocreateuser;
> REVOKE all on foo from pn407;
> REVOKE all on bar from pn407;
> another 1000 of revokes
> .
> .
> REVOKE all on foo2 from pn407;
> end;
>
> And while doing that I perform a vacuum.
> vacuumdb foodb
>
> Please tell me I'm doing something I really shouldn't be doing.
>
O.K. - you're doing something you really shouldn't be doing. ;-)
No, seriously, why do you need to simultaneously vacuum? can't you vacuum
after committing the transaction?
Ross
--
Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
