Thread: Find a broken table

Find a broken table

From
Ariunbold Gerelt-Od
Date:
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.


ever growing pkey files

From
matthew.copeland@honeywell.com
Date:
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




Re: ever growing pkey files

From
Tom Lane
Date:
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

Re: Find a broken table

From
Allan Engelhardt
Date:
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


Re: ever growing pkey files

From
matthew.copeland@honeywell.com
Date:
> 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


Re: ever growing pkey files

From
matthew.copeland@honeywell.com
Date:
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



Creating Users

From
Miguel Gonzalez
Date:
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




Re: Creating Users

From
Peter Eisentraut
Date:
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