Thread: Find a broken table
Hi guys, Suddenly, the processes for postmaster are overqueued and becoming more and more for every access. By investigating,it seems me that maybe several tables or indexes of database are broken and i was wondering is there any way to find out the exact broken table or index? thanx in advance.
I have a database that has two tables. One of the tables gets changed very often. (like every 5 minutes). The values that where in the table are replaced with a new set of values with new unique keys. Now, I vacuum these tables fairly often, but the pkey files for these tables never seem to get smaller. They just keep growing. What do I have to do to make the pkey files flush there extra blocks? PostgreSQL version 7.0.3 running under Linux. Matthew M. Copeland
matthew.copeland@honeywell.com writes:
> I have a database that has two tables. One of the tables gets changed
> very often. (like every 5 minutes). The values that where in the table
> are replaced with a new set of values with new unique keys. Now, I vacuum
> these tables fairly often, but the pkey files for these tables never seem
> to get smaller. They just keep growing. What do I have to do to make the
> pkey files flush there extra blocks?
Yeah, VACUUM doesn't shrink indexes presently (it's on the TODO
list...).
> PostgreSQL version 7.0.3 running under Linux.
You could try REINDEX to rebuild the indexes, but I'd recommend updating
to 7.1.2 (or soon, 7.1.3) first. I don't recall whether REINDEX is
available/trustworthy in 7.0.
A grottier solution is to DROP and re-CREATE the indexes.
regards, tom lane
It's probably beyond me, but other than the obvious (stopping and restarting postmaster) have you tried a database dump andrestore? Did that fix the problem? It should, at least, re-create all the indexes... --- Allan. Ariunbold Gerelt-Od wrote: > Hi guys, > Suddenly, the processes for postmaster are overqueued and > becoming more and more for every access. > By investigating,it seems me that maybe several tables or indexes of > database are broken and i was wondering is there any way to find out the > exact broken table or index? > thanx in advance. > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
> Yeah, VACUUM doesn't shrink indexes presently (it's on the TODO > list...). > > > PostgreSQL version 7.0.3 running under Linux. > > You could try REINDEX to rebuild the indexes, but I'd recommend updating > to 7.1.2 (or soon, 7.1.3) first. I don't recall whether REINDEX is > available/trustworthy in 7.0. > > A grottier solution is to DROP and re-CREATE the indexes. I can do that on a primary key? I didn't realize that. I tried switching to PostgreSQL 7.1 when it first came out, but it now handles ansynchronous notification differently to the point where my old code doesn't work anymore. It mentioned something at the time about not having the feature implemented or it being wrong or something. (when 7.1 first campe out, so I don't quite remember.) Anyhow, I have to have that feature. Do you know whether it is working again in the current 7.1.3? I will take a look at it and see if I can get it working though. Thanks for all the help, Matthew M. Copeland
Well, I reindexed on 7.0.3, and that worked. I also upgraded, and that
worked with my code also, so ignore the not working comment.
Thanks for the help,
Matthew M. Copeland
On Tue, 14 Aug 2001 matthew.copeland@honeywell.com wrote:
> > Yeah, VACUUM doesn't shrink indexes presently (it's on the TODO
> > list...).
> >
> > > PostgreSQL version 7.0.3 running under Linux.
> >
> > You could try REINDEX to rebuild the indexes, but I'd recommend updating
> > to 7.1.2 (or soon, 7.1.3) first. I don't recall whether REINDEX is
> > available/trustworthy in 7.0.
> >
> > A grottier solution is to DROP and re-CREATE the indexes.
>
> I can do that on a primary key? I didn't realize that. I tried switching
> to PostgreSQL 7.1 when it first came out, but it now handles ansynchronous
> notification differently to the point where my old code doesn't work
> anymore. It mentioned something at the time about not having the feature
> implemented or it being wrong or something. (when 7.1 first campe out, so
> I don't quite remember.) Anyhow, I have to have that feature. Do you
> know whether it is working again in the current 7.1.3? I will take a look
> at it and see if I can get it working though.
>
> Thanks for all the help,
>
> Matthew M. Copeland
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
You may be sure that when a man begins to call himself a "realist," he
is preparing to do something he is secretly ashamed of doing.
-- Sydney Harris
dear all, I am developing a client application with C++ Builder 3 to access a Database designed with PostgreSQL 7.0.4 using a BDE alias through the ODBC driver. I would like to have a form where I can add users to my users table of my database and also create the corresponding entry to the postgres pg_user table. What I have tried through the Database Explorer is the following transaction (I use begin and commit, otherwise the parser complains): BEGIN; CREATE USER user1 WITH PASSWORD 'passwd_user1' IN GROUP users_invui; COMMIT; I check in the pg_user table but no entry is added. I execute without the begin and the commit comands the same query in the backend (psql) and it works perfectly. What am I doing wrong? Many thanks in advance and sorry for my English Miguel
Miguel Gonzalez writes: > I am developing a client application with C++ Builder 3 to access a > Database designed with PostgreSQL 7.0.4 using a BDE alias through the ODBC > driver. Time to upgrade. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter