Обсуждение: Re: [ADMIN] Strange behaviour
Alexi and group, Another good question to ask wrt alexi's question is it good practice to everyonce in a while to reconstruct an index from scratch. Or would a normal vacuum do the trick? -lorenzo ---Alexei Vladishev <alex@gobbo.caves.lv> wrote: > > Greetings, > > I have an aplication which do pretty much updates/selects on table TABLE. > (about 3-6 updates/selects in a sec.). The TABLE has an unique index, lets > call it INDEX. It seems than everything works OK. But after 4-7 hours I > get the following errors from the application - PGresult is null, Backend > died... After a couple of days of investigation I understood that the > problem is in indexe, because it dies on SELECT * FROM TABLE WHERE > zzz=33; and zzz is the unique key of the table. After dropping index and > recreating one problem disappers. > > Is there a patch to heal postgres ? Can anyone help me ? > Thanks > > I am running Postgresql 6.3.2+Btree patch on Intel Linux 2.0.32 (RedHat > 5.0). > > Yours sincerely, > Alexei Vladishev > > > _________________________________________________________ DO YOU YAHOO!? Get your free @yahoo.com address at http://mail.yahoo.com
Hi all A vacuum once and a while is probably a very good idea. Aside from that, I don't know. On Thu, 10 Sep 1998, Lorenzo Huerta wrote: > Alexi and group, > > Another good question to ask wrt alexi's question is it > good practice to everyonce in a while to reconstruct an > index from scratch. Or would a normal vacuum do the trick? > > -lorenzo > > > ---Alexei Vladishev <alex@gobbo.caves.lv> wrote: > > > > Greetings, > > > > I have an aplication which do pretty much updates/selects on table > TABLE. > > (about 3-6 updates/selects in a sec.). The TABLE has an unique > index, lets > > call it INDEX. It seems than everything works OK. But after 4-7 > hours I > > get the following errors from the application - PGresult is null, > Backend > > died... After a couple of days of investigation I understood that the > > problem is in indexe, because it dies on SELECT * FROM TABLE WHERE > > zzz=33; and zzz is the unique key of the table. After dropping index > and > > recreating one problem disappers. > > > > Is there a patch to heal postgres ? Can anyone help me ? > > Thanks > > > > I am running Postgresql 6.3.2+Btree patch on Intel Linux 2.0.32 > (RedHat > > 5.0). > > > > Yours sincerely, > > Alexei Vladishev > > > > > > > > _________________________________________________________ > DO YOU YAHOO!? > Get your free @yahoo.com address at http://mail.yahoo.com > > Terry Mackintosh <terry@terrym.com> http://www.terrym.com sysadmin/owner Please! No MIME encoded or HTML mail, unless needed. Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3 ------------------------------------------------------------------- Success Is A Choice ... book by Rick Patino, get it, read it!
Greetings, Normal vacuum don't do the trick. I made simple script which once in a hour vacuums my most used table. Today, in the morning, i found that the script crashed with message "Cannot write duplicate key ...". So it is not right solution for the problem. Dropping and recreating indexes is a good idea if your table is not large. For small tables I reccomend "dump table"->"drop table"->"create table+loading data+creating indexes" schema. Why drop table ? Because dropping table inproves performance, it simply becomes smaller. Yours sincerely, Alexei Vladishev On Thu, 10 Sep 1998, Lorenzo Huerta wrote: > Alexi and group, > > Another good question to ask wrt alexi's question is it > good practice to everyonce in a while to reconstruct an > index from scratch. Or would a normal vacuum do the trick? > > -lorenzo > > > ---Alexei Vladishev <alex@gobbo.caves.lv> wrote: > > > > Greetings, > > > > I have an aplication which do pretty much updates/selects on table > TABLE. > > (about 3-6 updates/selects in a sec.). The TABLE has an unique > index, lets > > call it INDEX. It seems than everything works OK. But after 4-7 > hours I > > get the following errors from the application - PGresult is null, > Backend > > died... After a couple of days of investigation I understood that the > > problem is in indexe, because it dies on SELECT * FROM TABLE WHERE > > zzz=33; and zzz is the unique key of the table. After dropping index > and > > recreating one problem disappers. > > > > Is there a patch to heal postgres ? Can anyone help me ? > > Thanks > > > > I am running Postgresql 6.3.2+Btree patch on Intel Linux 2.0.32 > (RedHat > > 5.0). > > > > Yours sincerely, > > Alexei Vladishev > > > > > > > > _________________________________________________________ > DO YOU YAHOO!? > Get your free @yahoo.com address at http://mail.yahoo.com > >